Tech Junkie Blog - Real World Tutorials, Happy Coding!: ASP.NET: Programmatically Connect to The Database Using SqlConnection, DataReader and SqlCommand

Sunday, June 19, 2016

ASP.NET: Programmatically Connect to The Database Using SqlConnection, DataReader and SqlCommand

So you have following connection string to the Northwind database in your Web.config and you want to connect to the database in the code behind instead using query builder.

  <connectionStrings>
    <add name="NorthwindConnectionString" connectionString="Data Source=(local);
Initial Catalog=Northwind;Integrated Security=True" providerName="System.Data.
SqlClient"/></connectionStrings>

Here is how you would do it

1. Get the connection from the Web.config file programmatically, you can look at this blog to find out how perform this step by step.  But here is the code to get the connection string from the Web.config file

string connectString = WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].
ConnectionString;



2.  On the top of the file specify that you want to use the Sql.Data.SqlClient namespace by typing

using System.Data.SqlClient; 

3. Now type the following

 using (SqlConnection conn = new SqlConnection(connectString))
  {        
      conn.Open();
      SqlCommand cmd = new SqlCommand("SELECT ProductID,ProductName FROM Products", conn);
      SqlDataReader dataReader = cmd.ExecuteReader();

      try
      {
         while (dataReader.Read())
         {
            Response.Write("Product ID: " + dataReader["ProductID"] + dataReader["ProductName"] + 
"<br>");
                }
            }
            finally
            {
                dataReader.Close();
            }

        }
    }
By using the "using" keyword you assure that the connection is automatically closed after the code is executed inside the using code block. So you don't have remember to close the connection string.
        using (SqlConnection conn = new SqlConnection(connectString))
Then you open the SqlConnection with
        conn.Open();

Then you create a new SqlCommand using the connection you've just opened
SqlCommand cmd = new SqlCommand("SELECT ProductID,ProductName FROM Products", conn);
Then to execute command to retrieve the data object SqlDataReader which will store your result in a read-only forward only stream of database rows.

            try
            {
                while (dataReader.Read())
                {
                    Response.Write("Product ID: " + dataReader["ProductID"] +
 dataReader["ProductName"] + "<br>");
                }
            }
            finally
            {
                dataReader.Close();
            }

The while loop loops through the reader until there is no more rows to read and print out the ProductID, and ProductName of each database row. Make sure you always use the SqlDataReader in a try block and always remember to close it since, it's a data stream.
This is a quick and easy way to query the database.

4 comments:

  1. I like your blog.You have done Excellent work. I appreciate.Here I want to inform all of you if you are looking for to resolve your Norton Antivirus problems,so you are in right place.we always available for your support.So whenever you need any help so just click on this link- norton Antivirus ondersteuningsnummer

    ReplyDelete
  2. Excellent post. I certainly appreciate this website.Keep writing.well here if you want to Overcome the issues of Avast antivirus.Pick the Best Assistance over our site to resolve your queries.Visit us :avast antivirus ondersteuning

    ReplyDelete
  3. I am really enjoying your site.It’s simple, yet effective, thank you for this article.Now I have to share some information about How To Fix “mcafee Antivirus” problem. If you have any problem rearding Mcafee so click on this site:mcafee antivirus nummer belgie

    ReplyDelete
  4. I’m really impressed with your writing skills and also with the layout on your blog it's Very interesting to read.Now Here i would llike to share some information about HP Printer If you are facing any problem relate to your HP Printer's we wil resolve your queries at sam time.For any help please visit on our website:hp printer contact belgie

    ReplyDelete

Search This Blog