Introduction
Vascodagama the great Portuguese explorer reached the shores of India near a place called Calicut on the 20'th of May 1498. After he landed on the shores, the King of Calicut met him. During the first meeting, the King showed him a full cup of water gesturing that his country needed no more additions, as it was already complete in all respects. Vascodagama replied to this act by adding some sugar to the cup making the water sweet. Thus he gestured to the king that while a country may be complete there is still scope to make things sweeter. Similarly .Net framework is made sweeter by a whole set of languages. The fact that I saw COBOL.Net in the Borders bookrack today bears testimony to that. As we promised in the previous part, we shall have a brief interlude with .NET data access in this part. Data access in the .Net platform is accomplished through ADO.Net. ADO.Net is Microsoft's next generation data access object model that targets the .NET platform. ADO.NET is well suited for distributed and Internet applications. ADO.NET provides strong support for XML and disconnected data processing.
ADO vs ADO.NET: The difference
Disconnected RecordSet
ADO.NET is much different than ADO. In order to create disconnected data access in ADO the programmers have to code programmatically. The programmer creates a connection first then he creates a record set and then sets the cursor location to AdoUseClient to implement a disconnected record set. The ADO object model is very small compared to ADO.NET. ADO.NET provides lot of objects to handle specialized tasks. As per Microsoft, ADO.NET is not a replacement for ADO but rather an enhancement in the overall data access technology. Both ADO and ADO.NET can be used in a .NET application.
DataSet Vs Recordset
In ADO.Net, a Dataset is disconnected by default. Dataset is a memory representation of one or more tables whereas a record set just holds the query results from a single table in memory. Data from multiple tables can be stored in a record set through a join query.
In dataset all features associated with a database table like relations, constraints and what not can be implemented.
ADO.Net: Quick Facts
The following is the list of namespaces in the base class library associated with ADO.Net.
System.Data
System.Data.OleDb
System.Data.SQLClient
System.Data.SQLTypes
System.Xml (XML part of ADO.NET)
The following is the list of corresponding objects necessary to establish a connection with the database.
OleDbConnection / SQLConnection
OleDbCommand / SQLCommand
OleDbDataReader / SQLDataReader
OleDbDataAdapter / SQLDataAdapter
OleDbParameter / SQLParameter
DataSet
DataTable
DataView
DataRow
DataColumn
The .NET Data Providers
There are many data providers including OleDb, Odbc, Sql, Oracle and MySql. OleDb and Sql are very common of them.
The SQL Client Provider
SQL Client provider is meant only for the SQL Server database. We can use this provider only with SQL Server version 7.0 or higher. For earlier versions of SQL Server, OleDb provider (SQLOleDb driver) has to be used. The SQL Client provider gives good performance over OleDb, as it uses it's own protocol (TDS) to connect to the database directly. TDS stands for Tabular Data Stream. SQL Client has no layer like OleDb or ODBC in between. SQL-Server .NET provider is implemented in the System.Data.SQLClient namespace, which has the following classes to access the SQL-Client database.
SqlConnection
SqlCommand
SqlDataAdapter
SqlDataReader
OleDb .NET data provider has the same set of above objects.
The OleDb .NET Provider
OLE DB provider enables easy and reliable access to business-critical data for local, peer-to-peer, client/server, distributed and Web-based applications. In addition, OLE DB provider
- Works seamlessly with ADO and ADO.NET data-aware components for rapid application development.
- Provides native client/server access to the high performance advantage Database Server RDBMS.
- Provides fast, native access to the advantage Local Server for royalty-free distribution in local and peer-to-peer environments.
- Requires no other database engines (Jet, MSDE, BDE, ODBC, etc.)
- Includes full server-based transaction processing to eliminate database corruption, drastically minimizing support costs.
- Provides complete referential integrity support including primary/foreign key definition and cascaded updates and deletes.
- Includes database security functionality and encryption support.
- Is designed for use with Visual Basic, Access, Visual C++, Delphi, C++ Builder, Visual Studio .NET and other development environments that provide data access via ADO or OLE DB
The following diagram shows the interaction between the various objects of ADO.Net.
![WebSerBnBP3.gif]()
The different classes shown above are explained below: All of them correspond to the SQL Client provider. The same methodology can be followed for the OLE DB provider also.
SqlConnection
Connection objects are used to establish the database connection. A connection object uses the connection string to connect to a database. The most important thing to note is that the DSN name cannot be used to connect to the database using Sql Provider. The reason is SQL-Server provider does not use ODBC Layer to connect the database. It must be ensured that a connection is created and opened before commands are executed. Connection objects are often expensive to obtain, so connection pooling is an option worth considering.
An SqlConnection object can raise three events: StateChange, InfoMessage and Dispose.
StateChange - This event is fired when an Open or Close is called on a connection.
InfoMessage - This event is fired when a Database provider gives an error message.
The following piece of code shows how a connection object is created.
sConstring="user id=sa;password=sa;database=Sreenidotnet;server=localhost";
SqlConnection Con = new SqlConnection (sConstring);
Con.Open();
A OleDBconnection object can be created in the same way. In addition a OleDB connection object can be created using a DSN.
SqlDataAdapter
![WebSerBnBP4.jpg]()
An SqlDataAdapter object works as a mediator between a Dataset and a Database. The Data Adapter fills the Dataset with data using the 'Fill' method. It retrieves data from the data source, populates the Dataset object, and sends the changes back to the data source. The following piece of code shows the creation process of a DataAdapter object.
SqlDataAdapter da= new SqlDataAdapter(sSQL,Con);
da.Fill(ds,"raga");
SqlDataReader
An SqlDataReader implements a connected data model. Using SqlDataReader it is possible to create a forward only record set similar to ADO. This gives good performance as it reads one record at a time unlike Dataset, which fills itself completely. SqlDataReader is well suited for web applications.
To create an SqlDataReader, ExecuteReader method of the SqlCommand object must be called as shown below, rather than directly calling a constructor.
SqlDataReader dReader;
SqlCommand cmd;
SqlConnection cnn;
cnn = new SqlConnection("connection_string");
cnn.Open();
cmd = new SqlCommand("Select * form Authors",cnn);
dReader =cmd.ExecuteReader();
while (dReader.Read())
{
Response.Write(dReader["field1"]);
}
dReader.Close();
The read method must be called at least once to start accessing records through the SqlDataReader.
Dataset
A dataset is a collection of tables in memory. The main thing to note about a record set is that the record set normally holds data from a single table. Data from multiple tables can be stored in a record set using a join type query. On the other hand a dataset can store results of multiple queries thereby effectively maintaining a portion of the replica of the actual database. Since dataset is disconnected from the database there must be some kind of communication between the dataset and the database. DataAdapter is used for that purpose. The main functions performed by DataAdapter are:
- Populate the dataset by fetching data from database.
- Update changes made to the dataset back to the database, using the 'Update' method.
A Dataset is always used in conjunction with a DataAdapter.
A sample WebService that returns a DataSet
The following WebMethod returns all the Orders in the form of a DataSet.
WebMethod(Description="This Method Returns All Orders")]
public DataSet GetAllOrders()
{
SqlConnection Con;
string sConstring;
string sSQL;
sConstring="user id=sa; password=sa;database=Northwind;server=localhost";
sSQL ="select * from Orders";
Con = new SqlConnection(sConstring);
DataSet ds = new DataSet();
Con.Open();
SqlDataAdapter da= new SqlDataAdapter(sSQL,Con);
da.Fill(ds,"Orders");
return ds;
}
Output
In a Dataset each row is considered as a collection. The rows serialized as XML is shown below.
- <NewDataSet xmlns="">
- <Orders diffgr:id="Orders1" msdata:rowOrder="0">
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<EmployeeID>5</EmployeeID>
<OrderDate>1996-07-04T00:00:00.0000000-07:00</OrderDate>
<RequiredDate>1996-08-01T00:00:00.0000000-07:00</RequiredDate>
<ShippedDate>1996-07-16T00:00:00.0000000-07:00</ShippedDate> <ShipVia>3</ShipVia>
<Freight>32.38</Freight>
<ShipName>Vins et alcools Chevalier</ShipName>
<ShipAddress>59 rue de l'Abbaye</ShipAddress>
<ShipCity>Reims</ShipCity>
<ShipPostalCode>51100</ShipPostalCode>
<ShipCountry>France</ShipCountry>
</Orders>
- <Orders diffgr:id="Orders2" msdata:rowOrder="1">
<OrderID>10249</OrderID>
etc..
XML to Dataset
The Dataset class resides in System.Data namespace and is available in System.Data.dll assembly. The following are the DataSet class methods that are useful to read and write XML data.
| Read Method |
Write Method |
Description |
| ReadXml |
WriteXml |
Reads/Writes XML schema and data into the DataSet. |
| ReadXmlData |
WriteXmlData |
Used to read/write only XML data. |
| ReadXmlSchema |
WriteXmlSchema |
Used to read/write only XML schema. |
An XML file can be directly read into a DataSet object. This feature comes in handy during SOAP XML based communication across the web. The steps involve creating an empty DataSet object, opening a Filestream object in read mode and passing it the XML filename to be read and finally calling the ReadXmlData method on the empty DataSet object passing it the file stream object. The following code explains that :
MuraliAddressbook.XML
<?xml version="1.0" ?>
- <AddressBook>
<Name>Srimani Venkataraman</Name>
<Name>Venkateshwararagavan.R</Name>
<Name>Rajeev</ Name >
<Name>Saravanan Gurusamy</Name>
<Name>Sriram</Name>
<Name>Loganathan</Name>
<Name>MasilaMani</Name>
<Name>G.vijayKuamr</Name>
<Name>Pandey.O.M</Name>
<Name>Vijay Bamne</Name>
<Name>Parthasarathy Guru</Name>
</AddressBook>
//Create a DataSet object
DataSet ds = new DataSet();
//Create a FileStream to the Xml Database file in Read mode
FileStream data = new FileStream("MuraliAddressBook.xml", FileMode.Open,
FileAccess.Read,FileShare.ReadWrite);
//Read the DataBase into the DataSet
ds.ReadXmlData(data);
//Close the FileStream
findata.Close();
Conclusion
In this part we had a discussion about the data-access side of web services. In the next part let us discuss the.. Wait! Let's keep you guys guessing! Come back next week to find out about it...
Let's carry the torch forward! Hurrah!