Parameters in ADO.NET

This article has been excerpted from the 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 of information for the parameter. It consists of everything from strings to Global Unique Identifiers (GUIDs). SQL data provider has SqlDbType, and the 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 OleDbParameter properties.
Table 5-31. The Ole Db Parameter Class properties
Represents the DbType of the parameter.
Represents the direction of a parameter. A parameter can be input-only, Output-only, bi-directional, or a stored procedure.
Represents whether a parameter accepts null values.
Represents the OleDbType of the parameter.
Represents the name of the parameter.
Represents the maximum number of digits used to represent the Value property.
Represents the decimal places to which Value is resolved.
Represents the maximum size in bytes a column can store.
Represents the source column mapped to the DataSet.
Represents the DataRow version.
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
  1. 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 is 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 @ sign, such as
  1. SqlInsertCommand1.Command Text =  
  2.     @ " INSERT INTO Customers (CustomerID, CompanyName, ContactName)" +  
  3.     "VALUE (@CustomerID, @CompanyName, @ContactName)"
In OleDb, parameters appear as question marks such as
  1. 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
  1. 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:
  1. workParam.SourceColumn = "CustomerID";  
  2. 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.


Hope this article would have helped you in understanding Parameters in ADO.NET. See my other articles on the website on ADO.NET.
adobook.jpg This essential guide to Microsoft's ADO.NET overviews C# then leads you toward a deeper understanding of ADO.NET.

Similar Articles
Founded in 2003, Mindcracker is the authority in custom software development and innovation. We put best practices into action. We deliver solutions based on consumer and industry analysis.