Tuesday, August 26, 2008

Introduction to ADO.NET

ADO.NET deals with accessing Databases. It�s a new technology that runs within the .NET environment. It uses the concept of managed code. ADO.NET provides access to data sources such as Microsoft SQL Server, OLE DB and XML. Applications connect to these data sources to access, manipulate or update data.

The traditional difficulties regarding database is that it relies on connection-based two-tier model. Instead programmers today wish to make n-tier applications using a disconnected approach.

A two-tier model means there is a server and a client in the application. The server accesses the client. An n-tier applications means there will be more than a server or a client viz: - one or more databases (data-tier), business tier, and presentation-tier.
In early days the client had to be connected with the server and remain connected till the whole procedure or transaction was completed. This was called the connected approach. In such an approach lots of bandwidth was required because if 100 clients were to use a server, each would need a connection. Time, resources and bandwidth became major constraints on such architectures.

In ADO 2.0 disconnected data approach was used. In this approach ADO used Record Sets. All the contents of the server were copied in the Record Set. This allowed the client to get disconnected from the server, work on the Record Set and copy the changes back to the data store again. This approach did not catch on due to security problems. This approach used COM marshalling to transmit a disconnected data set, it supported only those data types defined by the COM standards and hence required type conversions.

ADO.NET is designed from ground up to help solve these problems. Here transmission of data is in XML format. XML format places no restriction on the data types and requires no type conversion.

ADO.NET Architecture

The ADO.NET architecture consists of two main components:- the DataSet and the NET Data Providers.


ADO.NET uses the disconnected approach using the DataSet. The DataSet is explicitly designed for data access independent of any data source. It can be used for data sources, XML data, or used to manage data local to the application. The DataSet acts as a container. All the data coming from the database is dumped in the DataSet for the local machine to use and manipulate. The database is then later updated back with the DataSet. The DataSet contains a collection of one or more DataTable objects made up of rows and columns of data, as well as primary key, foreign key, constraint and relation information about the data in the DataTable objects.

A DataSet can be thought of as a container of data. The DataSet consists of DataTables. DataTables are similar to tables or relations in DBMS (Data Base Management Systems). A table consists of zero or more columns. A table is also generically called as a schema. DataTables contain DataRows and DataColumns. Rows indicate one full entry or a tuple and columns indicate one property or attribute of the table.

The following figure will serve as an example

.NET Data Providers

There are two .NET data providers available: - The SQL Server .NET Data Provider and the OLE DB .NET Data Provider. Depending on the design and data source for our application we can use any one of these two. The SQL Server .NET Data Provider uses its own protocol to communicate with the SQL Server. The OLE DB .NET Data Provider uses native OLE DB through COM interoperability to enable data access. To use the SQL Server .NET Data Provider, you must have access to Microsoft SQL Server 7.0 or later. SQL Server .NET Data Provider classes are located in the System.Data.SqlClient namespace. To use the OLE DB .NET Data Provider, you must use an OLE DB provider (we have used Microsoft Access). OLE DB .NET Data Provider classes are located in the System.Data.OleDb namespace. To use the OLE DB .NET Data Provider, you will have to include the System.Data.OleDb namespace in your applications.

These .NET providers include the Connection object, the Command Object, the DataReader and the DataAdapter in their corresponding namespaces. For e.g. the Connection class in the Sql Server .NET Provider is written as SqlConnection and its equivalent in the OLE DB Service Provider is written as OleDbConnection. In the same way for all the four classes the corresponding names are prefaced with Sql or OleDb accordingly.

The Connection object provides connectivity to a data source. The Command object enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information. A command in its simplest form is nothing but an SQL query. The DataReader provides a stream of read-only and forward-only data from the data source. A read-only data means that it cannot be changed using the DataReader and forward-only means we cannot traverse backwards. Finally, the DataAdapter provides the bridge between the DataSet object and the data source.

You should see the next article to get this clear. We have written two programs using both the .NET Data Providers. Both programs do the same thing.

No comments:


My Blog List