ADO.NET SqlConnection

ADO.NET SqlConnection Class in C# with Examples

In this article, I am going to discuss the ADO.NET SqlConnection Class in C# with Examples. Please read our previous article where we discussed ADO.NET using SQL Server. As part of this article, we are going to discuss the following pointers in detail.

  1. What is ADO.NET SqlConnection class?
  2. How to instantiate SqlConnection object
  3. Using the SqlConnection object
  4. Why is it important to close a database connection
  5. How to properly close a connection
  6. What is the problem of hard-coding the connection string in application?
  7. How to store and retrieve the connection string from configuration file?
What we discussed in Introduction Part of this article?

Let us first recap what we discussed in our introduction to ADO.NET Article. We discussed 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

In our introduction part we discussed that Connection, Command, DataAdapter and DataReader objects are providers specific where the DataSet is provider independent. That mean if you are going to work with SQL Server database, then you need to use SQL specific provider objects such as SQLConnection, SqlCommand, SqlDataAdapter and SqlDataReader objects which are belongs to the System.Data.SqlClient namespace.

Note:

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

Here, in this article I am going to discuss the SqlConnection object in detail. The concepts that we discuss here are applicable to all the .NET data providers.

What is ADO.NET SqlConnection Class in C#?

The ADO.NET SqlConnection class is belongs to System.Data.SqlClient namespace and it is used to establish an open connection to the SQL Server database. The most important point that you need to remember is the connection does not close implicitly even it goes out of scope. Therefore, it is always recommended and always a good programming practice to close the connection object explicitly by calling Close() method of the connection object

Note: The connections should be opened as late as possible, and should be closed as early as possible as the connection is one of the most expensive resource. .

ADO.NET SqlConnection class Signature in C#:

Following is the signature of SqlConnection class. As you can see, it is a sealed class and inherited from DbConnection class and implement the ICloneable interface.

ADO.NET SqlConnection Class in Detail

SqlConnection Class Constructors:

The ADO.NET SqlConnection class has three constructors which are shown in the below image.

ADO.NET SqlConnection Class Constructirs

Let us discuss each of these constructors in detail.

  1. SqlConnection(): It initializes a new instance of the System.Data.SqlClient.SqlConnection class
  2. SqlConnection(String connectionString): This constructor is used to initializes a new instance of the System.Data.SqlClient.SqlConnection class when given a string that contains the connection string.
  3. SqlConnection(String connectionString, SqlCredential credential): It is used to initializes a new instance of the System.Data.SqlClient.SqlConnection class given a connection string, that does not use Integrated Security = true and a System.Data.SqlClient.SqlCredential object that contains the user ID and password.
C# SqlConnection class Methods:

Following are some of the important methods of SqlConnection object.

  1. BeginTransaction(): It is used to start a database transaction and returns an object representing the new transaction.
  2. ChangeDatabase(string database): It is used to change the current database for an open SqlConnection. Here, the parameter database is nothing but the name of the database to use instead of the current database.
  3. ChangePassword(string connectionString, string newPassword): Changes the SQL Server password for the user indicated in the connection string to the supplied new password. Here, the parameter connectionString is the connection string that contains enough information to connect to the server that you want. The connection string must contain the user ID and the current password. The parameter newPassword is the new password to set. This password must comply with any password security policy set on the server, including minimum length, requirements for specific characters, and so on.
  4. Close(): It is used to closes the connection to the database. This is the preferred method of closing any open connection.
  5. CreateCommand(): It Creates and returns a System.Data.SqlClient.SqlCommand object associated with the System.Data.SqlClient.SqlConnection.
  6. GetSchema(): It returns schema information for the data source of this System.Data.SqlClient.SqlConnection.
  7. Open(): This method is used to open a database connection with the property settings specified by the System.Data.SqlClient.SqlConnection.ConnectionString.
How to create Connection Object in C#?

You can create an instance of the SqlConnection class in three as there are three constructor in SqlConnection class. Here, I am going to show you two most preferred way of creating instance of SqlConnection class. They are as follows:

Using the constructor which takes connection string as the parameter.

The following image shows how to create an instance of SqlConnection class using the constructor which takes ConnectionString as the only parameter.

How to create Connection Object?

Using the parameter less constructor of C# SqlConnection class:

The following image shows how to create an instance of SqlConnection class using the parameter less constructor. It is a two step process. First you need to create an instance of SqlConnection class using the parameter-less constructor and then using the ConnectionString property of the connection object you need to specify the connection string.

