Introduction

OleDb.Net : The namespace for OleDb.Net is System.Data.Oledb namespace. This consists of different types of classes which are used to connect any type of database as shown below.

1. OleDbConnection
2. OleDbCommand
3. OleDbDataReader
4. OleDbDataAdapter

OleDb.Net is .Net based code communicating with OLEDB provider[i.e., COM based code] which makes communication slow. This is required for developer where there is no direct .Net data provider for data store. These providers can be called unmanaged .Net data providers which requires more processing. These providers are used:

• To communicate with Oracle version less than 8i
• To communicate with Sqlserver version less than 7.0
• To communicate with local databases
• To communicate with index server

OleDbConnection class : This will establish a bridge between client application and database. For this the namespace System.Data.OleDb is used.

//Intializing an instance of System.Data.OleDb.OleDbConnection class with the specified string
OleDbConnection con = new OleDbConnection("Provider=<providername>;User Id=<username>;Password=<pwd>;Data Source=<servername>");
//Opens a database connection with the property settings specified by System.Data.OleDb.OleDbConnection.ConnectionString
con.Open();
//This will close a conncetion by releasing network level resources.
con.Close();

The connectivity will be taken based on provider name. These provider names are given by microsoft for different data bases as shown below. Depending on the database, you have to choose the Provider name.

OleDbCommand cmd = new OleDbCommand("SqlStatement", con);

Methods : Methods used with OleDbCommand object are as follows
ExecuteScalar(): This is required when a Select statement returns one value. This is used as shown below.

OleDbCommand cmd = new OleDbCommand("Select max(empno) from emp", con);
cmd.ExecuteScalar();

ExecuteNonQuery():This is required when sql statement is insert or delete or update or a stored sub program.

OleDbCommand cmd = new OleDbCommand("Delete * from emp where deptno=10", con);
cmd.ExecuteNonQuery();

ExecuteReader():This is required when a select statement returns more than one value. This will return a DataReader object which is used to read record by record from memory location.

OleDbCommand cmd = new OleDbCommand("Select * from emp", con);
cmd.ExecuteReader();

Properties : Properties used with OleDbCommand object are as follows:

CommandText: This is used to attach sql statement to command object dynamically.

Connection: This is used to attach connection to command object dynamically.

CommandTimeout: Gets or sets the wait time before terminating an attempt to execute a command

CommandType: Gets or Sets a value that indicates how the System.Data.OleDb.OleDbCommand.CommandText property is interpreted.

• If the command text is a sql statement (select/insert/update/delete) then command type will be CommandType.Text.
• If the command text is a stored procedure,, then command type will be CommandType.StoredProcedure.

cmd.CommandText = "Select * from emp";
cmd.Connection = con;
cmd.CommandTimeout = 600;
cmd.CommandType = CommandType.Text;

OleDbDataReader dr;
dr = cmd.ExecuteReader();
dr.Read();