Chapter 13 ADO.NET

Download Sample App from here

ADO.NET provides consistent access to data sources such as Microsoft SQL Server and XML, as well as to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update the data that they contain.


ADO.NET separates data access from data manipulation into discrete components that can be used separately or in tandem. ADO.NET includes .NET Framework data providers for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in an ADO.NET DataSet object in order to be exposed to the user in an ad hoc manner, combined with data from multiple sources, or remoted between tiers. The ADO.NET DataSet object can also be used independently of a .NET Framework data provider to manage data local to the application or sourced from XML.


The ADO.NET classes are found in System.Data.dll, and are integrated with the XML classes found in System.Xml.dll. When compiling code that uses the System.Data namespace, reference both System.Data.dll and System.Xml.dll. For an example of an ADO.NET application that connects to a database, retrieves data from it, and then displays that data in a command prompt.


ADO.NET provides functionality to developers writing managed code similar to the functionality provided to native component object model (COM) developers by ActiveX Data Objects (ADO).


ADO.NET Architecture


There are two components of ADO.NET that you can use to access and manipulate data:


.NET Framework data providers
The DataSet


.NET Framework Data Providers


The .NET Framework Data Providers are components that have been explicitly designed for data manipulation and fast, forward-only, read-only access to data. The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. The DataReader provides a high-performance stream of data from the data source. Finally, the DataAdapter provides the bridge between the DataSet object and the data source. The DataAdapter uses Command objects to execute SQL commands at the data source to both load the DataSet with data, and reconcile changes made to the data in the DataSet back to the data source.


The DataSet


The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint, and relation information about the data in the DataTable objects.


The following diagram illustrates the relationship between a .NET Framework data provider and a DataSet.

Choosing a DataReader or a DataSet
When deciding whether your application should use a DataReader, you should consider the type of functionality that your application requires. Use a DataSet to do the following:

• Cache data locally in your application so that you can manipulate it. If you only need to read the results of a query, the DataReader is the better choice.
• 
  
• Remote data between tiers or from an XML Web service.
• 
  
• Interact with data dynamically such as binding to a Windows Forms control or combining and relating data from multiple sources.
• 
  
• Perform extensive processing on data without requiring an open connection to the data source, which frees the connection to be used by other clients.

If you do not require the functionality provided by the DataSet, you can improve the performance of your application by using the DataReader to return your data in a forward-only, read-only fashion. Although the DataAdapter uses the DataReader to fill the contents of a DataSet, by using the DataReader you can boost performance because you will save memory that would be consumed by the DataSet, as well as avoid the processing required to create and fill the contents of the DataSet.


XML and ADO.NET


ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET was designed hand-in-hand with the XML classes in the .NET Framework; both are components of a single architecture.


ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World-Wide Web Consortium (W3C) compliant XML, including its schema as XML Schema definition language (XSD) schema, regardless of the source of the data in the DataSet. Because of the native serialization format of the DataSet is XML, it is an excellent medium for moving data between tiers, making the DataSet an optimal choice for remoting data and schema context to and from an XML Web service.

Core Objects of .NET Framework Data Providers

Object	Description
Connection	Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.
Command	Executes a command against a data source. Exposes Parameters and can execute within the scope of a Transaction from a Connection. The base class for all Commandobjects is the DbCommand class.
DataReader	Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.
DataAdapter	Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.

In addition to the core classes listed in the table above, a .NET Framework data provider also contains the classes listed in the following table.

Object	Description
Transaction	Enables you to enlist commands in transactions at the data source. The base class for all Transaction objects is the DbTransaction class.
CommandBuilder	A helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. The base class for all CommandBuilder objects is the DbCommandBuilder class.
ConnectionStringBuilder	A helper object that provides a simple way to create and manage the contents of connection strings used by the Connection objects. The base class for allConnectionStringBuilder objects is the DbConnectionStringBuilder class.
Parameter	Defines input, output, and return value parameters for commands and stored procedures. The base class for all Parameter objects is the DbParameter class.
Exception	Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception. The base class for all Exception objects is the DbException class.
Error	Exposes the information from a warning or error returned by a data source.
ClientPermission	Provided for .NET Framework data provider code access security attributes. The base class for all ClientPermission objects is the DBDataPermission class.

ADO.NET DataSet


The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, with XML data, or to manage data local to the application. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. The following illustration shows the DataSet object model.

The methods and objects in a DataSet are consistent with those in the relational database model.
The DataSet can also persist and reload its contents as XML, and its schema as XML Schema definition language (XSD) schema. 


The DataTableCollection

An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.
A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by a DataColumnCollection, and constraints represented by a ConstraintCollection, which together define the schema of the table. A DataTable also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with its current state, a DataRow retains both its current and original versions to identify changes to the values stored in the row.

The DataRelationCollection

A DataSet contains relationships in its DataRelationCollection object. A relationship, represented by the DataRelation object, associates rows in one DataTable with rows in anotherDataTable. A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.
Relationships enable navigation from one table to another within a DataSet. The essential elements of a DataRelation are the name of the relationship, the name of the tables being related, and the related columns in each table. Relationships can be built with more than one column per table by specifying an array of DataColumn objects as the key columns. When you add a relationship to the DataRelationCollection, you can optionally add a UniqueKeyConstraint and a ForeignKeyConstraint to enforce integrity constraints when changes are made to related column values.

ExtendedProperties

The DataSet, DataTable, and DataColumn all have an ExtendedProperties property. ExtendedProperties is a PropertyCollection where you can place custom information, such as the SELECT statement that was used to generate the result set, or the time when the data was generated. The ExtendedProperties collection is persisted with the schema information for theDataSet.

Connecting to Data Sources


To connect to Microsoft SQL Server 7.0 or later, use the SqlConnection object of the .NET Framework Data Provider for SQL Server. To connect to an OLE DB data source, or to Microsoft SQL Server 6.x or earlier, use the OleDbConnection object of the .NET Framework Data Provider for OLE DB. To connect to an ODBC data source, use the OdbcConnection object of the .NET Framework Data Provider for ODBC. To connect to an Oracle data source, use the OracleConnection object of the .NET Framework Data Provider for Oracle. For securely storing and retrieving connection strings, see Securing Connection Strings.


Closing Connections


You can also use the Close or Dispose methods of the connection object for the provider that you are using. Connections that are not explicitly closed might not be added or returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Using connection As New SqlConnection(connectionString)
connection.Open()
' Do work here.
End Using

Connection Strings in General


To begin with, connection strings are simply string variables which contain database connection information. There's nothing magical about them so you can use all the string functions you're used to using to manipulate them if you need to. The only thing that makes them a connection string is that they are then passed to ADO which will interpret them and act accordingly. Since they're going to be passed to ADO, they need to be in a format ADO understands. Your standard connection string contains a number of arguments set equal to their associated values and seperated by semicolons. It's basic form is something like this:


argument1=value1; argument2=value2; argument3=value3;
Of those arguments, there are only four that ADO understands. The rest are simply passed through to the provider. The four ADO recognizes are Provider, File Name, Remote Provider and Remote Server. The last two are only used with RDS and I won't be covering them.


Microsoft.Jet.OLEDB.4.0


This is the OLE DB provider for Access. To connect to an Access database using this provider, the only other attribute required to make a connection is the Data Source attribute which is used to specify the full path and file name of the Access .mdb file. A minimal OLE DB Provider for Jet connection string would therefore look something like this:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb;

The only other commonly used parameters are User ID and Password which are used to specify user authentication values. A user name of "admin" and a blank password are the defaults we tend to use resulting in the following default connection string:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\path\filename.mdb; User ID=admin; Password=;
SQLOLEDB

The OLE DB provider for MS SQL Server is a little bit more complex. The Data Source parameter is once again used, but this time it specifies the name or address of the SQL Server. Because SQL Server can run multiple databases and we haven't yet picked one we need another parameter to indicate the databse we want to use. This parameter is called Initial Catalog. That combined with the same parameters as above for User ID and Password results in this basic form:


Provider=SQLOLEDB; Data Source=server_name_or_address; Initial Catalog=database_name; User ID=username; Password=password;
I'm not sure if it's more because of the networking required or the additional capabilities of the database, but there are more parameters one can specify when utilizing SQLOLEDB then there are with MS Jet. You can get a list of all of them from the SQL Server ADO Programmer's Reference in the SQL Server Books Online. In particular, you're looking for the Provider-Specific Connection Parameters of the connection object.


Honestly though... the only other parameter I ever use is Network Library and this one I use pretty religiously. It's relatively important and has been the key to solving many a connection problem. You see, Named Pipes is often set up as the default network library to use when connecting the client (IIS) to the server (SQL Server). While this can be changed via the SQL Server Client Network Utility, it rarely is. This configuration is fine if the client and server are on the same network, but in today's TCP/IP based world, Named Pipes is often not the network library of choice. To force the client to use TCP/IP to connect (ignoring the defaults altogether) specify a value of dbmssocn for the Network Library parameter.


ODBC Connection Strings


While I indicated earlier that we prefer OLE DB, ODBC (MSDASQL) has been around for a long time and back when ASP was first released it was really your only option for ASP database connections. Since it is still so widely in use and is actually the default provider, I felt I'd be somewhat remiss if I didn't cover it as well.


There are two styles of ODBC connection string: those that utilize a DSN (Data Source Name) and those that do not (often called DSN-less connections). The parameters they use are identical, it's just a matter of where they are specified and stored.


DSN Connections


DSN connections store their connection information in the Windows Registry. Naturally it's not a good idea to have people poking around in the registry so in the Windows NT4 Control Panel you'll find an applet called "ODBC Data Sources" which provides a wizard like interface to collecting connection settings. In Windows 2000, it's been moved to the "Administrative Tools" Folder in the Control Panel and renamed "Data Sources (ODBC)", but it functions the same.


In a nutshell, it's only real functionality is to help you create a connection string, save these connection settings to the registry, and associate them with a unique DSN. If you're using DSNs for ASP, make sure they're System DSNs so that they're available for all users. Since these settings are all previously saved, the connection string becomes simplicity itself:


Provider=MSDASQL; DSN=data_source_name;
or since MSDASQL is the default, simply:


DSN=data_source_name;
If you need to, you can also specify username and password information, but the parameter names are slightly different then with OLE DB resulting in a fully qualified connection to a DSN that looks something like this:

Provider=MSDASQL; DSN=data_source_name; UID=username; PWD=password;

Any other parameters you need, including what driver to use and even what type of database to connect to, is set via the wizard-like setup in the DSN configuration utility in the control panel discussed earlier.


DSN-less Connections


A DSN-less connection is operationally identical to a DSN except that the server doesn't have to access the registry to access the parameters since they're all specified in the connection string itself.


The only parameter required by all ODBC connection strings is Driver which specifies which driver to use. Unfortunately this isn't enough to get connected to anything and additional information is required by each driver. Most have a similar syntax and I'll provide samples of the couple most popular ones below:


Microsoft Access

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

Microsoft Excel

Provider=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\path\filename.xls;

Microsoft Text

Provider=MSDASQL; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\path\;

Notice there is no file name. The directory acts like a database with files working in the role of tables. Hence, you don't specify the file name until opening a recordset. It's a little weird... I recommend you experiment with it some first if you find a need to use this driver.


Microsoft SQL Server


The SQL Server driver again requires some additional information to get connected:

Provider=MSDASQL; Driver={SQL Server}; Server=server_name_or_address; Database=database_name; UID=username; PWD=password;

A few final notes about all the ODBC connections above. There is an interesting bug that I seem to remember causing problems for a while. If the above strings aren't working, try removing the space following the semi-colon after the Provider parameter. There used to be a bug in the OLE DB Provider for ODBC that would cause this to fail:

Provider=MSDASQL; Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

but this to work:

Provider=MSDASQL;Driver={Microsoft Access Driver (*.mdb)}; DBQ=C:\path\filename.mdb;

And remember that MSDASQL is the default value for Provider so if you want to, you can simply leave the parameter off altogether. In fact, you'll notice that almost all the sample code you run across does leave it off. I've included it in the samples above for illustration and because the Provider is a relatively central point to get in order to really understand how all these parameters fit together to allow these connection strings to actually get you connected.


Working with Commands


After establishing a connection to a data source, you can execute commands and return results from the data source using a DbCommand object. You can create a command using one of the command constructors for the .NET Framework data provider you are working with. Constructors can take optional arguments, such as an SQL statement to execute at the data source, a DbConnection object, or a DbTransaction object. You can also configure those objects as properties of the command. You can also create a command for a particular connection using the CreateCommand method of a DbConnection object. The SQL statement being executed by the command can be configured using the CommandText property.


Each .NET Framework data provider included with the .NET Framework has a Command object. The .NET Framework Data Provider for OLE DB includes an OleDbCommand object, the .NET Framework Data Provider for SQL Server includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the .NET Framework Data Provider for Oracle includes an OracleCommand object.



Each .NET Framework data provider included with the .NET Framework has its own command object that inherits from DbCommand. The .NET Framework Data Provider for OLE DB includes anOleDbCommand object, the .NET Framework Data Provider for SQL Server includes a SqlCommand object, the .NET Framework Data Provider for ODBC includes an OdbcCommand object, and the .NET Framework Data Provider for Oracle includes an OracleCommand object. Each of these objects exposes methods for executing commands based on the type of command and desired return value, as described in the following table.

Command	Return Value
ExecuteReader	Returns a DataReader object.
ExecuteScalar	Returns a single scalar value.
ExecuteNonQuery	Executes a command that does not return any rows.
ExecuteXMLReader	Returns an XmlReader. Available for a SqlCommand object only.

Each strongly typed command object also supports a CommandType enumeration that specifies how a command string is interpreted, as described in the following table.

CommandType	Description
Text	An SQL command defining the statements to be executed at the data source.
StoredProcedure	The name of the stored procedure. You can use the Parameters property of a command to access input and output parameters and return values, regardless of whichExecute method is called. When using ExecuteReader, return values and output parameters will not be accessible until the DataReader is closed.
TableDirect	The name of a table.

The following code example demonstrates how to create a SqlCommand object to execute a stored procedure by setting its properties. A SqlParameter object is used to specify the input parameter to the stored procedure. The command is executed using the ExecuteReader method, and the output from the SqlDataReader is displayed in the console window.

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub

Working with Command Parameters


Command objects use parameters to pass values to SQL statements or stored procedures, providing type checking and validation. Unlike command text, parameter input is treated as a literal value, not as executable code. This helps guard against "SQL injection" attacks, in which an attacker inserts a command into an SQL statement that compromises security on the server. In addition to the security benefits, parameterized commands provide a convenient method for organizing values passed to a data source.


A DbParameter object can be created using its constructor, or by adding it to the DbParameterCollection by calling the Add method of the DbParameterCollection collection. The Add method will take as input either constructor arguments or an existing parameter object, depending on the data provider.


Supplying the ParameterDirection Property


When adding parameters, you need to supply a ParameterDirection property for parameters other than input parameters. The following table shows the ParameterDirection values you can use with the


ParameterDirection enumeration.

Member name	Description
Input	The parameter is an input parameter. This is the default.
InputOutput	The parameter is capable of both input and output.
Output	The parameter is an output parameter.
ReturnValue	The parameter represents a return value from an operation such as a stored procedure, built-in function, or user-defined function.

Working with Parameter Placeholders
The syntax for parameter placeholders depends on the data source. The .NET Framework data providers handle naming and specifying parameters and parameter placeholders differently. This syntax is tailored to a specific data source, as described in the following table.

Data provider	Parameter naming syntax
SqlClient	Uses named parameters in the format @parametername.
OleDb	Uses positional parameter markers indicated by a question mark (?).
Odbc	Uses positional parameter markers indicated by a question mark (?).
OracleClient	Uses named parameters in the format :parmname (or parmname).

Specifying Parameter Data Types


The data type of a parameter is specific to the .NET Framework data provider. Specifying the type converts the value of the Parameter to the .NET Framework data provider type before passing the value to the data source. You may also specify the type of a Parameter in a generic fashion by setting the DbType property of the Parameter object to a particular DbType.


The .NET Framework data provider type of a Parameter object is inferred from the .NET Framework type of the Value of the Parameter object, or from the DbType of the Parameter object. The following table shows the inferred Parameter type based on the object passed as the Parameter value or the specified DbType.

.NET Framework type	System.Data.DbType	SqlDbType	OleDbType	OdbcType	OracleType
bool	Boolean	Bit	Boolean	Bit	Byte
byte	Byte	TinyInt	UnsignedTinyInt	TinyInt	Byte
byte[]	Binary	VarBinary. This implicit conversion will fail if the byte array is greater than the maximum size of a VarBinary, which is 8000 bytes. For byte arrays larger than 8000 bytes, explicitly set theSqlDbType.	VarBinary	Binary	Raw
char		Inferring a SqlDbType from char is not supported.	Char	Char	Byte
DateTime	DateTime	DateTime	DBTimeStamp	DateTime	DateTime
Decimal	Decimal	Decimal	Decimal	Numeric	Number
double	Double	Float	Double	Double	Double
float	Single	Real	Single	Real	Float
Guid	Guid	UniqueIdentifier	Guid	UniqueIdentifier	Raw
Int16	Int16	SmallInt	SmallInt	SmallInt	Int16
Int32	Int32	Int	Int	Int	Int32
Int64	Int64	BigInt	BigInt	BigInt	Number
object	Object	Variant	Variant	Inferring anOdbcType fromObject is not supported.	Blob
string	String	NVarChar. This implicit conversion will fail if the string is greater than the maximum size of anNVarChar, which is 4000 characters. For strings greater than 4000 characters, explicitly set theSqlDbType.	VarWChar	NVarChar	NVarChar
TimeSpan	Time	Inferring a SqlDbType from TimeSpan is not supported.	DBTime	Time	DateTime
UInt16	UInt16	Inferring a SqlDbType from UInt16 is not supported.	UnsignedSmallInt	Int	UInt16
UInt32	UInt32	Inferring a SqlDbType from UInt32 is not supported.	UnsignedInt	BigInt	UInt32
UInt64	UInt64	Inferring a SqlDbType from UInt64 is not supported.	UnsignedBigInt	Numeric	Number
	AnsiString	VarChar	VarChar	VarChar	VarChar
	AnsiStringFixedLength	Char	Char	Char	Char
	Currency	Money	Currency	Inferring anOdbcType fromCurrency is not supported.	Number
	Date	Inferring a SqlType from Date is not supported.	DBDate	Date	DateTime
	SByte	Inferring a SqlType from SByte is not supported.	TinyInt	Inferring anOdbcType fromSByte is not supported.	SByte
	StringFixedLength	NChar	WChar	NChar	NChar
	Time	Inferring a SqlType from Time is not supported.	DBTime	Time	DateTime
	VarNumeric	Inferring a SqlDbType from VarNumeric is not supported.	VarNumeric	Inferring anOdbcType fromVarNumeric is not supported.	Number

Example


This example demonstrates how to call a SQL Server stored procedure in the Northwind sample database. The name of the stored procedure is dbo.SalesByCategory and it has an input parameter named @CategoryName with a data type of nvarchar(15). The code creates a new SqlConnection inside of a using block so that the connection is disposed when the procedure ends. The SqlCommand and SqlParameter objects are created, and their properties set. A SqlDataReader executes the SqlCommand and returns the result set from the stored procedure, displaying the output in the console window.

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine("{0}: {1:C}", _
                  reader(0), reader(1))
            Loop
        Else
            Console.WriteLine("No rows returned.")
        End If
    End Using
End Sub

Using Parameters with an OleDbCommand or OdbcCommand


OleDb Example

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

Odbc Example

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OdbcParameter = command.Parameters.Add("RETURN_VALUE", OdbcType.Int)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

Working with DataAdapters


A DataAdapter is used to retrieve data from a data source and populate tables within a DataSet. The DataAdapter also resolves changes made to the DataSet back to the data source. The DataAdapter uses the Connection object of the .NET Framework data provider to connect to a data source, and it uses Command objects to retrieve data from and resolve changes to the data source.


Each .NET Framework data provider included with the .NET Framework has a DataAdapter object: the .NET Framework Data Provider for OLE DB includes an OleDbDataAdapter object, the .NET Framework Data Provider for SQL Server includes a SqlDataAdapter object, the .NET Framework Data Provider for ODBC includes an OdbcDataAdapter object, and the .NET Framework Data Provider for Oracle includes an OracleDataAdapter object.




Populating a DataSet from a DataAdapter


The ADO.NET DataSet is a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data including tables, constraints, and relationships among the tables. Because the DataSet is independent of the data source, a DataSet can include data local to the application, as well as data from multiple data sources. Interaction with existing data sources is controlled through the DataAdapter.


The SelectCommand property of the DataAdapter is a Command object that retrieves data from the data source. The InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter are Command objects that manage updates to the data in the data source according to modifications made to the data in the DataSet. These properties are covered in more detail in Updating Data Sources with DataAdapters.


The Fill method of the DataAdapter is used to populate a DataSet with the results of the SelectCommand of the DataAdapter. Fill takes as its arguments a DataSet to be populated, and a DataTable object, or the name of the DataTable to be filled with the rows returned from the SelectCommand.


Using the DataAdapter to fetch an entire table takes time, especially if there are many rows in the table. This is because accessing the database, locating and processing the data, and then transferring the data over the wire to your client is time consuming. Pulling the entire table to the client also locks all of the rows on the server. To improve performance, you can use the WHERE clause can to greatly reduce the number of rows returned to the client. You can also reduce the amount of data returned to the client by only explicitly listing required columns in the SELECT statement. Another good workaround is to retrieve the rows in batches—such as a few hundred rows at a time—and only retrieve the next batch when the client is done with the current batch.


The Fill method uses the DataReader object implicitly to return the column names and types used to create the tables in the DataSet, as well as the data to populate the rows of the tables in the DataSet. Tables and columns are only created if they do not already exist; otherwise Fill uses the existing DataSet schema. Column types are created as .NET Framework types according to the tables in Mapping .NET Data Provider Data Types to .NET Framework Data Types. Primary keys are not created unless they exist in the data source and DataAdapter.MissingSchemaAction is set to MissingSchemaAction.AddWithKey. If Fill finds that a primary key exists for a table, it will overwrite data in the DataSet with data from the data source for rows where the primary key column values match those of the row returned from the data source. If no primary key is found, the data is appended to the tables in the DataSet. Fill uses any mappings that may exist when populating the DataSet.


The following code example creates an instance of a SqlDataAdapter that uses a SqlConnection to the Microsoft SQL Server Northwind database and populates a DataTable in a DataSet with the list of customers. The SQL statement and SqlConnection arguments passed to the SqlDataAdapter constructor are used to create the SelectCommand property of the SqlDataAdapter.

' Assumes that connection is a valid SqlConnection object.
Dim queryString As String = _
  "SELECT CustomerID, CompanyName FROM dbo.Customers"
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
  queryString, connection)

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")

Populating a DataSet from Multiple DataAdapters


Any number of DataAdapter objects can be used with a DataSet. Each DataAdapter can be used to fill one or more DataTable objects and resolve updates back to the relevant data source. DataRelation and Constraint objects can be added to the DataSet locally, enabling you to relate data from dissimilar data sources. For example, a DataSet can contain data from a Microsoft SQL Server database, an IBM DB2 database exposed via OLE DB, and a data source that streams XML. One or more DataAdapter objects can handle communication to each data source.


Example


The following code example populates a list of customers from the Northwind database on Microsoft SQL Server 2000, and a list of orders from the Northwind database stored in Microsoft Access 2000. The filled tables are related with a DataRelation, and the list of customers is then displayed with the orders for that customer.

' Assumes that customerConnection is a valid SqlConnection object.
' Assumes that orderConnection is a valid OleDbConnection object.
Dim custAdapter As SqlDataAdapter = New SqlDataAdapter( _
  "SELECT * FROM dbo.Customers", customerConnection)

Dim ordAdapter As OleDbDataAdapter = New OleDbDataAdapter( _
  "SELECT * FROM Orders", orderConnection)

Dim customerOrders As DataSet = New DataSet()
custAdapter.Fill(customerOrders, "Customers")
ordAdapter.Fill(customerOrders, "Orders")

Dim relation As DataRelation = _
  customerOrders.Relations.Add("CustOrders", _
  customerOrders.Tables("Customers").Columns("CustomerID"), _ 
  customerOrders.Tables("Orders").Columns("CustomerID"))

Dim pRow, cRow As DataRow
For Each pRow In customerOrders.Tables("Customers").Rows
  Console.WriteLine(pRow("CustomerID").ToString())

  For Each cRow In pRow.GetChildRows(relation)
    Console.WriteLine(vbTab & cRow("OrderID").ToString())
  Next
Next 

Using Parameters with a DataAdapter


The DbDataAdapter has four properties that are used to retrieve data from and update data to the data source: the SelectCommand property returns data from the data source; and the InsertCommand , UpdateCommand, and DeleteCommand properties are used to manage changes at the data source. The SelectCommand property must be set before calling the Fill method of the DataAdapter. The InsertCommand, UpdateCommand, or DeleteCommand properties must be set before the Update method of the DataAdapter is called, depending on what changes were made to the data in the DataTable. For example, if rows have been added, the InsertCommand must be set before calling Update. When Update is processing an inserted, updated, or deleted row, the DataAdapter uses the respective Command property to process the action. Current information about the modified row is passed to the Command object through the Parameters collection.


When updating a row at the data source, you call the UPDATE statement, which uses a unique identifier to identify the row in the table be updated. The unique identifier is commonly the value of a primary key field. The UPDATE statement uses parameters that contain both the unique identifier and the columns and values to be updated, as shown in the following Transact-SQL statement.


UPDATE Customers SET CompanyName = @CompanyName
WHERE CustomerID = @CustomerID


The syntax for parameter placeholders depends on the data source. This example shows placeholders for a SQL Server data source. Use question mark (?) placeholders for System.Data.OleDb and System.Data.Odbc parameters.


In this Visual Basic example, the CompanyName field is updated with the value of the @CompanyName parameter for the row where CustomerID equals the value of the @CustomerID
parameter. The parameters retrieve information from the modified row using the SourceColumn property of the SqlParameter object. Following are the parameters for the preceding sample UPDATE statement. The code assumes that the variable adapter represents a valid SqlDataAdapter object.


adapter.Parameters.Add( _
"@CompanyName", SqlDbType.NChar, 15, "CompanyName")
Dim parameter As SqlParameter = _
adapter.UpdateCommand.Parameters.Add("@CustomerID", _
SqlDbType.NChar, 5, "CustomerID")
parameter.SourceVersion = DataRowVersion.Original


The Add method of the Parameters collection takes the name of the parameter, the DataAdapter specific type, the size (if applicable to the type), and the name of the SourceColumn from the DataTable. Notice that the SourceVersion of the @CustomerID parameter is set to Original. This ensures that the existing row in the data source is updated if the value of the identifying column or columns has been changed in the modified DataRow. In that case, the Original row value would match the current value at the data source, and the Current row value would contain the updated value. The SourceVersion for the @CompanyName parameter is not set and will use the default, Current row value.

Parameter.SourceColumn, Parameter.SourceVersion

The SourceColumn and SourceVersion may be passed as arguments to the Parameter constructor, or set as properties of an existing Parameter. The SourceColumn is the name of theDataColumn from the DataRow where the value of the Parameter will be retrieved. The SourceVersion specifies the DataRow version that the DataAdapter uses to retrieve the value.
The following table shows the DataRowVersion enumeration values available for use with SourceVersion.

DataRowVersion Enumeration	Description
Current	The parameter uses the current value of the column. This is the default.
Default	The parameter uses the DefaultValue of the column.
Original	The parameter uses the original value of the column.
Proposed	The parameter uses a proposed value.

The SqlClient code example in the next section defines a parameter for an UpdateCommand in which the CustomerID column is used as a SourceColumn for two parameters: @CustomerID(SET CustomerID = @CustomerID), and @OldCustomerID (WHERE CustomerID = @OldCustomerID). The @CustomerID parameter is used to update the CustomerID column to the current value in the DataRow. As a result, the CustomerID SourceColumn with a SourceVersion of Current is used. The @OldCustomerID parameter is used to identify the current row in the data source. Because the matching column value is found in the Original version of the row, the same SourceColumn (CustomerID) with a SourceVersion of Original is used.

Working with SqlClient Parameters

The following example demonstrates how to create a SqlDataAdapter and set the MissingSchemaAction to AddWithKey in order to retrieve additional schema information from the database. The SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties set and their corresponding SqlParameter objects added to the Parameters collection. The method returns a SqlDataAdapter object.

