ADO.NET SqlConnection

Back to: ADO.NET Tutorial For Beginners and Professionals

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 the ADO.NET SqlConnection class?
2. How to instantiate the 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 with hard-coding the connection string in the application?
7. How to store and retrieve the connection string from the configuration file?

What we discussed in the 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 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 whereas the DataSet is provider-independent. That means if you are going to work with the SQL Server database, then you need to use SQL-specific provider objects such as SQLConnection, SqlCommand, SqlDataAdapter, and SqlDataReader objects which belong 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 will be applicable to all the .NET data providers.

What is ADO.NET SqlConnection Class in C#?

The ADO.NET SqlConnection class belongs to System.Data.SqlClient namespace, and 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 if it goes out of scope. Therefore, it is always recommended and always a good programming practice to close the connection object explicitly by calling the 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 resources.

ADO.NET SqlConnection class Signature in C#:

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

SqlConnection Class Constructors:

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

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 initialize 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 initialize 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 the 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 close 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 a Connection Object in C#?

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

Using the constructor which takes the 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.

Using the parameterless constructor of C# SqlConnection class:

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

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

Here, the “data source” is the name or IP Address of the SQL Server that you want to connect to. 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 an SQL Server database. We have created a Student database in our previous article and we will connect to that Student database. Please have a look 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.

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 the 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:

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:

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.

The problem of hard-coding the connection string in application code:

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

1. Let’s 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 rebuild the application as well as it also required a re-deployment which is 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 is not only a maintenance overhead but is also error-prone.
3. In real-time applications, while developing you may point to your Development database. While moving to UAT, you may have a different server for UAT and in a 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 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 the 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 the 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 the 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. By default, this System.Configuration DLL is not included in our application, so we need to add this DLL first. 

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:

Note: Storing connection strings in web.config is similar to the app.config and the same ConfigurationManager class is used to read connection strings from the 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 help you with your need.  I would like to have your feedback. Please post your feedback, question, or comments about this article.