Using Command Objects in Code

Command, DataReader, and Parameter Object Reference

Now that we've examined the major features of the Command, DataReader, and Parameter objects, let's examine the properties and methods of each object.

Properties of the OleDbCommand Object

Table 4-1 lists the properties of the OleDbCommand object that you're most likely to use.

Table 4-1 Commonly Used Properties of the OleDbCommand Object

Property	Data Type	Description
CommandText	String	The text of the query that you want to execute.
CommandTimeout	Int32	Time (in seconds) that the adapter will wait for the query to execute before timing out. (Default = 30 seconds.)
CommandType	CommandType	Specifies the type of query to execute. (Default = Text.)
Connection	OleDbConnection	The connection to your data store that the Command will use to execute the query.
Parameters	OleDbParameterCollection	A collection of parameters for the query.
Transaction	OleDbTransaction	Specifies the transaction to use for the query.
UpdatedRowSource	UpdateRowSource	Controls how the results of the query will affect the current DataRow if the Command is used by calling the Update method of a DataAdapter. (Default = Both.) See Chapter 11 for more information on using this property when submitting pending changes to your database.

CommandTimeout Property

The CommandTimeout property determines how long, in seconds, the Command will wait for the results of your query before timing out. By default, this property is set to 30. If the query does not complete by the time specified in the CommandTimeout property, the Command will throw an exception.

Keep in mind that once the query starts returning results, the query won't time out. Let's say you want to use a DataAdapter to fetch the contents of a table into a DataSet. For the sake of argument, let's imagine that your table is so absurdly large that the process of fetching its contents takes more than 30 seconds, the default value for the Command object's CommandTimeout property. Because the Command that the DataAdapter uses retrieved the first row in less than the time specified in the CommandTimeout property, the query won't time out no matter how long it takes to retrieve the contents of the table—a minute, a day, or a year.

CommandType Property

ADO.NET can simplify the process of setting the text for your query through the CommandType property. You can set this property to any of the values in the CommandType enumeration (available in System.Data), which are described in Table 4-2.

Table 4-2 Members of the CommandType Enumeration

Constant	Value	Description
Text	1	The Command will not modify the contents of the CommandText property.
StoredProcedure	4	The Command will build a query to call a stored procedure using the value of the CommandText property as a stored procedure name.
TableDirect	512	The Command will prepend "SELECT * FROM " to the contents of the CommandText property.

By default, this property is set to Text. Using this default setting, the Command will use whatever text you've specified in the CommandText property to execute your query. In my opinion, you should leave the property set to the default. Here's why.

If you set the property to TableDirect, the Command will prepend "SELECT * FROM " to the contents of the CommandText property when you execute the query. This means that the Command will fetch all rows and all columns from the table—if the query succeeds.

If you query a table that has a space in its name, such as the sample Northwind database's Order Details table, the query will fail unless you surround the table name with a delimiter that the database can handle. I try to use square brackets rather than having to jump through hoops to embed double quote characters into my strings. Setting CommandType to TableDirect will not delimit your table name automatically. You still have to do that work yourself.

Of course, you can avoid such problems by not using spaces in the names of your tables, columns, views, and stored procedures. Seriously, how many times have database developers said, "Thank goodness I was able to put a space in that object name"?

	The constant name TableDirect is a bit of a misnomer and might lead ADO developers to believe that it maps to adCmdTableDirect in ADO's CommandTypeEnum. That's a perfectly logical assumption, but the constant actually maps to adCmdTable. Despite what the constant's name implies, setting CommandType to TableDirect doesn't make the Command fetch the contents of your table through the low-level interfaces that the Jet and SQL Server OLE DB providers support.

The StoredProcedure constant simplifies the process of calling a stored procedure, as shown here:

Visual Basic .NET

Dim cmd As New OleDbCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "MyStoredProc" cmd.CommandType = CommandType.CommandText cmd.CommandText = "{CALL MyStoredProc}"

Visual C# .NET

OleDbCommand cmd = new OleDbCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "MyStoredProc"; cmd.CommandType = CommandType.CommandText; cmd.CommandText = "{CALL MyStoredProc}";

