ADO.NET Core Using SQL Server

Back to: ADO.NET Core Tutorial For Beginners and Professionals

How to Communicate with SQL Server Database using ADO.NET Core

In this article, I will discuss How to Communicate with the SQL Server Database using ADO.NET Core with Examples. Please read our previous article discussing the Introduction to ADO.NET Core. ADO.NET Core provides a set of classes and interfaces that allow us to interact with SQL Server databases from our .NET Core applications. In this article, I will guide you through the steps to connect to the SQL Server database, create a database table, insert records, fetch records, and delete records using ADO.NET Core.

ADO.NET Core with SQL Server

When using ADO.NET Core with SQL Server, we can use the core components of ADO.NET Core to connect, execute commands, and manage data. This involves setting up a connection using SqlConnection, executing SQL queries or commands using SqlCommand, and reading data with SqlDataReader.

Creating a Database in SQL Server:

Creating a database in SQL Server involves several steps, which can be performed using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL) commands. 

Using SQL Server Management Studio (SSMS)

Launch SQL Server Management Studio and connect to the server where you want to create the database. Right-click on the Databases folder in the Object Explorer and select New Database… from the context menu, as shown in the image below.

In the New Database dialog, enter the database name in the Database name field. Under the Database Files and Options tabs, you can configure additional settings such as file locations, initial size, and more. For a simple setup, you can leave these at their default values. Click OK to create the database, as shown in the image below. Here, I am providing the database name StudentDB.

The new database will now appear under the Databases folder in the Object Explorer, as shown in the image below:

Using Transact-SQL (T-SQL)

Open a new query window in SSMS and connect to the appropriate server. Type the following T-SQL command to create a new database, replacing YourDatabaseName with the name you want to give to your database:

CREATE DATABASE YourDatabaseName;

For more advanced options, such as specifying the data and log file locations, you can use a command like:

CREATE DATABASE YourDatabaseName
ON 
( NAME = 'YourDatabase_Data',
  FILENAME = 'C:\Path\To\YourDatabase.mdf',
  SIZE = 10MB,
  MAXSIZE = 100MB,
  FILEGROWTH = 5MB )
LOG ON
( NAME = 'YourDatabase_Log',
  FILENAME = 'C:\Path\To\YourDatabase.ldf',
  SIZE = 5MB,
  MAXSIZE = 25MB,
  FILEGROWTH = 5MB );

Modify the FILENAME, SIZE, MAXSIZE, and FILEGROWTH parameters as needed for your environment. Press F5 or click the Execute button to run the command. SQL Server will create the database using the specified configuration.

Note: Regardless of your chosen method, you can verify that your database has been created by expanding the Databases folder in the Object Explorer in SSMS. Your new database should be listed there.

Establish Connection to SQL Server Database in a Console Application

Let’s understand how to establish a connection to the SQL Server database in a Console Application. ADO.NET Core is part of the .NET Core framework, which allows for database operations such as connecting to a database, executing commands, and handling data. Below is a step-by-step guide to achieving this within a .NET Core console application.

Create a New Console Application

First, create a new console application. Let us create a new Console Application named ADODOTNETCoreDemo using Visual Studio. I am using .NET 8 as the version while creating the project.

Add the Necessary NuGet Package

To use ADO.NET Core to connect with SQL Server Database, you need to add Microsoft.Data.SqlClient package to your project. You can install this package by using NuGet Package Manager or Package Manager Console. You can install Microsoft.Data.SqlClient package by running the following command in the Package Manager Console:

Install-Package Microsoft.Data.SqlClient

Once the Package is installed, you can verify the same in the Project packages folder, as shown in the below image:

Establish a Connection to the SQL Server Database

To interact with SQL Server, we first need to establish a connection using the SqlConnection class and connection string. The connection string contains information needed to connect to the database, such as the server name, database name, and authentication credentials. So, open the Program.cs file and copy and paste the following code to establish a connection to your SQL Server database. Make sure to replace the actual values with your actual database connection details.

using Microsoft.Data.SqlClient;

namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create the Connection String
            // string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

            //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 connection = new SqlConnection(connectionString))
            {
                //Open the Connection
                connection.Open();
                Console.WriteLine("Successfully connected to the database.");

                connection.Close();
            }
        }
    }
}

Output: Successfully connected to the database.

Note: We will discuss the SqlConnection Class in detail in our upcoming articles. Here, I only show how to use it to open and close the database connection.

Create a Table in SQL Server Database using ADO.NET Core:

Within the same using block where we opened the connection, we can execute an SQL command to create a new table. Once the connection is opened, we can execute commands using the SqlCommand object. So, modify the Program class as follows to create a simple table named Students with a few columns. The following code is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.Data.SqlClient;

namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                // Create the Connection String
                // string connectionString = "Server=your_server_name;Database=your_database_name;User Id=your_username;Password=your_password;";

                //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;";

                //While Creating the SqlConnection passing the Connection String
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Open the Connection
                    connection.Open();

                    //Create the Command Text
                    string createTableCommandText = @"
                    CREATE TABLE Students (
                        Id INT PRIMARY KEY IDENTITY(1,1),
                        FirstName NVARCHAR(50),
                        LastName NVARCHAR(50),
                        Email NVARCHAR(50)
                    );";

                    //Create an instance of the SqlCommand object by using the Command text and Connection object
                    using (SqlCommand command = new SqlCommand(createTableCommandText, connection))
                    {
                        //Call ExecuteNonQuery Method to Execute the command in the Provided Connection
                        command.ExecuteNonQuery();
                        Console.WriteLine("Table 'Students' Created Successfully.");
                    }

                    //Close the Connection
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something Went Wrong: {ex.Message}");
            }
        }
    }
}

