Introduction

In the previous article ADO.NET in C#:Part-II , we have seen about various classes in OLEDB.NET. Now the next class in this namespace is OleDbDataAdapter Class.

Before that, we should know about what are the drawbacks in previously used technology. In traditional Visual Basic Programming ADO is used to connect to the databases and used to do manipulations on a database. In ADO, it is provided with a memory component for data storage called 'Recordset'.

Limitations of Recordset are as follows:

1. A recordset can maintain data in the form of single table format. Two tables information can be joined in the form of one table format, this leads to data redundency.
2. It consumes more memory and manipulations are also not supported
3. Recordset can't maintain data from multiple databases.
4. Recordset does not support constraints.
5. Recordset native storage (internal storage) is ADTG(Active Data Table Gram) format. This is binary standard towards windows O.S. It does not support cross platform transmission.

The problems of recordset are identified towards distributed technology. The solution is memory component with purely disconnected implementation is required. It provides flexibility for data storage.

ADO.Net is providing purely disconnected memory component called DataSet. This is integrated with XML representation for cross platform transmission.

DataSet: Dataset is a in-memory database for the client application process. Dataset will maintain collection of tables called DataTables. Dataset is purely disconnected with xml integration.

DataAdapter: DataAdapter is a collection of command objects. It is mediator between Dataset and Database server. This makes dataset purely disconnected by performing two operations with database.

1. Retrieving data from database.
2. Placing this data into dataset.
3. Updating dataset manipulations to database.

Note: DataAdapter is required only when dataset is used. Without dataadapter dataset can read information from XML storage (xml file).
Dataset is independent component in ADO.Net without having relation with .Net data provider. Dataset class can be find in System.Data namaespace.

OleDbConnection con = new OleDbConnection("Provider=<providername>;User Id=<username>;Password=<pwd>;Data Source=<servername>");
           
OleDbDataAdapter da = new OleDbDataAdapter("Select * from emp", con);
DataSet ds = new DataSet();
da.Fill(ds, "Emp");

The Fill method will perform three tasks:

1. It will establish conncection with database.
2. It will send select statement to database using dataadapter. It will read records into dataset.
3. It will close connection.

This is about OLEDB.NET namespace. And the next namespace is Oracle.Net.

Oracle.Net Data Provider: It is provided in the form of System.Data.OracleClient.dll. The classes in this namespace are:

1. OracleConnection
2. OracleCommand
3. OracleDataReader
4. OracleDataAdapter

Oracle.Net is implementing native protocol programming of Oracle database. TNS(Tabular Network Substrate) is native protocol of Oracle. This makes communication faster.

According to testings it provides 60% - 70% performance gain over OLEDB.NET

It can be used for Oracle 8.0 and later versions.

The first three Oracle.Net classes are used as shown in the following example. For this add reference to System.Data.OracleClent.

OracleConnection con = new OracleConnection("User Id=scott; Password=tiger; data source=databasename");
OracleCommand cmd = new OracleCommand();
cmd.CommandText = "Select * from emp";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
con.Open();
OracleDataReader dr;
dr = cmd.ExecuteReader();
con.Close();

Note: Oracle.Net is not based on OLEDB provider programming. So it does not required provider name.

The OracleDataAdapter can be used in the following way:

OracleConnection con = new OracleConnection("User Id=scott; Password=tiger; data source=databasename");
OracleDataAdapter da = new OracleDataAdapter("select * from emp", con);
DataSet ds = new DataSet();
da.Fill(ds, "Emp");

In the next article we will see about System.Data.SqlClient namespace.