How To Execute SQL Query in C#? Simple Steps to Follow

When learning to work with Sql Server databases, you should already know the roles and how to execute queries. However, in C# programming, the execution of a Sql query is not quite the same. To execute a Sql query in a C# program, you need to use an ADO.NET support class called SqlCommand. In addition, to read the results of the Sql query also needs specific programming techniques.

This lesson will introduce you to all you need to know about how to execute Sql query in C# program using the SqlCommand class and techniques specific to this process.

How To Execute SQL Query in C#? The Detailed Tutorials

In the previous lesson, you learned how to connect to Sql Server. After a successful connection, interacting with the database requires two operations: (1) Specify the data that the program needs; (2) Get the result.

Specifying the data the program is interested in is done by sending a query written in the SQL language. SQL queries are essentially text strings.

Note that the SQL language is not exactly the same across DBMSs. Microsoft’s SQL Server uses T-SQL, while Oracle uses PL/SQL.

ADO.NET supports executing SQL queries (to SQL Server) and reading results using objects of the SqlCommand class.

SqlCommand Class

SqlCommand (full name is System.Data.SqlClient.SqlCommand) is the class responsible for executing queries on a connection to the Sql Server database.

We can think of the object of the SqlConnection class making a connection to the Sql Server similar to laying a pipeline from the C# program to the Sql Server. The object of the SqlCommand is like the pump placed at the top of the pipe on the program side. It is responsible for pushing the query to Sql Server and “sucking” data (results of the query) from Sql Server to the program.

Initialize object

There are several ways to initialize objects of the SqlCommand class:

var command1 = new SqlCommand();

// provide query string on initialization

var commandText = “Select * from Contacts”;

var command2 = new SqlCommand(commandText);

// provide query string + object connection on initialization

var connection = new SqlConnection();

var command3 = new SqlCommand(commandText, connection);

// can also create commands directly from connection

var connection = new SqlConnection();

var command = connection.CreateCommand();

Some Properties of SqlCommand

CommandText: contains the SQL query string to be executed

Connection: contains the object of the connection used to connect to the database

CommandType: determines whether you need to execute an SQL query (CommandType.Text value) or need to call a stored procedure function (CommandType.StoredProcedure value) via the SqlCommand object. The default value is Text.

Parameters: list of parameters used in the query. This content will be presented in a separate section at the end of the article.

The two most important pieces of information that the SqlCommand object needs to know is the object of the SqlConnection class and the Sql query string. These two pieces of information must be provided to the SqlCommand object before calling the command to execute any query.

Some important methods of SqlCommand

ExecuteNonQuery(): specializes in executing queries that return no data (INSERT, UPDATE, DELETE).

ExecuteScalar(): executes queries that return ONE unique value, usually the result of an Aggregate query (SELECT COUNT|MIN|MAX|AVG).

ExecuteReader(): executes queries that return a SET of values, like regular SELECT queries.

SqlCommand . Query Result Processing

As for getting results, depending on the type of query will be different techniques.

As you know, the SQL language has 4 main types of data queries: INSERT, UPDATE, DELETE, and SELECT. In which, INSERT, UPDATE, DELETE do not return results. The ExecuteNonQuery() method is used to execute these queries. The method execution result is the number of records affected by the query.

Particularly for SELECT, each query must use an appropriate way of storing data.

For queries of type SELECT COUNT|MIN|MAX|AVG, only a single value is returned. The ExecuteScalar method stores the result in a variable of type Object. You can cast it to the appropriate type for further use.

For other SELECT queries, the result is a set of data. Part of the results of these queries are stored (temporarily) in a SqlDataReader object.

SqlDataReader operates in a forward-only and read-only fashion. SqlDataReader allows reading through each row in a data set in a forward-only (forward-only) direction. Cannot read in reverse. SqlDataReader only allows to read data out, not to edit data (read-only). Therefore, if you want to store the results for long-term use, you must use a different storage mechanism.

The specific way of working of SqlDataReader will be considered in the example below.

Prerequisites

Before diving into the process of executing SQL queries in C#, it is important to ensure that you have a basic understanding of SQL and C#. In addition to this, you’ll need to have a database set up and installed necessary libraries.

SQL is a language used for managing and manipulating relational databases. You should have a basic understanding of SQL syntax and be familiar with concepts such as tables, columns, rows, and data types. You can find a variety of free online resources, tutorials, and courses that can help you gain a good understanding of SQL.

