This post provides code example to execute SQL queries safely and efficiently from NET Core using ADO.NET
, the built-in data access technology.
Before continuing this, you should have ideas about the basic SQL language (structured query language), basic SQL queries, etc. if you don’t have an idea about the SQL programming language, I would like to suggest to refer the tutorials SQL sites available on the internet.
Execute SQL Queries from NET Core consoles
Getting Started
In many enterprise or automation scenarios, running SQL queries directly from a .NET Core console application can be incredibly useful—whether for database health checks, data manipulation, migrations, or reporting.
Like .Net Framework, the .Net core also provides classes for SQL operations, There are no much more differences to execute DBMS queries from .Net core, you will find same class names and classes in same namespaces like in .net framework, but yes a little syntax difference is there.
Above are the little idea about the difference between .NET and .NET Core for executing stored procedure SQL server site .net core codes, let’s see the SQL queries examples in the below demonstration.
Add the SQL Client Library
To access SQL Server in .NET Core and .NET 5+, add the Microsoft.Data.SqlClient NuGet
NuGet package. This is the modern client library recommended for SQL Server.:
dotnet add package Microsoft.Data.SqlClient
Add Configuration
Create aappsettings.json
to store your database connection string.
{
"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=YourDbName;User Id=your_user;Password=your_password;"
}
}
using Microsoft.Extensions.Configuration;
var config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
string connectionString = config.GetConnectionString("DefaultConnection");
Execute SQL Queries Using ADO.NET
SELECT Query Example
Open theProgram.cs
file and replace the contents with the following:
private SqlConnection con =new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
DataTable table = new DataTable();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;;
cmd.CommandText = "SQL Select Query";
try
{
con.Open();
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
Execute SQL Queries With Parameter
The below example states how to run sql query with parameter using the SQLCommmand
in .Net core .
private SqlConnection con =new SqlConnection(ConfigurationManager.AppSettings["conStr"]);
DataTable table = new DataTable();
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;;
cmd.CommandText = "SQL Select Query";
var lstParameters = new List<SqlParameter>();
var objSqlParam = new SqlParameter();
objSqlParam.ParameterName = "@id";
objSqlParam.SqlDbType = SqlDbType.Int;
objSqlParam.Value = orgId;
lstParameters.Add(objSqlParam);
cmd.Parameters.AddRange(param);
try
{
con.Open();
SqlDataAdapter da = null;
using (da = new SqlDataAdapter(cmd))
{
da.Fill(table);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
cmd = null;
con.Close();
}
Examples of SQL Commands Execution with SQL Parameters
Execute SQL Procedure and SQL functions
To Execute SQL procedures and SQL server functions, in the above code just change the CommandType.Text to CommandType.StoredProcedure, the below example state how to change command type to execute SQL procedures.
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.USP_GetOrganization";
INSERT Query with Parameters
string insertQuery = "INSERT INTO Users (Name, Email) VALUES (@name, @email)";
using var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.Parameters.AddWithValue("@name", "UserName");
insertCommand.Parameters.AddWithValue("@email", "UserEmailID");
int rowsAffected = await insertCommand.ExecuteNonQueryAsync();
Console.WriteLine($"{rowsAffected} row(s) inserted.");
Summary
Using ADO.NET in a .NET Core console application is a powerful way to directly execute SQL queries. It’s simple, flexible, and efficient for a variety of tasks from automation to reporting.
Thanks