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.
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
Thanks,
Anil
No comments:
Post a Comment