9866550012
The SqlCommand Object
Objectives:
- 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. 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();
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.
Inserting Data
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:
// 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();
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.
This code is part of the Insertdata method of Listing 1 in the Putting it All 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:
// 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();
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.
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:
// prepare command string
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();
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.
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:
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteNonQuery to send command
int count = (int)cmd.ExecuteScalar();
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.
This code is part of the GetNumberOfRecords method.
Putting it All Together
For simplicity, we showed snippets of code in previous sections to demonstrate the
applicable techniques. It is also useful to have an entire code listing to see how
this code is used in a working program. All the code used in
this example, along with a driver in the Main method to produce formatted output.
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
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();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// use ExecuteScalar method
/// </summary>
/// <returns>number of records</returns>
public int GetNumberOfRecords()
{
int count = -1;
try
{
// Open the connection
conn.Open();
// 1. Instantiate a new command
SqlCommand cmd = new SqlCommand("select count(*) from Categories", conn);
// 2. Call ExecuteScalar to send command
count = (int)cmd.ExecuteScalar();
}
finally
{
// Close the connection
if (conn != null)
{
conn.Close();
}
}
return count;
}
}
No comments:
Post a Comment