Output: Table ‘Students’ Created Successfully.

Now, if you verify the StudentDB database, then it should have created the Students database table with the following structure:

Note: We will discuss the SqlCommand Class in detail in our upcoming articles. Here, I only show how to use it to execute database commands.

Inserting Record using ADO.NET Core to SQL Server Database

Inserting records into the SQL Server database table from a .NET Core application involves several steps, including setting up the database connection, creating a command to execute the SQL insert statement, and executing the command. We have the Students database table within the StudentDB database. As of now, the Students table is empty. Let us Insert one record into the Students database table using ADO.NET Core. So, modify the Program class as follows. The following code is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //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;";

                // Example student data to insert
                string studentFirstName = "Pranaya";
                string studentLastName = "Rout";
                string studentEmail = "Pranaya@Example.com";

                // SQL INSERT statement
                string insertSql = "INSERT INTO Students (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)";

                //While Creating the SqlConnection passing the Connection String
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Open the Connection
                    connection.Open();

                    //Create the Command Objecr
                    using (SqlCommand command = new SqlCommand(insertSql, connection))
                    {
                        // Add parameters to prevent SQL injection
                        command.Parameters.AddWithValue("@FirstName", studentFirstName);
                        command.Parameters.AddWithValue("@LastName", studentLastName);
                        command.Parameters.AddWithValue("@Email", studentEmail);

                        //Execute the Coomand
                        int result = command.ExecuteNonQuery();

                        // Check the result
                        if (result < 0)
                            Console.WriteLine("Error Inserting Data Into Database!");
                        else
                            Console.WriteLine("Data Inserted Successfully!");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something Went Wrong: {ex.Message}");
            }
        }
    }
}

Output: Data Inserted Successfully!

Now, if you verify the Students database table, then it should have 1 record inserted, as shown in the image below:

Retrieve All Records using ADO.NET Core from SQL Server Database

Retrieving All Records from SQL Server using ADO.NET Core involves several key steps. Let us see how to retrieve records from the SQL Server database using ADO.NET core. Modify the Program class as follows. The following code is self-explained, so please go through the comment lines for a better understanding.

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //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;";
                
                // SQL query to retrieve all records
                string sqlQuery = "SELECT * FROM Students";

                //While Creating the SqlConnection passing the Connection String
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Open the Connection
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"Id: {reader["Id"]}, First Name: {reader["FirstName"]}, Last Name: {reader["LastName"]}, Email: {reader["Email"]}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something Went Wrong: {ex.Message}");
            }
        }
    }
}

Output: Id: 1, First Name: Pranaya, Last Name: Rout, Email: Pranaya@Example.com

Retrieve a Specific Record using ADO.NET Core from the SQL Server Database.

Now, we want to retrieve a specific record from the database. So, basically, we want to retrieve the Student whose ID is 1. Let us see how we can do this. In this case, while creating the SQL query, we need to use the Where Clause and specify the condition on which we want to retrieve the record. 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
            {
                //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;";

                // SQL query to retrieve a specific record
                string sqlQuery = "SELECT * FROM Students WHERE Id = '1'";

                //While Creating the SqlConnection passing the Connection String
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Open the Connection
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"Id: {reader["Id"]}, First Name: {reader["FirstName"]}, Last Name: {reader["LastName"]}, Email: {reader["Email"]}");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something Went Wrong: {ex.Message}");
            }
        }
    }
}

Now, run the application, which should retrieve the student whose ID is 1. In our upcoming articles, we will discuss the SqlDataReader Class in detail.

Deleting a Record from SQL Server Database using ADO.NET Core

Deleting a record from a SQL Server database using ADO.NET Core involves several key steps. The student table currently contains one record. Let’s delete that record using ADO.NET Core. Please modify the Program class file as shown below, which will delete the record from the Students table.

using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //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;";

                // SQL query to delete a record
                string sqlQuery = "DELETE FROM Students WHERE Id = @Id";

                //While Creating the SqlConnection passing the Connection String
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //Open the Connection
                    connection.Open();

                    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                    {
                        // Replace @Id with the actual id of the record you want to delete
                        command.Parameters.AddWithValue("@Id", 1);

                        //Execute the Delete Query
                        int result = command.ExecuteNonQuery();

                        // Check if the delete operation was successful
                        if (result > 0)
                        {
                            Console.WriteLine("Record Deleted Successfully.");
                        }
                        else
                        {
                            Console.WriteLine("No Record Found with the Specified Id.");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Something Went Wrong: {ex.Message}");
            }
        }
    }
}

Output: Record Deleted Successfully.

In the next article, I will discuss the ADO.NET Core SqlConnection Class with Examples. Here, I explain ADO.NET Core using SQL Server, i.e., how to connect with the SQL Server Database using ADO.NET Core and create a database table, insert, fetch, and delete. I would like to have your feedback. Please post feedback, questions, or comments about this ADO.NET Core using the SQL Server Database article.