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 do you store and retrieve the connection string from the configuration file?

What do we discuss in the Introduction Part of this article?

Let us first recap what we discussed in our introduction to the 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 provider-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, you can easily work with any other one. 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 we discuss here will apply 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 is used to establish an open connection to the SQL Server database. The most important point you must remember is that 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 the SqlConnection class. As you can see, it is a sealed class inherited from the DbConnection class and implements the ICloneable interface.

SqlConnection Class Constructors:

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

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 the SqlConnection class. Here, I am going to show you the two most preferred ways of creating an instance of the 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 the 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 the SqlConnection class using the parameterless constructor. It is a two-step process. First, you need to create an instance of the 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 of Key/Value pairs with 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 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 are not required 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 use try-block instead of block and call the Close method in the 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 the problem is 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, you need to rebuild the application, as it also requires a re-deployment, which is time-consuming.
2. Again, if you hard-coded the connection string in multiple places, you need to change the connection in all the places, which is not only a maintenance overhead but also error-prone.
3. In real-time applications, you may point to your Development database while developing. 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 do we 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 do you 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 will communicate with the SQL Server database, 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, reads 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.

Summary of ADO.NET SqlConnection Class:

The SqlConnection class in ADO.NET is fundamental for establishing a connection to a SQL Server database. It provides methods and properties to manage the connection, execute commands, and handle transactions. Here’s an overview of the SqlConnection class and its usage:

Import the Namespace:

To use the SqlConnection class, you need to include the System.Data.SqlClient namespace in your code:
using System.Data.SqlClient;

Create a Connection String:

Before creating an instance of SqlConnection, you need a connection string specifying the details of the SQL Server instance you want to connect to. The connection string includes information such as the server name, database name, authentication method, and credentials.
string connectionString = “Server=MyServerAddress;Database=MyDataBase;User Id=MyUsername;Password=MyPassword;”;

Instantiate and Open Connection:

Create an instance of SqlConnection using the connection string and then open the connection using the Open() method:
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

Execute Commands:

Once the connection is open, you can create and execute SQL commands using the SqlCommand class. For example, executing a SELECT query:

string sqlQuery = "SELECT FirstName, LastName FROM Employees";
SqlCommand command = new SqlCommand(sqlQuery, connection);

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
      Console.WriteLine(reader["FirstName"] + " " + reader["LastName"]);
}
reader.Close();

Close the Connection:

After you’re done with the connection, close it to release resources:
connection.Close();

Using Statement (Recommended):

To ensure that the connection is properly closed, it’s a good practice to use the using statement, which automatically disposes of the resources even if an exception occurs:

using (SqlConnection connection = new SqlConnection(connectionString))
{
      connection.Open();
      // Execute commands and perform operations
} // Connection is automatically closed and disposed here

Error Handling:

Wrap your code with appropriate error handling to catch exceptions that might occur during database interactions. Always close the connection in a finally block to ensure it’s closed regardless of exceptions.

The SqlConnection class supports various events, connection pooling, and timeouts. Handling connections carefully to avoid leaks and ensure efficient resource usage is important. Additionally, remember that hardcoding sensitive information, like credentials in the connection string, is not secure. Consider using configuration files or other secure methods for storing connection strings.

In the next article, I am going to discuss ADO.NET SqlCommand Class in detail. 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 needs.  I would like to have your feedback. Please post your feedback, questions, or comments about this article.