using(SqlConnection) statement in c#

Main idea to use ‘using’ vs conventional way to open and close connection to server

Generally there are two types of datatypes which we commonly use

• Basic or primitive datatype.
  Whenever these datatypes are declared stack allocates memory for them and the data persists till the loop ends.
• Reference datatype
  When these datatypes are declared unlike for primitive datatypes memory is allocated in the heap memory where the data persists till the heap memory is full.

So when we create a sqlconnection object which is a reference datatype memory is allocated in the heap and the object persists even if the connection is closed.So if there are multiple instances in your code using the connection object ,each instance occupies memory in the heap which reduces the performance.

So using(SqlConnection con=new SqlConnection) overcomes this issue.

It helps disposing an object automatically once the connection is closed .So no extra memory is used even if you have multiple instances of your sql connection.

The using statement has an inbuilt a try-finally statement that calls the Dispose method.

Here in this post I would like to make you clear on how and why to use ‘using(SqlConnection) rather simply opening and closing the connection while you are trying to deal with SQL Server. Generally when we try to interact with the database ,we simply create a connection object as

SqlConnection con=new sqlConnection();

We then use the connection object to establish a connection to the server which we normally do it in the try block, then followed by a catch to display any exceptions and ultimately winding up in the finally winding up in the finally block in which we cclose the connection made to the server.

Here is the simple conventional example which most of us follow to establish a connection In the below example I am trying to insert the details of a person into the database.

            var cs = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
       SqlConnection cn = new SqlConnection(cs)
        {
            
            var addContact = "INSERT INTO Contacts  VALUES (@FirstName, @Email,@Phone, @LandLine,@Website, @Address)";
            SqlCommand cmd = new SqlCommand(addContact, cn);
            cmd.Parameters.Add("FirstName", SqlDbType.NVarChar).Value = cnt.FirstName;
            cmd.Parameters.Add("Email", SqlDbType.NVarChar).Value = cnt.Email;
            cmd.Parameters.Add("Phone", SqlDbType.NVarChar).Value = cnt.Mobile;
            cmd.Parameters.Add("LandLine", SqlDbType.NVarChar).Value = cnt.LandLine;
            cmd.Parameters.Add("Website", SqlDbType.NVarChar).Value = cnt.Website;
            cmd.Parameters.Add("Address", SqlDbType.NVarChar).Value = cnt.Address;
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                status = true;
            }
            catch (Exception e)
            {
                return status;
            }
            finally

       
 

As you see in the above example we just opened the connection to the server,inserted details then closed the connection. Now look at the same example using the ‘using’ statement

        var cs = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
       using( SqlConnection cn = new SqlConnection(cs))
        {
            // insert the parameters
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                
            }
            catch (Exception e)
            {
       
            }
            
}
// we need not specially close the connection ,con.close()
that is the speciality of using statement,
It automatically closes the instance and disposes the object.
       
 

Therefore using(sqlConnection) disposes the objects and releases the connection soon after the interaction with the database,which helps to reduce the heap memory used and thus increasing the perfomance.