The code snippet shows the standard syntax for calling stored procedures: {CALL MyStoredProc}. SQL Server also supports the EXEC MyStoredProc syntax. In fact, the SQL Server OLE DB provider will translate calls that use the CALL syntax and actually use the EXEC syntax when communicating directly with the database. For this reason, you might want to use the EXEC syntax to try to get your code to run just a tiny bit faster. I avoid using this syntax because I often have to write code that's back-end independent. Later in the chapter, we'll look at how to call parameterized stored procedures, which can add a touch of complexity to the syntax.

Like the TableDirect constant, setting CommandType to StoredProcedure doesn't delimit the stored procedure name when you execute your query. For that reason, I prefer leaving CommandType as Text and using the CALL syntax in the CommandText property.

Parameters Property

The Parameters property returns an OleDbParameterCollection, which contains a collection of OleDbParameter objects. We'll examine the properties and methods of the OleDbParameter object later in the chapter.

Transaction Property

You use the Command object's Transaction property to execute your Command within a transaction. If you've opened a Transaction object on your Connection and try to execute your Command without associating it with that Transaction via this property, the Command object will generate an exception.

UpdatedRowSource Property

The UpdatedRowSource property is designed to help you refetch data for the row you're updating using a DataAdapter and Command objects that contain updating logic. Table 4-3 lists the values accepted by UpdatedRowSource. We'll discuss the use of this property in Chapter 11.

Table 4-3 Members of the UpdateRowSource Enumeration

Constant	Value	Description
Both	3	Command will fetch new data for the row through both the first returned record and output parameters.
FirstReturnedRecord	2	Command will fetch new data for the row through the first returned record
None	0	Command will not fetch new data for the row upon execution.
OutputParameters	1	Command will fetch new data for the row through output parameters

Methods of the OleDbCommand Object

Now let's take a look at the methods of the OleDbCommand object, which are listed in Table 4-4.

Table 4-4 Commonly Used Methods of the OleDbCommand Object

Method	Description
Cancel	Cancels the execution of the query
CreateParameter	Creates a new parameter for the query
ExecuteNonQuery	Executes the query (for queries that do not return rows)
ExecuteReader	Executes the query and retrieves the results in an OleDbDataReader
ExecuteScalar	Executes the query and retrieves the first column of the first row, designed for singleton queries such as "SELECT COUNT(*) FROM MyTable WHERE..."
Prepare	Creates a prepared version of the query in the data store
ResetCommandTimeout	Resets the CommandTimeout property to its default of 30 seconds

Cancel Method

You can use the Cancel method to cancel the execution of a query. If the Command object whose Cancel method you've called is not currently executing a query, the Cancel method does nothing.

The Cancel method also causes the Command object to discard any unread rows on a DataReader object. The following sample code fetches the results of a simple query. The code displays the results, followed by the number of rows retrieved. In the code, there's a call to the Cancel method that's commented out. Remove the comment character(s) and re-run the code to demonstrate that the Cancel method discards the results of the query.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim strSQL As String = "SELECT CustomerID FROM Customers" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader() Dim intRowsRetrieved As Integer 'cmd.Cancel() Do While rdr.Read     Console.WriteLine(rdr.GetString(0))     intRowsRetrieved += 1 Loop Console.WriteLine(intRowsRetrieved & " row(s) retrieved") rdr.Close() cn.Close()

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; string strSQL = "SELECT CustomerID FROM Customers"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr = cmd.ExecuteReader(); int intRowsRetrieved = 0; //cmd.Cancel(); while (rdr.Read()) {     Console.WriteLine(rdr.GetString(0));     intRowsRetrieved++; } Console.WriteLine(intRowsRetrieved + " row(s) retrieved"); rdr.Close(); cn.Close();

ExecuteNonQuery Method

The ExecuteNonQuery method executes the query without creating a DataReader to fetch the rows returned by the query. Use ExecuteNonQuery if you want to issue an action query or don't want to examine the rows returned by the query. Values for return and output parameters are available upon completion of the call to ExecuteNonQuery.

ExecuteNonQuery returns an integer to indicate the number of rows modified by the query you've executed. If you're using batch queries, see the discussion of batch queries and the return value of ExecuteNonQuery earlier in this chapter.

ExecuteReader Method

If you want to examine the row(s) returned by a query, use the Command object's ExecuteReader method to return that data in a new DataReader object. We discussed the basic use of this method earlier in the chapter. However, there are some interesting options on the method.

The Command object's ExecuteReader method is overloaded and can accept a value from the CommandBehavior enumeration. Table 4-5 describes each of these options.

Table 4-5 Members of the CommandBehavior Enumeration

Constant	Value	Description
CloseConnection	32	Closing the DataReader will close the connection.
KeyInfo	4	Causes the DataReader to fetch primary key information for the columns in the result set.
SchemaOnly	2	The DataReader will contain only column information without actually running the query.
SequentialAccess	16	Values in the columns will be available only sequentially. For example, after you examine the contents of third column, you won't be able to examine the contents of the first two columns.
SingleResult	1	The DataReader will fetch the results of only the first row-returning query.
SingleRow	8	The DataReader will fetch only the first row of the first row-returning query.

CloseConnection

If you supply CloseConnection when calling the ExecuteReader method, when you call the Close method on the DataReader, the DataReader will call the Close method on the Connection with which it is associated.

This feature can be extremely handy if you're building business objects and passing data from one object to another. You might encounter situations in which you want a business object to return a DataReader to the calling object rather than returning the data in a DataTable or some other structure. In such cases, you might want the calling object to be able to close the Connection object after it's done reading the results of the query from the DataReader.

But what if you don't trust the calling object? You might not want to hand it a direct connection to the database. Using CloseConnection can simplify this scenario without compromising the security and architecture of your application.

KeyInfo and SchemaOnly

When we look at methods of the DataReader object, we'll discuss the GetSchemaTable method. This method returns metadata about the columns in the DataReader—column names, data types, and so on. Such information can be helpful if you're building code-generation tools. If you're going to use the DataReader object's GetSchemaTable method, you should also look at the KeyInfo and SchemaOnly options of the Command's ExecuteReader method.

If you call ExecuteReader and use the SchemaOnly value in the Options parameter, you'll retrieve schema information about the columns but you won't actually execute the query.

Using KeyInfo in the Options parameter forces the DataReader to fetch additional schema information from your data source to indicate whether the columns in the result set are part of the key columns in the tables in your data source.

If you use the SchemaOnly option, you don't need to include KeyInfo as well. The key information will be included in the schema automatically.

SequentialAccess

If you use the SequentialAccess option when calling ExecuteReader, the columns of data will be available only sequentially through the DataReader. For example, if you look at the contents of the second column, the contents of the first column will no longer be available.

Use of the SequentialAccess value might increase the performance of your DataReader slightly, depending on the data source you're using.

SingleRow and SingleResult

If you're interested in examining only the first row or first result set returned by your query, you might want to use SingleRow or SingleResult when calling ExecuteReader.

Supplying SingleRow in the Options parameter will create a DataReader that contains, at most, one row of data. If you issue a query that returns 10 rows of data but you use SingleRow in your call to ExecuteReader, only the first row of data will be available through the DataReader. All other rows will be discarded. Similarly, using SingleResult causes subsequent result sets to be discarded.

ExecuteScalar Method

The ExecuteScalar method is similar to ExecuteReader except that it returns the first column of the first row of the result set in a generic Object data type. If the query returns more than one cell of data, this data is discarded.

If your query returns a single cell of data, like the following query does, you can improve the performance of your code by using ExecuteScalar.

SELECT COUNT(*) FROM MyTable

Prepare Method

One of the major benefits of stored procedures is that they generally run faster than dynamic queries. This is because the database system can prepare an execution plan for them ahead of time. It's sort of like the difference between script code and compiled code. Script code is often more flexible because you can generate it at run time, but compiled code runs faster.

Most database systems support the notion of a "prepared" query. You can think of a prepared query as a temporary stored procedure. If you're going to execute the same query multiple times, you might get better performance by preparing the query.

To prepare a Command, you simply call its Prepare method.

If you're building multi-tiered applications, prepared queries aren't likely to improve the performance. In fact, I don't recommend using prepared queries in such situations. With multi-tiered applications, the code in your server components will likely connect, run a query or two, and then disconnect. Most multi-tiered applications take advantage of connection pooling at the middle-tier level.

Simply put, a connection pool will hold onto a connection for a brief amount of time. If your code requests a connection that matches one in a pool, your code will receive an open connection from the pool rather than opening a new one. This process can greatly improve the performance of your code at the middle tier.

However, if your connections are constantly recycled through connection pooling rather than being truly closed, your database won't have a chance to discard all of the temporary stored procedures that it created for your prepared queries. Recent versions of SQL Server have changed how these temporary stored procedures are stored in order to better handle this scenario, but you're better off not preparing your queries if you're building multi-tiered applications.

ResetCommandTimeout Method

Calling the ResetCommandTimeout method resets the Command object's CommandTimeout property to its default value of 30 seconds. If you find yourself wondering "Why would I need a property to do that?," you're not alone.

Properties of the OleDbDataReader Object

Now let's look at the properties of the OleDbDataReader (Table 4-6).

Table 4-6 Commonly Used Properties of the OleDbDataReader Object

Property	Data Type	Description
Depth	Int32	Indicates the depth of nesting for the current row (read-only).
FieldCount	Int32	Returns the number of fields contained by the DataReader (read-only).
IsClosed	Boolean	Indicates whether the DataReader is closed (read-only).
Item	Object	Returns the contents of a column for the current row (read-only).
RecordsAffected	Int32	Indicates the number of records affected by the queries submitted (read-only).

Depth Property and GetData Method

The Depth property and the GetData method are reserved for queries that return hierarchical data. These features are not supported in the current release of ADO.NET.

FieldCount Property

The FieldCount property returns an integer to indicate the number of columns of data in the result set.

IsClosed Property

The IsClosed property returns a Boolean value to indicate whether the DataReader object is closed.

Item Property

The DataReader object's Item property is similar, in form and function, to the DataRow object's Item property. You can supply the name of a column as a string or the integer position of a column, and the property will return the value stored in that column in the generic object data type.

If you know the data type of the column, you'll get better performance by calling the Get<DataType> method (such as GetInteger or GetString) instead.

RecordsAffected Property

You can use the RecordsAffected property to determine the number of rows that your action query (or queries) modified. If you want to execute a single action query, use the ExecuteNonQuery method of the Command object instead. The ExecuteNonQuery method returns the number of rows the action query affected.

If you're executing a batch of queries and you want to determine the number of rows affected, see the section on batch queries earlier in the chapter.

Methods of the OleDbDataReader Object

And now for your programming pleasure, Table 4-7 presents the methods of the OleDbDataReader that you're most likely to encounter.

Table 4-7 Commonly Used Methods of the OleDbDataReader Object

Method	Description
Close	Closes the DataReader.
Get<DataType>	Returns the contents of a column in the current row as the specified type based on its ordinal.
GetBytes	Retrieves an array of bytes from a column in the current row.
GetChars	Retrieves an array of characters from a column in the current row.
GetData	Returns a new DataReader from a column.
GetDataTypeName	Returns the name of the data type for a column based on its ordinal.
GetFieldType	Returns the data type for a column based on its ordinal.
GetName	Returns the name of a column based on its ordinal.
GetOrdinal	Returns the ordinal of a column based on its name.
GetSchemaTable	Returns the schema information (column names and data types) of the DataReader as a DataTable.
GetValue	Returns the value of a column based on its ordinal.
GetValues	Accepts an array that the DataReader will use to return the contents of the current column. This call returns a 32-bit integer that indicates the number of entries returned in the array.
IsDBNull	Indicates whether a column contains a Null value.
NextResult	Moves to the next result.
Read	Moves to the next row.

Read Method

The Read method accesses the next row of data. Remember that the first row in the result set will not be available through the DataReader until you call the Read method. The first time you call the Read method, the DataReader will move to the first row in the result set. Subsequent calls to Read will move to the next row of data.

The Read method also returns a Boolean value to indicate whether there are any more results for the query. The sample code we examined earlier continually examines results until the Read method returns False.

GetValue Method

The GetValue method is similar to the Item property. Supply an integer, and the GetValue method will return the contents of that column in the generic object type. The GetValue method and the various Get<DataType> methods accept only integers for the column index and do not perform string-based lookups such as the Item property.

The DataReader is designed for speed; referencing an item in a collection by its ordinal value is faster than having the collection locate the item by its name.

Get<DataType> Methods

The DataReader also offers methods that return specific data types. If you know that a column contains string data, you can call the GetValue method of the DataReader and convert the data to a string or simply call the GetString method, as shown here:

Visual Basic .NET

Dim strCompanyName As String Dim rdr As OleDbDataReader ... strCompanyName = rdr.GetString(intCompanyNameIndex) 'or strCompanyName = rdr.GetValue(intCompanyNameIndex).ToString

Visual C# .NET

string strCompanyName; OleDbDataReader rdr; ... strCompanyName = rdr.GetString(intCompanyNameIndex); //or strCompanyName = rdr.GetValue(intCompanyNameIndex).ToString();

The DataReader has methods to return each of the data types available in the .NET Framework—GetByte, GetChar, GetDateTime, and so on.

GetValues Method

The GetValues method lets you store the contents of a row in an array. If you want to retrieve the contents of each column as quickly as possible, using the GetValues method will provide better performance than checking the value of each column separately.

The DataAdapter uses a DataReader to fetch data from your database to store the results in DataTables. To provide the best performance possible, the DataAdapter objects in the .NET data providers included in Visual Studio .NET use the DataReader object's GetValues method. Here's a simple example of how to use GetValues:

Visual Basic .NET

Dim rdr As OleDbDataReader = cmd.ExecuteReader() Dim aData(rdr.FieldCount - 1) As Object While rdr.Read     rdr.GetValues(aData)     Console.WriteLine(aData(0).ToString) End While

Visual C# .NET

OleDbDataReader rdr = cmd.ExecuteReader(); object[] aData = new object[rdr.FieldCount]; while (rdr.Read()) {     rdr.GetValues(aData);     Console.WriteLine(aData[0].ToString()); }

	Visual Basic .NET and Visual C# .NET create arrays differently. The preceding code snippets take this difference into account. For example, Dim aData(4) As Object creates an array of length 5 (0 to 4) in Visual Basic and Visual Basic .NET, but object[] aData = new object[4]; creates an array of length 4 (0 to 3) in Visual C# .NET.

NextResult Method

If you're working with batch queries that return multiple result sets, use the NextResult method to move to the next set of results. Like the Read method, NextResult returns a Boolean value to indicate whether there are more results.

The sample code under "Questions That Should Be Asked More Frequently" shows how to use a DataReader to examine the contents of a batch query. It also shows how to use the NextResult method in a loop.

Close Method

When you're using DataReader objects, it's important that you loop through the results and close the DataReader as quickly as possible. Your Connection object is blocked from performing any other work while a live firehose cursor is open on the connection. If you try to use a Connection that has an open DataReader on it, you'll receive an exception that states that the operation "requires an open and available connection."

	Some databases allow you to have multiple queries with pending results on the same connection. In the initial release of ADO.NET, having an open DataReader on a connection prevents you from performing any other operations on that connection until you've closed the DataReader object, regardless of whether the database supports having multiple queries with pending results on the same connection. This behavior might change in a future release of ADO.NET.

Developers who have some experience with ADO might be surprised by this restriction, but those who've used RDO might not. Various Microsoft data access technologies have handled this scenario differently.

If you try to open two firehose cursors against a SQL Server database using ADO, everything will work and you won't receive an error. This is because the OLE DB specification states that when the current connection is blocked, the OLE DB provider will perform the requested action on a new connection.

RDO developers might recognize the error message "Connection is busy with results from another hstmt." ODBC does not do any behind-the-scenes work to try to help you out. If you try to use a connection that's busy, you simply receive an error message.

Which of these approaches (raising an error or performing the desired action on a new connection) is better? Developers, both inside and outside of Microsoft, can't seem to agree. In fact, each successive Microsoft data access technology has handled the scenario differently than its predecessor. VBSQL raises an error, DAO/Jet creates a new connection, RDO raises an error, ADO creates a new connection, and ADO.NET raises an error.

GetName, GetOrdinal, and GetDataTypeName Methods

The DataReader has methods that you can use to learn more about the results returned by your query. If you want to determine the name of a particular column, you can call the GetName method. If you already know the column name you want to access but don't know its ordinal position within the result set, you can pass the column name into the GetOrdinal method to retrieve its ordinal position. The GetDataTypeName method accepts an integer denoting the ordinal position of the column and returns the data type for that column as a string.

GetSchemaTable Method

The DataReader object's GetSchemaTable method is similar to the DataAdapter object's FillSchema method. Each method lets you create a DataTable containing DataColumn objects that correspond to the columns returned by your query. The GetSchemaTable method accepts no parameters and returns a new DataTable. The DataTable contains a DataColumn for each column returned by your query, but the Rows collection of the DataTable is empty. GetSchemaTable populates the new DataTable with schema information only.

The data that GetSchemaTable returns might be a little difficult to grasp initially. The GetSchemaTable method returns a DataTable with a predefined structure. Each DataRow in the DataTable returned by this method corresponds to a different column in the query results, and the DataColumn objects represent properties or attributes for those columns.

The following code snippet prints the name and database data type for each column the query returns.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader Dim tbl As DataTable = rdr.GetSchemaTable Dim row As DataRow For Each row In tbl.Rows     Console.WriteLine(row("ColumnName").ToString & " - " & _                       CType(row("ProviderType"), OleDbType).ToString) Next row

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr = cmd.ExecuteReader(); DataTable tbl = rdr.GetSchemaTable(); foreach (DataRow row in tbl.Rows)     Console.WriteLine(row["ColumnName"] + " - " +                       ((OleDbType) row["ProviderType"]).ToString());

	The code snippet for each language converts the integer stored in the ProviderType column to the OleDbType enumeration.

Various .NET data providers use different table schemas in the DataTable returned by GetSchemaTable. For example, the DataTable returned by the SQL Server .NET data provider DataReader object's GetSchemaTable method includes columns not available through the OLE DB .NET data provider.

GetData Method and Depth Property

The Depth property and the GetData method are reserved for queries that return hierarchical data. These features are not supported in the current release of ADO.NET.

Creating Parameter Objects

The Parameter has six constructors. The ParameterCollection has six overloaded Add methods that you can use to create a Parameter and append it to the collection. You can also use the CreateParameter method on the Command. So many choices.

Which method of creating a Parameter is right for you? That depends on which properties on the Parameter you want to set. One of the constructors for OleDbParameter that lets you supply values for the ParameterName, OleDbType, Size, Direction, IsNullable, Precision, Scale, SourceColumn, SourceVersion, and Value properties. Think about the properties you want to set and then use the constructor that provides the functionality you need.

Properties of the OleDbParameter Object

Table 4-8 lists the commonly used properties of the OleDbParameter object.

Table 4-8 Commonly Used Properties of the OleDbParameter Object

Property Name	Data Type	Description
DataType	Type	Specifies the data type for the parameter object.
DbType	OleDbType	Specifies the database data type for the parameter.
Direction	ParameterDirection	Specifies the direction for the parameter—input, output, input/output, or return.
IsNullable	Boolean	Indicates whether the parameter can accept Null.
OleDbType	OleDbType	Specifies the OLE DB data type for the parameter.
ParameterName	String	Specifies the name of the parameter.
Precision	Byte	Specifies the precision for the parameter.
Scale	Byte	Specifies the numeric scale for the parameter.
Size	Int32	Specifies the size of the parameter.
SourceColumn	String	Specifies the name of the column in the DataSet that this parameter references. See Chapter 10 for more information on binding query parameters to DataSet objects.
SourceVersion	DataRowVersion	Specifies version (current or original) of the column in the DataSet that this parameter references. See Chapter 10 for more information on binding query parameters to DataSet objects.
Value	Object	Specifies the value for the parameter.

ParameterName Property

Generally speaking, the ParameterName property of the Parameter is designed solely to let you locate the desired Parameter in a Command object's Parameters collection. If you're calling a stored procedure with the OLE DB .NET data provider, for example, you don't need to have the ParameterName property on your Parameter objects match the names of the parameters in your stored procedure. But setting the ParameterName property on your Parameter objects can make your code easier to read.

	The SQL Server .NET data provider matches your Parameter objects to the parameter markers in your query based on the ParameterName property of the Parameter objects. So, if you use SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders WHERE CustomerID = @CustomerID as your query, you need to set the ParameterName property of your Parameter to @CustomerID.