C# is a powerful object-oriented programming language used to create a variety of applications, including desktop and web-based applications. You should have a basic understanding of C# syntax, programming concepts, and object-oriented programming.

To execute SQL queries in C#, you’ll need to have a database set up. This can be a local or remote database, and it can be any relational database management system (RDBMS) like MySQL, SQL Server, or Oracle. You’ll also need to ensure that you have the necessary libraries installed, such as the ADO.NET library, which is used to connect to the database.

It is important to have these prerequisites in place before attempting to execute SQL queries in C#. Having a good understanding of SQL and C# and having the necessary tools and resources in place will make the process smoother and more efficient.

Establishing Database Connection

Establishing a connection to the database is the first step in executing SQL queries in C#. It is essential to ensure that the connection is set up correctly to avoid errors in the process.

To establish a database connection, you will need to use the ADO.NET library, which provides a set of classes to connect to the database. The library provides various connection objects, such as SqlConnection, OracleConnection, and OdbcConnection, depending on the type of database you are connecting to.

The process of establishing a database connection involves several steps. Firstly, you need to create an instance of the connection object, which takes the connection string as a parameter. The connection string contains information such as the database name, server name, and credentials required to connect to the database.

For example, if you are connecting to a SQL Server database, the connection string will look something like this:

string connectionString = “Server=myServerName;Database=myDatabaseName;User Id=myUsername;Password=myPassword;”;

Once you have created the connection string, you can create an instance of the SqlConnection object and pass the connection string as a parameter, like this:

using System.Data.SqlClient;

SqlConnection connection = new SqlConnection(connectionString);

The SqlConnection object provides various methods to open and close the connection to the database. To open the connection, you can call the Open() method of the SqlConnection object, like this:

connection.Open();

It is important to ensure that the connection is closed after the query is executed. To close the connection, you can call the Close() method of the SqlConnection object, like this:

connection.Close();

Testing the database connection is also an essential step to ensure that the connection is set up correctly. You can use the Try-Catch block to catch any exceptions that may occur during the connection process and handle them appropriately.

Executing Sql Query in C# with SqlCommand

Aggregate query execution

The single data returned from Aggregate queries such as SELECT COUNT|MIN|MAX|AVG are called scalar values.

We’ll take a look at how to execute and get the results for this type of query by doing an example.

This demonstration uses the database from the Basic ADO.NET Programming exercise.

Step 1. Create a new solution named S02_SqlCommand. In this solution create a project (Console App) named P01_Scalar.

how to execute sql query in c#

Step 2. Open the Program.cs file and write the code:

using System;

using System.Data.SqlClient;

namespace P01_Scalar

