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 communicating with the SQL Server Database using ADO.NET Core with Examples. Please read our previous article, which discusses the Introduction to ADO.NET Core. ADO.NET Core is a framework within the .NET ecosystem that facilitates data access for .NET applications. It allows us to establish connections to SQL Server databases, execute SQL queries, and perform CRUD (Create, Read, Update, and Delete) operations programmatically. So, at the end of this article, you will learn the following pointers:
- Create a SQL Server database
- Establish a connection from a .NET Core Console Application
- Create tables, insert data, retrieve records, update, and delete records
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) and Transact-SQL (T-SQL) commands. Let’s proceed to understand how to create a database using SSMS and T-SQL commands.
Using SQL Server Management Studio (SSMS)
Open SSMS: Launch SQL Server Management Studio and connect to the server where you want to create the database.
Create New 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.
Configure Database Settings:
In the New Database dialog, enter the database name (such as StudentDB) in the Database name field. You can optionally configure additional settings, such as file locations and initial size. For a simple setup, you can leave these at their default values. Click OK to create the database, as shown in the image below.
The new database will now appear under the Databases folder in the Object Explorer, as shown in the image below:
You can also verify the database by executing the below query in the SQL server database.
SELECT name FROM sys.databases WHERE name = ‘StudentDB’;
Using Transact-SQL (T-SQL) to Create a Database
Connect to the Database Server: Open a new query window in SSMS and connect to the appropriate server.
Execute the CREATE DATABASE Command: 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;
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 a Connection to the SQL Server Database in a Console Application
Establishing a connection to a SQL Server database in a console application involves several steps. The following is a step-by-step guide to achieving this within a .NET Core console application.
Create a New Console Application
First, you need to create a new console application. You can do this using the .NET Core CLI or Visual Studio. Let us create a new Console Application named ADODOTNETCoreDemo using Visual Studio.
Add the Necessary NuGet Package
To use ADO.NET Core to connect with the SQL Server Database, we need to add the Microsoft.Data.SqlClient package. You can install this package using NuGet Package Manager or Package Manager Console. Please execute the following command in the Visual Studio Package Manager Console to install the ADO.NET Core Data Provider Package for the SQL server database:
Install-Package Microsoft.Data.SqlClient
This package provides the classes required to work with the SQL Server. Once the Package is installed, you can verify the same in the Dependencies\Packages folder as shown in the below image:
Note: This library replaced System.Data.SqlClient in .NET Core for SQL Server connectivity. It offers newer features and security updates.
Establish a Connection to the SQL Server Database
A connection string provides the details your application needs to connect to the database, such as server name, database name, security credentials, and other parameters. You do not need to include a username and password for Windows Authentication. Use Trusted_Connection=True for Windows Auth or provide User Id and Password for SQL Auth. For example:
If using Windows Authentication: string connectionString = “Server=YOUR_SERVER_NAME; Database=StudentDB; Trusted_Connection=True; TrustServerCertificate=True;”;
If using SQL Server Authentication: string connectionString = “Server=YOUR_SERVER_NAME; Database=StudentDB; User Id=YOUR_USERNAME; Password=YOUR_PASSWORD; TrustServerCertificate=True;”;
Note:
- Trusted_Connection=True (or Integrated Security=True) means Windows Authentication is used.
- TrustServerCertificate=True is often used in local development to bypass certificate validation warnings.
Basic Connection Example
So, please modify the Program.cs file as follows to establish a connection to the SQL Server database. Make sure to replace the values with your actual database connection details.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
public class Program
{
static void Main(string[] args)
{
// Using Windows Authentication
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Wrap your connection and command objects in using statements to ensure resources are properly disposed.
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
Console.WriteLine("Successfully connected to the database.");
// Always good practice but not strictly necessary inside a using block:
connection.Close();
}
}
}
}
Output: Successfully connected to the database.
Note: We will discuss the SqlConnection Object 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 using ADO.NET Core:
Once your application is connected to the database, you can create database objects by executing T-SQL commands using an SqlCommand object. So, please modify the Program class as follows to create a table named Students with a few columns.
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
public class Program
{
public static void Main(string[] args)
{
try
{
// Define your connection string. This example uses Windows Authentication.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Use a 'using' statement to ensure the SqlConnection is properly disposed.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the database connection.
connection.Open();
// Define the SQL command text to create a 'Students' table.
string createTableCommandText = @"
CREATE TABLE Students (
Id INT PRIMARY KEY IDENTITY(1,1),
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Email NVARCHAR(50)
);";
// Use another 'using' statement for the SqlCommand object to ensure proper disposal.
using (SqlCommand command = new SqlCommand(createTableCommandText, connection))
{
// ExecuteNonQuery is used for creating, inserting, updating, or deleting data without returning rows.
command.ExecuteNonQuery();
Console.WriteLine("Table 'Students' created successfully.");
}
// Connection will be closed automatically at the end of the using block.
}
}
catch (Exception ex)
{
// Log or display the exception message.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output: Table ‘Students’ Created Successfully.
Key Points:
- ExecuteNonQuery() is used for SQL statements that do not return rows (e.g., CREATE, INSERT, UPDATE, DELETE).
- Always handle exceptions (try/catch) to manage any SQL errors.
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 Object 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 from a .NET Core console application involves several steps, including setting up the database connection, creating a command object to execute the SQL insert statement, and executing the command using the ExecuteNonQuery method.
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. When inserting records, it’s essential to use parameterized queries to protect against SQL injection. So, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
public static void Main(string[] args)
{
try
{
// Windows Authentication connection string.
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";
// Parameterized INSERT statement to prevent SQL injection.
string insertSql = @"
INSERT INTO Students (FirstName, LastName, Email)
VALUES (@FirstName, @LastName, @Email);";
// Establish a connection using a 'using' statement.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create a command using the insert SQL and the open connection.
using (SqlCommand command = new SqlCommand(insertSql, connection))
{
// Add parameters to the command to safely pass values.
command.Parameters.AddWithValue("@FirstName", studentFirstName);
command.Parameters.AddWithValue("@LastName", studentLastName);
command.Parameters.AddWithValue("@Email", studentEmail);
// Execute the command. ExecuteNonQuery returns the number of rows affected.
int rowsAffected = command.ExecuteNonQuery();
// Provide user feedback based on rowsAffected.
if (rowsAffected > 0)
Console.WriteLine("Data inserted successfully!");
else
Console.WriteLine("No rows were inserted into the database.");
}
}
}
catch (Exception ex)
{
// Handle and log any exceptions.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output: Data Inserted Successfully!
Now, if you verify the Students database table, then it should have one record inserted, as shown in the image below:
Retrieve All Records using ADO.NET Core from SQL Server Database.
Let us see how to retrieve records from the SQL Server database using ADO.NET core. We need to use the SqlDataReader object to retrieve rows in a forward-only, read-only manner. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
public static void Main(string[] args)
{
try
{
// Connection string for Windows Authentication.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// SQL query to select all rows from the 'Students' table.
string selectAllQuery = "SELECT * FROM Students;";
// Use a 'using' statement to handle the connection disposal automatically.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create the command object with the query and the active connection.
using (SqlCommand command = new SqlCommand(selectAllQuery, connection))
{
// ExecuteReader is used for queries that return rows.
using (SqlDataReader reader = command.ExecuteReader())
{
// Read each row from the SqlDataReader.
while (reader.Read())
{
// Access columns by name or index.
Console.WriteLine(
$"Id: {reader["Id"]}, " +
$"First Name: {reader["FirstName"]}, " +
$"Last Name: {reader["LastName"]}, " +
$"Email: {reader["Email"]}"
);
}
}
}
}
}
catch (Exception ex)
{
// Catch and log any error that occurs during data retrieval.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output: Id: 1, First Name: Pranaya, Last Name: Rout, Email: Pranaya@Example.com
Note. In our upcoming articles, we will discuss the SqlDataReader Object in detail.
Retrieve a Specific Record using ADO.NET Core from SQL Server Database
Now, we want to retrieve a specific record from the database. So, we want to retrieve the Student whose ID is 1. Let’s 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 we want to retrieve the record. To filter rows, use a WHERE clause. Always parameterize queries for security and clarity. For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
public static void Main(string[] args)
{
try
{
// Connection string for Windows Authentication.
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Parameterized SQL query to retrieve a student by Id.
string selectOneQuery = "SELECT * FROM Students WHERE Id = @Id;";
// Use a 'using' statement to manage the SqlConnection resource.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the database connection.
connection.Open();
// Create the SqlCommand with the parameterized query.
using (SqlCommand command = new SqlCommand(selectOneQuery, connection))
{
// Provide the parameter value (in this case, 1).
command.Parameters.AddWithValue("@Id", 1);
// Execute the command using ExecuteReader for retrieval of rows.
using (SqlDataReader reader = command.ExecuteReader())
{
// Iterate through the results (typically one row if the Id is unique).
while (reader.Read())
{
Console.WriteLine(
$"Id: {reader["Id"]}, " +
$"First Name: {reader["FirstName"]}, " +
$"Last Name: {reader["LastName"]}, " +
$"Email: {reader["Email"]}"
);
}
}
}
}
}
catch (Exception ex)
{
// Catch any exceptions that occur.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Now, run the application, which should retrieve the student whose ID is 1.
UPDATE Record in SQL Server Database using ADO.NET Core
Updating records involves modifying existing rows in a table. Using ADO.NET Core, you can achieve this by:
- Connecting to the database.
- Use a parameterized UPDATE query to specify the new values and a condition (e.g., updating the record where Id = 1).
For a better understanding, please modify the Program class as follows:
using Microsoft.Data.SqlClient;
namespace ADODOTNETCoreDemo
{
internal class Program
{
public static void Main(string[] args)
{
try
{
// Define the connection string (Windows Authentication in this sample).
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// Prepare the SQL UPDATE statement with parameters.
// We are updating the LastName and Email for a specific student Id.
string updateSql = @"
UPDATE Students
SET LastName = @LastName,
Email = @Email
WHERE Id = @Id;
";
// Define the new values you want to set, including the unique Id of the record.
int studentIdToUpdate = 1;
string newLastName = "UpdatedLastName";
string newEmail = "UpdatedEmail@Example.com";
// Use a 'using' statement to create the connection and ensure disposal.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create a SqlCommand object with the parameterized UPDATE query.
using (SqlCommand command = new SqlCommand(updateSql, connection))
{
// Add parameter values to the SqlCommand to avoid SQL injection.
command.Parameters.AddWithValue("@LastName", newLastName);
command.Parameters.AddWithValue("@Email", newEmail);
command.Parameters.AddWithValue("@Id", studentIdToUpdate);
// ExecuteNonQuery will return the number of rows updated.
int rowsAffected = command.ExecuteNonQuery();
// Provide user feedback.
if (rowsAffected > 0)
Console.WriteLine("Record updated successfully.");
else
Console.WriteLine("No record found with the specified Id or no changes made.");
}
}
}
catch (SqlException sqlEx)
{
// Handle SQL-specific exceptions.
Console.WriteLine($"SQL Exception: {sqlEx.Message}");
}
catch (Exception ex)
{
// Catch any exception that might happen during the process.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output: Record updated successfully.
Deleting a Record from SQL Server Database using ADO.NET Core
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
{
public static void Main(string[] args)
{
try
{
// Connection string (Windows Authentication).
string connectionString = "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=StudentDB;Trusted_Connection=True;TrustServerCertificate=True;";
// SQL DELETE statement with a parameter for safety.
string deleteSql = "DELETE FROM Students WHERE Id = @Id;";
// Id of the student we want to remove from the database.
int studentIdToDelete = 1;
// Use 'using' to manage the connection lifecycle.
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Open the connection.
connection.Open();
// Create a SqlCommand with the DELETE statement.
using (SqlCommand command = new SqlCommand(deleteSql, connection))
{
// Pass the parameter value for the 'Id' placeholder.
command.Parameters.AddWithValue("@Id", studentIdToDelete);
// ExecuteNonQuery and check the number of rows affected.
int rowsAffected = command.ExecuteNonQuery();
// Provide feedback.
if (rowsAffected > 0)
Console.WriteLine("Record deleted successfully.");
else
Console.WriteLine("No record found with the specified Id.");
}
}
}
catch (SqlException sqlEx)
{
// Handle SQL-specific exceptions.
Console.WriteLine($"SQL Exception: {sqlEx.Message}");
}
catch (Exception ex)
{
// Catch and log the exception.
Console.WriteLine($"Something went wrong: {ex.Message}");
}
}
}
}
Output: Record Deleted Successfully.
FAQs
What is ADO.NET Core, and how does it differ from ADO.NET?
ADO.NET Core is a cross-platform version of ADO.NET optimized for .NET Core applications. It supports various database providers (like SQL Server, SQLite, etc.) and emphasizes high performance and scalability. Unlike the traditional ADO.NET, which is limited to Windows, ADO.NET Core can run on multiple operating systems, making it ideal for cloud and containerized environments.
What is the difference between System.Data.SqlClient and Microsoft.Data.SqlClient?
System.Data.SqlClient is older and not actively maintained for .NET Core. Microsoft.Data.SqlClient is the latest, optimized for .NET Core/.NET 5+ and supports new SQL Server features.
What is the use of ExecuteNonQuery()?
ExecuteNonQuery() executes commands that do not return any rows, such as INSERT, UPDATE, DELETE, or CREATE TABLE statements. It returns the number of rows affected.
How do you prevent SQL Injection in ADO.NET Core?
Use parameterized queries with SqlCommand.Parameters.AddWithValue() or stored procedures. These methods ensure input values are treated as parameters rather than executable code.
What happens if you don’t close a SqlConnection?
The connection may remain open and lead to connection pool exhaustion. Always use using or explicitly close the connection.
How do you handle database connections in .NET Core applications?
Always open connections just before executing commands and close them immediately afterward. Use using statements or explicit calls to Close() to ensure that resources are released.
What are the key classes used in ADO.NET?
- SqlConnection: Manages the connection to a SQL Server database.
- SqlCommand: Executes SQL statements and stored procedures.
- SqlDataReader: Provides a forward-only, read-only stream of data.
- SqlDataAdapter and DataSet (for disconnected operations).
What is the role of the SqlDataReader in ADO.NET Core?
The SqlDataReader is a forward-only, read-only stream that retrieves query results from a SQL Server database. It is efficient and allows you to iterate over the result set one row at a time.
What are some best practices when working with ADO.NET Core?
- Use using statements to ensure proper disposal of connections and commands.
- Handle exceptions with try-catch blocks.
- Use parameterized queries to prevent SQL injection.
- Validate connection strings and manage sensitive information securely (consider using configuration files or environment variables).
In this article, we provide a comprehensive overview of using ADO.NET Core with SQL Server, from setting up the database to executing CRUD operations and preparing for interview questions. Each code snippet and explanation is designed to help you understand the underlying principles and best practices while working with database applications in a .NET Core environment.
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 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.