Direction Property

If you're calling a stored procedure and you want to use output or return parameters, you should set the Direction property of your Parameter to one of the values listed in Table 4-9.

Table 4-9 Members of the ParameterDirection Enumeration

Constant	Value	Description
Input	1	Default value. The parameter is input-only.
Output	2	The parameter is output-only.
InputOutput	3	The parameter is input/output.
ReturnValue	6	The parameter will contain the return value of a stored procedure.

Because the default value for Direction is Input, you need to explicitly set this property only on Parameter objects that are not input-only.

Most code generation tools will query your database for parameter information, including the direction of the parameters. Even if you're using a robust code generation tool, such as the ones included in Visual Studio .NET, you might still need to modify the Direction value of your Parameter objects in some cases.

Why, you ask? Most databases support the use of input, output, and input-output parameters on stored procedures, but not all databases have language constructs to let you explicitly specify the direction for your stored procedure parameters. SQL Server, for example, supports the OUTPUTkeyword in stored procedure definitions to specify that the parameter can return a value. However, the definition of the parameter in the stored procedure is the same regardless of whether the parameter is input/output or output-only. As a result, code generation tools cannot determine whether the parameter is input/output or output-only. The Visual Studio .NET tools assume that the parameter is input/output. If you want an output-only parameter, you must set the direction explicitly in your code.

Value Property

Use the Value property to check or set the value of your Parameter. This property contains an Object data type. As a result, you might need to convert the data in order to store it in a data type such as a string or integer.

SourceColumn and SourceVersion Properties

The SourceColumn and SourceVersion properties control how the Parameter fetches data from a DataRow when you submit pending changes to your database by calling the Update method on the DataAdapter.

I'll cover this feature in much more depth in Chapter 10 when I cover updating your database.

DbType and OleDbType Properties

The Parameter is the only class in the ADO.NET object model that requires you to use the data types used by your database.

For example, when you retrieve the CustomerID field from the Customers table into a DataSet using a DataAdapter, you don't need to know whether the field in the database is a fixed length or a variable length, nor do you need to know whether the field in the database can handle Unicode data. The data type for the DataColumn object is simply a string.

The DataColumn object's DataType controls the data type that ADO.NET will use to store the contents of the column and accepts a .NET type as returned by the GetType or typeof function, depending on your language of choice. This data type has a loose connection to the data type that the database uses to store the data. String-based database data types (such as char and varchar) are mapped to the .NET data type String, noninteger numeric database data types (money, decimal, numeric) are mapped to the .NET data type Decimal, and so forth.

The data type for the Parameter must be more precise. In the earlier code snippet, we used the query

SELECT OrderID, CustomerID, EmployeeID, OrderDate     FROM Orders WHERE CustomerID = ?

with a parameter whose data type is wchar (the w stands for wide to indicate that the string handles double-byte Unicode characters rather than single-byte ANSI characters) and whose length is 5. If we don't use the appropriate data type for the parameter, the database might not handle the information stored in the parameter the way we expect.

Each Parameter exposes a DbType property and a data type property that's specific to the .NET data provider. For example, the OleDbParameter has an OleDbType property and the SqlParameter has a SqlDbType property.

The DbType and OleDbType properties are closely related. Setting the value of one of these properties affects the value of the other. For example, if you set the DbType property of an OleDbParameter to DbType.Int32, you're implicitly setting the OleDbType to OleDbType.Integer. Similarly, if you set the OleDbType to OleDbType.UnsignedTinyInt, you're implicitly setting the DbType property to DbType.Byte.

Precision, Scale, and Size Properties

When you define the structure for a table in a database, some data types require that you specify additional information beyond simply the name of the data type. Binary and character-based columns often have a maximum size. If you're using a Parameter with such data, you must set the Size property to the desired size. Numeric data types often let you specify the scale (number of digits) and precision (number of digits to the right of the decimal point).