Staying within the Parameters

ADO.NET wraps a class around the parameters used for each column of the database. You can use the parameters in conjunction with SelectCommand to help you to select data for the DataSet. You also use it in conjunction with the other commands of the CommandDataSet (InsertCommand, UpdateCommand, DeleteCommand) to place data into the DataSet. These are generated automatically when you insert a OleDbDataAdapter component from the Toolbox.

The OleDbType describes the type information for the parameter. It consists of everything from strings to Global Unique Identifiers (GUIDs).

The Sql data provider has a SqlDbType, and the Odbc data provider has an OdbcType. These type names and definitions differ, depending upon the provider you're using; for example, the Money type is the same in Odbc and Sql data providers, but it's called Currency in OleDb data providers.

Not only does a parameter have a DbType property, but a parameter has a Direction (input, output), Size, and even a Value. Table 4-14 describes the SqlParameter properties.

Table 4-14: The SqlParameter Class Properties

PROPERTY	DESCRIPTION
DbType	Represents the DbType of the parameter.
Direction	Represents the direction of a parameter. A parameter can be input-only, output-only, bi-directional, or a stored procedure.
IsNullable	Represents whether a parameter accepts null values.
OleDbType	Represents the OleDbType of the parameter.
Offset	Represents the offset to the Value property.
ParameterName	Represents the name of the parameter.
Precision	Represents the maximum number of digits used to represent the Value property.
Scale	Represents the decimal places to which Value is resolved.
Size	Represents the maximum size in bytes a column can store.
SourceColumn	Represents the source column mapped to the DataSet.
SourceVersion	Represents the DataRow version.
SqlDbType	Represents the SqlDbType of the parameter
Value	Represents the value of the parameter.

Now you're going to see the construction of parameters using the Integrated Development Environment (IDE) as well as manually. Listing 4-35 shows the construction of an OleDbParameter generated by the IDE for the Northwind database. All commands have a collection of parameters; in this example, the parameter ContactName is being added to a command used for deleting from the database.

Listing 4-35: Creating a Parameter

 Me.oleDbDeleteCommand2.Parameters.Add(New System.Data.OleDb.OleDbParameter("ContactName", System.Data.OleDb.OleDbType.Char, 30, System.Data.ParameterDirection.Input, False, ((System.Byte)(0)), ((System.Byte)(0)), "ContactName", System.Data.DataRowVersion.Original, Nothing)) 

Luckily, the IDE automatically generates the parameters for you; as you can see, this is a lot of code to write for just one parameter. Imagine if you had to manually deal with a database table of 50 parameters!

You need to create and add parameters to the command for each parameter reference that appears in the SQL command. If the SQL command only describes a single row insert or update, then you don't have parameters. But, more often than not, when you're using DataSets, DataTables, and DataRows, you'll need parameters because these in-memory structures operate on several rows.

Parameters appear in a SQL Server INSERT command proceeded by an @ sign, as shown in the following code:

 sqlInsertCommand1.CommandText = @"INSERT INTO Customers(CustomerID, CompanyName, ContactName)"+ " VALUES (@CustomerID, @CompanyName, @ContactName)" In OleDb, parameters appear as question marks such as: oleDbInsertCommand2.CommandText = "INSERT INTO Customers(Address, City, CompanyName, ContactName)"+ " VALUES (?, ?, ?, ?)" 

To add the parameter @CustomerID to the InsertCommand of the SqlDataAdapter, simply call Add on the command's ParameterCollection. This will return a parameter in which you can further assign properties, such as:

 SqlParameter  workParam = theSqlServerAdapter.InsertCommand.             Parameters.Add("@CustomerID", SqlDbType.Int) 

Two other crucial properties are the name of the column that the parameter is mapping to and the RowVersion. Typically, it's good to give the parameter the same name as the column of the database to avoid the confusion:

 workParam.SourceColumn = "CustomerID" workParam.SourceVersion = DataRowVersion.Original 

The SourceVersion can be either Current or Original. The SourceVersion property helps the DataAdapter's Update command decide which value version to load when executing the SQL UpdateCommand on the database. (InsertCommand and DeleteCommand ignore the SourceVersion.) The SourceVersion property comes in handy when you're updating a row whose primary key you may want to change. If the value is DataRowVersion.Original, then the primary key will retain its original value.

You just saw an example of a parameter generated by the VS .NET IDE. It's always a better idea to write your own code if you really want to know what goes under the hood. So, let's create parameters and use them in a SqlCommand. The following code creates a parameter by reading a value from a TextBox control and deletes the record from the Categories table that matches the CategoryID as TextBox1.Text:

 dim strId as string = TextBox1.Text    dim sql = "DELETE Categories WHERE CategoryID=@catID"    dim cmd as SqlCommand = new SqlCommand(sql, conn)    cmd.Parameters.Add("@catID", strId)    cmd.ExecuteNonQuery() 

As you can see, the Parameters property of SqlCommand returns an object of type SqlParameters, which represents a collection of parameters. It provides an Add method to add a parameter to the collection.

Now let's construct a SqlCommand with an UPDATE SQL statement using three parameters. The following code creates and adds three parameters to the collection. The code also constructs an UPDATE SQL statement with parameters and calls the ExecuteNonQuery method to execute the command:

 dim strId as string = TextBox1.Text    dim strCatName as string = TextBox2.Text    dim strDesc as string = TextBox3.Text    dim sql = "UPDATE Categories SET CategoryName=@catName, " & _    " Description=@desc WHERE CategoryID=@catID"    dim cmd as SqlCommand = new SqlCommand(sql, conn)    cmd.Parameters.Add("@catID", strId)    cmd.Parameters.Add("@catName", strCatName)    cmd.Parameters.Add("@desc", strDesc)    cmd.ExecuteNonQuery() 

You can even directly assign parameter values by using the Value property. For example, the following code adds two parameters to the command with the values NewCategory and New Description:

 cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 80).Value = _ "NewCategory" cmd.Parameters.Add("@Description", SqlDbType.Int).Value = _ "New Description"