Parameters in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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

            this.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)), "Contact Name", System.Data.DataRowVersion.Original, null));

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

            SqlInsertCommand1.Command Text =
            @" 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

            SqlParameter workParam = 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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.