Commands


The “Using Database Connections” section briefly touched on the idea of issuing commands against a database. 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 Command class, as shown in this example:

 string source = "server=(local);" +                 "integrated security=SSPI;" +                 "database=Northwind"; string select = "SELECT ContactName,CompanyName FROM Customers"; SqlConnection conn = new SqlConnection(source); conn.Open(); SqlCommand cmd = new SqlCommand(select, conn); 

The <provider>Command 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.

Open table as spreadsheet

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.AddWithValue(“@CustomerID”, “QUICK”);

TableDirect

OleDbCommand cmd = new OleDbCommand(“Categories”, conn);

cmd.CommandType = CommandType.TableDirect;

When executing a stored procedure, it might be necessary to pass parameters to that procedure. The previous example sets the @CustomerID parameter directly, although there are other ways of setting the parameter value, which you look at later in the chapter. Note that in .NET 2.0, the AddWithValue method has been added to the command parameters collection - and the Add ( name, value) member has been attributed as Obsolete. If like me you have used this original method of constructing parameters for calling a stored procedure, you’ll receive compiler warnings when you recompile your code. I would suggest altering your code now because I think it likely that Microsoft will remove the older method in a subsequent release of .NET.

Tip 

The TableDirect command type is only valid for the OleDb provider; other providers will throw an exception if you attempt to use this command type with them.

Executing Commands

After you have defined the command, you need to execute it. A number of ways exist to issue the statement, depending on what you expect to be returned (if anything) from that command. The <provider>Command classes provide the following execute methods:

  • ExecuteNonQuery() - Executes the command but does not return any output

  • ExecuteReader() - Executes the command and returns a typed IDataReader

  • ExecuteScalar() - Executes the command and returns a single value

In addition to these methods, the SqlCommand class exposes the following method:

  • ExecuteXmlReader() - Executes the command and returns an XmlReader object, which can be used to traverse the XML fragment returned from the database

As with the other chapters, you can download the sample code from the Wrox Web site at www.wrox.com.

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);" +                       "integrated security=SSPI;" +                       "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 typed data reader 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);" +                       "integrated security=SSPI;" +                        "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]);       }    } }

Figure 25-2 shows the output of this code.

image from book
Figure 25-2

The <provider>DataReader objects are 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);" +                       "integrated security=SSPI;" +                       "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 to the appropriate type if required. If the SQL you are calling only returns one column, it is preferable to use ExecuteScalar over any other method of retrieving that column. That also applies to stored procedures that return a single value.

ExecuteXmlReader() (SqlClient Provider Only)

As its name implies, this method executes the command and returns 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 - Maps result set rows to elements, with columns mapped to attributes

  • FOR XML EXPLICIT - Requires that you 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, use AUTO:

 using System; using System.Data.SqlClient; using System.Xml; public class ExecuteXmlReaderExample {    public static void Main(string[] args)    {       string source = "server=(local);" +                       "integrated security=SSPI;" +                       "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 data;       do       {           data = xr.ReadOuterXml();           if (!string.IsNullOrEmpty(data))              Console.WriteLine(data);        } while (!string.IsNullOrEmpty(data));        conn.Close();    } } 

Note that you have to import the System.Xml namespace in order to output the returned XML. This namespace and further XML capabilities of .NET Framework are explored in more detail in Chapter 26, “Manipulating XML.”

Here, you include the FOR XML AUTO clause in the SQL statement, then call the ExecuteXmlReader() method. Figure 25-3 shows the output of this code.

image from book
Figure 25-3

In the SQL clause, you 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 definition for each parameter of the procedure, and then executing the command with one of the methods presented in the previous section.

To make the examples in this section more useful, a set of stored procedures has been defined that can be used to insert, update, and delete records from the Region table in the Northwind sample database. Despite its small size, this is a good candidate to choose for the example, because 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. Two such procedures are defined in the following two subsections: one for updating a preexisting Region record and one for deleting a given Region record.

Record Update

Updating a Region record is fairly trivial, because 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 that is part of the downloadable code for this chapter. This file installs 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 reselect 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. You 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. After 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, the value of each parameter is set, then the stored procedure is executed. Because the procedure returns nothing, ExecuteNonQuery() will suffice. Command parameters can be set by ordinal numbers (as shown in the previous example) or 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 requires only 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, you 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, 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 must be generated, and then a new row needs to be inserted into the database. The primary key generation in this example has been simplified by creating one within the stored procedure. The method used is exceedingly crude, which is why there is a section on key generation later in this chapter. For now, this primitive example suffices:

  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, the UpdateRowSource enumeration is used to indicate that data will be returned from this stored procedure via output parameters. This flag is mainly used when issuing stored procedure calls from a DataTable (which is discussed later in this chapter).

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

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

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

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




Professional C# 2005 with .NET 3.0
Professional C# 2005 with .NET 3.0
ISBN: 470124725
EAN: N/A
Year: 2007
Pages: 427

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net