ADO.NET Core Classes:Commands – Part 2

Every interaction between a client and a data source, whether it is to retrieve information, delete a record, or commit a change, is governed by a Command object. In this chapter, we introduce the Command object in detail. You’ll learn how to define a command and use it to execute nonquery commands such as direct record updates, insertions, and deletions. You’ll also learn how to use parameterized commands and commands that access stored procedures.

Command Object Overview

The Command object is the heart of data processing with ADO.NET. Typically, the Command object wraps a SQL statement or a call to a stored procedure. For example, you might use a Command object to execute a SQL UPDATE, DELETE, INSERT, or SELECT statement. However, ADO.NET providers that don’t represent databases may use their own nomenclature. The only rule is that the Command.CommandText property, which defines the command, must be a string.

As with the Connection object, the Command object is specific to the data provider. Two examples are:

• System.Data.SqlClient.SqlCommand executes commands against SQL Server Version 7.0 or later.
• System.Data.OleDb.OleDbCommand executes commands against an OLE DB data provider.

Each Command object implements the System.Data.IDbCommand interface. That means it is guaranteed to support the members shown in Tables 4-1 and 4-2. At a minimum, you must set the CommandText and a reference to a valid Connection before using a Command. In addition, you must modify the CommandType default value if you wish to invoke a stored procedure.

Table 4-1. IDbCommand properties
Member	Description
CommandText	Contains the SQL statement, stored procedure name, or table name. For an unusual provider (one that doesn’t work with a database), this can contain something entirely different and proprietary; the only requirement is that is must be formatted as a string.
CommandTimeout	The amount of time (in seconds) to wait for a command to complete before giving up and throwing an exception. The default is 30 seconds.
CommandType	Indicates the format used for the CommandText property. You can use Text (the default) for a SQL command, StoredProcedure for a stored procedure call, or TableDirect for one or more tables (which is a poor scalability choice because it returns all rows and columns from the named table).
Connection	References the IDbConnection object to use for this command. The connection must be open before you execute the command.
Parameters	A collection of input, output, or bidirectional parameter objects. This is used only for parameterized queries or stored procedure calls.
Transaction	Gets or sets the transaction that this command is part of.
UpdatedRowSource	Specifies how this command updates a data source when it is used with a DataSet and IDbDataAdapter. We’ll return to this topic in the next chapter.
Table 4-2. IDbCommand methods
Member	Description
Cancel(  )	Tries to stop a running command. In order to invoke this method, you must start the command on a separate thread, because all commands execute synchronously. Otherwise, your code will be stalled and won’t have a chance to call the Cancel( ) method.
CreateParameter(  )	Creates a new Parameter object, which can be added to the Command.Parameters collection.
ExecuteReader(  )	Executes the command and returns a forward-only read-only cursor in the form of a DataReader.
ExecuteNonQuery(  )	Executes the command and returns the number of rows that were affected. Often used with record UPDATE, DELETE, or INSERT statements.
ExecuteScalar(  )	Executes the command, and retrieves a single value. Used with aggregate functions and in cases where you want to return the first column of the first row of a result set.
Prepare(  )	If CommandType is StoredProcedure, you can use this method to precompile the command in the data source. If you perform this task before calling the same stored procedure with different parameters, you may achieve a small performance increase, depending on the provider. However, it requires an additional roundtrip to the data source, so don’t use it unless you have tested it and are sure it actually provides a measurable benefit.

Creating and Executing a Command

When creating a Command object, you have the choice of several constructors. The most useful accepts a CommandText value and a Connection. Here’s an example with the SqlCommand class:

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(commandText, con);

For standard providers, there are three ways to execute a command: ExecuteNonQuery( ) , ExecuteReader( ), and ExecuteScalar( ). You choose one of these methods, depending on the type of command you are executing. For example, ExecuteReader( ) returns a DataReader and provides read-only access to query results.

Some providers include additional members. For example, the ADO.NET SQL Server provider includes an ExecuteXmlReader( ) method that retrieves data as an XML document.

Executing a Command That Doesn’t Return Rows

The SQL language includes several nonquery commands. The best known include UPDATE, DELETE, and INSERT. You can also use other commands to create, alter, or drop tables, constraints, relations, and so on. To execute any of these commands, just set the CommandText property with the full SQL statement, open a connection, and invoke the ExecuteNonQuery( ) method.

Updating a record

