Execute SQL Queries from NET Core consoles

Kailash Chandra Behera | Friday, July 17, 2020

Introduction

This blog demonstration and provides Dotnet core code snippet and SQL queries examples for SQL operations like (execute SQL basic commands(SQL statements, basic SQL queries), execute SQL procedures and SQL function, etc.

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.

Getting Started

Like .Net Framework, the .Net core also provides classes for SQL operations mentioned above in the introduction section, 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.

Demonstration

This demonstration is conducted in SQL Server 2017 and .net core 3.0 and fetches data from the table of SQL using ADO.net core codes using SQL function and SQL procedure.

 CREATE TABLE [dbo].[organization_master](  
      [id] [int] IDENTITY(1,1) NOT NULL,  
      [name] [varchar](150) NOT NULL,  
      [logo_path] [varchar](150) NOT NULL,  
      [website] [varchar](150) NULL,  
      [url] [varchar](150) NULL,  
      [created_on] [date] NOT NULL,  
      [created_by] [int] NOT NULL,  
      [modified_on] [date] NULL,  
      [modified_by] [int] NULL,  
      [is_active] [bit] NULL,  
  CONSTRAINT [PK_organization_master] PRIMARY KEY CLUSTERED   
 (  
      [id] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]  
 ) ON [PRIMARY]

SQL creating table example

The below example is the basic .net core syntax, which states how to connect SQL to execute the SQL command(select statement SQL).

 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();  
 }

Examples of SQL Commands Execution

The below example states how to executes SQL command with SQL command parameter. This can be used to query SQL data through simple select statement or SQL procedure.

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

Summary

In the above, we say how to execute select statement SQL procedure and SQL function from .net core. I hope you have enjoyed it a lot.

Thanks

By at