Role Of ADO.NET

Introduction

ADO.NET is used to provide consistent and reliable access to various data sources such as XML. It also provides access to data sources uncovered through OLE DB and ODBC. ADO.NET is used by applications to connect to these data sources and retrieves and modify data that they contain.

ADO.NET contains two main data access components, namely. .NET Framework data providers and Dataset. The .NET Framework data providers are used to connect to a database to run commands in the database and finally to retrieve the results of the operation performed. These retrieved results can be processed directly or can be placed in a dataset object. Dataset helps in storing the records that are retrieved from the data source.

Features of ADO.NET

Server Enumeration

Supports active instances of Microsoft SQL Server 2000 and later.

New SQL Server Max Data Types

Supports varchar(max) and nvchar(max) , varbinary(max) data types in SQL Server 2005.

SQL Server User-Defined Types

Supports user-defined data types (UDIs) in SQL Server 2005.

Notifications

Enables .NET framework applications to send commands to the SQL Server. This generates a notice if the result of the execution of the command is different from the original result.

Transactions with SQL Server Snapshot isolation

Supports transactions with snapshot isolation, a SQL Server 2005 mechanism designed to reduce blocking in OLTP applications.

Database Mirroring

Supports database mirroring in SQL Server 2005 that allows using a failover partner server.

Provider Statistics

Retrieves runtime counter statistics in SQL Server 2005.

Change Password

Supports changing user accounts passwords in SQL Server 2005 without the involvement of the administrators.

Differentiate between DAO, RDO, ADO, and ADO.NET

Data Access Objects (DAO) is used for connecting to databases like MS Access which has a built-in Jet Engine, also known as Joint Engine Technology. Jet Engine is a database engine on which several Microsoft products such as Access or Visual Basic were built. It allowed multiple users to access the database at the same time.

Remote Data Objects (RDO) was basically developed for both Client Server and Distributed Architecture. Through ADO was quite similar to ADO.NET, It supports RecordSet. RecordSet is a COM object that cannot be easily passed over the network as it cannot penetrate through firewalls.

Few points represent the difference between ADO and ADO.NET

Features	ADO	ADO.NET
Representation of Data	Recordset object is used to represent data in which appears as a single table.	DataSet object is used to represent data in the memory which contains multiple tables.
Relationship with tables	JOIN query is used to collect data from multiple database tables into a single table.	DataRelation object is used to link rows in multiple tables.
Data Navigation	Sequential scanning of data using MoveNext() method.	Provide random access to rows within a table or loop access within a collection of rows.
Disconnected access	Supports connected data access architecture	Supports disconnected data access architecture.
Data Sharing	COM marshalling allows disconnected transmission of RecordSet.	Transmit a DataSet as an XML stream.
Data transfer through firewalls	COM Marshalling does allow transferring data through firewalls	DataSet objects use XML that allows transferring of data through firewalls.

Disconnected Data Access Approach

An application can access data from the database by two methods, connected data access, and disconnected data access. In connected data access, the connection between the application and the database is continuously maintained.

Disconnected database access establishes a connection to the database only when required. Once a local copy of the data is saved, the connection to the database is closed. This process helps in reducing the traffic between application and database and thus increases the efficiency of the connectivity.

The disconnected data access approach includes two strategies:

Storing Data in Datasets
Disconnected data access acts as cache memory to store the records in tabular format. This allows the user to process records even when the application is disconnected from the data source. In addition, using this strategy allows exchanging data between multiple tables and applications.
Performing Direct Database Operations
Disconnected data access allows the user to directly perform database operations. It uses the data command object, opens a connection, executes the command for a particular operation, and then the connection is closed.

.NET Framework Data Provider for ODBC

Data provider for Open Database Connectivity (ODBC) allows the user to access ODBC data sources. The two important components of ODBC are ODBC Driver Manage and ODBC Driver. ODBC Driver manager selects and loads ODBC drivers on behalf of the application. Thus, the ODBC Driver Manager acts as a link between an application and an ODBC driver. ODBC drivers are dynamically loaded by the ODBC Driver Manager for providing a connection to the target database.

An Advantage of using ODBC is that it provides interoperability between the application and the database. Data provider for ODBC allows an application to access any ODBC-related database.

An advantage of using ODBC is that it provides interoperability between the application and the database. Data provider for ODBC allows an application to access any ODBC-related database.

.NET Framework Data Provider for Oracle

Data provider for Oracle allows the user to access Oracle data sources. This can be achieved using the Oracle client connectivity software. Unlike other data providers, it provides high-performance access while accessing the advanced Oracle database. Furthermore, the data provider for Oracle does not use any extra bridge for accessing data. Thus, its performance is better than OLEDB and Oracle.

.NET Framework data provider for Oracle provides various features such as Multiple Active Result Sets (MARS), allows binding array parameters, Unicode support, and so on…

Datasets in ADO.NET

Datasets are a collection of data retrieved from the database. This collection is saved as a local copy, which allows the user to access data irrespective of the data source. The advantages of using dataset model are:

Data kept in the dataset can be easily moved between the tiers of the application.
The dataset contains multiple tables that help in working with tables individually or even for navigating between parent and child tables.
Dataset can be used for efficiently transferring data within components of an application or even with other applications.
Dataset facilities working with the same records frequently without re-querying the database.
Dataset can be used to represent data retrieved from various sources into a table and these tables can be manipulated as if data is retrieved from a single source.
Dataset helps in easier binding of controls to data when working on home.
Dataset helps in creating a class file that represents the structure as objects. This makes it easier, clearer, and less error-prone to code with.

The advantages of performing database operations directly are,

