SqlCommand class can be used in various ways to access database data and it’s true story that many developers are somehow confused in which one to choose each time the want to execute queries in the SQL Server. In this post we will try to make this clear and show the best practices to access your database using one of the following commands:

Let’s start. Create a simple Console Application project and open the Program.cs file. Make sure you add using statements for the following namespaces. These are all you need to execute queries using the SqlCommand class.

using System.Data;
using System.Data.SqlClient;

Let us first create a Database with a simple table, so we can run our queries. Create a database named SchoolDB and add a simple table as follow:

Make sure not to mark the StudentID column as an Identity.

SqlDataAdapter.Fill

SqlDataAdapter class is used to retrieve a dataset from the database. That is when you want to get multiple results from your database you can make use of SqlDataAdapter.Fill(DataSet ds) function. In the select query you can have as many SELECT statements you want. If you do so, the first SELECT result set will fill the first DataSet’s table, the Second SELECT statement the second table and so on.. For the simplicity of this post, first we will add some records (students) to the Students table and then we will create a stored procedure to retrieve them. In the C# code, we’ ll use the SqlDataAdapter to fill a DataSet with all the Students in the SchoolDB.

INSERT INTO dbo.Students (StudentID, StudentName, StudentAge) VALUES (1,'Chris S.',28)
INSERT INTO dbo.Students (StudentID, StudentName, StudentAge) VALUES (2,'Catherin',23)
INSERT INTO dbo.Students (StudentID, StudentName, StudentAge) VALUES (3,'Nick',30)
INSERT INTO dbo.Students (StudentID, StudentName, StudentAge) VALUES (4,'Maria',40)

CREATE PROCEDURE GetStudents
AS
BEGIN
   SELECT * FROM dbo.Students
END

Add a GetStudents function in the Program.cs file as follow and run in through the main method. Also, make sure you define your connection string that points to your SQL Server.

const string sqlConnectionString = "Data Source=localhost;Initial Catalog=SchoolDB;Integrated Security=SSPI;";

