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.