How to Use SqlClient in ASP.NET Core – Guidelines
Today we shall see how to Use SqlClient in ASP.NET Core using simple examples.
There are multiple techniques to connect to the SQL server and the easiest and preferred techniques are using ADO.NET and Entity Framework (ORM) etc.
However, you may want to keep it simple due to many other reasons and you may want to continue to use SQLClient in your project then these guidelines will help you how to achieve the same.
Today in this article, we will cover below aspects,
Today we will see simple techniques of connecting SQL servers using SqlClient and performing basic CRUD operations like Reading, Create, Delete, Update, etc.
If you are interested to know how to use Entity Framework core for CRUD operation on SQL, please refer to the below articles. Entity Framework Repository Implementation in ASP.NET Core
Getting started
Create an ASP.NET Core 3.1 or 5.0 application.
Please install System.Data.SqlClient Nuget package
PM> Install-Package System.Data.SqlClient -Version 4.8.2
Note– Please use the latest version as available.
Also please add the below-using statement to your module,
using System.Data.SqlClient;
System.Data.SqlClient is the ADO.NET provider for accessing SQL Server or Azure SQL Databases. The .NET Framework Data Provider System.Data.SqlClient uses its own protocol to communicate with SQL Server.
Synchronous SQL command execution
Below logic or similar, you need to put in any module of your concern and that’s all you can perform any CRUD operations.
using (SqlConnection _con = new SqlConnection(connectionString))
{
string queryStatement = "SELECT * FROM dbo.employee ORDER BY employeeID";
using (SqlCommand _cmd = new SqlCommand(queryStatement, _con))
{
employeeTable = new DataTable("Employees");
SqlDataAdapter _dap = new SqlDataAdapter(_cmd);
_con.Open();
_dap.Fill(employeeTable);
_con.Close();
}
}
In the above example,
SqlConnection– Represents a connection to a SQL Server database.
SqlCommand – Represents a Transact-SQL statement or stored procedure to be executed against a SQL Server database.
SqlDataAdapter –
Represents a set of data commands and a database connection that is used to fill the DataSet or DataTable and or update a SQL Server database.
- SelectCommand– In the above example, we have used Select commands which represent the SelectCommand property of the DataAdapter that retrieves data from the data source.
- InsertCommand – Inserts new data to the data source.
- UpdateCommand– Update existing data int the data source.
- DeleteCommand – Delete data from the data source.
In the above commands, employeeTable will be filled with the required data.
Asynchronous SQL Method support
If your API supports asynchronous operation then please use async methods provided by the above library as appropriate.
Example:
using (var connection = new SqlConnection(ConnectionString))
{
try
{
await connection.OpenAsync(cancellationToken);
var command = connection.CreateCommand();
command.CommandText = "select 1";
await command.ExecuteNonQueryAsync(cancellationToken);
}
catch (DbException ex)
{
throw;//Catch exception only at Global exception middleware
}
}
Once implemented you can fetch the data from SQL Database,
Note– Above I am getting SQL as Data Table, I am just converting it to JSON
If you would like to learn EFCore(ORM) or Dapper(Micro-Orm) usage which are the most preferred and recommended technique while dealing with database operations.
please visit the below article for your references.
That’s all!
Do you have any comments or ideas or any better suggestions to share?
Please sound off your comments below.
Happy Coding !!
Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.