Public Function CreateSqlDataAdapter( _
    ByVal connection As SqlConnection) As SqlDataAdapter

    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

    ' Create the commands.
    adapter.SelectCommand = New SqlCommand( _
        "SELECT CustomerID, CompanyName FROM CUSTOMERS", connection)
    adapter.InsertCommand = New SqlCommand( _
        "INSERT INTO Customers (CustomerID, CompanyName) " & _
         "VALUES (@CustomerID, @CompanyName)", connection)
    adapter.UpdateCommand = New SqlCommand( _
        "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = " & _
        "@CompanyName WHERE CustomerID = @oldCustomerID", connection)
    adapter.DeleteCommand = New SqlCommand( _
        "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection)

    ' Create the parameters.
    adapter.InsertCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.InsertCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")

    adapter.UpdateCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID")
    adapter.UpdateCommand.Parameters.Add("@CompanyName", _
        SqlDbType.VarChar, 40, "CompanyName")
    adapter.UpdateCommand.Parameters.Add("@oldCustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    adapter.DeleteCommand.Parameters.Add("@CustomerID", _
        SqlDbType.Char, 5, "CustomerID").SourceVersion = _
        DataRowVersion.Original

    Return adapter
End Function

OleDb Parameter Placeholders


For the OleDbDataAdapter and OdbcDataAdapter objects, you must use question mark (?) placeholders to identify the parameters.

Dim selectSQL As String = _
  "SELECT CustomerID, CompanyName FROM Customers " & _
  "WHERE CountryRegion = ? AND City = ?"
Dim insertSQL AS String = _
  "INSERT INTO Customers (CustomerID, CompanyName) VALUES (?, ?)"
Dim updateSQL AS String = _
  "UPDATE Customers SET CustomerID = ?, CompanyName = ? " & _
  WHERE CustomerID = ?"

Dim deleteSQL As String = "DELETE FROM Customers WHERE CustomerID = ?"
The parameterized query statements define which input and output parameters must be created. To create a parameter, use the Parameters.Add method or the Parameter constructor to specify the column name, data type, and size. For intrinsic data types, such as Integer, you do not need to include the size, or you can specify the default size.


The following code example creates the parameters for the SQL statement from the preceding example, and then fills a DataSet.


OleDb Example

' Assumes that connection is a valid OleDbConnection object.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter 

Dim selectCMD AS OleDbCommand = New OleDbCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD

' Add parameters and set values.
selectCMD.Parameters.Add( _
  "@CountryRegion", OleDbType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add( _
  "@City", OleDbType.VarChar, 15).Value = "London"

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")

Odbc Parameters

' Assumes that connection is a valid OdbcConnection object.
Dim adapter As OdbcDataAdapter = New OdbcDataAdapter

Dim selectCMD AS OdbcCommand = New OdbcCommand(selectSQL, connection)
adapter.SelectCommand = selectCMD

' Add Parameters and set values.
selectCMD.Parameters.Add("@CountryRegion", OdbcType.VarChar, 15).Value = "UK"
selectCMD.Parameters.Add("@City", OdbcType.VarChar, 15).Value = "London"

Dim customers As DataSet = New DataSet
adapter.Fill(customers, "Customers")

Retrieving Data Using a DataReader


Retrieving data using a DataReader involves creating an instance of the Command object and then creating a DataReader by calling Command.ExecuteReader to retrieve rows from a data source. The following example illustrates using a DataReader where reader represents a valid DataReader and command represents a valid Command object.


reader = command.ExecuteReader();


You use the Read method of the DataReader object to obtain a row from the results of the query. You can access each column of the returned row by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). For a list of typed accessor methods for data provider-specific DataReaders, see OleDbDataReader and SqlDataReader. Using the typed accessor methods, assuming the underlying data type is known, reduces the amount of type conversion required when retrieving the column value.


The following code example iterates through a DataReader object, and returns two columns from each row.

Private Sub HasRows(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;", _
          connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        If reader.HasRows Then
            Do While reader.Read()
                Console.WriteLine(reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If

        reader.Close()
    End Using
End Sub

The DataReader provides an unbuffered stream of data that allows procedural logic to efficiently process results from a data source sequentially. The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory.


Closing the DataReader


You should always call the Close method when you have finished using the DataReader object.


If your Command contains output parameters or return values, they will not be available until the DataReader is closed.


While a DataReader is open, the Connection is in use exclusively by that DataReader. You cannot execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.


Retrieving Multiple Result Sets using NextResult


If multiple result sets are returned, the DataReader provides the NextResult method to iterate through the result sets in order. The following example shows the SqlDataReader processing the results of two SELECT statements using the ExecuteReader method.

Private Sub RetrieveMultipleResults(ByVal connection As SqlConnection)
    Using connection
        Dim command As SqlCommand = New SqlCommand( _
          "SELECT CategoryID, CategoryName FROM Categories;" & _
          "SELECT EmployeeID, LastName FROM Employees", connection)
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        Do While reader.HasRows
            Console.WriteLine(vbTab & reader.GetName(0) _
              & vbTab & reader.GetName(1))

            Do While reader.Read()
                Console.WriteLine(vbTab & reader.GetInt32(0) _
                  & vbTab & reader.GetString(1))
            Loop

            reader.NextResult()
        Loop
    End Using
End Sub

Download Sample App from here