Connecting directly to the database with the help of commands provides more control over SQL statements or stored procedures.
Accessing data directly from the database, without using the dataset, reduces the memory usage of the application. This is because the dataset requires memory for storing the retrieved data as a local copy.
When a web application accesses data using a dataset, some extra steps are required for saving the state of the dataset. These steps can be avoided by reading the data directly from the database using a data reader.

Command Object

The Command object is used to execute a query in a database and store the records in a Recordset object. One of the major features of the Command object is the ability to use stored queries and procedures that accept parameters.

The properties and methods of the OdbcCommand object as follows,

CommandText: This property specifies or retrieves a value that indicates how the CommandText property is interpreted.
CommandType: This property specifies or retrieves a value that indicates how the CommandText property is interpreted.
Connection: This property specifies or retrieves the OdbcConnection.
Cancel: This method cancels the execution of the OdbcCommand object.
CreateParameter: This method is used to create an instance of the OdbcParameter object.
ExecuteReader: This method is used to pass CommandText to the connection and build an OdbcDataReader.

The code below creates and initializes an OdbcCommand object. It sets few common properties of the OdbcCommand object.

OdbcConnection odbcconDiary=new OdbcConnection(“Driver=(SQLServer);Server=MYSERVER\\ASHISH;uid=sa;pwd=helloash;Database=Student;”);
OdbcCommand odbccomDiary=new OdbcCommand();
odbccomDiary.Connection= odbccomDiary;
odbccomDiary.CommandText=”Select * from Students”;
odbccomDiary.Open();
odbcDataReader odbcdreaderDiary=odbccomDiary.ExecuteReader();
while(odbcreaderDiary.Read())
MessageBox.Show(odbcdreaderDiary.GetValue(1).ToString());

This source code creates an instance of the OdbcConnection class is created namely, odbcconDiary. An instance of the OdbcCommand class is created. The Connection property specifies the connection to the database. The CommandText sets the command to be executed. The Open() method opens the connection to the database. The ExecuteReader() method executes the command on the connection. The result of the execution is read by an instance of OdbcDataReader namely odbcdreaderDiary. While the data is been read from the data reader, a message box is displayed showing the value in the second column of the current row. The ToString() method converts the data into a string value.

DataAdapter Object

The DataAdapter class acts as a link between the ADO.NET objects and the data source. It is used to retrieve data from the data source into DataSet or a DataTable by using the Fill() method. The Update() method updates any changes made in the DataSet and the DataTable back to the data source.

After retrieving data from the DataAdapter, the disconnected objects do not have any information about the connection, tables, columns, or the source of data. The retrieved data can be manipulated and later updated in the data source. DataAdapter helps in bridging the data source and the dataset. Thus, the data can be passed between applications without the risk of revealing the details of the data source.

Creating a DataAdapter Object

The object of the DataAdapter class can be created in four different ways. Two of the most common ways are mentioned below in the source code.

This source code creates a DataAdapter object by specifying a commandText string and the Connection string in the constructor.

SqlConnection sqlconInvoice=new SqlConnection(“Data Source=MYSERVER\\ASHISH;uid=sa;pwd=studdata;Initial Catalog=Northwind;”);
String commandText=”select * from employees”;
DataAdapter daInvoice=new SqlDataAdapter(commandText, sqlconInvoice);
DataSet dsetEmployees=new DataSet();
daInvoice.Fill(dsetEmployees);
MessageBox.show(dsetEmployees.Table[0].Rows[0][1].ToString());

In this code, an instance of the SqlConnection class is created namely, sqlconInvoices. The commandText string is assigned the SELECT SQL statement. An instance of the DataAdapter class is created by invoking the constructor of the SqlDataAdapter class. This constructor takes the commandText string and an instance of the SqlConnection class as the parameter. A dataset is created and the Fill() method fills the dataset with the records of the Employees table. A message box is displayed showing the value in the second column of the first row. The ToString() method converts the column value into the string format.

The above technique is common, it becomes difficult when used with the parameterized queries. The user can use the SqlCommand class while working with parameterized queries.

Code below creates a SqlCommand object and uses it in the instance of the DataAdapter object, which is created using one of its constructors.

SqlConnection sqlconInvoice=new SqlConnection(“Data Source=MYSERVER\\ASHISH;uid=sa;pwd=emplodata;Initial Catalog=Northwind;”);
SqlCommand sqlcomInvoice=new SqlCommand(”select * from employees”,sqlconInvoice);
DataSet dsetEmployees=new DataSet();
DataAdapter daInvoice=new SqlDataAdapter(sqlcomInvoice);
daInvoice.Fill(dsetEmployees);
MessageBox.show(dsetEmployees.Table[0].Rows[0][1].ToString());

In this code, an instance of the SqlConnection class is created namely, sqlconInvoice. An instance of SqlCommand class namely, sqlcomInvoice, specifies two parameters. The first parameter is the SELECT SQL statement and the second parameter is the instance of the SqlConnection class.

An instance of the DataAdapter class is created by invoking the constructor of the SqlDataAdapter class. This constructor takes the SqlCommand object as the parameter. A dataset is created and the Fill() method fills the dataset with the records of the Employee table. A message box is displayed showing the value in the second column of the first row. The ToString() method converts the column value into the string format.

Summary

ADO.NET components, data providers and Dataset allow the user to connect and access data. The advantage of using XML, for transferring data is that they are not blocked by firewalls. The disconnected data access architecture maintains the database connection only when it is required. This helps in reducing the traffic over the network. Dataset allows the user to transfer data within the components of an application or other applications. The CommandText property of the command object retrieves the text of a command statement to be used against a data provider. The connection property of the command object sets the database connection. DataAdapter acts as a link between the ADO.NET objects and the data source.