ODBC, OLE-DB, and OLE-DB.NET Providers

Overview of ADO.NET

ODBC, OLE-DB, and OLE-DB.NET Providers

Communication withdatabases has been typically performed through some proprietary interfaces such as Open Database Connectivity (ODBC) or Object Linking and Embedding Database (OLE-DB). These interfaces translate commands between your application and the database. These software are provided as part of the Microsoft Data Access Components (MDAC) free from Microsoft.

• ODBC drivers are used to provide access to an ODBC compliant database, including both Microsoft products (Access, SQL Server) and non-Microsoft products (Oracle, DB2).

• OLE-DB providers are available for most common data stores, including Access, SQL Server, and Exchange Server.

A Web application interfaces with the database through a set of objects defined in the ActiveX Data Objects (ADO) model. The ADO objects interface with the database via the ODBC and OLE-DB.

The .NET framework changed the ways we used to access and manipulate the database, although the fundamental steps of accessing and manipulating the database are not changed. Thus an ASP.NET application interacts with the ADO.NET objects built into the ADO.NET Model. Then ADO.NET uses the interface via the .NET manage provider to access the database.

Thus, if your application accesses the Microsoft Access data using ADO objects, and you upgrade your data to a SQL Server database, you will only need to have a few changes within your application. The ADO interface to the data will remain the same for most of your application.

Working with the Database

There are few general steps that are required to communicate between a Web page and a database:

1. Build database tables and queries.
2. Create a connection to the database. The connection identifies the location of the database (the data source) and the connection method (an ODBC driver, OLE-DB provider, or an OLE-DB.NET data provider), along with any other settings such as username or password.
3. Create an ASP.NET Web page.
4. Add an ADO.NET connection object that connects to the database, executes commands, and returns data from the database.
5. Create code that will interact with the data, display the data in an ASP.NET control, perform calculations on the data, or upload changes to the database.

ADO Object Model

The Microsoft ActiveX Data Object Model (ADO) has been the preferred method of communication with the database. It provided a set objects that were used to open a database connection, fetch a recordset from the database and perform a set of database operation such as , display, insert, delete, and update database records.

In the ADO model, when a recordset is being opened, it would keep the connection active with the database until looping through the recordset is completed. For a Web connection, this would require a lot of resources for the server when many browsers are communicating with the server at the same time.

ADO.NET Object Model

The Web itself employs a disconnected client-server approach. That is, after a page is received from the server, the connection between the browser and the Web server is broken. The ADO.NET model is built on the same principle. It separates the process of connecting to the data source from the manipulation of the data.

The ADO.NET model consists of several components, the two central components are: the DataSet and the .NET Data Provider. The .NET data provider is a set of components including the Connection, Command, DataReader, and DataAdapter objects.

The ADO.NET DataSet is the core component of the disconnected architecture of ADO.NET. The 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 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. It replaces the RecordSet object of the ADO architecture.

The other core element of the ADO.NET architecture is the .NET data provider, whose components are 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.

Managed Providers (.NET Data provider)

A .NET data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, or placed in an ADO.NET DataSet in order to be exposed to the user in an ad-hoc manner, combined with data from multiple sources, or remoted between tiers.

The following table outlines the four core objects that make up a .NET data provider.

The ADO.NET Objects communicate to the data sources through the managed providers. There are two managed providers in ADO.NET:

• SQL Server.NET data provider is used to connect to a SQL Server database version 7.0 or later. It provides a direct communication with the SQL Server database using a protocol called TDS (Tabular Data System) which provides performance improvement.

• OLE-DB.NET data provider is used to connect to any other data source including SQL Server, Access database, XML file, or any other database. It does not provide specific performance improvement.

ADO.NET Objects by Data Provider

There are basically four objects in the ADO.NET: Connection, Command, DataReader, and DataAdapter. Depending on the data provider, the names of these objects are different. There is also a Dataset object that is part of the ADO.NET object model as shown in the figure. The names of the objects are different depending on the data provider.

• SQL Server .NET data provider
• SqlConnection,
• SqlCommand,
• SqlDataReader, and
• SqlDataAdapter

• OLE-DB data provider (like Access)
• OleDbConnection,
• OleDbCommand,
• OleDbDataReader, and
• OleDbDataAdapter.

• DataSet object - to retrieve data from the database.

The Connection Object

The ADO.Net Connection Object provides the connection to the database. It requires a connection string that can be written through codes or it can be configured through the Data Connections window of Visual Studio.NET environment.

The connection string requires the following fields:

• Provider– The name of the managed provider for the SQL Server or OLE-DB Provider.
• Data Source– The name of the SQL Server computer or full path of the database like Access.
• Initial Catalog – The name of the database in case of database like SQL Server.
• User ID and Password – It identifies the authentication for databases like SQL Server. The default user ID in SQL Server is sa and password is blank.

Sample OLE-DB connection string to a SQL Server or similar database:

Dim CS as String

CS = "Data Source=MyServer; Initial Catalog=MyDatabase;

User ID=sa; PASSWORD=;"

Sample OLE-DB connection string to Access Database:

Dim CS as String

CS = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source=c:\Mypath\Northwind.mdb”

When the full path of the Access database is not known, the path can be obtained by using the mappath method of the server object. For example:

Dim CS as String

CS = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source= “ & server. mappath (“Northwind.mdb”)

The Command Object

The command object is used to execute a command to process an SQL query, a stored procedure, or a table from a database. Following are the methods and properties of the command object.

• Connection property- The Connection used by the Command object
• CommandText property- identifies the command to execute
• CommandType property - indicates the type of command being executed
• Text - by default, indicates that the command is an SQL text string
• TableDirect- specifies the name of a table to return
• StoredProcedure - specifies the name of a stored procedure
• ExecuteReader method - executes the command and passes the results to the DataReader object
• ExecuteNonQuery method - does not return rows because it’s used to insert, modify, and delete data, but returns an integer of the number of rows affected by the command

For example, the command object is constructed by providing a SQL query and a connection object as parameters, and then a method is used to execute the query.

Dim CS As String, Sql As String

CS = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source= “ & server. mappath (“Northwind.mdb”)

Dim myConnection As OleDbConnection

myConnection = New OleDbConnection(CS)

Sql = “Select * from Customer”

Dim myCommand As New OleDbCommand(Sql, myConnection)

myConnection.Open()

myCommand.ExecuteNonquery()

myConnection.Close()

The DataReader Object

The data reader object

• delivers a stream of data from the database.
• provides a high-performance method of accessing read-only data.
• read-only, forward-only stream of data from the database.
• requires continual access to the database, while the DataAdaptor uses a disconnected dataset to access the data.
• It holds one row in memory at a time, as opposed to DataDet which holds a complete table in memory.

The methods and properties of DataReader object are:

• ExecuteReader method - retrieves rows of data as they are located in the database.
• Read - returns a single row and caches each row in memory only once, then moves the current record pointer to the next record or row.
• CommandBehavior property - closes the connection.
• Close method - closes the DataReader object and releases the references to the rowset.

Foe example, the ExecuteReader method is used on command object to return a resultset from the database.

Dim CS As String, Sql As String

CS = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source= “ & server. mappath (“Northwind.mdb”)

Dim myConnection As OleDbConnection

myConnection = New OleDbConnection(CS)

Sql = “Select * from Customer”

Dim myCommand As New OleDbCommand(Sql, myConnection)

myConnection.Open()

Dim dR As OleDbDataReader

dR = myCommand.ExecuteReader()

myConnection.Close()

The DataAdapter Object

The DataAdapter Object accesses a DataSet object. It is the only way that a database data can be manipulated (insert, delete, update). Because the DataSet is disconnected, there must be methods used to maintain the original set of data, and the changes.

The Methods and Properties of the DataAdapter are:

• Fill method acts as the bridge between the datasource and the DataSet. It loads the data stored in the DataAdapter to the DataSet object.
• SelectCommand method is used to retrieve data.

• InsertCommand method is used to add a new record.
• UpdateCommandmethod is used to modify the data within an existing record.
• DeleteCommand method is used to permanently remove a record from the database.

For example,

Dim CS As String

CS = "Provider=Microsoft.Jet.OLEDB.4.0;

Data Source= “ & server. mappath (“Northwind.mdb”)

Dim myConnection As OleDbConnection

myConnection = New OleDbConnection(CS)

Dim Sql As String

Sql = “Select * from Customer”

Dim myAdapter As OleDbAdapter

myAdapter = New OleDbAdapter(Sql, myConnection)

Dim ds As New DataSet

myAdapter.Fill (ds, “Masters”)

Here “Masters” is the table where data is returned.

The DataSet Object

The RecordSet object in ADO has been dropped; instead a new object called DataSet is introduced in ADO.NET.

• The DataSet object is a disconnected collection of one or more tables that are stored in memory.
• The DataSet is effectively a private copy of the database in the memory, complete with tables, columns, relationships, constraints, and data. However, it may not necessarily reflect the current state of the database.
• If you want to see the latest changes made by other users, you can refresh the dataset by calling the appropriate Fill method of the DataAdapter object.
• A DataSet not only can receive data from a database, but also from files such as XML, text, Excel, and so on.

A DataSet contains a DataTable collection, which in turn contains other collections such as DataRow, DataColumn, and DataConstraint.

1