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.
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.
|Ms-Access 2003 to 2007||microsoft.jet.oledb.4.0|
|Ms-Access 2007 and above||microsoft.ace.oledb.12.0|
|Sybase||sybase ase oledb provider|
OleDbCommand class : This will represent sql statement and it will send sqlstatement to database and return the result.
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 class : DataReader is read-only and forward-only implementation.
- Readonly - DataReader doesn't support manipulations.
- Forwardonly - DataReader supports reading data single time only.
If the requirement is reading data only once without manipulations , then DataReader is recommended.
Methods : Methods used with OleDbDataReader class are as follows
Read() : It will fetch a record into application process from memory managed by database.
OleDbDataReader dr; dr = cmd.ExecuteReader(); dr.Read();
GetByte() : It will fetch the value of specfed column as a Byte.
GetString() : It will fetch the value of specfed column as a String.
Close() : It will close the datareader object
Dispose() :This will releases all resources used by current instance of the datareader class
Properties : Properties used with OleDbDataReader object are as follows:
FieldCount : Get the number of columns in the current row
HasRows : Gets the value that indicates whether the DataReader contains one or more rows
IsClosed : Gets the value that indicates whether the DataReader is closed or not.
Note : Datareader is strongly recommended when the application requirement is reading of large amount of data without any manipulations. This will fetch record by record into application process which consumes less amount of memory within application process. This will reduce burden on process and provide better performance. DataReader is connection oriented and it will make use connection exclusively.
Database will support only one memory process towards a conncetion. This makes datareader using connection exclusively.
Sqlserver 2005 supports morethan one memory process for a single connection. This feature is called MARS(Multiple Active Result Sets).
Comments/Suggestions are invited. Happy coding......!