Tech Junkie Blog: 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.

2 comments:


  1. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.

    SELENIUM TRAINING IN BANGALORE

    ReplyDelete
  2. Confronting Oracle Connection Issue with Gridview? Contact to Remote DBA Service and fathom it rapidly
    In the majority of the situation when clients attempting to associate an Oracle database in aps.net and handle the association with SQLdatasource all of a sudden they get some mistake message like this "The framework can't discover the record indicated". In the event that any of the client who confronting that sort of issue at that point let me reveal to you first you need to arrange your SQLdatasource then you can advantageously utilize it. Be that as it may, if as yet confronting a similar situation at that point contact to Cognegic's Database Administration for Oracle or Online Oracle DB Support. Here we rapidly recognize all the specialized issues and resolve them as quickly as time permits.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete