ACCESSING DATA USING ADO.NET: PART 1 OF 3

0 Comments by admin

The ADO.NET provides disconnected access to any data source for which aManaged Provider exists. A Managed Provider is nothing but set ofobjects that talks to a data source directly. Each RDBMS vendors willprovide 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 OLEDBProviders. This is for compatibility with old data source for which noManaged Providers exists.

The architecture of ADO.NET can be shown like this:

|—————————————————|

Managed Provider

The above figure shows three of possible waysof accessing data in ADO.NET architecture. The most common way isthrough DataSet, which is the primary object in ADO.NET. The DataSetobject can be regarded as a virtual database, which will reside in theclient memory. Unlike ADO RecordSet, DataSet object providesdisconnected access to underlying data source, i.e. any modification orinsertion of data will not be made to underlying data source until weexplicitly call the DataSetCommand object's Update method, thusimproving scalability of the data source.

One big advantage of this model is, we asprogrammers, don't need to bother about data source we are accessingbut only DataSet. The DataSet in turn will talk to data source throughManaged Providers, here DataSetCommand. Each Managed provider willeffectively implement the methods of DataSetCommand, Command,Connection, DataReader etc, appropriate to their data source. In caseof changing the data source, we don't need to alter the program, onlything we will need is changing the appropriate connection string.

The DataSetCommand is a mediator betweenDataSet and Data Source. This object can be used to fill the DataSetwith appropriate tables and other stuffs such as relations,constraints, etc. Once DataSet is filled with tables we are interestedin, it can be accessed freely with DataSet object hierarchy.

The following figure shows the hierarchy ofthe DataSet objects (Lot more there in this hierarchy, showing all isout of scope of this article.)

If you do not need in-memory copy of database,you can use DataReader object. This object provides sequential (onerecord at time) access to a data source. This object is very similar toour ADO's Read-only Forward-only cursor in nature. But provides lessflexibility than DataSet.

Classes & namespaces involved in ADO.NET:

The System.Data namespace defines thefollowing classes to interact with in-memory copy of a database. AgainI am showing only few important classes and you should feel free tovisit 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 classesthat are derived from the above classes and other internal classes toaccess 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 anunderstanding of what the ADO.NET architecture is. Now its time toblast 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 objectreference to point our new ADODataSetCommand object. You can also useADODataSetCommand reference instead. But writing like this will improvecode reusability. For example, if you need to change from OLEDB datasource 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 thestrCommand to fetch the records from data source.EMPLOYEES represents name the table that will be used by the DataSetobject. Similarly we can fetch more than one table and put them in thesame DataSet object.

3) Well, now we have a DataSet object filledwith tables we are interested in! So we can explore some of usualthings we will do with databases such as reading records, modifyingcontents, etc.

Navigating a particular table

To navigate a particular table, create aDataTable reference and use DataSet's Tables collection property to getthat table. Now the table is ready. Similarly to access each row, useDataTable'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,

ass="smallblack">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 DataRowobject using DataTable's NewRow method, fill the fields withappropriate 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 willnot be removed from the data source but only in in-memory copy of thedatabase i.e. what we call DataSet. Got it? Now you might ask how todelete a record in data source? Well, when you have completed all ofaccessing with a DataSet object you can send entire content of DataSetobject back to the database by calling DataSetCommand object's Updatemethod.

Note: The DataSet object is very intelligentin the case of sending the contents back to data source. It maintains aflag called Row State for each record in the table. When we add, modifyor delete a record it simply sets that flag with one of the followingvalues.

Deleted The row was deleted using the Deletemethod of the DataRow. Detached The row has been created but is notpart of any RowsCollection. A DataRow is in this state immediatelyafter it has been created and before it is added to a collection, or ifit has been removed from a collection. Modified The row was modifiedand AcceptChanges has not been called. New The row was added to aRowsCollection, and AcceptChanges has not been called. UnchangedThe rowhas 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 veryintelligent to assemble the parts mentioned above to make what youwant. So feel free to write your comments to seganmca@yahoo.com. Thiswill help me to bring you the Part 2 and 3 more effective and useful.

Data Access

.NET Serialization

.NET Is The Future : Why?

ACCESSING DATA USING ADO.NET: PART 1 OF 3

No comments yet... Be the first to leave a reply!

Leave a Reply

newsletters