The UPDATE statement, at its simplest, uses the following syntax:

UPDATE table SET update_expression WHERE search_condition

The UPDATE expression can thus modify a single record, or it can apply a change to an entire batch of records in a single table. Example 4-1 puts the UPDATE statement to work with a simple command that modifies a single field in a single category record in the Northwind database.

Example 4-1. Updating a record

// UpdateRecord.cs – Updates a single Category record

using System;

using System.Data.SqlClient;

public class UpdateRecord

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “UPDATE Categories SET CategoryName=’Beverages'” +

“WHERE CategoryID=1″;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

// Execute the command.

con.Open();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

// Display the result of the operation.

Console.WriteLine(rowsAffected.ToString() + ” row(s) affected”);

}

}

Note that the ExecuteNonQuery( ) method returns the number of rows affected, not the row itself. In order to see the results of the change, you need to either query the row or use a tool such as SQL Server’s Enterprise Manager to browse the database.

If the UPDATE statement fails to update any records because the WHERE clause is too restrictive, an error isn’t generated. You must examine number of affected rows to determine if this is this case. If you are adding this logic to a custom data access component, you might want to raise an exception if this happens, because it indicates that no update took place.

Deleting a record

The SQL DELETE statement simply specifies a search condition that selects one or more records to be removed:

DELETE FROM table WHERE search_condition

You can modify the previous example to delete a record simply by changing the SQL variable:

string SQL = “DELETE FROM Categories WHERE CategoryID=1”;

Inserting a record

Finally, you can insert a record using a list of column names, followed by a list of column values in the same order:

INSERT INTO table (column_list) VALUES (value_list)

Once again, the console example can be adapted to insert a category record just by modifying the SQL text:

string SQL = “INSERT INTO Categories (CategoryName, Description) ” +

“VALUES (‘Beverages’, ‘Soft drinks, coffees, teas, beers, and ales’)”;

Note that the category table includes a CategoryID column that is configured as a unique identity value. That means the CategoryID number is created by the data source, which ensures that duplicate IDs don’t occur. For that reason, the INSERT statement doesn’t include a CategoryID value. As a side effect, this code will always succeed and create a new row with identical information, but with a new CategoryID.

Executing a Command That Returns a Single Value

ExecuteScalar( ) method returns a single value. If you perform a query, this will be the first value in the first column of the first row. More likely, you’ll use ExecuteNonQuery( ) to return an aggregate value, which is the result of a calculation using a subset of rows.

An aggregate function must be part of a SQL SELECT statement, which indicates the table and (optionally) a search filter and sort order:

SELECT aggregate_expression FROM tables [WHERE search_condition]

[ORDER BY order_expression ASC | DESC]

Example 4-2. Executing an aggregate function

// TotalOrders.cs – Gets the number of order records from 1996

using System;

using System.Data.SqlClient;

public class TotalOrders

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “SELECT COUNT(*) FROM Orders WHERE ” +

“OrderDate >= ‘1996-01-01’ AND OrderDate < ‘1997-01-01′”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

// Execute the command.

con.Open();

int result = (int)cmd.ExecuteScalar();

con.Close();

// Display the result of the operation.

Console.WriteLine(result.ToString() + ” rows in 1996″);

}

}

Here’s the sample output for this code:

152 rows in 1996

Parameter Object Overview

In the examples shown so far, the SQL command text and the data values have been embedded in a single string. This approach is easy, and convenient for writing data access code. However, it also has significant drawbacks that make it unsuitable for a production-level application. These include inflexibility, poor performance, and potential security problems when using user-supplied values.

To overcome these problems, you need to use another feature of the Command object: parameters. Command parameters are conceptually the same as method parameters in an ordinary piece of .NET code. The most common type of parameter is an input parameter, which carries information from your application to the data source. You can use an input parameter when calling a stored procedure or when coding a parameterized query. In addition, you can use output parameters, which return information from the data source to your code, or bidirectional parameters, which transmit values in both directions. Output and bidirectional parameters are used only when you are making stored procedure calls.

Every Command object has an associated collection of Parameter objects (referenced by its Parameters property). The Parameter object is a provider-specific object, which means a SqlCommand uses a SqlParameter, an OleDbCommand uses an OleDbParameter, and so on.

Creating Parameters

In order to create a Parameter object, you must specify a parameter name, and the exact data type for the information it will contain. For the managed OLE DB provider, you specify data types using the System.Data.OleDb.OleDbType enumeration. For the SQL Server data provider, you use the System.Data.SqlDbType enumeration. If the data type is a variable-length field such as a string or binary field, you also need to indicate the field length.

For example, the following code snippet shows how to create a SqlParameter object named @MyParam with a SQL Server integer type. Note that the name is preceded with an @ symbol; this is a convention of stored procedure programming with SQL Server, but it is by no means a necessity in your code.

SqlParameter param = new SqlParameter(“@MyParam”, SqlDbType.Int);

To use a variable-length data type, you need to use a different constructor that accepts a field length, as shown here:

SqlParameter param = new SqlParameter(“@MyParam”, SqlDbType.NVarChar,

15);

Once you’ve created a Parameter, you will probably want to assign a value and add it to an existing Command:

SqlCommand cmd = new SqlCommand(commandText, con);

SqlParameter param = new SqlParameter(“@Description”, SqlDbType.VarChar,  88, “Description”);

param.Value = “This is the description”;

cmd.Add(param);

Alternatively, you can create the Parameter and add it to the Command in one step using an overloaded version of the Add( ) method. This method returns a reference to the newly created Parameter object, allowing you to quickly set a value.

SqlCommand cmd = new SqlCommand(commandText, con);

SqlParameter param = cmd.Add(“@Description”, SqlDbType.VarChar,

88, “Description”);

param.Value = “This is the description”;

By default, when you create a parameter, it is configured as an input parameter, meaning that the Parameter.Direction property is set to ParameterDirection.Input.

You can retrieve parameters from the Parameters collection by index number or by the assigned parameter name:

// Select the first parameter.

param = cmd.Parameters[0];

// Select the parameter with the name “@Description”.

param = cmd.Parameters[“@Description”];

Now that you can create and configure Parameter objects, it’s time to consider how to use them to build a parameterized command.

Parameterized Commands

Parameterized commands are executed in the same way as normal commands. They simply use placeholders to separate literal values from the query itself. For example, consider the following dynamically constructed command:

UPDATE Categories SET CategoryName=’Beverages’  WHERE CategoryID=1

As a parameterized command with the SQL Server provider, it takes this form:

UPDATE Categories SET CategoryName=@CategoryName   WHERE CategoryID=@CategoryID

You then add two Parameter objects to the Command, with the names @CategoryName and @CategoryID. Now set the values for both these Parameter objects to Beverages and 1, respectively, and invoke the command. Example 4-3 shows a full example that rewrites Example 4-1 to use a parameterized command.

Example 4-3. Updating a record with a parameterized command (SQL Server)

// ParameterizedUpdateSQL.cs – Updates a single Category record

using System;

using System.Data;

using System.Data.SqlClient;

public class UpdateRecord

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “UPDATE Categories SET CategoryName=@CategoryName ” +

“WHERE CategoryID=@CategoryID”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

SqlParameter param;

param = cmd.Parameters.Add(“@CategoryName”, SqlDbType.NVarChar, 15);

param.Value = “Beverages”;

param = cmd.Parameters.Add(“@CategoryID”, SqlDbType.Int);

param.Value = 1;

// Execute the command.

con.Open();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

// Display the result of the operation.

Console.WriteLine(rowsAffected.ToString() + ” row(s) affected”);

}

}

Note that in order for this to work, the Command.CommandType property must be CommandType.Text, which is the default.

The SQL Server provider matches the parameter values to the query placeholders by using the parameter name. With the OLE DB provider, parameterized queries take a slightly different syntax. Instead of using named parameters, you use question-mark placeholders:

SELECT * FROM Customers WHERE CustomerID = ?

If you have more than one question mark in the same query, the OLE DB provider matches them to the question marks based on their order. Thus the first parameter you add should correspond to the first question mark in your query. Example 4-4 shows how you would approach the same task using the OLE DB provider. In this case, both Parameter objects are still assigned the same names, but these names aren’t used in the query. The position alone is significant.

Example 4-4. Updating a record with a parameterized command (OLE DB)

// ParameterizedUpdateOLEDB.cs – Updates a single Category record

using System;

using System.Data.OleDb;

public class UpdateRecord

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Provider=SQLOLEDB;” +

“Integrated Security=SSPI”;

string SQL = “UPDATE Categories SET CategoryName=? ” +

“WHERE CategoryID=?”;

// Create ADO.NET objects.

OleDbConnection con = new OleDbConnection(connectionString);

OleDbCommand cmd = new OleDbCommand(SQL, con);

OleDbParameter param;

param = cmd.Parameters.Add(“@CategoryName”, OleDbType.VarWChar, 15);

param.Value = “Beverages”;

param = cmd.Parameters.Add(“@CategoryID”, OleDbType.Integer);

param.Value = 1;

// Execute the command.

con.Open();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

// Display the result of the operation.

Console.WriteLine(rowsAffected.ToString() + ” row(s) affected”);

}

}

Parameterized commands have several benefits:

• They are less error-prone. You can code the SQL statement in a single long string, rather than piece it together, where it is notoriously easy to mistype.
• They are more secure. Metacharacters within parameters are escaped automatically, reducing the risk of some classes of SQL injection attacks.
• They prevent syntax errors with different data types. In SQL, you need to escape different data types (strings, numbers, and dates) differently. In a parameterized query, this is performed automatically.
• They are reusable. Parameterized queries make it easy to reuse Command objects. For example, you can use the Command in Example 4-3 to update multiple category records, simply by modifying the value of the parameters and executing the query again.

A parameterized command won’t improve performance as compared to the original dynamic SQL statement. Unlike a stored procedure, a parameterized query isn’t stored in the database and isn’t precompiled. The difference is simply one of syntax.

Commands with Stored Procedures

Stored procedures—SQL scripts stored in the database—are a key ingredient in any successful large-scale database applications. One advantage of stored procedures is improved performance. Stored procedures typically execute faster than ordinary SQL statements because the database can create, optimize, and cache a data access plan in advance. Stored procedures also have a number of other potential benefits. They:

• Improve security. A client can be granted permissions to execute a stored procedure to add or modify a record in a specify way, without having full permissions on the underlying tables.
• Are easy to maintain, because they are stored separately from the application code. Thus, you can modify a stored procedure without recompiling and redistributing the .NET application that uses it.
• Add an extra layer of indirection, potentially allowing some database details to change without breaking your code. For example, a stored procedure can remap field names to match the expectations of the client program.
• Reduce network traffic, because SQL statements can be executed in batches.

Of course, stored procedures aren’t perfect. Most of their drawbacks are in the form of programming annoyances:

• Using stored procedures in a program often involves importing additional database-specific details (such as parameter data types) into your code. You can control this problem by creating a dedicated component that encapsulates all your data access code.
• Stored procedures are created entirely in the SQL language (with variations depending on the database vendor) and use script-like commands that are generally more awkward than a full-blown object-oriented language such as C# or VB .NET, particularly with respect to error handling and code reuse. Microsoft promises that the next version of SQL Server (code-named Yukon) will allow stored procedures to be written using .NET languages like C#.

Stored procedures can be used for any database task, including retrieving rows or aggregate information, updating data, and removing or inserting rows.

Executing a Stored Procedure

Using a stored procedure with ADO.NET is easy. You simply follow four steps:

1. Create a Command, and set its CommandType property to StoredProcedure.
2. Set the CommandText to the name of the stored procedure.
3. Add any required parameters to the Command.Parameters collection.
4. Execute the Command with the ExecuteNonQuery( ), ExecuteScalar( ), or ExecuteQuery( ) method (depending on the type of output generated by the stored procedure).

For example, consider the generic update command defined earlier:

UPDATE Categories SET CategoryName=@CategoryName

WHERE CategoryID=@CategoryID

You can encapsulate this logic in a stored procedure quite easily. You’ll probably use Visual Studio .NET or a third-party product (like SQL Server’s Enterprise Manager) to create the stored procedure, but the actual stored procedure code will look something like this:

CREATE PROCEDURE UpdateCategory

(

@CategoryID int,

@CategoryName nvarchar(15)

)

AS

UPDATE Categories SET CategoryName=@CategoryName

WHERE CategoryID=@CategoryID

GO

You’ll notice that the actual SQL statement is unchanged. However, it is now wrapped in a SQL stored procedure called UpdateCategory that requires two input parameters. The stored procedure defines the required data types for all parameters, and you should pay close attention: your code must match exactly.

Example 4-5 rewrites Example 4-3 to use this stored procedure. The only two changes are found in the CommandText and CommandType properties of the Command object.

Example 4-5. Updating a record with a stored procedure

// SProcUpdateSQL.cs – Updates a single Category record

using System;

using System.Data;

using System.Data.SqlClient;

public class UpdateRecord

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “UpdateCategory”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param;

param = cmd.Parameters.Add(“@CategoryName”, SqlDbType.NVarChar, 15);

param.Value = “Beverages”;

param = cmd.Parameters.Add(“@CategoryID”, SqlDbType.Int);

param.Value = 1;

// Execute the command.

con.Open();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

// Display the result of the operation.

Console.WriteLine(rowsAffected.ToString() + ” row(s) affected”);

}

}

Output Parameters

One common use of a stored procedure is to insert a record in a table that uses a unique identity field. This type of stored procedure accepts several input parameters that identify the data for new row and one output parameter that returns the automatically generated unique ID to your .NET code. This saves you re-querying the database to find this information.

Here is the CustomerAdd stored procedure code in the store database:

CREATE Procedure CustomerAdd

(

@FullName   nvarchar(50),

@Email      nvarchar(50),

@Password   nvarchar(50),

@CustomerID int OUTPUT

)

AS

INSERT INTO Customers

(

FullName,

EMailAddress,

Password

)

VALUES

(

@FullName,

@Email,

@Password

)

SELECT

@CustomerID = @@Identity

GO

This stored procedure defines three input parameter and one output parameter for the generated ID. The stored procedure begins by inserting the new record and sets the output parameter using the special global SQL Server system function @@Identity.

Using this routine in code is just as easy, but you need to configure the @CustomerID parameter to be an output parameter (input is the default) (see Example 4-6).

Example 4-6. Using a stored procedure with an output parameter

// AddCustomer.cs – Runs the CustomerAdd stored procedure.

using System;

using System.Data;

using System.Data.SqlClient;

public class AddCustomer

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=store;Integrated Security=SSPI”;

string procedure = “CustomerAdd”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(procedure, con);

// Configure command and add input parameters.

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param;

param = cmd.Parameters.Add(“@FullName”, SqlDbType.NVarChar, 50);

param.Value = “John Smith”;

param = cmd.Parameters.Add(“@Email”, SqlDbType.NVarChar, 50);

param.Value = “john@mydomain.com”;

param = cmd.Parameters.Add(“@Password”, SqlDbType.NVarChar, 50);

param.Value = “opensesame”;

// Add the output parameter.

param = cmd.Parameters.Add(“@CustomerID”, SqlDbType.Int);

param.Direction = ParameterDirection.Output;

// Execute the command.

con.Open();

cmd.ExecuteNonQuery();

con.Close();

Console.WriteLine(“New customer has ID of ” + param.Value);

}

}

Your stored procedure is free to return any type of information in an output parameter, as long as it uses the correct data type. There’s also no limit to the number of parameters, output or otherwise, that you can use with a stored procedure.

Stored Procedure Return Values

Stored procedures can also return information through a return value. The return value works in much the same way as an output parameter, but it isn’t named, and every stored procedure can have at most one return value. In SQL Server stored procedure code, the return value is set using the RETURN statement.

Here’s how the CustomerAdd stored procedure can be rewritten to use a return value instead of an output parameter:

CREATE Procedure CustomerAdd

(

@FullName   nvarchar(50),

@Email      nvarchar(50),

@Password   nvarchar(50),

)

AS

INSERT INTO Customers

(

FullName,

EMailAddress,

Password

)

VALUES

(

@FullName,

@Email,

@Password

)

RETURN @@Identity

GO

This revision carries no obvious advantages or disadvantages. It’s really a matter of convention. Different database developers have their own system for determining when to use a return value; many uses a return value to provide ancillary information such as the number of rows processed or an error condition.

As with input and output parameters, the return value is represented by a Parameter object. The difference is that the Parameter object for a return value must have the Direction property set to ReturnValue. In addition, some providers (e.g., the OLE DB provider) require that the Parameter object representing the return value is the first in the Parameter collection for a Command.

Example 4-7 shows how to call the revised CustomerAdd stored procedure.

Example 4-7. Using a stored procedure with a return value

// AddCustomerReturn.cs – Runs the CustomerAdd stored procedure.

using System;

using System.Data;

using System.Data.SqlClient;

public class AddCustomer

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=store;Integrated Security=SSPI”;

string procedure = “CustomerAdd”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(procedure, con);

// Configure the command.

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param;

// Add the parameter representing the return value.

param = cmd.Parameters.Add(“@CustomerID”, SqlDbType.Int);

param.Direction = ParameterDirection.ReturnValue;

// Add the input parameters.

param = cmd.Parameters.Add(“@FullName”, SqlDbType.NVarChar, 50);

param.Value = “John Smith”;

param = cmd.Parameters.Add(“@Email”, SqlDbType.NVarChar, 50);

param.Value = “john@mydomain.com”;

param = cmd.Parameters.Add(“@Password”, SqlDbType.NVarChar, 50);

param.Value = “opensesame”;

// Execute the command.

con.Open();

cmd.ExecuteNonQuery();

con.Close();

param = cmd.Parameters[“@CustomerID”];

Console.WriteLine(“New customer has ID of ” + param.Value);

}

}

Deriving Parameters

So far, the stored procedure examples suffer in one respect: they import numerous database-specific details into your code. Not only do you need to hardcode exact parameter names, but you need to know the correct SQL Server data type, and the field length for any text data.

One way to get around these details is to use a CommandBuilder class. This class is used with DataSet updates, but it also is useful when dealing with stored procedures. It allows you to retrieve and apply all the parameter metadata for a command. The disadvantage of this approach is that it requires an extra round trip to the data source. This is a significant price to pay for simplified code, and as a result, you won’t see it used in enterprise-level database code.

Once the parameter information is drawn from the database, all you need to do is set the parameter values. You can retrieve individual parameter objects either by index number or by parameter name from the Command.Parameters collection. Example 4-8 shows how the AddCustomer code can be rewritten to use this technique.

Example 4-8. Retrieving parameter information programmatically

// DeriveParameter.cs – Retrieves stored procedure parameter information

using System;

using System.Data;

using System.Data.SqlClient;

public class AddCustomer

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=store;Integrated Security=SSPI”;

string procedure = “CustomerAdd”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(procedure, con);

// Configure command and add input parameters.

cmd.CommandType = CommandType.StoredProcedure;

// Execute the command.

con.Open();

SqlCommandBuilder.DeriveParameters(cmd);

cmd.Parameters[1].Value = “Faria MacDonald”;

cmd.Parameters[2].Value = “joe@mydomain.com”;

cmd.Parameters[3].Value = “opensesame”;

cmd.Parameters[4].Value = DBNull.Value;

cmd.ExecuteNonQuery();

con.Close();

Console.WriteLine(“New customer has ID of ” +

cmd.Parameters[4].Value);

}

}

Because deriving parameters adds extra overhead, it’s not suitable for a performance-critical application. It’s a much better idea to create a dedicated database component that encapsulates the code that creates and populates stored procedure parameters and all the database-specific details.

Commands and Data Definition Language (DDL)

Most data-access code focuses on Data Manipulation Language (DML) commands. These instructions change, delete, or retrieve information about the values in tables. Typically, a database administrator creates the tables as part of a separate process, using a dedicated tool. This isn’t always the case. Sometimes you want to create or modify table structure directly from your code. To do this, you need Data Definition Language (DDL) commands. SQL defines many basic commands various database vendors use to implement and sometimes extend. These include old standbys such as DROP TABLE and CREATE DATABASE.

ADO, the previous generation of data-access technology, had a sibling called ADOX that provided an object-oriented wrapper for the DDL commands. ADO.NET doesn’t have any such niceties. However, you can still modify table structure or create new tables programmatically; you just have to do it the hard way, by constructing a Command and executing it with the ExecuteNonQuery( ) method. Example 4-9 shows a trivial example that uses the CREATE TABLE statement to create a single table with two columns. In this case, the table is created in the Northwind database because that’s the initial database selected when the connection is opened.

Example 4-9. Inserting a new table programmatically

// DDL.cs – Inserts a new table

using System;

using System.Data.SqlClient;

public class UpdateRecord

{

public static void Main()

{

string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

string SQL = “CREATE TABLE Users (“+

“UserName nvarchar(20), Password nvarchar(20) )”;

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(SQL, con);

// Execute the command.

con.Open();

int rowsAffected = cmd.ExecuteNonQuery();

con.Close();

// Display the result of the operation.

Console.WriteLine(rowsAffected.ToString() + ” row(s) affected”);

}

}

In this case, rowsAffected will be -1 because the Command didn’t execute an UPDATE, DELETE, or INSERT statement.