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 an 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). Sql data provider has SqlDbType, and ODBC data provider has an ODBC type. These type names and definitions differ, depending upon the provider you're using; for example the Money type is the same in ODBC and Sqldata providers, but is called Currency in OleDb data providers.
Not only does a parameter have DbType property, but a parameter has a Direction (input, output), size, and even a Value. Table 5-31 describes the OleDbParameter properties.
Table 5-31. The Ole Db Parameter 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.
|
|
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.
|
|
Value
|
Represents the Value of the parameter.
|
Listing 5-55 shows the construction of an OleDbParameter generated by the framework 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 5-55. Creating a parameter
Me.oleDbDeleteCommand2.Parameters.Add(NewSystem.Data.OleDb.OleDbParameter("ContactName", System.Data.OleDb.OleDbType.Char, 30, System.Data.ParameterDirection.Input, False, (CType((0), System.Byte)), (CType((0), System.Byte)), "Contact Name", System.Data.DataRowVersion.Original, Nothing))
Luckily, you'll find that are the framework will automatically generate the parameters for you because, as you can see, this is a lot of code to write for just one parameter. Imagine if you had 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 insertion 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 @ sing, such as
Dim Text As SqlInsertCommand1.Command = " INSERT INTO Customers (CustomerID, CompanyName, ContactName)" & "VALUE (@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 parameter in which you can further assign properties, such as
Dim workParam As SqlParameter = theSqlServerAdapter.InsertCommand.Parameter.Add("@ Customers ID", 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:
workParam.SourceColumn = "CustomerID"
workParam.SourceVersion = DataRowVersion.Original
The SourceVersion can take on the value Current or Original. The SourceVersion property helps the DataAdapter's Update command to 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 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.
Conclusion
Hope this article would have helped you in understanding Parameters in ADO.NET. See my other articles on the website on ADO.NET.