Execute SQL Queries from NET Core consoles

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 a appsettings.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 the Program.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