Make connection to database in ASP.NET MVC3

Question

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?

Darin Dimitrov · Accepted Answer · 2011-07-05 18:12:56Z

5

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.

answered Jul 5, 2011 at 18:12

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hector Correa · Accepted Answer · 2011-07-05 18:25:04Z

0

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.

answered Jul 5, 2011 at 18:25

Hector Correa

26.7k8 gold badges61 silver badges76 bronze badges

Comments

macou · Accepted Answer · 2011-07-06 00:39:59Z

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>

Collectives™ on Stack Overflow

Make connection to database in ASP.NET MVC3

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related