SQLConnection in ADO.NET - Part 2

Suggested Videos
Part 1 - What is ADO.NET

In this video we will learn about
1. Instantiating a SqlConnection object
2. Using the SqlConnection object
3. Why is it important to close a database connection
4. How to properly close a connection

In Part 1 of ADO.NET video series, we have had an introduction to ADO.NET. We also understood about the different .NET data providers. The key to understanding ADO.NET, is to understand about the following objects. 
1. Connection
2. Command
3. DataReader
4. DataAdapter
5. DataSet

From Part 1, it should be clear that Connection, Command, DataReader and DataAdapter objects are providers specific and DataSet is provider independent. So, if we are working with SQL Server, we will be using SQLConnection, SqlCommand, SqlDataReader and SqlDataAdapter objects from System.Data.SqlClient namespace. On the other hand if we are working with Oracle database, then we will be using OracleConnection, OracleCommand, OracleDataReader and OracleDataAdapter objects from System.Data.OracleClient namespace. The same logic applies when working with OleDb and Odbc data sources.

If we understand how to work with one database, then we should be able to easily work with any other database. All we have to do is, change the provider specific string (Sql, Oracle, Oledb, Odbc) on the Connection, Command, DataReader and DataAdapter objects depending on the data source you are working with.

So, in the next few sessions of this video series we will be discussing about each of the ADO.NET objects(Connection, Command, DataReader, DataSet and DataAdapter). In this session we will be discussing about SqlConnection object. The concepts that we discuss here are applicable to all the .NET data providers.



The first thing that we will have to do, when working with databases is to create a connection object. There are 2 ways to create an instance of SqlConnection class as shown below.

Create an instance of SqlConnection class uwing the constructor that takes ConnectionString parameter
SqlConnection connection = new SqlConnection("data source=.; database=SampleDB; integrated security=SSPI");

//First create an instance of SqlConnection class using the parameter-less constructor 
SqlConnection connection = new SqlConnection();
//Then set the ConnectionString property of the connection object
connection.ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

The ConnectionString parameter is a string made up of Key/Value pairs that has the information required to create a connection object. 

To create a connection object with windows authentication
string ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

To create a connection object with SQL Server authentication
string ConnectionString = "data source=.; database=SampleDB; user id=MyUserName; password=MyPassword";

The "data source" is the name or IP Address of the SQL Server that we want to connect to. If you are working with a local instance of sql server, you can just specify DOT(.). If the server is on a network, then use Name or IP address.

Sample ADO.NET code that 
1. Creates a connection
2. The created connection object is then passed to the command object, so that the command object knows on which sql server connection to execute this command.
3. Execute the command, and set the command results, as the data source for the gridview control.
4. Call the DataBind() method
5. Close the connection in the finally block. Connections are limited and are very valuable. Connections must be closed properly, for better performance and scalability.

Note: Connections should be opened as late as possible, and should be closed as early as possible.



protected void Page_Load(object sender, EventArgs e)
{
    //Create the connection object
    SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI");;
    try
    {
        // Pass the connection to the command object, so the command object knows on which
        // connection to execute the command
        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);
        // Open the connection. Otherwise you get a runtime error. An open connection is
        // required to execute the command
        connection.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
    }
    catch (Exception ex)
    {
        // Handle Exceptions, if any
    }
    finally
    {
        // The finally block is guarenteed to execute even if there is an exception.
        //  This ensures connections are always
 properly closed.

        connection.Close();
    }
}

We can also use "using" statement to properly close the connection as shown below. We don't have to explicitly call Close() method, when using is used. The connection will be automatically closed for us.
protected void Page_Load(object sender, EventArgs e)
{
    using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))
    {
        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);
        connection.Open();
        GridView1.DataSource = cmd.ExecuteReader();
        GridView1.DataBind();
    }
}

Common Interview Question: What are the 2 uses of an using statement in C#?
1. To import a namespace. Example: using System;
2. To close connections properly as shown in the example above

7 comments:

  1. most i have seen on internet code database connection are connected under the exception.why?

    ReplyDelete
    Replies
    1. The database connection are handled in try catch block .. bcoz if there is any exception like database name spelled wrong or wrong connection name etc .. during these consequences it throws an exception if it is not handled in try catch block..

      Throwing an exception is bad in any application bcoz it can display the confidential informations and the NON-IT users cannot understand whats happening, so they do not visit ur application again,,

      Delete
  2. Hi Venkat Sir,

    I faced one interview question, instead of try catch block, we can use "Using" keyword to close the connection automatically.


    As you said " We don't have to explicitly call Close() method, when using is used. The connection will be automatically closed for us."

    They asked me, how do you handle if you get any exception?

    Kindly answer me..

    Regards
    Reddy A
    Bangalore

    ReplyDelete
    Replies
    1. Hi Reddy,

      This is a very good question. Thanks for asking.

      Irerespective of whether there is an exception or not the connection gets closed when the using block is exited. So this means the connection is guaranteed to close.

      Depending on the application architecture and type of exception there are several ways to handle the exceptions. If the exceptions are handled and logged at the application level in Global.asax or in a base page, I will let the exception bubble up and let the handler handle and log it.

      However, if there is a situation where you want to handle the exception and provide an error message to the end user so he can correct and resubmit the form, then you can handle exceptions locally still with using block as shown below.

      using (SqlConnection connection = new SqlConnection(connectionString))
      {
         try
         {
            connection.Open();
            // Code that could throw exceptions
         }
         catch (Exception)
         {
            // Handle exception
         }
      }

      Hope this answers your question.
      Venkat

      Delete
  3. MR.... PERFECT is Venkat.....Venkat.......Venkat.

    ReplyDelete
  4. Thanks for this nice and neat article

    ReplyDelete
  5. Hello Venkat Sir,

    Can you please provide some information about "SQL Server Connection Pooling". I recently heard that opening and Closing connection is not a good approach and hence uses Connection pooling.
    Can you please please provide some inputs about this?
    Thanks as always!!!!

    ReplyDelete

It would be great if you can help share these free resources