Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core SqlConnection Class with Examples
In this article, I will discuss the ADO.NET Core SqlConnection Class in Detail with Examples. Please read our previous article discussing ADO.NET Core using SQL Server database.
What is the ADO.NET Core SqlConnection Object?
The SqlConnection class (typically from the Microsoft.Data.SqlClient namespace) in ADO.NET Core is used to establish and manage a session with a SQL Server database. It is the starting point for all ADO.NET operations such as querying, inserting, updating, and deleting data. This class encapsulates details like the connection string and the connection state and provides methods like Open(), Close(), and Dispose() to manage the database connection lifecycle. The SqlConnection class encapsulates:
- Connection String Details: Stores information like server address, database name, credentials, etc.
- Connection State: Represents whether the connection is open, closed, connecting, etc.
- Lifecycle Management: This provides methods for opening and closing the connection, as well as methods for disposing of resources when you are finished.
How Do We Instantiate a SqlConnection Object in ADO.NET Core?
The SqlConnection class offers several constructors to initialize the connection. You can either instantiate it using the default constructor and assign the connection string or directly pass it to the constructor. In addition, you can use SqlCredential to handle authentication data more securely, such as user IDs and passwords.
So, depending on your scenario, you can use the default constructor, the constructor with a connection string, or the constructor that accepts a SqlCredential for enhanced security. Now, if you go to the definition of the SqlConnection class, then you will see the following three constructors, using which we can instantiate a SqlConnection object:
Create the SqlConnection String:
A connection string specifies the SQL Server instance, the database, the authentication method, and additional parameters. It’s a good practice to store this string in a configuration file rather than hard-coding it in your source code. When using Windows Authentication, you can omit the username and password and set Trusted_Connection=True.
String connectionString = “Server=myServerAddress; Database=myDataBase; User Id=myUsername; Password=myPassword; TrustServerCertificate=True;”;
Default Constructor
Use the default constructor when you want to instantiate the connection first and then assign a connection string later. This pattern can be helpful if the connection string is determined dynamically at runtime. For a better understanding, please modify the Program class as follows. Make sure the database connection string is correct and the database exists in the SQL server:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Define a connection string (using Windows Authentication here)
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Instantiate the SqlConnection using the default constructor.
// The connection string is assigned later.
using (SqlConnection connection = new SqlConnection())
{
// Assign the connection string before opening the connection.
connection.ConnectionString = connectionString;
// Open the connection to the SQL Server database.
connection.Open();
// Connection established; you can now execute queries or commands.
Console.WriteLine("Connection Established Successfully");
} // The using block ensures the connection is closed and disposed automatically.
}
catch (Exception ex)
{
// Catch any exception that occurs during connection or operations.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
Constructor with Connection String
This overloaded constructor allows us to pass the connection string directly when instantiating the object, eliminating the need to assign it separately. This approach is useful when the connection string is already known, and it is the most common approach. For a better understanding, please modify the Program class as follows.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Define the connection string (using Windows Authentication here)
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Instantiate the SqlConnection by directly providing the connection string.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Connection is now open; perform database operations.
Console.WriteLine("Connection Established Successfully");
} // The connection is automatically closed when exiting the using block.
}
catch (Exception ex)
{
// Handle exceptions if the connection fails.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
SqlConnection with Connection String and Sql Credential
Using the SqlCredential object is a more secure way to pass sensitive authentication information than placing it directly in the connection string. The password is stored in a SecureString, which encrypts the data in memory. This approach helps ensure sensitive information is not exposed in plain text.
The SqlCredential class requires a user ID and a password, where the password is a SecureString. For a better understanding, please modify the Program class as follows. The following code is self-explained, so please read the comment lines for a better understanding.
using System.Security;
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Define the connection string without user credentials.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;TrustServerCertificate=True;";
// Create a SecureString for the password to enhance security.
SecureString securePassword = new SecureString();
foreach (char c in "Abcd@1234")
{
securePassword.AppendChar(c); // Append each character securely.
}
// Prevent further modification of the password.
securePassword.MakeReadOnly();
// Create a SqlCredential object with the username and the SecureString password.
SqlCredential credential = new SqlCredential("Testuser", securePassword);
// Instantiate the SqlConnection using both the connection string and SqlCredential.
using (SqlConnection connection = new SqlConnection(connectionString, credential))
{
// Open the connection asynchronously if needed (or use Open() for synchronous operations).
connection.Open();
// Connection established; perform your database operations.
Console.WriteLine("Connection Established Successfully");
} // The connection is disposed automatically when leaving the using block.
}
catch (Exception ex)
{
// Handle any exceptions that might occur during the process.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
Note: The SecureString class stores text that should be kept confidential. The value of a SecureString object is automatically encrypted, making it more secure than a regular string.
When to Use Which Overloaded Constructor?
Default Constructor
- Use Case: When the connection string isn’t immediately available or you want to construct it dynamically at runtime.
- Typical Scenario: You might load connection details from multiple sources or user inputs and set them programmatically.
Constructor with Connection String
- Use Case: This is ideal when you already have the connection string (from configuration files or environment variables). It makes your code cleaner and less error-prone since you don’t need to take an extra step to set the connection string.
- Typical Scenario: Quickly create and open a connection in minimal code.
Constructor with SqlConnectionString and SqlCredential
- Use Case: Ideal when you need to separate the sensitive credentials (using a SecureString) from the connection string. It enhances security by not embedding passwords directly in the string.
- Typical Scenario: Required when organizational or security policies prohibit storing or passing plain-text credentials in the connection string.
How Do We Properly Close ADO.NET Core Connection Object?
It is essential to close the connection once your operations are complete. Failure to do so can exhaust database resources and prevent other users or applications from connecting. The following are the two mechanisms to close the ADO.NET Core Connection Object properly.
Closing ADO.NET Core Connection Object Using the “using” Statement
The best practice is to use the using statement, which ensures that the Dispose method on the connection object is called automatically when the block completes, even if an exception occurs. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Define connection string.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// The using block ensures that the connection is automatically closed and disposed.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Execute your database operations here.
Console.WriteLine("Connection Established Successfully");
} // Connection is automatically closed here.
}
catch (Exception ex)
{
// Handle any exceptions.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
Explicitly Close the Connection
Alternatively, you can manually close the connection by calling the Close() or Dispose() method, typically within a finally block to ensure execution even if an exception is thrown. For a better understanding, please modify the Program class as follows:
using System.Data;
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
// Define the connection string.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Create a new SqlConnection object.
SqlConnection connection = new SqlConnection(connectionString);
try
{
// Open the connection.
connection.Open();
Console.WriteLine("Connection Established Successfully");
// Perform your database operations here.
}
catch (Exception ex)
{
// Log or handle the exception as needed.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
finally
{
// Check if the connection is open and close it.
if (connection.State == ConnectionState.Open)
{
connection.Close();
// Alternatively, you could call connection.Dispose();
Console.WriteLine("Connection is Closed");
}
}
}
}
}
Why is it important to close the ADO.NET Core Database Connection?
Leaving connections open can lead to resource exhaustion, as each open connection consumes system resources. Additionally, unclosed connections may prevent others from accessing the database and could cause performance degradation. By closing connections when they are no longer needed, you ensure efficient use of resources and maintain the stability of the application. So, closing your connection is essential because:
- Resource Management: Database connections are expensive resources. Keeping them open consumes both client and server memory and can lead to resource exhaustion.
- Connection Pooling Efficiency: ADO.NET Core uses connection pooling by default. When you close a connection, it is returned to the pool for reuse, minimizing the overhead of creating new connections.
- Performance Optimization: Open connections can lead to resource leaks and eventually degrade the performance of both the application and the database server, potentially preventing new connections from being established.
How Do We Enable Connection Pooling in ADO.NET Core?
Connection pooling is enabled by default in ADO.NET Core when using SqlConnection. It reuses active connections instead of creating new ones each time, which improves performance. Connection pooling is configured through the connection string used in SqlConnection. The connection string includes several parameters related to pooling, such as Pooling, Max Pool Size, and Min Pool Size. For a better understanding, please check the following connection string:
string connectionString = “Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV; Database=StudentDB; Trusted_Connection=True; TrustServerCertificate=True; Pooling=true; Min Pool Size=5; Max Pool Size=50;”;
Here,
- Pooling: Setting Pooling=true enables connection pooling. It’s true by default for SQL Server connections.
- Min Pool Size: It sets the minimum number of connections in the pool. The default is 0.
- Max Pool Size: It sets the maximum number of connections allowed. The default is 100.
By including these parameters in the connection string, we tell ADO.NET how to manage the pool of connections. The connection pooling mechanism helps improve performance by repeatedly reducing the overhead of repeatedly opening and closing connections. If you don’t include these explicitly, .NET will still pool connections for you with default settings. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Connection string with pooling parameters.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;Pooling=true;Min Pool Size=5;Max Pool Size=50;";
// Using block ensures the connection is returned to the pool.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
Console.WriteLine("Connection Established Successfully");
// Execute database operations as needed.
} // Connection is automatically closed and returned to the pool.
}
catch (SqlException ex)
{
// Handle SQL-specific exceptions.
Console.WriteLine($"Sql Exception Occurred: {ex.Message}");
}
catch (Exception ex)
{
// Handle any other exceptions.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
What is the Problem with Hard-Coding the Connection String?
Hard-coding a connection string in your source code can lead to multiple issues:
- Security Risks: The source code exposes sensitive information (like usernames, passwords, and server details). If the source code is leaked, shared, or compromised, this poses a major risk.
- Lack of Flexibility: Changes in the database configuration require modifying the source code and redeploying the application.
- Configuration Management Issues: Different environments (development, testing, production) typically require different connection strings. Hard-coding these values complicates deployment and increases the risk of misconfiguration.
How do We Store and Retrieve the Connection String from the Configuration file?
To avoid hard-coding, store your connection string in a configuration file (typically appsettings.json in .NET Core). This approach improves security and makes it easier to manage different environments. Storing the connection string in a configuration file like appsettings.json is a common approach in .NET Core. While ASP.NET Core projects come with appsettings.json by default, a .NET Core console application requires a few additional steps.
Let us proceed and see how we can create and use the appsettings.json file in the .NET Core Console Application. To use the appsettings.json file in the Console Application, we need to install the Microsoft.Extensions.Configuration.Json package from NuGet either by using NuGet Package Manager UI or Package Manager Console. So, execute the following command in the Visual Studio Package Manager Console:
Install-Package Microsoft.Extensions.Configuration.Json
Once the Package is installed, you can verify the package inside the Dependencies => Packages folder of your project, as shown in the image below:
Adding appsettings.json File:
Once you install the package, the next step is to add a JSON file named appsettings.json to your project. While the name does not always need to be app settings, this is a naming convention we generally follow in .NET Core Applications.
So, right-click on your project and select Add => New Item from the context menu to open the following Add New Item window. Here, search for JSON and then select JavaScript JSON Configuration File. Provide the file name as appsettings.json and click on the add button, as shown in the image below. This will add appsettings.json to the root directory of your project.
Once you add the appsettings.json file, please open it and copy and paste the following code. Here, we are adding the database connection string.
{
"ConnectionStrings": {
"SQLServerConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV; Database=StudentDB; Trusted_Connection=True; TrustServerCertificate=True; Pooling=true; Min Pool Size=5; Max Pool Size=50;"
}
}
How Do We Fetch the Connection String from the appsettings.json file?
In Program.cs class, use the Microsoft.Extensions.Configuration package to read the configuration. For a better understanding, please modify the Program class as follows. The following code is self-explained, so please read the comment lines for a better understanding.
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;
namespace ADODOTNETCoreDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
// Set up a configuration builder to load the appsettings.json file.
var configBuilder = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
// Get the Section to Read from the Configuration File
var configSection = configBuilder.GetSection("ConnectionStrings");
// Retrieve the connection string from the configuration file based on the Config key.
var connectionString = configSection["SQLServerConnection"] ?? null;
if (!string.IsNullOrWhiteSpace(connectionString))
{
// Use the connection string to instantiate the SqlConnection.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
Console.WriteLine("Connection Established Successfully");
// Perform your database operations.
} // The connection is disposed automatically.
}
else
{
Console.WriteLine("Connection String is Missing");
}
}
catch (Exception ex)
{
// Handle exceptions that occur during configuration or database operations.
Console.WriteLine($"Something Went Wrong: {ex.Message}");
}
}
}
}
With the above changes in place, now run the application, and you should get the following Runtime Exception.
Something Went Wrong: The configuration file ‘appsettings.json’ was not found and is not optional. The expected physical path was ‘D:\Projects\ADODOTNETCoreDemo\ADODOTNETCoreDemo\bin\Debug\net8.0\appsettings.json’.
Why are we getting the above Exception?
The above exception clearly says that the appsettings.json file was not found inside the project bin=>debug=>.net8.0 folder. That means we need to ensure that once we build the project, the appsettings.json should be stored inside the above location. To do so in Visual Studio:
- Find the appsettings.json file in the Solution Explorer.
- Right-click on the file and select Properties.
- In the Properties window, find the “Copy to Output Directory” setting.
- Change this setting to “Copy if newer” or “Copy always”.
This ensures the appsettings.json file is copied to the Debug or Release folder every time the project is built as shown in the below image:
Using a .csproj File:
If you are not using Visual Studio or prefer to edit the project file directly, you can open the .csproj file for your project in a text editor. Add the following ItemGroup to your .csproj file, ensuring it includes appsettings.json:
<ItemGroup>
<None Update="appsettings.json">
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
</None>
</ItemGroup>
With the above changes in place, build the project and run the application. You should get the expected output.
What Do You Mean by ADO.NET Core Asynchronous SqlConnection?
ADO.NET Core supports asynchronous database operations to improve scalability and responsiveness by freeing up threads while waiting for I/O-bound operations (like opening a connection or executing a command) to complete. This is particularly helpful in applications that need to handle many concurrent database operations or remain responsive (e.g., UI applications or high-performance services).
Asynchronous programming helps keep your application responsive, especially when performing long-running I/O operations. In ADO.NET Core, methods like OpenAsync(), ExecuteReaderAsync(), and others are available for such purposes. The following is an example of using an asynchronous SqlConnection and executing a query asynchronously in ADO.NET Core. Instead of calling Open(), you can call OpenAsync() to open the connection without blocking the current thread.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
// Main method is declared async to support asynchronous calls.
static async Task Main(string[] args)
{
// Define the connection string.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
try
{
// The using block ensures the connection is closed properly.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection asynchronously.
await connection.OpenAsync();
Console.WriteLine("Connection opened successfully.");
// Execute asynchronous database operations here.
} // Connection is automatically closed when leaving the using block.
}
catch (Exception ex)
{
// Handle any exceptions that occur during asynchronous operations.
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
}
}
Note: Using asynchronous methods like OpenAsync() helps avoid blocking the main thread, especially when you want to maintain application responsiveness or process multiple operations concurrently.
FAQs
What is the SqlConnection class in ADO.NET Core?
The SQLConnection class (from the Microsoft.Data.SqlClient namespace) establishes and manages a connection to a SQL Server database. It handles the connection string and state and provides methods for opening and closing the connection, which is essential for executing queries, retrieving data, and managing transactions.
How do I create a SqlConnection object in ADO.NET Core?
You can instantiate a SqlConnection object either by using the default constructor and then setting the ConnectionString property or by using a constructor that directly accepts a connection string. Both approaches are valid, but the choice depends on whether you need to set additional properties or rely on a pre-defined connection string.
What is SecureString, and why is it used in SqlCredential?
SecureString is a class that encrypts text in memory to prevent it from being exposed in plain text (e.g., in memory dumps). It’s used to securely pass passwords into SqlCredential.
When should I use a SqlCredential object?
An SqlCredential object is recommended when you need to securely pass credentials (like a username and password) to the SQL Server. It separates sensitive information from the connection string and uses a SecureString for the password, enhancing security. This approach is particularly useful in environments where security is a primary concern.
How can I properly close a database connection in ADO.NET Core?
The preferred approach is to use the using statement, which ensures the connection is closed and disposed of as soon as the block is exited, even in the event of an exception. Alternatively, you can manually call Close() or Dispose() on the SqlConnection object in a finally block, ensuring the connection is closed no matter what happens in the code.
Why is it essential to close a database connection after use?
Leaving connections open can lead to resource exhaustion, as each connection consumes system resources. Open connections that aren’t properly closed might prevent other operations from succeeding and can cause performance issues. Properly closing connections ensures efficient resource usage and prevents potential bottlenecks or application instability.
What is connection pooling, and how is it managed in ADO.NET Core?
Connection pooling is a technique that reuses existing database connections instead of creating new ones for each request. In ADO.NET Core, connection pooling is enabled by default for SqlConnection objects. It can be controlled using connection string parameters like Pooling, Min Pool Size, and Max Pool Size. This significantly improves performance by reducing the overhead of opening and closing connections repeatedly.
What is the default max and min size of the connection pool?
- Min Pool Size: 0 (default)
- Max Pool Size: 100 (default)
These can be overridden via the connection string.
Why is it a bad practice to hard-code connection strings in source code?
Hard-coding connection strings in your code can lead to security vulnerabilities (exposing sensitive credentials), decreased flexibility (it is difficult to change configurations without redeploying), and configuration management issues (not practical for managing different environments like development, testing, and production). Instead, store connection strings in a configuration file such as appsettings.json and read them at runtime.
How can I store a connection string securely in a console application?
Use the appsettings.json file along with the Microsoft.Extensions.Configuration package. Add the connection string under a ConnectionStrings section in appsettings.json and read it in code using a configuration builder. This keeps your connection strings out of the source code, makes them easier to update, and enhances security.
How do I enable asynchronous operations with a SqlConnection?
To enable asynchronous database operations, use methods like OpenAsync(), ExecuteReaderAsync(), ExecuteNonQueryAsync(), and others. These methods allow the calling thread to continue execution without blocking while waiting for the database operation to complete. Asynchronous operations are particularly beneficial for applications that need to maintain responsiveness or handle many simultaneous database requests.
What are the best practices for managing SQL connections in ADO.NET Core?
- Always use using statements to ensure connections are closed and disposed of properly.
- Use connection pooling by enabling pooling (default behavior) and adjusting the pool size as needed.
- Avoid hard-coding connection strings; store them in configuration files.
- Use SqlCredential and SecureString for sensitive authentication details.
- Opt for asynchronous methods when performing long-running operations or when responsiveness is critical.
In the next article, I will discuss the ADO.NET Core SqlCommand Class in detail. In this article, I explain the ADO.NET Core SqlConnection Class with examples. I would like your feedback. Please post your feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.