How to instantiate SqlConnection object

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

Using the SqlConnection object

Here, the “data source” is the name or IP Address of the SQL Server that you want to connect. If you are working with a local instance of SQL Server, then you can simply put a DOT(.). If the server is on a network, then you need to use either the Name or IP address of the server.

SqlConnection Example in C#

Let us see an example to understand how to connect to SQL Server database. We have created a Student database in our previous article and we will connect to that Student database. Please have a kook at the following C# code which will create the connection object and then establish an open connection when the Open method is called on the connection object.

SqlConnection Example

Note: Here, we are using the using block to close the connection automatically. If you are using the using block then you don’t require to call close() method explicitly to close the connection. It is always recommended to close the database connection using the using block in C#.

The complete code is given below.
using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().Connecting();
            Console.ReadKey();
        }

        public void Connecting()
        {
            string ConnectionString = "data source=.; database=student; integrated security=SSPI"; 
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                con.Open();
                Console.WriteLine("Connection Established Successfully");
            }
        }
    }
}

Output:

Why is it important to close a database connection

What, if we don’t use using block?

If you don’t use the using block to create the connection object, then you have to close the connection explicitly by calling the Close method on the connection object. In the following example, we are using try-block instead of using block and calling the Close method in finally block to close the database connection.

using System;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            new Program().Connecting();
            Console.ReadKey();
        }

        public void Connecting()
        {
            SqlConnection con = null;
            try
            {
                // Creating Connection  
                string ConnectionString = "data source=.; database=student; integrated security=SSPI";
                con = new SqlConnection(ConnectionString);
                con.Open();
                Console.WriteLine("Connection Established Successfully");
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            finally
            {   // Closing the connection  
                con.Close();
            }
        }
    }
}

Output:

How to properly close a connection

Here, we hard-coded the connection strings in the application code. Let us first understand what is the problem when we hard-coded the connection string within the application code and then we will see how to overcome this problem.

Problem of hard-coding the connection string in application code:

There are 3 problem when we hard coded the connection strings in application code. They are as follows:

  1. Let say, you move your database to a different server, then you need to change the database details in the application code itself. Once you change the application code, then you need to re-build the application as well as it also required a re-deployment which is a time consuming.
  2. Again if you hard-coded the connection string in multiple places, then you need to change the connection in all the places which not only a maintenance overhead but also error prone.
  3. In real time application, while developing you may point to your Development database, while moving to  UAT, you may have a different server for UAT and in production environment you need to point to the production database.
How to solve the above problems?

We can solve the above problems, by storing the connection string in the application configuration file. The configuration file in a windows or console application is app.config whereas for ASP.NET MVC or ASP.NET Web API application, the application configuration file is web.config.

How to store the connection string in configuration file?

As we are working with a console application, the configuration file is app.config. So, we need to store the connection string in the app.config file as shown below. Give a meaningful name to your connection string. As we are going to communicate with SQL Server database, so, we need to provide the provider name as System.Data.SqlClient.

<connectionStrings>
    <add name="ConnectionString"
         connectionString="data source=.; database=student; integrated security=SSPI"
         providerName="System.Data.SqlClient" />
</connectionStrings>

Note: You need to put the above connection string inside the configuration section of the configuration file.

How to read the connection string from app.config file?

In order to read the connection string from the configuration file, you need to use the ConnectionStrings property of the ConfigurationManager class. The ConfigurationManager class is present in System.Configuration namespace.

Example to read the connection string from the configuration file:

Please modify the Program.cs class file as shown below read the connection string from the configuration file.

using System;
using System.Configuration;
using System.Data.SqlClient;
namespace AdoNetConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string ConString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                using (SqlConnection connection = new SqlConnection(ConString))
                {
                    connection.Open();
                    Console.WriteLine("Connection Established Successfully");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("OOPs, something went wrong.\n" + e);
            }
            Console.ReadKey();
        }
    }
}

Output:

Problem of hard-coding the connection string in application code:

Note: Storing connection strings in web.config is similar to app.config and in the same ConfigurationManager class is used to read connection string from web.config file.

In the next article, I am going to discuss ADO.NET SqlCommand Class in detail. Here, in this article, I try to explain the ADO.NET SqlConnection class in C# with examples. I hope this C# SqlConnection article will helps you with your need.  I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *