Supplying Parameters to Commands

In the examples you've seen up to this point, the values for each column have been hard-coded in the SQL statements. For example, in Listing 8.9, shown earlier, the INSERT statement that added the row to the Customers table was:

 INSERT INTO Customers   CustomerID, CompanyName ) VALUES   'J3COM', 'Jason Price Corporation' ) 

As you can see, the values for the CustomerID and CompanyName columns are hard-coded to 'J3COM' and 'Jason Price Corporation'. If you had to execute many such INSERT statements, hard-coding column values would be tiresome and inefficient. Fortunately, you can use parameters to solve this problem. Parameters allow you specify different column values when running your program.

To execute a command containing parameters, you use the following high-level steps:

  1. Create a Command object containing a SQL statement with parameter placeholders. These placeholders mark the position where a parameter will be supplied.

  2. Add parameters to the Command object.

  3. Set the parameters to specified values.

  4. Execute the command.

Let's take a look at the details of the four steps when using parameters with SQL Server.

Step 1: Create a Command Object Containing a SQL Statement with Parameter Placeholders

This is straightforward: wherever you would normally place a column value in your SQL statement, you specify a parameter placeholder instead. A placeholder marks the position where a value will be supplied later.

The syntax you use for the placeholders depends on the database you are using. With SQL Server, example placeholders would be @CustomerID and @CompanyName. The following INSERT statement uses these placeholders for the CustomerID, CompanyName, and ContactName column values of the Customers table:

 INSERT INTO Customers   CustomerID, CompanyName, ContactName ) VALUES   @CustomerID, @CompanyName, @ContactName ) 

You can use a placeholder anywhere a column value is valid in a SELECT, INSERT, UPDATE, or DELETE statement. Here are some examples of SELECT, UPDATE, and DELETE statements with placeholders:

 SELECT * FROM Customers WHERE CustomerID = @CustomerID UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID DELETE FROM Customers WHERE CustomerID = @CustomerID 

Let's take a look at some code that creates a SqlCommand object and sets its CommandText property to an INSERT statement:

 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText =   "INSERT INTO Customers (" +   "  CustomerID, CompanyName, ContactName" +   ") VALUES (" +   "  @CustomerID, @CompanyName, @ContactName" +   ")"; 

This INSERT statement will be used to add a row to the Customers table. The column values for this row will be specified using parameters. All that's been done in the previous code is to create a SqlCommand object with an INSERT statement that has placeholders. Before you can execute this INSERT statement, you need to add the actual parameters to the SqlCommand object-and you'll do that in the next step.

Step 2: Add Parameters to the Command Object

To add parameters to your Command object, you use the Add() method. It is overloaded, and the version used in this section accepts three parameters:

  • The placeholder string for the parameter in your SQL statement. For example, @CustomerID is the first placeholder in the INSERT statement shown in the previous section.

  • The type for the column in the database. For SQL Server, these types are defined in the System .Data.SqlDbType enumeration. Table 8.9 shows these database types.

    Table 8.9: SqlDbType ENUMERATION MEMBERS

    MEMBER

    DESCRIPTION

    BigInt

    A 64-bit signed integer between -263 (-9,223,372,036,854,775,808) and 263-1 (9,223,372,036,854,775,807).

    Binary

    An array of bytes with a maximum length of 8,000.

    Bit

    An unsigned numeric value that can be 0, 1, or a null reference.

    Char

    A string of non-Unicode characters with a maximum length of 8,000.

    DateTime

    A date and time between 12:00:00 AM January 1, 1753 and 11:59:59 PM December 31, 9999. This is accurate to 3.33 milliseconds.

    Decimal

    Fixed precision and scale numeric value between -1038 + 1 and 1038 - 1.

    Float

    A 64-bit floating-point number between -1.79769313486232E308 and 1.79769313486232E308 with 15 significant figures of precision.

    Image

    An array of bytes with a maximum length of 231 - 1 (2,147,483,647).

    Int

    A 32-bit signed integer between -231 (-2,147,483,648) and 231 - 1 (2,147,483,647).

    Money

    A currency value between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. This is accurate to 1/10,000th of a currency unit.

    NChar

    A string of Unicode characters with a maximum length of 4,000.

    Ntext

    A string of Unicode characters with a maximum length of 230 - 1 (1,073,741,823).

    NVarChar

    A string of Unicode characters with a maximum length of 4,000.

    Real

    A 32-bit floating-point number between -3.402823E38 and 3.402823E38 with seven significant figures of precision.

    SmallDateTime

    A date and time between 12:00:00 AMJanuary 1, 1900 and 11:59:59 PM June 6, 2079. This is accurate to 1 minute.

    SmallInt

    A 16-bit signed integer between -215 (-32,768) and 215 - 1 (32,767).

    SmallMoney

    A currency value between -214,748.3648 and 214,748.3647. Accurate to 1/10,000th of a currency unit.

    Text

    A string of non-Unicode characters with a maximum length of 231 - 1 (2,147,483,647).

    Timestamp

    A date and time in the format yyyymmddhhmmss.

    TinyInt

    An 8-bit unsigned integer between 0 and 28 - 1 (255).

    UniqueIdentifier

    A 128-bit integer value (16 bytes) that that is unique across all computers and networks.

    VarBinary

    An array of bytes with a maximum length of 8,000.

    VarChar

    A string of non-Unicode characters with a maximum length of 4,000.

    Variant

    A data type that can contain numbers, strings, bytes, or dates.

  • The maximum length of the parameter value. You specify this parameter only when using variable length types, for example, Char and VarChar.

Earlier in step 1, the CommandText property for mySqlCommand had three placeholders and was set as follows:

 mySqlCommand.CommandText =   "INSERT INTO Customers (" +   "  CustomerID, CompanyName, ContactName" +   ") VALUES (" +   "  @CustomerID, @CompanyName, @ContactName" +   ")"; 

The following statements use the Add() method to add the three parameters to mySqlCommand:

 mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5); mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40); mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30); 

Notice that you call the Add() method through the Parameters property of mySqlCommand. This requires some explanation. A SqlCommand object stores parameters using a SqlParameterCollection object, which is a collection of SqlParameter objects (a SqlParameter object contains the details of a parameter). One of the SqlParameterCollection methods is Add(), which you use to add a SqlParameter object to the collection. Therefore, to add a parameter to mySqlCommand, you call the Add() method through its Parameters property.

As you can see from the previous code that added the three parameters to mySqlCommand, the @CustomerID parameter is defined as an NChar-a string of Unicode characters with a maximum length of 4,000. A value of 5 is passed as the third parameter to the Add() method for @CustomerID, meaning that a maximum of five characters may be supplied as the parameter value. Similarly, the @CompanyName and @ContactName parameters are defined as an NVarChar-a string of Unicode characters-with a maximum length of 40 and 30 characters respectively, as indicated by the third parameter to the Add() method. You'll see the setting of these parameters to values in the next step.

Step 3: Set the Parameters to Specified Values

You use the Value property of each parameter to set it to a specified value in your Command object. These values are substituted for the placeholders in your SQL statement.

The following example uses the Value property to set the values of the parameters added in the previous section:

 mySqlCommand.Parameters["@CustomerID"].Value = "J4COM"; mySqlCommand.Parameters["@CompanyName"].Value = "J4 Company"; mySqlCommand.Parameters["@ContactName"].Value = "Jason Price"; 

In this example, the @CustomerID, @CompanyName, and @ContactName parameters are set to J4COM, J4 Company, and Jason Price respectively. These values will be substituted for the placeholders in the INSERT statement, which becomes

 INSERT INTO Customers (   CustomerID, CompanyName, ContactName ) VALUES (   'J4COM', 'J4 Company', 'Jason Price' ) 

As you can see, the column values are the same as those specified in the Value property for each parameter.

You can also add a parameter and set its value in one step. For example:

 mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value =   "J4COM"; 

You can also set a parameter to a null value. As you learned in Chapter 2, "Introduction to Databases," a column defined as null can store a null value. A null value indicates that the column value is unknown. You indicate that a parameter can accept a null value by setting the IsNullable property to true (the default is false). For example:

 mySqlCommand.Parameters["@ContactName"].IsNullable = true; 

You can then set the Value property of the parameter to null using the System.DBNull class. For example:

 mySqlCommand.Parameters["@ContactName"].Value = DbNull.Value; 

The DBNull.Value property returns a null value. In this example, the final INSERT statement becomes:

 INSERT INTO Customers (   CustomerID, CompanyName, ContactName ) VALUES (   'J4COM', 'J4 Company', NULL ) 

The only thing left to do is to execute the SQL statement.

Step 4: Execute the Command

To execute the command, you use one of your Command object's execute methods. For example:

 mySqlCommand.ExecuteNonQuery(); 

This runs the INSERT statement that adds the new row to the Customers table. I used the ExecuteNonQuery() method because an INSERT statement doesn't return a result set from the database. You can also use this method to execute UPDATE and DELETE statements. If you were executing a SELECT statement, you would use the ExecuteReader(), ExecuteScalar(), or ExecuteXmlReader() methods.

Listing 8.10 illustrates these four steps.

Listing 8.10: USINGPARAMETERS.CS

start example
 /*   UsingParameters.cs illustrates how to run an INSERT   statement that uses parameters */ using System; using System.Data; using System.Data.SqlClient; class UsingParameters {   public static void Main()   {     SqlConnection mySqlConnection =       new SqlConnection(         "server=localhost;database=Northwind;uid=sa;pwd=sa"       );     mySqlConnection.Open();     // step 1: create a Command object containing a SQL statement     // with parameter placeholders     SqlCommand mySqlCommand = mySqlConnection.CreateCommand();     mySqlCommand.CommandText =       "INSERT INTO Customers (" +       "  CustomerID, CompanyName, ContactName" +       ") VALUES (" +       "  @CustomerID, @CompanyName, @ContactName" +       ")";     // step 2: add parameters to the Command object     mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);     mySqlCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40);     mySqlCommand.Parameters.Add("@ContactName", SqlDbType.NVarChar, 30);     // step 3: set the parameters to specified values     mySqlCommand.Parameters["@CustomerID"].Value = "J4COM";     mySqlCommand.Parameters["@CompanyName"].Value = "J4 Company";     mySqlCommand.Parameters["@ContactName"].IsNullable = true;     mySqlCommand.Parameters["@ContactName"].Value = DBNull.Value;     // step 4: execute the command     mySqlCommand.ExecuteNonQuery();     Console.WriteLine("Successfully added row to Customers table");     mySqlConnection.Close();   } } 
end example

The output from this program is as follows:

 Successfully added row to Customers table 




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

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