By K. Selvaganapathi
The ADO.NET provides disconnected access to any data source for which a Managed Provider exists. A Managed Provider is nothing but set of objects that talks to a data source directly. Each RDBMS vendors will provide appropriate Managed Providers.
For now, Microsoft has provided two Managed Providers:
SQL Managed Provider - Provides access to SQL Server version 7 or later.
ADO Managed Provider - Provides access to any data source through OLEDB Providers. This is for compatibility with old data source for which no Managed Providers exists.
The architecture of ADO.NET can be shown like this:
------ --> DataReader _______________________
| v
|
Client --> DataSet --> DataSetCommand-->Connection --> Data source
|
| ^
------ --> Command _______________________|
|---------------------------------------------------|
Managed Provider
The above figure shows three of possible ways of accessing data in ADO.NET architecture. The most common way is through DataSet, which is the primary object in ADO.NET. The DataSet object can be regarded as a virtual database, which will reside in the client memory. Unlike ADO RecordSet, DataSet object provides disconnected access to underlying data source, i.e. any modification or insertion of data will not be made to underlying data source until we explicitly call the DataSetCommand object's Update method, thus improving scalability of the data source.
One big advantage of this model is, we as programmers, don't need to bother about data source we are accessing but only DataSet. The DataSet in turn will talk to data source through Managed Providers, here DataSetCommand. Each Managed provider will effectively implement the methods of DataSetCommand, Command, Connection, DataReader etc, appropriate to their data source.
In case of changing the data source, we don't need to alter the program, only thing we will need is changing the appropriate connection string.
The DataSetCommand is a mediator between DataSet and Data Source. This object can be used to fill the DataSet with appropriate tables and other stuffs such as relations, constraints, etc. Once DataSet is filled with tables we are interested in, it can be accessed freely with DataSet object hierarchy.
The following figure shows the hierarchy of the DataSet objects (Lot more there in this hierarchy, showing all is out of scope of this article.)
DataSet
|
|_____ Tables (Collection)
|______ DataTable
|______ Columns (Collection)
| |_____DataColumn
|______Rows (Collection)
|_____DataRow
If you do not need in-memory copy of database, you can use DataReader object. This object provides sequential (one record at time) access to a data source. This object is very similar to our ADO's Read-only Forward-only cursor in nature. But provides less flexibility than DataSet.
Classes & namespaces involved in ADO.NET:
The System.Data namespace defines the following classes to interact with in-memory copy of a database. Again I am showing only few important classes and you should feel free to visit the documentation provided with .NET SDK.
DataSet:In-memory copy of entire database.
DataTable: Represents a single table.
DataColumn:Represents a single column of a table.
DataRow:Represents a single row in a table.
The System.Data.Internal namespace contains the following abstract base classes from which Managed Provider objects are derived.
DataSetCommand:Represents an abstract DataSetCommand, which would talk to the data source.
DBCommand:Represents an abstract Command object.
DBConnection:Represents an abstract Connection object, which maintains the connection between DataSetCommand and data source.
DBDataReader: Represents an abstract DataReader.
The System.Data.SQL namespace contains classes that are derived from the above classes and other internal classes to access SQL Server (7 or later) data sources.
Similarly the System.Data.ADO namespace contains classes to access OLEDB enabled data sources.
I think now you would have got an understanding of what the ADO.NET architecture is. Now its time to blast the same using our great weapon, C#!
In general, to access a data source through DataSet object we will do the following.
1) Create an object of type ADODataSetCommand or SQLDataSetCommand by providing a connection string and a command string.
Ex:
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mydb.mdb; user id=;password=;";
string strCommand = "SELECT * FROM EMPLOYEES";
DataSetCommand DSC = new ADODataSetCommand( strCommad,strConnection);
Note: I have used DataSetCommand object reference to point our new ADODataSetCommand object. You can also use ADODataSetCommand reference instead. But writing like this will improve code reusability. For example, if you need to change from OLEDB data source to SQL data source, you don't need to alter rest of the coding. By only changing connection string and object creation statement, program will work fine forever!
2) Create a DataSet object and use DataSetCommand's FillDataSet method to populate the DataSet object with tables we want.
Ex:
DataSet ds = new DataSet();
DSC.FillDataSet( ds , "EMPLOYEES" );
Note: The FillDataSet method will use the strCommand to fetch the records from data source.
EMPLOYEES represents name the table that will be used by the DataSet object. Similarly we can fetch more than one table and put them in the same DataSet object.
3) Well, now we have a DataSet object filled with tables we are interested in! So we can explore some of usual things we will do with databases such as reading records, modifying contents, etc.
Navigating a particular table
To navigate a particular table, create a DataTable reference and use DataSet's Tables collection property to get that table. Now the table is ready. Similarly to access each row, use DataTable's Rows collection property!
Ex:
DataTable aTable = ds.Tables[0];
Or you can use table name as index,
DataTable aTable = ds.Tables["EMPLOYEES"];
foreach( DataRow aRow in aTable.Rows)
{
Console.WriteLine("Field 1 contains {0}",aRow[0]); // First name
Console.WriteLine("Field 2 contains {0}",aRow[1].Item); // Midle name
Console.WriteLine("Field 3 contains {0}",aRow["LastName"]); // Last name
}
also you can use the following notation to access a particular field in a particular row,
aTable.Rows[0][1] --- represents the value in first row and second column.
Adding a new record
To add a new record simply create a DataRow object using DataTable's NewRow method, fill the fields with appropriate values and add that row to DataTable's Rows collection. Cool.
Ex:
DataRow newrow = aTable.NewRow();
newrow[0] = "Selva";
newrow["MidleName"]="Ganapathi";
newrow[2].Item = "Kasi";
aTable.Tables.Add(newrow);
Deleting a record
To delete a record, use DataRow's Delete method, cool isn't? But you should mention the index of that record that's all.
aTable.Rows[0].Delete(); // will delete the first row.
Even if you use Delete method, the record will not be removed from the data source but only in in-memory copy of the database i.e. what we call DataSet. Got it? Now you might ask how to delete a record in data source? Well, when you have completed all of accessing with a DataSet object you can send entire content of DataSet object back to the database by calling DataSetCommand object's Update method.
Note: The DataSet object is very intelligent in the case of sending the contents back to data source. It maintains a flag called Row State for each record in the table. When we add, modify or delete a record it simply sets that flag with one of the following values.
Deleted The row was deleted using the Delete method of the DataRow.
Detached The row has been created but is not part of any RowsCollection. A DataRow is in this state immediately after it has been created and before it is added to a collection, or if it has been removed from a collection.
Modified The row was modified and AcceptChanges has not been called.
New The row was added to a RowsCollection, and AcceptChanges has not been called.
UnchangedThe row has not changed since AcceptChanges was last called.
Which are defined as DataRowState enumeration.
Now you might get it! Right, the DataSet object does only updates records for which row state flag in not "unchanged".
Getting Field/Column Attributes
To get information about each column use DataTable's Columns collection property and DataColumn object.
Ex:
foreach( DataColumn dc in aTable.Columns)
{
Console.WriteLine("Field Name : {0}", dc.ColumnName);
Console.WriteLine("Type : {0}", dc.DataType);
Console.WriteLine("Default Value : {0}", dc.DefaultValue);
:
:
}
Putting altogether
Here I assume that you are all very intelligent to assemble the parts mentioned above to make what you want. So feel free to write your comments to seganmca@yahoo.com. This will help me to bring you the Part 2 and 3 more effective and useful.