{

class Program

{

static void Main(string[] args)

{

Console.Title = “Retrieve scalar value”;

// note to change my connectionString

var connectionString = @”Data Source =.sqlexpress; Initial Catalog = Contacts; Integrated Security = True”;

// use the using structure to automatically close the connection after the end of the code block

using (var connection = new SqlConnection(connectionString))

{

// SQL query

var commandText = “SELECT COUNT(*) FROM CONTACTS”;

// initialize object of class SqlCommand

var command = new SqlCommand(commandText, connection);

// Note to open the connection before executing the query

connection.Open();

// execute query to get scalar results

var count = (int)command.ExecuteScalar();

Console.WriteLine($”{count} contacts found in the database”);

}

Console.ReadKey();

}

}

It can be seen that executing and reading results from Aggregate queries with SqlCommand is very simple: (1) use the ExecuteScalar method of SqlCommand; (2) transform (squeeze) the data type of the result to the desired type.

The ExecuteScalar method of the SqlCommand class specializes in executing Sql queries that return a single result. The value returned by ExecuteScalar is always of type Object. Therefore, if you want to use this result in other calculations, you must type casting to your desired type.

Execute Select Query

Step 1. Create more project P02_DataReader in solution. Set this project to be a StartUp project.

Step 2. Write the following code:

using System;

using System.Data;

using System.Data.SqlClient;

namespace P02_DataReader

{

class Program

{

private static void Main(string[] args)

{

Console.Title = “Retrieve data set”;

var connectionString = @”Data Source=.sqlexpress;Initial Catalog=Contacts;Integrated Security=True”;

using (var connection = new SqlConnection(connectionString))

using (var command = new SqlCommand(“SELECT * FROM CONTACTS”, connection))

{

connection.Open();

var sqlDataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

if (sqlDataReader.HasRows)

{

while (sqlDataReader.Read())

{

var id = sqlDataReader.GetInt32(0);

var contactName = sqlDataReader.GetString(1);

var alias = sqlDataReader.GetString(2);

Console.WriteLine($”[{id}] {contactName} ({alias})”);

}

}

}

Console.ReadLine();

}

}

}

This example reads all records from the Contacts table and prints it to the screen. In this example use the ExecuteReader method and the object of the SqlDataReader class.

Using the ExecuteReader . method

Notice that the ExecuteReader method has a parameter of type CommandBehavior (System.Data.CommandBehavior) used to specify the action to take on completion of the query execution. The CloseConnection value signals the need to close the SqlDataReader after reading the data has been completed.

If the SqlDataReader is not closed after reading the data, subsequent queries on the SqlCommand will not be executed. Closing the SqlDataReader does not mean closing the connection.

Some other values of CommandBehavior include:

Default: queries can return multiple result sets and can affect the state of the database. Calling ExecuteReader(CommandBehavior.Default) is equivalent to calling ExecuteReader() with no parameters.

KeyInfo: the query only retrieves information about the column and primary key.

SchemaOnly: the query only returns the information of the columns, not the data.

SingleResult: the query returns only one data set. Distinguish from Default – returns multiple datasets.

SingleRow: the query returns only one row of data.

Each SELECT query returns several rows belonging to the same table. ADO.NET calls this group of result lines a result set. SqlCommand allows to execute MANY queries at the same time. Then an ExecuteReader command can return MULTIPLE result sets. This is how it works by default (Default parameter). On the other hand, if CommandBehavior is specified as SingleResult then SqlCommand only returns one result set.

Using SqlDataReader

The return result of ExecuteReader is an object of SqlDataReader. This object supports reading data in forward-only and read-only styles as you know.

The general working principle of SqlDataReader is as follows:

Use the HasRows property to check if the query returns data.

If there is data, then go through each line in turn to read the information.

To cycle through each line, use the Read() method. This method will pass the “cursor” through each line in turn. When it stops at any line, we can use methods to read each data cell of that line.

To read data from a cell you can use the Get methods for the corresponding data type. The parameter of this method is the ordinal number of the cell in the row. In the above example we used this method. In addition, you can also use other ways to read the data in each cell in each line:

Using the indexer operation and the cell number on the object of SqlDataReader:

var id = (int) sqlDataReader[0];

var contactName = sqlDataReader[1] as string;

var alias = sqlDataReader[2] as string;

Using field names in indexer operations:

var id = (int) sqlDataReader[“Id”];

var contactName = sqlDataReader[“ContactName”] as string;

var alias = sqlDataReader[“Alias”] as string;

It should be noted that, when reading data from SqlDataReader, it is necessary to keep the connection open. Only when finished reading the data will close the connection. The reason is because the SqlDataReader does not contain a local copy of the data. It is just a tool to help read query results.

Execute query INSERT – UPDATE – DELETE

Let’s take a look at how to perform three types of INSERT – UPDATE – DELETE queries.

Step 1. Create more project P03_InsertUpdateDelete (Console App) in solution.

Step 2. Write the following code:

using System;

using System.Data.SqlClient;

namespace P03_InsertUpdateDelete

{

class Program

{

static void Main(string[] args)

{

Console.Title = “Insert – Update – Delete”;

var connectionString = @”Data Source=.;Initial Catalog=Contacts;Integrated Security=True”;

using (var connection = new SqlConnection(connectionString))

using (var command = new SqlCommand { Connection = connection })

{

connection.Open();

Console.WriteLine(“Before inserting:”);

Retrieve(command);

var insertQuery = “INSERT INTO [dbo].[Contacts] ([ContactName], [Alias], [FirstName], [LastName], [DateOfBirth]) VALUES (N’George Bush’, N’bush’, N’Bush ‘, N’George’, N’2019-06-26 12:21:29′)”;

command.CommandText = insertQuery;

var count = command.ExecuteNonQuery();

Console.WriteLine($”rn{count} record inserted!”);

Console.WriteLine(“rnAfter inserting:”);

Retrieve(command);

var updateQuery = “UPDATE [dbo].[Contacts] SET [ContactName] = ‘George Jr. Bush’ WHERE [ContactName] like ‘%Bush%'”;

command.CommandText = updateQuery;

count = command.ExecuteNonQuery();

Console.WriteLine($”rn{count} record updated!”);

Console.WriteLine(“rnAfter updating:”);

Retrieve(command);

var deleteQuery = “DELETE FROM [dbo].[Contacts] WHERE [ContactName] like ‘%Bush%'”;

command.CommandText = deleteQuery;

count = command.ExecuteNonQuery();

Console.WriteLine($”rn{count} record deleted!”);

Console.WriteLine(“rnAfter deleting:”);

Retrieve(command);

}

Console.ReadLine();

}

static void Retrieve(SqlCommand command)

{

command.CommandText = “SELECT * FROM CONTACTS”;

var sqlDataReader = command.ExecuteReader();

if (sqlDataReader.HasRows)

{

while (sqlDataReader.Read())

{

var id = (int)sqlDataReader[“Id”];

var contactName = sqlDataReader[“ContactName”] as string;

var alias = sqlDataReader[“Alias”] as string;

Console.WriteLine($”[{id}] {contactName} ({alias})”);

}

}

sqlDataReader.Close();

}

}

}

Compile and run the program to get the following output:

how to execute sql query in c#

As is known to perform these types of queries we use the ExecuteNonQuery() method of SqlCommand. This method returns the number of rows affected by the corresponding query.

Parameters in SQL query, SqlParameter

Problem generating queries from user data

In the examples above you already know how to execute basic SQL queries in C#. However, these queries are all “static”, “hard-coded”. So how does INSERT a new record into the database whose values are entered by the user?

Surely you can think of a solution right away. Because the SQL query is actually just a text string (string type). Then let the user enter the data. You will use string formatting techniques to generate queries from user input data. For example:

var contactName = Console.ReadLine();

var fistName = Console.ReadLine();

var lastName = Console.ReadLine();

var commandText = $”INSERT INTO [dbo].[Contacts] ([ContactName], [FirstName], [LastName]) VALUES (‘{contactName}’, ‘{firstName}’, ‘{lastName}’)”;

// the remaining operations are ignored

Simple to understand but this is a GOOD solution. Why?

Anything the user enters via variables will be placed in your SQL query. Even another query string can be inserted into your query string. This practice leads to a very common security flaw: SQL Injection.

Therefore, you should NEVER use this method to generate query strings. ADO.NET provides a solution: use Parameter.

SqlParameter class – illustrative example

To make it easier to imagine, let’s do an example:

using System;

using System.Data.SqlClient;

using System.Data;

namespace P04_Parameter

{

class Program

{

static void Main(string[] args)

{

Console.Title = “Parameters”;

var connectionString = @”Data Source=.;Initial Catalog=Contacts;Integrated Security=True”;

while (true)

{

Console.WriteLine(“Create new contact:”);

Console.Write(“Contact name: “);

var contactName = Console.ReadLine();

Console.Write(“First name: “);

var firstName = Console.ReadLine();

Console.Write(“Last name: “);

var lastName = Console.ReadLine();

// Write a normal query string, however, where you need a parameter, write @ before the parameter name

var query = “INSERT INTO [dbo].[Contacts] ([ContactName], [FirstName], [LastName], [DateOfBirth]) VALUES (@ContactName, @FirstName, @LastName, @DoB)”;

// Create SqlParameter objects to hold parameters. There are different overloads of constructors

// Note that the parameter name of the object must match the name used in the query

var contactNameParam = new SqlParameter(“ContactName”, contactName);

var firstNameParam = new SqlParameter(“FirstName”, firstName);

// or use initialization using property

var birthDayParam = new SqlParameter

{

DbType = DbType.Date,

ParameterName = “DoB”,

Value = DateTime.Now

};

using (var connection = new SqlConnection(connectionString))

using (var command = new SqlCommand { Connection = connection })

{

command.CommandText = query;

// add each SqlParameter object in turn to the command’s Parameters list

command.Parameters.Add(contactNameParam);

command.Parameters.Add(firstNameParam);

command.Parameters.Add(birthDayParam);

// can even add parameter-value pairs directly this way without declaring separate object

command.Parameters.AddWithValue(“LastName”, lastName);

connection.Open();

var count = command.ExecuteNonQuery();

Console.WriteLine($”{count} contact inserted!”);

Retrieve(command);

}

Console.ReadKey();

}

}

static void Retrieve(SqlCommand command)

{

command.CommandText = “SELECT * FROM CONTACTS”;

var sqlDataReader = command.ExecuteReader();

if (sqlDataReader.HasRows)

{

while (sqlDataReader.Read())

{

var id = (int)sqlDataReader[“Id”];

var contactName = sqlDataReader[“ContactName”] as string;

var alias = sqlDataReader[“Alias”] as string;

Console.WriteLine($”[{id}] {contactName} ({alias})”);

}

}

sqlDataReader.Close();

}

}

}

Translate and run the program:

how to execute sql query in c#

Using SqlParameter

Through the above example, it can be seen that using parameters in a Sql query with SqlParameter is very simple:

In the query set parameters where necessary; parameter starts with @ character; The @<parameter name> assembly is called the placeholder.

Declare SqlParameter objects and assign values to them; as many parameters, create as many objects; The ParameterName parameter must match the parameter in the query string.

Assign the created SqlParameter objects to the Parameters property of the SqlCommand object.

Now anything put into a parameter will be treated as data. SqlCommand will put the correct value in the place of the parameter for us and will do the appropriate processing to make the query safe.

The way to write the placeholder for the parameter is slightly different between providers. Sql Server uses the @ character, Oracle – colon :, OLEDB and ODBC use question mark ?.

Handling Exceptions

While executing SQL queries in C#, it is possible to encounter exceptions such as network errors, syntax errors, and permission errors. Therefore, it is essential to handle exceptions properly to prevent unexpected program termination and ensure that the user is informed of any errors that may occur.

The Try-Catch block is a commonly used method to handle exceptions in C#. The Try block contains the code that may throw an exception, while the Catch block handles the exception if it occurs.

For example, if an exception occurs during the connection process, the Catch block can be used to display an error message to the user and provide instructions on how to resolve the issue.

try
{
// code that may throw an exception
}
catch(Exception ex)
{
Console.WriteLine(“An error occurred: ” + ex.Message);
// provide instructions on how to resolve the issue
}

It is essential to catch specific exceptions to ensure that they are handled appropriately. For example, if a syntax error occurs in the SQL query, you can catch the SqlException and display a more detailed error message to the user.

try
{
// code that may throw an exception
}
catch(SqlException ex)
{
Console.WriteLine(“A SQL error occurred: ” + ex.Message);
// provide instructions on how to resolve the issue
}

You can also use the Finally block to perform any cleanup operations that need to be done, such as closing the database connection.

try
{
// code that may throw an exception
}
catch(Exception ex)
{
Console.WriteLine(“An error occurred: ” + ex.Message);
// provide instructions on how to resolve the issue
}
finally
{
connection.Close();
}

In summary, handling exceptions is essential when executing SQL queries in C#. By using the Try-Catch block and catching specific exceptions, you can ensure that any errors are handled appropriately and prevent unexpected program termination. The Finally block can also be used to perform any cleanup operations that need to be done.

Conclusion

This lesson has helped you to understand how to execute Sql query in C# and the technique of reading data from the results of the queries. You should also keep in mind that the techniques we look at here are more illustrative than practical. The lessons in this section are intended to help you understand the principles of programming with databases from C#, instead of how to apply them.

This is the last lesson of ADO.NET programming content.

If you remember the article about ADO.NET architecture, you will wonder why not talk about DataAdapter, DataSet, DataTable, i.e. disconnected components of ADO.NET. The reason is that Microsoft itself has recommended not to continue using DataSet and related classes because they are considered old technology. Instead, use Entity Framework – the main content of this lecture series.

If so then why still learn SqlConnection, SqlCommand, SqlParameter, SqlDataReader? The reason is that the Entity Framework builds on these components of ADO.NET. Entity Framework itself must use these components to work with the database.

Leave a Reply

Related Posts

  • c# Exception Tostring vs Message – Complete Guide

  • c# Yield Exception Handling – Explained!

  • c# Excel Error 0x800a03ec – Complete Guide

  • c# get Error Message from Modelstate – Explained & Solved

  • c# Error Netsdk1005 – Solved!

  • c# Error Parsing Infinity Value – Explained & Solved!