Data Providers for ADO.NET - IBM DB2

In ADO.NET architecture, applications (also called Data Consumers) connect to database (also referred to as Resource) using data provider. The data provider encapsulates data and provides a means to interact with the database including connection, execution of SQL command, and retrieval of results. As mentioned in Chapter, “DB2 application development overview”, IBM DB2 provides three data providers for ADO.NET applications. These are:

  • DB2 .NET Data Provider
  • OLE DB .NET Data Provider
  • ODBC .NET Data Provider

Managed provider and unmanaged provider
In ADO.NET, Data Provider can be separated into two categories according to how it is implemented:

  • Managed Provider: This executes within the ADO.NET environment, which controls all aspects of application execution, including memory allocation, memory deallocation, application domains, and so on.
  • Unmanaged Provider: This is pre-ADO.NET Windows 32-bit operating system-based drivers.

DB2 .NET Data Provider is managed type. DB2 .NET Data provider is ADO.NET data provider, which is recommended for use with DB2 family databases. The following namespaces are required for DB2 .NET Data Provider:

OLE DB .NET Data Provider is a bridge provider that passes the ADO.NET request to native IBM OLE DB provider (IBMDADB2). The following namespaces are required for OLE DB .NET Data Provider:

ODBC .NET Data Provider is a bridge provider that passes ADO.NET requests to the IBM ODBC Driver. The following namespaces are required for ODBC .NET Data Provider:

Use of OLE DB .NET Data Provider or ODBC .NET Data Provider is recommended if the application is connecting to multiple vendor databases and you do not wish to change any code within the application.

DB2 .NET Data Provider is recommended for any new ADO.NET application development. It will yield the best performance due to the elimination of the extra unmanaged layer as shown below

DB2 Data Provider

DB2 Data Provider

Five key managed provider components in ADO.NET
There are five key managed provider components in ADO.NET that are common to all IBM DB2 data providers. These include:

  • Connection
  • Command
  • DataReader
  • DataAdapter
  • DataSet

Connection
The Connection object is used to connect to a database and control the transactions in ADO. NET. Each data provider has different Connection Objects. Each of the three data providers IBM DB2 incorporates has its own Connection Objects (DB2Connection, Ole Db Connection, and OdbcConnection).

The data connection class enables you to specify the connection string used to connect to the target database server:

  • This is implemented as DB2Connection object in DB2 .NET Data Provider, forexample:
  • This is implemented as OleDbConnection object in DB2 OLE DB Data Provider, for example:
  • This is implemented as OdbcConnection object in DB2 ODBC Data Provider, for example:

The connection object has public property ConnectionString, which is required for establ- ishing connection with a database. It requires database name and other parameters such as user ID and password, for example:

However, ConnectionString property can be set through passing the string to the Connection Object Constructor as shown in following example:

DB2Connection connection = new DB2Connection(“Database=SAMPLE”);

Connection objects have the following public methods:

  • Open: This opens a database connection as specified in a ConnectionString, for example:
  • Connections can be opened by explicitly calling the Open method on the connection as shown above or by implicitly using a DataAdapter.
  • Close: This closes the database connection, for example:
  • CreateCommand: This returns a command object associated with the connection, for example:
  • BeginTransaction: This begins the database transaction, for example: connection. Begin Transaction():

Command
The Command object allows for execution of any supported SQL statement or stored procedure using a data connection object. Connection object should be created but do not needed to be opened prior to creating SQL commands:

  • This is implemented as DB2Command in DB2 .NET Data Provider, for example:
  • This is implemented as OleDbCommand in DB2 OLE DB Data Provider, for example:
  • This is implemented as OdbcCommand in DB2 ODBC Data Provider, for example:

The Command object has public properties CommandType and CommandText. The Command Type describes whether an SQL statement or a stored procedure will be executed. The CommandText is used to set or get an SQL statement or a stored procedure that is to be executed, for example:

Command object has the following public methods:

  • CreateParameter: This is used for parameter handling, for example:
  • ExecuteNonQuery: Use this to execute a SQL command that does not return any data, such as UPDATE, INSERT, or DELETE SQL operations. Method returns the number of rows affected for given execution, as shown:
  • ExecuteReader: Use this to execute a SQL query that returns a Data Reader. Data Reader is fast forward only stream of data, for example:
  • ExecuteScalar: Use this to execute a SQL command that retrieve a single value from a database, for example:

DataAdapter
The data adapter object populates a disconnected DataSet with data and performs update. It contains the four optional commands for the select, insert, update, and delete. Use it between DataSet and database for loading and unloading data.

  • Implement this as DB2DataAdapter in DB2 .NET Data Provider, for example:
  • Implement this as OleDbDataAdapter in DB2 OLE DB Data Provider, for example:
  • Implement this as OdbcDataAdapter in DB2 ODBC Data Provider, for example:

Data adapter object has these public properties:

  • DeleteCommand
  • InsertCommand
  • SelectCommand
  • UpdateCommand

The DeleteCommand deletes records using SQL statements or stored procedures from the data set, for example:

The InsertCommand inserts new records into a database using SQL or stored procedures, for example:

The SelectCommand selects records in a database using SQL or Stored Procedures, for example:

The UpdateCommand update records in a database using SQL or Stored Procedures, for example:

Data Adapter has the following public methods:

  • Fill: This fills records in DataSet, as shown below:
  • Update: This updates Records in DataSet and a database through INSERT, UPDATE, and DELETE operations, for example:

DataReader
Utilized for fast forward-only, read-only access to connected record sets that are returned from executing SQL statements or stored procedure calls. The DataReader object cannot be directly instantiated and needs to be returned as the result of the Command Object’s ExecuteReader method.

  • Implement this as DB2DataReader in DB2 .NET Data Provider, for example:
  • Implement this as OleDbDataReader in DB2 OLE DB Data Provider, for example:
  • Implement this as OdbcDataReader in DB2 ODBC Data Provider, for example:

The DataReader object has FieldCount and HasRows public properties. The FieldCount property returns the total number of columns in the current row while HasRows property indicates whether DataReader has one or more rows by returning true or false, for example:

The DataReader object has the following public methods:

  • Read: Reads in records one row at a time and advances the cursor to the next row. It returns true or false to indicate whether there are any rows to read, for example:
  • Close: This closes the DataReader, for example:
  • Getxxxx: This is used to get data of type xxxx, for example:

DataSet
The DataSet object represents an “In-memory cache of data”, which was retrieved from the database. The DataSet object is a disconnected dataset, which provides a consistent relational programming model independent of the data source. Since it is disconnected from the database, it reduces the communication overhead to the database server.

The DataSet object has the public property DataSetName, which gets or sets DataSet name, for example:

The DataSet object has the following public methods:

  • AcceptChanges: This commits changes to the DataSet, for example:
  • Clear: This clears the DataSet contents, for example:
  • GetXML: This gets XML representation of data in the DataSet, for example:
  • ReadXML: This reads XML schema and XML into DataSet, for example:
  • WriteXML: This writes XML schema and XML into DataSet, for example:

The short ADO.NET sample C# codes shown in Example below demonstrate the use of various DB2 Data Providers. They perform the same functionality where “selects * from staff” query is issued and displays the name of the staff (second column in staff table) to the screen. These sample codes require DB2 Sample database. Key differences between the codes are highlighted in bold. DB2 .NET Data Provider C# sample is shown in Example below

Short C# sample code using DB2 .NET Data Provider

DB2 OLE DB Data Provider C# sample is shown below

Short C# sample code using DB2 OLE DB Data Provider

DB2 ODBC Data Provider C# sample is shown below

Short C# sample code using DB2 ODBC Data Provider

The above samples can be compiled using the following command:

csc NETSamp.cs /r:<DB2 Install Path>in etf20IBM.Data.DB2.dll

The <DB2 Install Path> is the path where db2 is installed. Below example shows sample application output.

Sample application output

Connecting to the database. Execute: SELECT * FROM staff FieldCount: 7 HasRows?: True Sanders Pernal Marenghi O'Brien Hanes ... Closing reader and disconnecting from the database.

In Microsoft .NET Framework, classes are organized into a hierarchical structure of related groups called namespaces. System.Data namespace contains classes associated with the use of ADO.NET. In C#, all errors are treated as instances of an exception. Error handling in ADO.NET is done in the form of the try/catch/finally or the On Error construct.

Summary
shows the summary various DB2 data providers comparison.

DB2 Data Provider comparison

DB2 Data Provider comparisonDB2 Data Provider comparison

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM DB2 Topics