SqlCommand: The Entity Framework alternative to access databases

SqlCommand is the alternative way to query, insert, update or delete database data. We have seen in lot’s of other posts on this blog, how to execute CRUD operations on a database using the Entity Framework and more specifically an instance of the DbContext class. We have also shown how to map stored procedures in an entity, a post you can read here. Mind though that if you want to know exactly what is executed in the database using the Entity Framework, you have to be a guru on that area. Entity Framework may simplify data accessing but as always what gained in simplicity is loosed in performance. For example when you query an entity to view some of it’s’ properties, EF may (due to it’s default configuration) fill other properties related to this entity even though you didn’t ask for (lazy loading). But what if you only wanted some of those? The alternative solution is to use classes such as SqlConnection and SqlCommand. You can pass exactly the query you want to be executed in database level, either this is a CRUD statement, a View or even a Stored Procedure. Here’s what we gonna see on this post:

  • CRUD operations using the SqlCommand
  • How to read scalar results with the SqlCommand
  • How to read table results with the SqlCommand
  • How to execute Store Procedure with the SqlCommand
  • How to query a View with the SqlCommand
  • How to invoke a User Defined Function with the SqlCommand

Let’s start. We will use the Chinook database which is a simple but quite useful database for testing. You can download it for free here, otherwise you can find and execute the SQL script inside the App_Data of the project we will build (check download link at the bottom).
sqlcommand_01
Make sure you have installed this database in your SQL Server instance and create a C# Console application in your Visual Studio. Add the following two using statements in the Program.cs file:

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

Those are the only assemblies you need to start querying data with the SqlCommand class. Also inside the Program class create a variable to hold your sql connection string. You will use it every time you need to open a connection to your database.

private const string sqlConnString = @"Data Source=localhost; Initial Catalog=Chinook; 
              Integrated Security=True; MultipleActiveResultSets=True;";

Make the respective modification to reflect your sql server instance.

Selecting Data

Let’s start with the Select statements. Assuming you want to retrieve all Album table records you would write something like this:

protected static void SelectStatement_01()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM Album", con);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader[0] + " " + reader[1] + " " + reader[2]);
                }
            }
        }

You have to create a SqlConnection object and open the connection through it. Then you create an SqlCommand where you pass exactly the SQL query you want to execute in your database pointed from you connection string. Since your SQL query returns multiple results you retrieve them using a SqlDataReader object. You can iterate through it’s records using a while statement. The ninth line of the above code could be replaced with the following, showing that each index corresponds to the column name retrieved from your database.

Console.WriteLine(reader["AlbumId"] + " " + reader["Title"] + " " + reader["ArtistId"]);

You can execute any Select statement you want using the SqlCommand. Hence, if you want to filter your results and get specific albums (let’s say those that belongs to artist with ArtistId=1) you would write something like this.

protected static void SelectStatement_02()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT * FROM Album WHERE ArtistId=1", con);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader["Title"]);
                }
            }
        }

If you wish to query a column value with different name that’s fine. The SqlDataReader object will understand exactly what you want.

protected static void SelectStatement_03()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand("SELECT AlbumId AS 'ID', Title AS 'Album' FROM Album WHERE ArtistId=1", con);
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader["ID"] + " " + reader["Album"]);
                }
            }
        }

Here we changed the AlbumId column name to ID and the Title to Album. Later we retrieved those values through the reader[“ID”] and reader[“Album”] respectively. Now let’s try something different. Let’s query a scalar result, an integer for example from the database. Assuming we want to get the total Invoices for a customer we would write..

protected static void SelectStatement_04()
        {
            decimal Total = 0m;
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();

                SqlCommand cmd = new SqlCommand(@"SELECT SUM(Total) AS Total
                    FROM Invoice Group By CustomerId Having CustomerId=1", con);
                Total = (decimal)(cmd.ExecuteScalar());
                Console.WriteLine("Customer ID:1 has {0} Total Invoices", Total);
            }
        }

SqlCommand.ExecuteScalar function returns the first column of the first row of the result set. You often use it when we want to retrive a specific value, not a record.
Let’s try to query a View result now. Create the following View in your Chinook database in order to get all Album Tracks.

create view [dbo].[vw_GetAlbumTracks]
AS
select Name,Composer from Track

If you want to print the TOP 10 of those tracks you can write:

protected static void ViewStatement_05()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = @"SELECT TOP 10 * from vw_GetAlbumTracks";
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand(query, con);
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        Console.WriteLine("Track: \t Composer");
                        while (reader.Read())
                        {
                            Console.WriteLine(reader[0] + "\t" + reader[1]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
        }

Since you request a result set you use the ExecuteReader function, not the ExecuteScalar. Executing a Stored Procedure with the SqlCommand object is slightly different. You have to declare that the command is of type CommandType.StoredProcedure and then define the parameters and their values. Let’s create a stored procedure that returns all tracks of an album. It accepts two parameters, the album title and an output parameter that will have the number of tracks retrieved.

CREATE PROCEDURE [dbo].[GetAlbumTracks]
(
@AlbumTitle nvarchar(50),
@TotalTracks int output
)
AS
declare @AlbumId int
set @AlbumId = (select AlbumId From Album where Title=@AlbumTitle)
select Name,Composer,UnitPrice 
from Track
where AlbumId=@AlbumId
set @TotalTracks = @@ROWCOUNT

Let’s count and print the “Restless and Wild” album tracks.

protected static void StoredProcedure_06()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = "dbo.GetAlbumTracks";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AlbumTitle", SqlDbType.NVarChar);
                cmd.Parameters["@AlbumTitle"].Value = "Restless and Wild";
                SqlParameter outputParam = new SqlParameter("@TotalTracks", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                cmd.Parameters.Add(outputParam);
                try
                {
                    con.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(reader[0] + "\t" + reader[1] + " " + reader[2]);
                            }
                        }
                    }
                    Console.WriteLine(outputParam.Value);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }

            }
        }

First of all we changed the CommandType to StoredProcedure. We also defined an output parameter with the Direction = ParameterDirection.Output. Mind that you can use it’s new value after the SqlDataReader using statement. Normally, we use User Defined Functions inside a Store Procedure but anyway, let’s see how to query both scalar and multi-statement results from a User Defined Function. The following function returns the total invoices for a customer.

CREATE function [dbo].[fn_GetCustomerTotalInvoices]
(
@LastName nvarchar(20)
)
returns int
AS
begin
Declare @TotalInvoices int
declare @CustomerId nvarchar(20)
select @CustomerId = (select CustomerId from Customer where LastName=@LastName)
if (@CustomerId is null)
begin
 RETURN -1
end
else
select @TotalInvoices = (select count(CustomerId) from Invoice group by CustomerId having CustomerId=@CustomerId)
return @TotalInvoices

end

You can call that function in the way you called the view.

protected static void UserDefinedFunction_07(string lastName)
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();
                string query = "select dbo.fn_GetCustomerTotalInvoices('" + lastName + "')";
                SqlCommand cmd = new SqlCommand(query, con);
                int totalInvoices = (Int32)cmd.ExecuteScalar();
                if (totalInvoices != -1)
                {
                    Console.WriteLine("There are {0} invoices for {1}", totalInvoices, lastName);
                }
                else
                {
                    Console.WriteLine("There is no such customer in db..");
                }
            }
        }

The following function returns a table. More specifically returns a customer invoices.

CREATE function [dbo].[fn_GetCustomerInvoices]
(
@LastName nvarchar(20)
)
returns @Table table(ID int,InvoiceDate date,Total int)
as
begin
declare @CustomerId int
set @CustomerId = (select CustomerId from Customer where LastName=@LastName)
insert into @Table
select InvoiceId, InvoiceDate, Total
from Invoice
where CustomerId=@CustomerId
return
end

You can view them like this.

protected static void UserDefinedFunction_08(string lastName)
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                con.Open();
                string query = "select * from dbo.fn_GetCustomerInvoices('" + lastName + "')";
                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataReader reader = cmd.ExecuteReader();
                int invoices=0;
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        invoices++;
                        Console.WriteLine("Invoice---- ID {0}, Date {1}, Total {2}",
                            reader[0], reader[1], reader[2]);
                    }
                    Console.WriteLine("Total invoices {0}",invoices);
                }
            }
        }

Inserting Data

You can pass an Insert SQL statement to the SqlCommand as well. Add and configure any parameters needed. Assume you want to add an Album record.

protected static void InsertStatement_01()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = @"insert into Album (Title,ArtistId) values (@Title,@ArtistId)
                                 select CAST(scope_identity() AS int)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar);
                cmd.Parameters["@Title"].Value = "ASP.NET Programming";
                cmd.Parameters.Add("@ArtistId", SqlDbType.Int);
                cmd.Parameters["@ArtistId"].Value = 1;
                try
                {
                    con.Open();
                    int Id = (Int32)cmd.ExecuteScalar();
                    Console.WriteLine("New album inserted with ID:" + Id);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }

            }
        }

