Lesson 03: The SqlCommand Object
This lesson describes the SqlCommand object and how you use it to interact with
a database. Here are the objectives of this lesson:
-
Know what a command object is.
-
Learn how to use the ExecuteReader method to query data.
-
Learn how to use the ExecuteNonQuery method to insert and delete data.
-
Learn how to use the ExecuteScalar method to return a single value.
Introduction
A SqlCommand object allows you to specify what type of interaction you want to
perform with a database. For example, you can do select, insert, modify,
and delete commands on rows of data in a database table. The SqlCommand
object can be used to support disconnected data management scenarios, but
in this lesson we will only use the SqlCommand object alone. A later
lesson on the SqlDataAdapter will explain how to implement an application that
uses disconnected data. This lesson will also show you how to retrieve a
single value from a database, such as the number of records in a table.
Creating a SqlCommand Object
Similar to other C# objects, you instantiate a SqlCommand object via the new
instance declaration, as follows:
SqlCommand cmd = new SqlCommand("select
CategoryName from Categories", conn);
The line above is typical for instantiating a SqlCommand object. It takes
a string parameter that holds the command you want to execute and a reference
to a SqlConnection object. SqlCommand has a few overloads, which you will
see in the examples of this tutorial.
Querying Data
When using a SQL select command, you retrieve a data set for
viewing. To accomplish this with a SqlCommand object, you would use
the ExecuteReader method, which returns a SqlDataReader object. We'll
discuss the SqlDataReader in a future lesson. The example below shows how
to use the SqlCommand object to obtain a SqlDataReader object:
// 1. Instantiate a new command with a query
and connection
SqlCommand cmd = new
SqlCommand("select CategoryName from Categories", conn);
// 2. Call Execute reader to get query
results
SqlDataReader rdr = cmd.ExecuteReader();
In the example above, we instantiate a SqlCommand object, passing the command
string and connection object to the constructor. Then we obtain a
SqlDataReader object by calling the ExecuteReader method of the SqlCommand
object, cmd.
This code is part of the ReadData method of Listing 1 in the Putting it All
Together section later in this lesson.
Inserting Data
// prepare command string
This code is part of the Insertdata method of Listing 1 in the Putting it All
// prepare command string
This code is part of the UpdateData method of Listing 1 in the Putting it All
// prepare command string
This code is part of the DeleteData method of Listing 1 in the Putting it All
// 1. Instantiate a new command
This code is part of the GetNumberOfRecords method of Listing 1 in the Putting
To insert data into a database, use the ExecuteNonQuery method of the
SqlCommand object. The following code shows how to insert data into a
database table:
string insertString
= @"
insert into Categories
(CategoryName, Description)
values ('Miscellaneous', 'Whatever doesn''t fit
elsewhere')";
// 1. Instantiate a new command
with a query and connection
SqlCommand cmd =
new SqlCommand(insertString, conn);
// 2. Call ExecuteNonQuery to send
command
cmd.ExecuteNonQuery();
The SqlCommand instantiation is just a little different from what you've seen
before, but it is basically the same. Instead of a literal string as the
first parameter of the SqlCommand constructor, we are using a variable,
insertString. The insertString variable is declared just above the
SqlCommand declaration.
Notice the two apostrophes ('') in the insertString text for the word
"doesn''t". This is how you escape the apostrophe to get the string to
populate column properly.
Another observation to make about the insert command is that we explicitly
specified the columns CategoryName and Description. The Categories table
has a primary key field named CategoryID. We left this out of the list
because SQL Server will add this field itself. trying to add a value to a
primary key field, such as CategoryID, will generate an exception.
To execute this command, we simply call the ExecuteNonQuery method on the
SqlCommand instance, cmd.
Together section later in this lesson.
Updating Data
The ExecuteNonQuery method is also used for updating data. The following
code shows how to update data:
string updateString = @"
update Categories
set CategoryName = 'Other'
where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
Again, we put the SQL command into a string variable, but this time we used a
different SqlCommand constructor that takes only the command. In step 2,
we assign the SqlConnection object, conn, to the Connection property of the
SqlCommand object, cmd.
This could have been done with the same constructor used for the insert
command, with two parameters. It demonstrates that you can change the
connection object assigned to a command at any time.
The ExecuteNonQuery method performs the update command.
Together section later in this lesson.
Deleting Data
You can also delete data using the ExecuteNonQuery method. The following
example shows how to delete a record from a database with the ExecuteNonQuery
method:
string deleteString = @"
delete from Categories
where CategoryName = 'Other'";
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand();
// 2. Set the CommandText property
cmd.CommandText = deleteString;
// 3. Set the Connection property
cmd.Connection = conn;
// 4. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
This example uses the SqlCommand constructor with no parameters. Instead,
it explicity sets the CommandText and Connection properties of the SqlCommand
object, cmd.
We could have also used either of the two previous SqlCommand constructor
overloads, used for the insert or update command, with the same result.
This demonstrates that you can change both the command text and the connection
object at any time.
The ExecuteNonQuery method call sends the command to the database.
Together section later in this lesson.
Getting Single values
Sometimes all you need from a database is a single value, which could be a
count, sum, average, or other aggregated value from a data set.
Performing an ExecuteReader and calculating the result in your code is not the
most efficient way to do this. The best choice is to let the database
perform the work and return just the single value you need. The following
example shows how to do this with the ExecuteScalar method:
SqlCommand cmd = new SqlCommand("select
count(*) from Categories", conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
The query in the SqlCommand constructor obtains the count of all records from
the Categories table. This query will only return a single value.
The ExecuteScalar method in step 2 returns this value. Since the return
type of ExecuteScalar is type object, we use a cast operator to convert the
value to int.
it All Together section later in this lesson.
Putting it All Together
For simplicity, we showed snippets of code in previous sections to demonstrate
the applicable techniques
code is used in a working program. Listing 1 shows all of the code used
in this example, along with a driver in the Main method to produce formatted
output.
Listing 1. SqlConnection Demo
using
System;
using System.Data;
using System.Data.SqlClient;
///
<summary>
/// Demonstrates how to work with
SqlCommand objects
///
</summary>
class SqlCommandDemo
{
SqlConnection conn;
public SqlCommandDemo()
{
// Instantiate the connection
conn = new SqlConnection(
"Data
Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI");
}
// call methods that demo SqlCommand capabilities
static void Main()
{
SqlCommandDemo
scd = new SqlCommandDemo();
Console.WriteLine();
Console.WriteLine("Categories
Before Insert");
Console.WriteLine("------------------------");
// use ExecuteReader method
scd.ReadData();
// use ExecuteNonQuery method for Insert
scd.Insertdata();
Console.WriteLine();
Console.WriteLine("Categories
After Insert");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Update
scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Categories
After Update");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteNonQuery method for Delete
scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Categories
After Delete");
Console.WriteLine("------------------------------");
scd.ReadData();
// use ExecuteScalar method
int numberOfRecords =
scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number
of Records: {0}", numberOfRecords);
}
///
<summary>
/// use ExecuteReader method
///
</summary>
public
void ReadData()
{
SqlDataReader rdr = null;
try
{
// Open the
connection
conn.Open();
// 1.
Instantiate a new command with a query and connection
SqlCommand cmd =
new SqlCommand("select CategoryName from Categories", conn);
// 2.
Call Execute reader to get query results
rdr = cmd.ExecuteReader();
//
print the CategoryName of each record
while
(rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
finally
{
// close the reader
if
(rdr != null)
{
rdr.Close();
}
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
<summary>
/// use ExecuteNonQuery method for Insert
///
</summary>
public void
Insertdata()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string insertString = @"
insert into
Categories
(CategoryName, Description)
values ('Miscellaneous', 'Whatever doesn''t fit
elsewhere')";
// 1. Instantiate a new command with a query
and connection
SqlCommand cmd = new SqlCommand(insertString,
conn);
// 2. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if
(conn != null)
{
conn.Close();
}
}
}
///
<summary>
/// use ExecuteNonQuery method for Update
///
</summary>
public
void UpdateData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string updateString
= @"
update Categories
set CategoryName = 'Other'
where CategoryName = 'Miscellaneous'";
// 1. Instantiate a new command with command
text only
SqlCommand cmd = new SqlCommand(updateString);
// 2. Set the Connection property
cmd.Connection = conn;
// 3. Call ExecuteNonQuery to send command
cmd.ExecuteNonQuery();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
///
<summary>
/// use ExecuteNonQuery method for Delete
///
</summary>
public
void DeleteData()
{
try
{
// Open the connection
conn.Open();
// prepare command string
string deleteString = @"
delete from Categories
size="2"> size="2"> where CategoryName = 'Other'";
size="2">
// 1. Instantiate a new command
SqlCommand
cmd = new SqlCommand();
size="2">// 2. Set the CommandText property
cmd.CommandText = deleteString;
size="2">// 3. Set the Connection property
size="2">
cmd.Connection = conn;
size="2">
// 4. Call ExecuteNonQuery to send command
size="2">
cmd.ExecuteNonQuery();
}
finally
{
size="2">// Close the connection
if (conn != null)
{
size="2"> conn.Close();
}
}
}
size="2">///
<summary>
size="2">/// use ExecuteScalar method
///
</summary>
size="2">///
<returns>number of records color="#808080" size="2"></returns>
public
int GetNumberOfRecords()
{
int count = -1;
try
{
size="2">// Open the connection
conn.Open();
size="2"> size="2">// 1. Instantiate a new command
size="2">
SqlCommand cmd = new
SqlCommand("select count(*) from Categories", conn);
size="2">
// 2. Call ExecuteScalar to send command
size="2">
count = (int)cmd.ExecuteScalar();
}
finally
size="2"> {
size="2">// Close the connection
if (conn != null)
{
size="2"> conn.Close();
}
}
return
count;
}
}
In Listing 1, the SqlConnection object is instantiated in the SqlCommandDemo structure.
This is okay because the object itself will be cleaned up when the CLR garbage collector
executes. What is important is that we close the connection when we are done
using it. This program opens the connection in a try block and closes it in
a finally block in each method.
The ReadData method displays the contents of the CategoryName column of the Categories
table. We use it several times in the Main method to show the current status
of the Categories table, which changes after each of the insert, update, and delete
commands. Because of this, it is convenient to reuse to show you the effects
after each method call.
Summary
A SqlCommand object allows you to query and send commands to a database. It
has methods that are specialized for different commands. The ExecuteReader
method returns a SqlDataReader object for viewing the results of a select query.
For insert, update, and delete SQL commands, you use the ExecuteNonQuery method.
If you only need a single aggregate value from a query, the ExecuteScalar is the
best choice.
I hope you enjoyed this lesson and welcome you to the next one in this series,
Lesson 04: Reading Data with the SqlDataReader.
Your feedback and constructive contributions are welcome. Please feel free
to contact me for feedback or comments you may have about this lesson.
0 Comments:
Post a Comment