string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword; TrustServerCertificate=True;";
Creating ADO.NET Core SqlConnection Instance Default Constructor
The default constructor creates an instance of the SqlConnection class without initially specifying a connection string. After using this constructor, you must assign a connection string to the ConnectionString property before opening the connection. 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)
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
//Using Defailt SqlConnection Constructor to Create an instance of the SqlConnection Class
using (SqlConnection connection = new SqlConnection())
//Before Opening the Connection Set the connection string
connection.ConnectionString = connectionString;
//Open the Connection
connection.Open();
//Use the Connection
Console.WriteLine("Connection Established Successfully");
//Close the Connection is Optional here as we are creating the instance within the using block
// connection.Close();
//Automatically the Connection Close here
catch (Exception ex)
Console.WriteLine($"Something Went Wrong: {ex.Message}");
Creating SqlConnection Instance Using the Constructor with Connection String
The Constructor, which takes the ConnectionString as a parameter, allows you to pass the connection string directly, which initializes the ConnectionString property of the SqlConnection object. 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)
//I am using Windows Authentication and hence no need to pass the User Id and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
//Using SqlConnection Constructor which takes connectionString as a parameter
using (SqlConnection connection = new SqlConnection(connectionString))
//Open the Connection
connection.Open();
//Use the Connection
Console.WriteLine("Connection Established Successfully");
} //Automatically using block close the connection even if exception has been occurred
catch (Exception ex)
Console.WriteLine($"Something Went Wrong: {ex.Message}");
Creating SqlConnection Instance Using Constructor with ConnectionString and SqlCredential
Using the SqlConnection constructor with a SqlCredential object in ADO.NET Core (via the Microsoft.Data.SqlClient namespace) is a secure way to specify credentials for SQL Server authentication without including sensitive information directly in the connection string. This approach enhances security by separating the password from the connection string and using a SecureString for the password.
The SqlCredential class requires a user ID and a password, the password of which is a SecureString. Below is how you can create a SecureString and use it with SqlCredential.
using Microsoft.Data.SqlClient;
using System.Security;
namespace ADODOTNETCoreDemo
internal class Program
static void Main(string[] args)
//I am using SQL Server Authentication
// Connection string without the sensitive user credentials like UserId and Password
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;TrustServerCertificate=True;";
// Create a SecureString for the password
SecureString securePassword = new SecureString();
foreach (char c in "Abcd@1234")
securePassword.AppendChar(c);
securePassword.MakeReadOnly();
// Create a SqlCredential object using the UserId and the SecureString
var credential = new SqlCredential("Testuser", securePassword);
// Once you have your SqlCredential object, you can use it to initialize a SqlConnection
// Initialize SqlConnection with connectionString and SqlCredential
using (SqlConnection connection = new SqlConnection(connectionString, credential))
//Open the Connection
connection.Open();
// Perform your database operations here
Console.WriteLine("Connection Established Successfully");
catch (Exception ex)
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. However, it’s important to note that SecureString has some limitations. The SqlCredential is only supported for SQL Server Authentication. It cannot be used with Windows Authentication.
How Do We Properly Close ADO.NET Core Connection Object?
The following are the different mechanisms to close the ADO.NET Core Connection Object properly. Open a connection when necessary and close it as soon as you finish your database operations. Holding connections open longer than necessary can lead to performance issues.
Closing ADO.NET Core Connection Object Using the “using” Statement
The using statement in C# is the most recommended way to work with ADO.NET Core Connection Objects. It ensures the connection is automatically closed and disposed of, even if an exception occurs. This approach is based on the IDisposable interface, which SqlConnection and other connection objects implement. 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)
//Connection String
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;User Id=Testuser;Password=Abcd@1234;TrustServerCertificate=True;";
//Creating an Instance of SqlConnection using the using statement
using (SqlConnection connection = new SqlConnection(connectionString))
//Open the Connection
connection.Open();
//Use the Connection
Console.WriteLine("Connection Established Successfully");
} //Automatically close the connection even if an exception is raised
catch (Exception ex)
Console.WriteLine($"Something Went Wrong: {ex.Message}");
Explicitly Close the SqlConnection
If, for some reason, you cannot use the “using” statement, then you must explicitly close the connection by calling the Close method. It is also a good practice to dispose of the connection object by calling the Dispose method or using another using statement around the connection object. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
using System.Data;
namespace ADODOTNETCoreDemo
internal class Program
static void Main(string[] args)
//Connection String
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;User Id=Testuser;Password=Abcd@1234;TrustServerCertificate=True;";
//Creating an Instance of SqlConnection Object
SqlConnection connection = new SqlConnection(connectionString);
//Open the Connection
connection.Open();
Console.WriteLine("Connection Established Successfully");
// Perform database operations
catch (Exception ex)
Console.WriteLine($"Something Went Wrong: {ex.Message}");
finally
// Ensure the connection is closed even if an exception occurs
if (connection.State == ConnectionState.Open)
connection.Close();
Console.WriteLine("Connection is Closed");
connection.Dispose();
Why is it Important to Close the ADO.NET Core Database Connection?
Closing a database connection in ADO.NET Core is essential for several reasons, primarily for resource management, performance, and security. Here’s why closing a database connection is essential:
Resource Management: Database connections are expensive resources on a database server. Each connection consumes server resources. By closing a connection when it’s no longer needed, you free up these resources for other users or processes.
Performance Optimization: Leaving connections open unnecessarily can lead to resource leaks and performance degradation of the .NET application and the database server. It can cause slowdowns due to excessive memory use, increased CPU load, or even lead to a situation where new connections cannot be established because the pool of available connections has been exhausted.
Connection Pooling Efficiency: ADO.NET Core uses connection pooling to minimize the overhead of opening and closing connections to the database. When you close a connection in your application, it’s not actually closed but returned to the connection pool, making it available for subsequent requests. This significantly improves application performance. However, for connection pooling to work effectively, the application must explicitly close connections.
How Do We Enable Connection Pooling in ADO.NET Core?
Connection pooling reuses existing database connections from a pool, reducing the overhead associated with opening and closing connections, especially in high-load scenarios. By default, ADO.NET Core manages connection pooling for SQL Server (through the SqlConnection object) and other databases efficiently without requiring explicit configuration in most cases. The following is a step-by-step guide to enable and configure connection pooling in ADO.NET Core:
For SQL Server, connection pooling is managed by the SqlConnection object. Ensure your application uses SqlConnection to interact with SQL Server. 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;User Id=Testuser;Password=Abcd@1234;TrustServerCertificate=True;Pooling=true;Min Pool Size=0;Max Pool Size=100;";
Here,
Pooling: Setting Pooling=true enables connection pooling. It’s true by default for SQL Server connections.
Min Pool Size: The minimum number of connections in the pool. The default is 0.
Max Pool Size: The maximum number of connections in the pool. The default is 100.
For connection pooling to work effectively, ensure your application opens connections as late as possible and closes them as soon as possible. Use the using statement to guarantee connections are closed and returned to the pool. 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)
//Connection String
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;User Id=Testuser;Password=Abcd@1234;TrustServerCertificate=True;Pooling=true;Min Pool Size=0;Max Pool Size=100;";
//Creating an Instance of SqlConnection using the using statement
using (SqlConnection connection = new SqlConnection(connectionString))
//Open the Connection
connection.Open();
Console.WriteLine("Connection Established Successfully");
//Perform the Database Operations
catch (SqlException ex)
// Handle SQL Exception
Console.WriteLine($"Sql Exception Occurred: {ex.Message}");
catch (Exception ex)
Console.WriteLine($"Something Went Wrong: {ex.Message}");
What is the Problem with Hard-Coding the Connection String?
Hard-coding Connection String directly within an application’s source code gives several significant problems. The following are the primary issues:
Security Risks: Hard-coding connection strings, especially those containing sensitive information like usernames, passwords, and server addresses, can lead to security vulnerabilities. If the source code is exposed or shared, malicious users could gain unauthorized access to your database, leading to potential data breaches or other security incidents.
Lack of Flexibility: Hard-coding connection strings make it difficult to change the database configuration without modifying the source code. This lack of flexibility can be problematic in various scenarios, such as migrating to a different database server, changing credentials, or configuring different environments (development, testing, production). Each change would require a new build and deployment of the application.
Configuration Management Issues: Managing configurations within the source code is not practical for applications deployed across different environments. It is common practice to have different connection strings for development, testing, and production environments. Hard-coding these values would complicate the deployment process and increase the risk of configuration errors, such as accidentally connecting to a production database from a development environment.
How do We Store and Retrieve Connection Strings from the Configuration File in a Console Application?
Storing and retrieving the connection string from the configuration file in a console application involves several steps. Let us understand this step by step:
In .NET Core, we need to use the appsettings.json file to store our application configuration values, such as Connection Strings, Application-Level Global Variables, etc. However, the appsettings.json file is not available for the Console Application by default. 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 the NuGet Package Manager UI or Package Manager Console. So, execute the following command using 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 with the name appsettings.json to your project. While the name does not need to always be appsettings, 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, which 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;User Id=Testuser;Password=Abcd@1234;TrustServerCertificate=True;"
How Do We Fetch the Connection String from the appsettings.json file?
We must follow the steps to get the Connection String from the appsettings.json file.
Step 1: Import the following namespace.
using Microsoft.Extensions.Configuration;
Step 2: Load the Configuration File.
var configBuilder = new ConfigurationBuilder().AddJsonFile(“appsettings.json”).Build();
Here,
ConfigurationBuilder: This class builds an application’s configuration and is the foundation for reading from various configuration sources, such as JSON files and environment variables.
.AddJsonFile(“appsettings.json”): This method specifies that the configuration should include the appsettings.json file. This JSON file typically contains configuration settings like connection strings, application settings, and more in a structured format.
.Build(): This method compiles the configuration sources added to the ConfigurationBuilder into an IConfiguration object that can be used to read the configuration values.
Step 3: Get the Section to Read from the Configuration File
var configSection = configBuilder.GetSection(“ConnectionStrings”);
Here,
GetSection(“ConnectionStrings”): This method retrieves a specific section from the configuration. In this case, it’s fetching the ConnectionStrings section from the appsettings.json file. The ConnectionStrings section typically contains the application’s database connection strings.
Step 4: Get the Configuration Values based on the Config key.
var connectionString = configSection[“SQLServerConnection”] ?? null;
Here,
configSection[“SQLServerConnection”]: This line accesses the value associated with the key SQLServerConnection within the ConnectionStrings section. It expects to find a specific connection string for SQL Server that the application can use to connect to a database.
?? null: This is the null-coalescing operator in C#. It means that if the left-hand operand (configSection[“SQLServerConnection”]) is non-null, it will be used; otherwise, it defaults to null. This safeguard prevents exceptions if the configuration key is not found.
For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
//Step1: Import the following Namespace
using Microsoft.Extensions.Configuration;
namespace ADODOTNETCoreDemo
internal class Program
static void Main(string[] args)
//Get the Connection String from appsettings.json file
//Step2: Load the Configuration File.
var configBuilder = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
// Step3: Get the Section to Read from the Configuration File
var configSection = configBuilder.GetSection("ConnectionStrings");
// Step4: Get the Configuration Values based on the Config key.
var connectionString = configSection["SQLServerConnection"] ?? null;
if (connectionString != null)
//Creating an Instance of SqlConnection using the using statement
using (SqlConnection connection = new SqlConnection(connectionString))
//Open the Connection
connection.Open();
Console.WriteLine("Connection Established Successfully");
//Perform the Database Operations
Console.WriteLine("Connection String is Missing");
catch (Exception ex)
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.
Find the “Copy to Output Directory” setting in the Properties window.
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’re 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.
ADO.NET Core Asynchronous SqlConnection:
Asynchronous methods in ADO.NET Core help avoid blocking the main thread, allowing for more efficient resource use and a better user experience in web and desktop applications. The following is an example of using an asynchronous SQLConnection and executing a query asynchronously in ADO.NET Core.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
internal class Program
static async Task Main(string[] args)
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
await connection.OpenAsync();
Console.WriteLine("Connection opened successfully.");
catch (Exception ex)
Console.WriteLine($"An error occurred: {ex.Message}");
Here,
SqlConnection: This object represents a connection to the database. The OpenAsync method is used to open the connection asynchronously.
Async/Await Pattern: The example uses the async and await keywords essential for asynchronous programming in C#. This pattern helps write non-blocking code and efficiently handle I/O-bound operations.
Using Statements: Ensures that resources are disposed of properly, preventing resource leaks.
Error Handling: The try-catch block handles any exceptions during connection, command execution, or data reading processes.
Note: For new development, primarily targeting .NET Core or .NET Standard, it’s recommended to use Microsoft.Data.SqlClient instead of System.Data.SqlClient. This newer package is more actively maintained and includes additional features and performance improvements suitable for modern applications.
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 to have your feedback. Please post your feedback, questions, or comments about this ADO.NET Core SqlConnection Class 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.