Since we wanted to get back the new AlbumId generated we added the “select CAST(scope_identity() AS int)” statement. Let’s try something interesting now. Assume that you wanted to get back not only the generated Id but the Title and the ArtistId too.

protected static void InsertStatement_02()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = @"insert into Album (Title,ArtistId)
                                output inserted.AlbumId, inserted.Title, inserted.ArtistId
                                values (@Title,@ArtistId)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar);
                cmd.Parameters["@Title"].Value = "ASP.NET Programming";
                cmd.Parameters.Add("@ArtistId", SqlDbType.Int);
                cmd.Parameters["@ArtistId"].Value = 1;
                try
                {
                    con.Open();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("New album inserted with ID:{0}, Title: {1} and ArtistId : {2}",
                            reader[0], reader[1], reader[2]);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }

            }
        }

We used the output inserted.AlbumId, inserted.Title, inserted.ArtistId Insert properties and an SqlDataReader to access them. Let’s insert a new album now using the following stored procedure.

CREATE procedure [dbo].[InsertAlbum]
(
@Title nvarchar(160),
@ArtistId int = null
)
as
if @ArtistId is null
begin
	Raiserror('ArtistId cannot be null Dude!',16,1)
	return
end
insert into Album (Title,ArtistId) Values (@Title,@ArtistId)
select CAST(IDENT_CURRENT( 'Album' ) AS int)

Execute this stored procedure as follow:

protected static void InsertStoredProcedure_03()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = "dbo.InsertAlbum";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar);
                cmd.Parameters["@Title"].Value = "ASP.NET Programming";
                cmd.Parameters.Add("@ArtistId", SqlDbType.Int);
                cmd.Parameters["@ArtistId"].Value = 1;
                try
                {
                    con.Open();
                    int albumId = (Int32)cmd.ExecuteScalar();
                    Console.WriteLine("Album with ID {0} added", albumId);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
        }

Updating Data

Updating data is pretty much the same. Let’s assume we want to update an album’s title, and get back both the old and it’s new title. Let’s try it without a stored procedure.

protected static void UpdateStatement_01()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = @"update Album Set Title='.NET Programing' 
                                output deleted.Title, inserted.Title
                                where Title='.NET Programingf'";
                try
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand(query, con);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("Title was updated from {0} to {1}", reader[0], reader[1]);
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
        }

Create a Stored procedure that updated an Employee’s record values.

CREATE procedure [dbo].[UpdateEmployee]
(@EmployeeId int,
@LastName nvarchar(20),
@FirstName nvarchar(20),
@HireDate datetime,
@Address nvarchar(70))
as
begin
begin try
	begin transaction
		update Employee 
		set LastName=@LastName, FirstName=@FirstName,
							HireDate=cast(@HireDate as datetime),
							Address=@Address
		where EmployeeId=@EmployeeId 
		if @@rowcount = 0
		begin
			Raiserror('Employee cannot be updated..',16,1)
		end
		commit transaction
		end try
		begin catch
		begin
			rollback transaction
			Raiserror('Employee cannot be updated..',16,1)
	    end
		end catch
end

Execute it as follow. Notice that this time we only want to know if a row affected that is updated or not and that’s why we use the SqlCommand.ExecuteNonQuery() function.

protected static void UpdateStoredProcedure_02()
        {
            using (SqlConnection con = new SqlConnection(sqlConnString))
            {
                string query = "dbo.UpdateEmployee";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@EmployeeId", SqlDbType.Int);
                cmd.Parameters["@EmployeeId"].Value = 0;
                cmd.Parameters.Add("@HireDate", SqlDbType.DateTime);
                cmd.Parameters["@HireDate"].Value = DateTime.Now.AddYears(3);
                cmd.Parameters.Add("@LastName", SqlDbType.NVarChar);
                cmd.Parameters["@LastName"].Value = "Adams";
                cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar);
                cmd.Parameters["@FirstName"].Value = "Ken";
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar);
                cmd.Parameters["@Address"].Value = "Athens";

                try
                {
                    con.Open();
                    int employeeUpdated = cmd.ExecuteNonQuery();
                    Console.WriteLine("{0} Employee updated",employeeUpdated);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Exception: " + ex.Message);
                }
            }
        }

That’s it, I hope you understood how to use an SqlCommand object to access your database. Download the project we created from here. Make sure you follow this blog to get notified for new posts!

Advertisements


Categories: ADO.NET

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Diary Of A Programmer

Because every day is worth noting

Chara Plessa

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

%d bloggers like this: