ADO.NET is an improvement over traditional ADO as a data
access technology that is used in the managed environment of Microsoft .NET. This
article discusses this technology, its classes and the providers that it
supports.
What is ADO.NET?
ADO.NET, an object oriented data access technology, is essentially
a collection of classes used to communicate between an application and a
database. The best part of this architecture is its disconnected access. Thus,
after an application retrieves the data from the data store it can readily
disconnect to the underlying database and perform business operations and then
connect to the database again and submit the data. The following are some of
the advantages or striking features of this technology:
·
Fast
·
Rich Object Model
·
XML support
·
Scalability
·
Disconnected Architecture
When learning a technology one should always be aware of
both the advantages and the disadvantages of using it. The following are the
disadvantages of this architecture:
·
Managed Only Access
·
Few Data Providers
Components of ADO.NET Architecture
The two basic components of this architecture are:
·
DataProvider
·
DataSet
DataProvider
A DataProvider encapsulates the protocols that ADO.NET uses
to interact with different types of databases. The following are the supported
DataProviders:
·
Odbc Data Provider
·
OleDb Data Provider
·
Oracle Data Provider
·
SqlServer Data Provider
The DataProvider is composed of the following core objects:
·
Connection
·
Command
·
DataReader
·
DataAdapter
These data
provider classes implement the following interfaces:
·
IDbConnection
·
IDataReader
·
IDbCommand
·
IDbDataAdapter
Connection
The Connection object establishes a connection to the
database using the user name, password and the database name as parameters. The
following are the supported Connection classes:
·
OleDbConnection
·
OdbcConnection
·
OracleConnection
·
SqlConnection
Command
The Command object is used to send the SQL Statements to the
database. Commands are used to insert data, retrieve data and execute stored
procedures in the database. The following are the methods of the Command
class:
·
ExecuteScalar
·
ExecuteReader
·
ExecuteNonQuery
·
ExecuteXmlReader
The ExecuteScalar method returns a single value from a
query. This is faster compared to using ExecuteReader and is the preferred
choice to retrieve a single value from a database table. The ExecuteReader
method returns a single record at a time. ExecuteNonQuery is used to change
the state of a table. It can be used to insert, update or delete records from
the table. ExecuteNonQuery can also be used to execute stored procedures. The
ExecuteXmlReader method returns an XmlReader object. The ExecuteNonQuery
method returns an integer representing the number of rows affected in the
operation.
DataReader
A DataReader is a connected, forward only, read only stream
of data that is retrieved from the database. The DataReader requires an open
connection so it works in the connected mode. A DataReader is much faster
compared to the disconnected DataSet, but it does require an open available
connection.
DataAdapter
The DataAdapter
is a bridge between the DataSet and the underlying database. It provides a set
of methods and properties to move data to and fro between a database and its
in-memory representation, the DataSet. It is also used to update the data in
the database with the data in the DataSet. The following are two of the most
important methods of the DataAdapter class:
·
Fill
·
Update
The DataAdapter
uses the Fill method to populate a DataSet or a DataTable object with data from
the database. The Update method of the DataAdapter class commits the changes
back to the database.
The DataAdapter provides the following commands:
·
SelectCommand
·
InsertCommand
·
UpdateCommand
·
DeleteCommand
DataSet
A DataSet is an in-memory, disconnected representation of the
database. It contains one or more tables and constraints and has the ability
to persist its state to XML, etc. The following are some of its features:
·
Disconnected
·
Tracks changes to the data
·
XML support
·
Caches Data locally
Data View
A DataView is a class that provides a customized view of the
DataSet. It is typically used to sort or filter the rows.
The following code snippet shows how a DataView can be used.
Listing 1: Using a DataView
//Code to connect to the database and then populatea DataSet object using a //DataAdapter.
DataView dataView =dataSet.Tables["emp"].DefaultView;
dataView.RowFilter = "name like'Kanjilal%'";
dataView.Sort = "basic ASC";
Transactions
A transaction is a block of statements that guarantees that
all or none of the statements in the block are executed. In ADO.NET there are
separate classes for using transactions, one for each type of Connection. A
transaction can be started by using the method BeginTransaction on the
currently active Connection. This method returns a Transaction object. To
commit the transaction, the method CommitTransaction is used. In order to
abandon the transaction, the method Rollback is executed. Transactions can be
of the following two types:
·
Database transactions
·
Connection oriented transactions
The following code examples make use of the classes
discussed above and show how one can use the ADO.NET for efficient data storage
and retrieval in a managed environment.
Listing 2: Reading data using the DataReader
private void Page_Load(object sender,System.EventArgs e)
{
SqlConnection sqlConnection = newSqlConnection();
try
{
sqlConnection.ConnectionString =ConfigurationSettings.AppSettings[
"CString"].ToString();
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandText = "Select * fromcountry";
ddListCountry.DataSource =sqlCommand.ExecuteReader();
ddListCountry.DataValueField ="id";
ddListCountry.DataTextField = "name";
ddListCountry.DataBind();
}
catch (Exception ex)
{
//Usual code
}
finally
{
sqlConnection.Close();
}
}Listing 3: Reading data into a DataSet using the
DataAdapter
private void Page_Load(object sender,System.EventArgs e)
{
SqlConnection sqlConnection = newSqlConnection();
try
{
sqlConnection.ConnectionString =ConfigurationSettings.
AppSettings["CString"].ToString();
sqlConnection.Open();
SqlDataAdapter sqlDataAdapter = newSqlDataAdapter("Select * from
country ", sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
ddListCountry.DataSource = dataSet;
ddListCountry.DataValueField ="id";
ddListCountry.DataTextField = "name";
ddListCountry.DataBind();
}
catch (Exception ex)
{
//Usual code
}
finally
{
sqlConnection.Close();
}
}Listing 4: A transactional insert
SqlConnection sqlConnection = newSqlConnection();
sqlConnection.ConnectionString =
ConfigurationSettings.AppSettings["CString"].ToString();
SqlCommand sqlCommand = new SqlCommand();
SqlTransaction sqlTransaction = null;
try
{
sqlConnection.Open();
sqlTransaction =sqlConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
sqlCommand.CommandText = "Insert intocustomervalues(8,'Joydip')";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlCommand.CommandText = "Insert intoproduct
values(1287,'Colgate')";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
sqlTransaction.Commit();
}
catch (Exception e)
{
sqlTransaction.Rollback();
}
finally
{
sqlConnection.Close();
}The following code shows how we can use the Update() method
of the DataAdapter class to update a record.
Listing 5: Updating a record using the DataSet
private void Update()
{
SqlConnection sqlConnection = new
SqlConnection(ConfigurationSettings.
AppSettings["ConnectionString"]);
try
{
sqlConnection.Open();
SqlDataAdapter dataAdapter = newSqlDataAdapter(
"select id,name from customer",sqlConnection);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
SqlCommandBuilder sqlCom = newSqlCommandBuilder(dataAdapter);
DataRow dataRow =dataSet.Tables[0].NewRow();
dataRow[0] = 100;
dataRow[1] = "Jini";
dataSet.Tables[0].Rows.Add(dataRow);
dataAdapter.Update(dataSet);
}
catch (Exception e)
{
//Usual Code
}
finally
{
sqlConnection.Close();
}
}
Suggested Readings
Please refer to the following links for further references
on this topic
http://www.startvbdotnet.com/ado/default.aspx
http://www.vbip.com/books/1861005563/chapter_5563_06.asp
Conclusion
ADO.NET is a powerful technology that can be used from a
managed environment to talk to the underlying database. The one basic drawback
of this technology is that it can only be used from Microsoft .NET’s managed
environment. Please post your comments and suggestions regarding this article
and I will reply as soon as possible.