// Retrieve multiple records using an SqlDataAdapter
        public static DataSet GetStudents()
        {
            DataSet dsStudents = new DataSet();

            using (SqlConnection con = new SqlConnection(sqlConnectionString))
            {
                SqlCommand cmd = new SqlCommand("GetStudents", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                try
                {
                    da.Fill(dsStudents);
                    dsStudents.Tables[0].TableName = "Students";
                }
                catch (Exception)
                {
                    return dsStudents;
                }
            }
            return dsStudents;
        }

ExecuteScalar

ExecuteScalar function is the best option to figure out the result of your executed query in the database. Let me explain it a little further. What I meant is that you use the ExecuteScalar command when you don’t care to read or retrieve a result set from your dabase but you expect a code description of what actually happened. Let’s see it on action now. Create another stored procedure named AddStudent in order to add Student records in the respective table. This procedure though has some limitations.

CREATE PROCEDURE [dbo].[AddStudent]
@studentID int,
@studentName nvarchar(50),
@studentAge int
AS
BEGIN
DECLARE @result int = 0;
DECLARE @tempID int;

SET @tempID = (SELECT StudentID FROM dbo.Students WHERE StudentID = @studentID)

IF @tempID IS NOT NULL 
	SET @result = -1 -- There is already a student with this ID
ELSE IF @studentAge < 5 OR @studentAge > 120 
	SET @result = -2 -- Invalid Age number
ELSE
	INSERT INTO dbo.Students VALUES (@studentID, @studentName, @studentID)
SELECT @result ResultCode
END

Add the AddStudent method into the Program.cs file as follow.

// Add student using the execute scalar in order to get the result code
        public static int AddStudent(int id, string name, int age)
        {
            int resultCode;

            using (SqlConnection con = new SqlConnection(sqlConnectionString))
            {
                SqlCommand cmd = new SqlCommand("AddStudent", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@studentID", DbType.Int32).Value = id;
                cmd.Parameters.Add("@studentName", DbType.String).Value = name;
                cmd.Parameters.Add("@studentAge", DbType.Int32).Value = age;
                try
                {
                    con.Open();
                    resultCode = (int)cmd.ExecuteScalar();
                    con.Close();
                }
                catch (Exception)
                {
                    return -10; // unknown error
                }
            }
            return resultCode;
        }

In the main method..

// ExecuteScalar
int resultCode = AddStudent(1, "Chris", 28); // Should get -1 (already record with this ID)
int resultCode2 = AddStudent(5, "Smith", 130); // Should get -2 (Invalid age number)
int resultCode3 = AddStudent(5, "Jason", 21); // Should get 0 (Success)

ExecuteNonQuery

This function is a very good choice when you want to know how many rows your query has affected. You can use it while inserting, updating or deleting records. For this example let’s create a stored procedure to update a student’s name based on it’s StudentID.

CREATE PROCEDURE [dbo].[UpdateStudentsName]
@studentID int,
@studentNewName nvarchar(50)
AS
BEGIN
   UPDATE dbo.Students SET StudentName = @studentNewName WHERE StudentID = @studentID
END

In Program.cs add the UpdateStudent method and call it in the main method.

public static int UpdateStudent(int id, string newName)
        {
            int rowsAffected;

            using (SqlConnection con = new SqlConnection(sqlConnectionString))
            {
                SqlCommand cmd = new SqlCommand("UpdateStudentsName", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@studentID", DbType.Int32).Value = id;
                cmd.Parameters.Add("@studentNewName", DbType.String).Value = newName;
                try
                {
                    con.Open();
                    rowsAffected = cmd.ExecuteNonQuery();
                    con.Close();
                }
                catch (Exception)
                {
                    return 0; // unknown error
                }
            }
            return rowsAffected;
        }

// ExecuteNonQuery
int row1 = UpdateStudent(1, "Chris Sak."); // Should return 1 // Row found and updated
int row2 = UpdateStudent(30, "Helen K."); // Should return 0 // Row not found

Batch Queries using SqlTransaction

To be honest, most of the times you will want to execute more complex queries than the above but you got the main idea. One of the best practices when you want to execute multiple (batch) queries against your database, is to encapsulated them in a single transaction. Let’s see how easy is to handle the results of each of the query and rollback the transaction in case you have unexpected results. In the following method we try to add 4 Students in the table but the 4th insert should fail and cause the transaction to rollback.

// Use SqlCommands in a Transaction
        public static bool ExecuteBatchQueries(string conStr)
        {
            bool transactionExecuted = false;

            List<Student> studentList = new List<Student>();
            Student s1 = new Student { ID = 10, Name = "John", Age = 34 };
            Student s2 = new Student { ID = 11, Name = "Helen", Age = 23 };
            Student s3 = new Student { ID = 12, Name = "Mary", Age = 54 };
            Student s4 = new Student { ID = 10, Name = "Christiano", Age = 31 };

            studentList.Add(s1); studentList.Add(s2); studentList.Add(s3); studentList.Add(s4);

            using (var con = new SqlConnection(conStr))
            {
                SqlTransaction trans = null;
                try
                {
                    con.Open();
                    trans = con.BeginTransaction();
                    foreach (Student student in studentList)
                    {
                        SqlCommand cmd = new SqlCommand("AddStudent", con,trans);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@studentID", DbType.Int32).Value = student.ID;
                        cmd.Parameters.Add("@studentName", DbType.String).Value = student.Name;
                        cmd.Parameters.Add("@studentAge", DbType.Int32).Value = student.Age;
                        int resultCode = (int)cmd.ExecuteScalar();
                        if (resultCode != 0)
                        {
                            trans.Rollback();
                            return transactionExecuted; // false
                        }
                    }
                    trans.Commit();
                    transactionExecuted = true;
                    con.Close();
                }
                catch (Exception Ex)
                {
                    if (trans != null) trans.Rollback();
                    return false;
                }
                return transactionExecuted;
            }

        }

public class Student
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

In the main:

// Compininations in SqlTransaction : Very helpfull!!
            if(ExecuteBatchQueries(sqlConnectionString))
            {
                Console.WriteLine("Transaction completed");
            }
            else
            {
                Console.WriteLine("Transaction failed to complete");
            }

That’s it, we saw when and how to use the different functions the SqlCommand class offers. I hope you have enjoyed the post. Download the project we created and the database as well from here.

Categories: ADO.NET

Tags: ExecuteNonQuery, ExecuteScalar, SqlCommand, SqlDataAdapter, SqlTransaction