ADO.NET

ADO.NET Connection Pooling
Introduction to ADO.NET
For those of you familiar with ActiveX Data Objects (ADO) in Visual Basic, you will be well aware of the concept of the  Connection, the  Command  and  the  Recordset.  These  are  the  primary  classes  that  constitute  ADO’s  data access technology. In the .NET framework, ADO has been through major reengineering  and  has  been  renamed to ADO.NET to reflect its position as the underlying data framework for .NET. 

The Connection and Command are still there, but the Recordset has been replaced by the  DataSet  and we have a  new  Object  called  a   DataAdapter ,  which  is  a  toolset  that  handles  processing  between  a  Command  and  a Connection . There is 

also a new Object called a DataReader, which is essentially what would have been a Read- Only Forward -Only Cursor Recordset in ADO and is the most efficient way to get data.

You will notice that the DataSet has a DataTable collection, which reveals its ability to hold resultsets from more than  one  table,  a  big  improvement  over  ADO  Recordsets.  Moreover,  the  tables  can  be  related  through Relationship objects, which allow the programmer to perform joins without going back to the database. In addition to  table  relationships,  ADO.NET  is  aware  of  Primary  Key,  Foreign  Key  and  Unique  constraints,  as  well  as Autonumber (IDENTITY/SEQU ENCE) fields.

A very popular way of working with data in VB and ADO was to keep the connection open, while making changes directly to the database. 

ADO.NET encourages greater use of the disconnected model. The programmer should keep a connection to the database open for as little as 

possible. This improves scalability and makes more sense

in a web-enabled system. The process of retrieving and updating data should be as follows:

1.    Open Connection

2.    Get DataSet

3.    Close Connection

4.    Perform Operations on the DataSet

When you are ready to commit the changes back to the database:

5.    Open Connection

6.    Submit Changes from DataSet

7.    Close Connection

And so on.

In  the  diagram  above,  Connected  Objects  are  the  ones  that  are  normally  used  while  the  Connection  to  the database is open and Disconnected Objects are the ones that can be used once the connection is closed.

Note for ASP.NET

ASP.NET  no  longer  relies  on  VBScript,  but  is  instead  powered  by  the  .NET  framework,  which  means  that  it  can

be built using VB.NET or C# for example. The source code in this guide will therefore work equally well, whether you are making a Windows Application, or a Web Application in ASP.NET

ADO.NET

After our brief overview with ADO.NET let’s have a look at how our source code would look. I have provided examples  in  both  VB.NET  and  C#.  I  will  be  assuming  Access  as  the  back -end  and  will  be  going  through OLE  DB.  ADO.NET  has  a  specialized  set  of  classes  that  are  optimised  for  SQL  Server  (such  as SqlConnection), but for Access we need to use the regular OL E DB classes.

private OleDbConnection ConnObj=null;

ConnObj = OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath)

ConnObj.Open();

Now  the  Connection  is  open,  we  have  several  options,  each  of  which  I  will  demonstrate.  Most  of  these objecti ves  can  work  using  a DataSet,  but  the  new  methods  have  been  provided  to  make  specialised operations more efficient. For example, the system shouldn’t be made to go through the trouble of building a complete DataSet in memory, if all we need is a single value.

1. Get a DataSet that can be used offline.                   

2. Get a DataReader (can only be used online).

3. Perform a non-DataSet operation on the Database,                                                            

4. such as UPDATE, INSERT, or DELE TE

5. Get a Single value from a query, such as “SELECT COUNT(*)”.

And Here for..

6. Use a DataAdapter and call Fill.

7. Use a Command and call ExecuteReader. 

8. Use a Command and call, ExecuteNonQuery.  

9. Use a Command and call ExecuteScalar.        

DataSet dsObj;

OleDbDataAdapter daObj = new OleDbDataAdapter(sqltext,m_Conn);

daObj .Fill( dsObj );

ConnObj.Close();

The  Connection  is  closed  and  the  DataSet  is  now  filled  with  data  and  ready  for  processing.  To  better understand  the  code  below,  keep  in  mind  the  object  hierarchy.  A  DataSet  contains  many  Tables,  which contain many Rows, which contain Fields.

DataSet

    L  Tables as DataTable

    L  Rows as DataRow

ADO.NET Connection Pooling

For More Details:

http://msdn.microsoft.com/en-us/library/h43ks021(v=vs.71).aspx

http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.80).aspx

http://msdn.microsoft.com/en-us/library/e80y5yhx(v=vs.71).aspx

Thanks,

Anil