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).
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.

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.

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.

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..

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.

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.

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.

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.

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!