Commands

 

Chapter 9 - Data Access with .NET
bySimon Robinsonet al.
Wrox Press 2002

  

I briefly touched on the idea of issuing commands against a database in the Using Database Connections section. A command is, in its simplest form, a string of text containing SQL statements that is to be issued to the database. A command could also be a stored procedure, or the name of a table that will return all columns and all rows from that table (in other words, a SELECT * -style clause).

A command can be constructed by passing the SQL clause as a parameter to the constructor of the SqlCommand class, as shown below:

 string source = "server=(local)\NetSDK;" +                 "uid=QSUser;pwd=QSPassword;" +                  "database=Northwind"; string select = "SELECT ContactName,CompanyName FROM Customers"; SqlConnection conn = new SqlConnection(source); conn.Open();   SqlCommand cmd = new SqlCommand(select, conn);   

The SqlCommand and OleDbCommand classes have a property called CommandType , which is used to define whether the command is a SQL clause, a call to a stored procedure, or a full table statement (which simply selects all columns and rows from a given table). The following table summarizes the CommandType enumeration:

CommandType	Example
Text (default)	String select = "SELECT ContactName FROM Customers"; SqlCommand cmd = new SqlCommand(select , conn);
StoredProcedure	SqlCommand cmd = new SqlCommand("CustOrderHist", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", "QUICK");
TableDirect	OleDbCommand cmd = new OleDbCommand("Categories", conn); cmd.CommandType = CommandType.TableDirect;

When executing a stored procedure, it may be necessary to pass parameters to that procedure. The example above sets the @CustomerID parameter directly, although there are other ways of setting the parameter value, which we will look at later in the chapter.

Important 

Note: The TableDirect command type is only valid for the OleDb provider an exception is thrown by the Sql provider if you attempt to use this command type with it.

Executing Commands

Once you have the command defined, you need to execute it. There are a number of ways to issue the statement, depending on what you expect to be returned (if anything) from that command. The SqlCommand and OleDbCommand classes provide the following execute methods :

• ExecuteNonQuery() Execute the command but do not return any output

• ExecuteReader() Execute the command and return a typed IDataReader

• ExecuteScalar() Execute the command and return a single value

In addition to the above methods, the SqlCommand class also exposes the following method

• ExecuteXmlReader() Execute the command, and return an XmlReader object, which can be used to traverse the XML fragment returned from the database.

The example code in this section can be found in the Chapter 09\01_ExecutingCommands subdirectory of the code download.

ExecuteNonQuery()

This method is commonly used for UPDATE , INSERT , or DELETE statements, where the only returned value is the number of records affected. This method can, however, return results if you call a stored procedure that has output parameters.

   using System;     using System.Data.SqlClient;     public class ExecuteNonQueryExample     {     public static void Main(string[] args)     {     string source = "server=(local)\NetSDK;" +     "uid=QSUser;pwd=QSPassword;" +     "database=Northwind";     string select = "UPDATE Customers " +     "SET ContactName = 'Bob' " +     "WHERE ContactName = 'Bill'";     SqlConnection  conn = new SqlConnection(source);     conn.Open();     SqlCommand cmd = new SqlCommand(select, conn);     int rowsReturned = cmd.ExecuteNonQuery();     Console.WriteLine("{0} rows returned.", rowsReturned);     conn.Close();     }     }   

ExecuteNonQuery() returns the number of rows affected by the command as an int .

ExecuteReader()

This method executes the command and returns a SqlDataReader or OleDbDataReader object, depending on the provider in use. The object returned can be used to iterate through the record(s) returned, as shown in the following code:

 using System; using System.Data.SqlClient;   public class ExecuteReaderExample   {    public static void Main(string[] args)    {       string source = "server=(local)\NetSDK;" +                       "uid=QSUser;pwd=QSPassword;" +                        "database=Northwind";       string select = "SELECT ContactName,CompanyName FROM Customers";       SqlConnection conn = new SqlConnection(source);       conn.Open();       SqlCommand cmd = new SqlCommand(select, conn);   SqlDataReader reader = cmd.ExecuteReader();     while(reader.Read())     {     Console.WriteLine("Contact : {0,-20} Company : {1}" ,     reader[0] , reader[1]);     }   } } 

The SqlDataReader and OleDbDataReader objects will be discussed later in this chapter.

ExecuteScalar()

On many occasions it is necessary to return a single result from a SQL statement, such as the count of records in a given table, or the current date/time on the server. The ExecuteScalar method can be used in such situations:

 using System; using System.Data.SqlClient;   public class ExecuteScalarExample   {    public static void Main(string[] args)    {       string source = "server=(local)\NetSDK;" +                       "uid=QSUser;pwd=QSPassword;" +                        "database=Northwind";   string select = "SELECT COUNT(*) FROM Customers";   SqlConnection conn = new SqlConnection(source);       conn.Open();       SqlCommand cmd = new SqlCommand(select, conn);   object o = cmd.ExecuteScalar();     Console.WriteLine (o) ;   } } 

The method returns an object, which you can cast into the appropriate type if required.

ExecuteXmlReader() (SqlClient Provider Only)

As its name implies, this method will execute the command and return an XmlReader object to the caller. SQL Server permits a SQL SELECT statement to be extended with a FOR XML clause. This clause can take one of three options:

• FOR XML AUTO builds a tree based on the tables in the FROM clause

• FOR XML RAW result set rows are mapped to elements, with columns mapped to attributes

• FOR XML EXPLICIT you must specify the shape of the XML tree to be returned

Professional SQL Server 2000 XML (Wrox Press, ISBN 1-861005-46-6) includes a complete description of these options. For this example I shall use AUTO :

 using System; using System.Data.SqlClient;   using System.Xml;     public class ExecuteXmlReaderExample     {     public static void Main(string[] args)     {   string source = "server=(local)\NetSDK;" +                       "uid=QSUser;pwd=QSPassword;" +                        "database=Northwind";   string select = "SELECT ContactName,CompanyName " +     "FROM Customers FOR XML AUTO";   SqlConnection conn = new SqlConnection(source);       conn.Open();       SqlCommand cmd = new SqlCommand(select, conn);   XmlReader xr = cmd.ExecuteXmlReader();     xr.Read();     string s;     do     {     s = xr.ReadOuterXml();     if (s!="")     Console.WriteLine(s);     } while (s!= "");     conn.Close();     }     }   

Note that we have to import the System.Xml namespace in order to output the returned XML. This namespace and further XML capabilities of the .NET Framework are explored in more detail in Chapter 11.

Here we include the FOR XML AUTO clause in the SQL statement, then call the ExecuteXmlReader() method. A screenshot of the possible output from this code is shown below:

In the SQL clause, we specified FROM Customers , so an element of type Customers is shown in the output. To this are added attributes, one for each column selected from the database. This builds up an XML fragment for each row selected from the database.

Calling Stored Procedures

Calling a stored procedure with a command object is just a matter of defining the name of the stored procedure, adding a parameter's definition for each parameter of the procedure, then executing the command with one of the methods presented in the previous section.

In order to make the examples in this section more useful, I have defined a set of stored procedures that can be used to insert, update, and delete records from the Region table in the Northwind example database. I have chosen this table despite its small size , as it can be used to define examples for each of the types of stored procedures you will commonly write.

Calling a Stored Procedure that Returns Nothing

The simplest example of calling a stored procedure is one that returns nothing to the caller. There are two such procedures defined below, one for updating a pre-existing Region record, and the other for deleting a given Region record.

Record Update

Updating a Region record is fairly trivial, as there is only one column that can be modified ( assuming primary keys cannot be updated). You can type these examples directly into the SQL Server Query Analyzer, or run the StoredProcs.sql file in the Chapter 09\02_StoredProcs subdirectory, which will install each of the stored procedures in this section:

   CREATE PROCEDURE RegionUpdate (@RegionID INTEGER,     @RegionDescription NCHAR(50)) AS     SET NOCOUNT OFF     UPDATE Region     SET RegionDescription = @RegionDescription     WHERE RegionID = @RegionID     GO   

An update command on a more real-world table might need to re-select and return the updated record in its entirety. This stored procedure takes two input parameters ( @RegionID and @RegionDescription ), and issues an UPDATE statement against the database.

To run this stored procedure from within .NET code, you need to define a SQL command and execute it:

   SqlCommand aCommand = new SqlCommand("RegionUpdate", conn);     aCommand.CommandType = CommandType.StoredProcedure;     aCommand.Parameters.Add(new SqlParameter ("@RegionID",     SqlDbType.Int,     0,     "RegionID"));     aCommand.Parameters.Add(new SqlParameter("@RegionDescription",     SqlDbType.NChar,     50,     "RegionDescription"));     aCommand.UpdatedRowSource = UpdateRowSource.None;   

This code creates a new SqlCommand object named aCommand , and defines it as a stored procedure. We then add each parameter in turn , and finally set the expected output from the stored procedure to one of the values in the UpdateRowSource enumeration, which is discussed later in this chapter.

The stored procedure takes two parameters: the unique primary key of the Region record being updated, and the new description to be given to this record.

Once the command has been created, it can be executed by issuing the following commands:

   aCommand.Parameters[0].Value = 999;     aCommand.Parameters[1].Value = "South Western England";     aCommand.ExecuteNonQuery();   

Here we are setting the value of the parameters, then executing the stored procedure. As the procedure returns nothing, ExecuteNonQuery() will suffice.

Command parameters may be set by ordinal as shown above, or set by name.

Record Deletion

The next stored procedure required is one that can be used to delete a Region record from the database:

   CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS     SET NOCOUNT OFF     DELETE FROM Region     WHERE       RegionID = @RegionID     GO   

This procedure only requires the primary key value of the record. The code uses a SqlCommand object to call this stored procedure as follows :

   SqlCommand aCommand = new SqlCommand("RegionDelete" , conn);     aCommand.CommandType = CommandType.StoredProcedure;     aCommand.Parameters.Add(new SqlParameter("@RegionID" , SqlDbType.Int , 0 ,     "RegionID"));     aCommand.UpdatedRowSource = UpdateRowSource.None;   

This command only accepts a single parameter as shown in the following code, which will execute the RegionDelete stored procedure; here we see an example of setting the parameter by name:

   aCommand.Parameters["@RegionID"].Value= 999;     aCommand.ExecuteNonQuery();   

Calling a Stored Procedure that Returns Output Parameters

Both of the previous examples execute stored procedures that return nothing. If a stored procedure includes output parameters, then these need to be defined within the .NET client so that they can be filled when the procedure returns.

The following example shows how to insert a record into the database, and return the primary key of that record to the caller.

Record Insertion

The Region table only consists of a primary key ( RegionID ) and description field ( RegionDescription ). To insert a record, this numeric primary key needs to be generated, then a new row inserted into the database. I have chosen to simplify the primary key generation in this example by creating one within the stored procedure. The method used is exceedingly crude, which is why I have devoted a section to key generation later in the chapter. For now this primitive example will suffice:

   CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50),     @RegionID INTEGER OUTPUT)AS     SET NOCOUNT OFF     SELECT @RegionID = MAX(RegionID)+ 1     FROM Region     INSERT INTO Region(RegionID, RegionDescription)     VALUES(@RegionID, @RegionDescription)     GO   

The insert procedure creates a new Region record. As the primary key value is generated by the database itself, this value is returned as an output parameter from the procedure ( @RegionID ). This is sufficient for this simple example, but for a more complex table ( especially one with default values), it is more common not to utilize output parameters, and instead select the entire inserted row and return this to the caller. The .NET classes can cope with either scenario.

   SqlCommand  aCommand = new SqlCommand("RegionInsert" , conn);     aCommand.CommandType = CommandType.StoredProcedure;     aCommand.Parameters.Add(new SqlParameter("@RegionDescription" ,     SqlDbType.NChar ,     50 ,     "RegionDescription"));     aCommand.Parameters.Add(new SqlParameter("@RegionID" ,     SqlDbType.Int,     0 ,     ParameterDirection.Output ,     false ,     0 ,     0 ,     "RegionID" ,     DataRowVersion.Default ,     null));     aCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;   

Here, the definition of the parameters is much more complex. The second parameter, @RegionID , is defined to include its parameter direction, which in this example is Output . In addition to this flag, on the last line of the code, we utilize the UpdateRowSource enumeration to indicate that we expect to return data from this stored procedure via output parameters. This flag is mainly used when issuing stored procedure calls from a DataTable (covered later in the chapter).

Calling this stored procedure is similar to the previous examples, except in this instance we need to read the output parameter after executing the procedure:

   aCommand.Parameters["@RegionDescription"].Value = "South West";     aCommand.ExecuteNonQuery();     int newRegionID = (int) aCommand.Parameters["@RegionID"].Value;   

After executing the command, we read the value of the @RegionID parameter and cast this to an integer.

You may be wondering what to do if the stored procedure you call returns output parameters and a set of rows. In this instance, define the parameters as appropriate, and rather than calling ExecuteNonQuery() , call one of the other methods (such as ExecuteReader() ) that will permit you to traverse any record(s) returned.