Make connection to database in ASP.NET MVC3

I have a mvc3 application which has a database for its own. but 开发者_如何转开发my site need to get data from another program which uses its own database and also I need to run a store procedure which is located in that database.

I want to know that is the best action is to make sql connection and run that store procedure and make query for those data or there is a better way for handling this issue in mvc3?

There are many ways to perform database access in .NET. If this other program doesn't provide you with a strongly typed API to query the database you could use plain ADO.NET with SqlConnection, SqlCommand (which among other allow you to invoke stored procedures) or an ORM such as Entity Framework.

In ASP.NET MVC you should put your data access code in your models (i.e. not in your views or controllers) but other than that you can use whatever data access technique you are comfortable with.

As Darin has already said there are many ways to perform database access in .NET. Here's my example of using the SqlConnection and SqlCommand. Of course this is assumming your connecting to a SQL Db.

using (SqlConnection con = new SqlConnection(Global.GetConnectionString()))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = con;
                    cmd.CommandText = @"SELECT [ID],[suburb],[state],[postcode],[country],[latitude],[longitude]
                                      FROM [suburbGeocodes]
                                     WHERE ID = @ID";
                    //include the ID in the command to make the Load() generic

                    cmd.Parameters.Add(new SqlParameter("@ID", id));

                    using (SqlDataReader drd = cmd.ExecuteReader(System.Data.CommandBehavior.SingleResult))
                    {
                        if (drd.Read())
                        {
                            this.Load(drd);
                        }
                    }
                }
            }

The connection string is in the Web.config file. I'm just using a global object I have created to form it is all. It can be read from the Web.config file as below

ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }

And the connection string in the web.config file is...

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=datasource;Initial Catalog=databasename;Persist Security Info=True;User ID=user;Password=password" providerName="System.Data.SqlClient" />
  </connectionStrings>