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.
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!
Categories: ADO.NET
chsakell's Blog 
Leave a comment