AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=836&pId=-1
An Insight into ADO.NET
page
by Joydip Kanjilal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 14527/ 37

An Insight into ADO.NET

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.


Product Spotlight
Product Spotlight 

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-14 5:02:24 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search