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:
Managed provider and unmanaged provider
In ADO.NET, Data Provider can be separated into two categories according to how it is implemented:
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
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:
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:
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:
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:
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:
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.
Data adapter object has these public properties:
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:
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.
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:
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:
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 outputConnecting 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.
shows the summary various DB2 data providers comparison.
DB2 Data Provider comparison
IBM DB2 Related Interview Questions
|IBM Websphere Interview Questions||IBM-REXX Interview Questions|
|IBM Cognos Interview Questions||IBM DB2 Interview Questions|
|J2EE Interview Questions||COBOL Interview Questions|
|IBM-JCL Interview Questions||DB2 Using SQL Interview Questions|
|IBM WAS Administration Interview Questions||IBM WebSphere Administration Interview Questions|
|Database Administration Interview Questions||DB2 SQL Programming Interview Questions|
|Mainframe DB2 Interview Questions|
IBM DB2 Related Practice Tests
|IBM Websphere Practice Tests||IBM Cognos Practice Tests|
|IBM DB2 Practice Tests||J2EE Practice Tests|
|COBOL Practice Tests||IBM-JCL Practice Tests|
|DB2 Using SQL Practice Tests||IBM WAS Administration Practice Tests|
|Database Administration Practice Tests||DB2 SQL Programming Practice Tests|
Ibm Db2 Tutorial
Db2 Application Development Overview
Application Development With Db2 Purexml
Application Development With Php
Application Development With C/c++
Application Development With Java
Application Development With .net
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.