How to Use SqlConnection for Database Operations in C#

We deal with databases all the time. When we build any type of application that can store the data, the most efficient and persistent location to store the resulting data is a database.

Relational databases are some of the most common and powerful databases that can suit both simple and complex applications due to their extensibility and ACID compliance.

However, in this tutorial, we will mainly focus on the SqlConnection class from the ADO.NET framework. It provides us with efficient and intuitive methods of connecting and interacting with SQL databases.

We will explore from the basic tasks such as connecting to the database, creating a table, adding records, querying the table, updating the existing records, and deleting them from the database.

Prerequisites:

We need the following tools for our project:

Visual Studio – Ensure that you have the Visual Studio with the tools for building the “.NET” applications that are installed on your machine.
SQL Server – You also need to have the SQL Server installed. For this tutorial, we will use the SQL Server 2022 Developer Edition. It is good to keep in mind that the queries that are discussed in this tutorial are compatible with the older versions of the SQL Server.

Installing the SQL Client

Once you created a new C# project, you need to ensure that you have the SQL tools installed on your machine.

For our case, we need to install the System.Data.SqlClient package on our project. This provides an SQL data provider for the SQL server which allows us to work with the database from the C# application.

Run the following command in the NuGet package manager console:

NuGet\Install-Package System.Data.SqlClient -Version 4.8.5

Once installed, we can proceed and discuss the process of connecting and working with the database.

Setting Up the Connection String

Before we can connect to the database, we need to define a connection string. This allows us to define all the information that we need to connect the database. It includes information such as the database name, authentication method, target database, and more.

In our case, the target server name is localhost\MSSQLSERVER. We wish to connect to the target database called SampleDB and connect to it using the Windows Authentication method.

Hence, we can define the connection string as follows:

string connectionString = "Server= localhost\MSSQLSERVER;Database=SampleDB;Integrated Security=True;";

Connecting to the Database

Once we define the connection string, we can proceed and open a connection to the database using the SqlConnection.

The code is as follows:

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("connected to database");
}

This code opens a connection to the SQL Server using the specified connection string.

Inserting the Data

Suppose we have an existing table on the target database to insert the data into the table. We can use the query string.

Take the following example query that creates a basic table in the SQL Server to store the log information:

CREATE TABLE LogInformation (
LogID INT PRIMARY KEY IDENTITY(1,1),
LogMessage NVARCHAR(MAX),
LogType NVARCHAR(255),
CreatedAt DATETIME DEFAULT GETDATE()
);

The following code demonstrates how to define an insert statement to add the data into the previously provided LogInformation table.

string connectionString = "Server=CAPTAINSALEAC4C;Database=SampleDB;Integrated Security=True;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string query = @"
INSERT INTO LogInformation (LogMessage, LogType, CreatedAt) VALUES
('User root attempted to login and failed due to incorrect password.', 'ERROR', '2023-10-11 08:35:22'),
('User admin logged in successfully from IP address 192.168.1.101.', 'INFO', '2023-10-11 09:10:45'),
('Database backup completed successfully.', 'INFO', '2023-10-11 03:00:00'),
('Low disk space warning on server CAPTAINSALEAC4C.', 'WARNING', '2023-10-11 11:25:30'),
('User root changed password successfully.', 'INFO', '2023-10-11 12:14:50');
";

using (SqlCommand command = new SqlCommand(query, connection))
{
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} rows inserted.");
}
}

Output:

5 rows inserted.

Reading the Data

To read the data from the database table, we can use the SqlCommand along with SqlDataReader as shown in the following example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM LogInformation";
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine($"{reader["LogMessage"]}, {reader["LogType"]}, {reader["CreatedAt"]}");
}
}
}
}

This should read the data that is stored in the table and print it into the console as follows:

Deleting the Records

To delete the data, use the DELETE SQL query as follows:

using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = "DELETE FROM LogInformation WHERE LogID = 1";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.ExecuteNonQuery();
}
}

This should remove the first log entry from the table.

Conclusion

In this tutorial, we covered how to use the SqlConnection to connect to an SQL Server database and perform the operations on the database.