In this tutorial, we will learn the fundamentals of connecting a C# application with an SQL Server database. We will also cover the other examples such as inserting the data, deleting the data, and running the database operations asynchronously.
Requirements:
The first steps is to ensure that we have all the required tools and resources to build our application. For this tutorial, we use the basic configuration. Hence, you need to have the following:
- Installed Microsoft SQL Server
- Installed “.NET” compiler
- Installed “.NET” compiler
- Basic SQL and C# understanding
With the given requirements met, we can proceed.
Example 1: Connecting to the SQL Server
The most fundamental and essential step before performing any database operations is connecting to the database server. In C#, we have access to ADO.NET or ActiveX Data Objects .NET which are a suite of built-in classes in the .NET framework.
The ADO.NET tools provide powerful and essential features to connect, retrieve, update, and manipulate the data that is stored in the databases. Think of it like a bridge that allows us to connect and operate on database servers such as Oracle, SQL Server, MySQL, and more.
To connect to an SQL Server instance using ADO.NET, we can use the “SqlConnection” class and provide the connection details as shown in the following:
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = @"Server=localhost#SQLEXPRESS;Database=sampledb;TrustedConnection=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Connected successfully!");
}
}
}
In this example, we start by importing the required namespaces. Here, we require the system and the “Data.SqlClient” namespaces.
Next, we define a string that contains the connection details. In this case, we specify the SQL Server instance name, the target database that we wish to connect and the parameter to allow the application to use the Windows Authentication to connect to the server.
We then create a new connection object using the “SqlConnection” class and specify the connection details and call the connection.Open().
Note: We use the “using” directive to allow the application to release the resources to the database after use.
Install the .NET SQL Tools
Depending on your environment configuration and project setup, you may need to install the SQL Tools before you can access the ADO.NET classes and methods. You can use the “NuGet” command as follows:
Once you run the previous command, you should have the required ADO.NET tools to connect to your database.
Example 2: Retrieving the Data
Once we established the connection to the database, we can proceed and run an SQL query to select the data from the database.
Take the example code as shown in the following:
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = @"Server=localhost#SQLEXPRESS;Database=sakila;TrustedConnection=True";
string sqlQuery = "SELECT * FROM film";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["film_name"]);
}
}
}
}
}
}
In this example, we start by creating a new command using the “SqlCommand” class. Next, we create a reader instance which provides a cursor to read the data from the provided table. We then query the data and print the results to the console as shown.
Example 3: Running the Parameterized Queries
You might also want to run the parameterized queries which can help you to reduce the security issues such as the SQL injection. The following shows a snippet on how you can run a parameterized query using C#:
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
command.Parameters.AddWithValue("@year", "2006");
using (SqlDataReader reader = command.ExecuteReader())
{… }
}
In this case, we define a parameterized query which can help to reduce the security issues and provide extensibility for more complex queries.
Example 4: Inserting the Data
To insert the data into a table, we can use the parameterized queries and the insertQuery() method as shown in the following snippet:
using (SqlCommand command = new SqlCommand(insertQuery, connection))
{
command.Parameters.AddWithValue("@username", "user1");
command.Parameters.AddWithValue("@email", "[email protected]");
command.ExecuteNonQuery();
}
This should insert the provided values into the user’s table.
Example 5: Async Operations
Tasks such as database operations can be long running. This can be very inefficient especially when running them synchronously. Hence, to avoid blocking the main thread, it is recommended to run any database operation in async manner.
You can check our C# tutorial on async programming to get the basics. However, the following function shows a basic iteration that can run a database operation asynchronously:
{
string connectionString = @"Server=localhost#SQLEXPRESS;Database=sakila;TrustedConnection=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
await connection.OpenAsync();
// database operations
}
}
await ConnectToSqlAsync();
The given illustration shows a basic function that can run the database operations in async manner to avoid blocking the main thread.
Conclusion
In this tutorial, we covered the fundamentals of building and working with the SQL Server database using a C# application. Feel free to reference the ADO.NET to learn more about the supported operations.
