An Insight into ADO.NET
page 1 of 1
Published: 10 May 2006
Unedited - Community Contributed
Abstract
This article discusses ADO.NET with a special focus on the disconnected data access model that is supported by ADO.NET. It discusses DataSets, DataReaders, DataAdapters and the Connection classes with sample programs.
by Joydip Kanjilal
Feedback
Average Rating: 
Views (Total / Last 10 Days): 23292/ 54

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.



User Comments

Title: Nice   
Name: Ashok Kandasamy
Date: 2006-12-01 5:57:19 PM
Comment:
ya.. your discription regarding ado.net is good but it must be improved in the sense of oledb then only user can connect in all database
thank u
ashok
Title: ADO.Net   
Name: Silver-Gray
Date: 2006-09-20 10:15:52 AM
Comment:
what about OleDbConnection? your article was very interesting but slanted to SQL.
Title: DataSet Question   
Name: Javid
Date: 2006-09-20 5:57:44 AM
Comment:
If a A DataSet is an in-memory.

Where is the Object Stored in Client Browser or Server it has been created.
Title: nice article   
Name: sagar
Date: 2006-08-18 1:48:27 AM
Comment:
this is really a nice article about ADO.NET basics. thanks Joydip
Title: Executing Stored Procedures to Insert/Update Data in Database   
Name: Syed Saleem
Date: 2006-08-07 2:54:10 PM
Comment:
the following code will teach you how to execute Stored Procedure in .net. The Code here is for the Stored Procedure which inserts data into the database by sending the data for the insert as parameters to the Stored procedure where you can use them in the Insert query of a Stored procedure.

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 = "MySP_Insert";
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.Parameters.Add("@param_like_EmpName","value");
sqlCommand.Parameters.Add("@param_like_EmpDesignation","value");
int NumOfRecordsinsertedUpdated= sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
//Usual code
}
finally
{
sqlConnection.Close();
}
}
Title: hi   
Name: Vinay Nanda
Date: 2006-08-04 3:21:44 AM
Comment:
this is very understandable description of ADO.NET basics.nice article for the beginers. i really apriciate this.
Title: reflecting changes from dataview to dataset   
Name: saleem
Date: 2006-07-04 6:37:45 AM
Comment:
When we take default view of a datatable from a dataset into a dataview and add/delete/update data in the dataview then how to reflect that changes back to the dataset so that we can update it into the database
Title: thanks   
Name: ravi kumar
Date: 2006-05-24 5:55:53 AM
Comment:
the article is great for learners and beginers.
Title: Hi   
Name: Jasani Pankaj
Date: 2006-05-24 2:20:49 AM
Comment:
It's extremly good document given for ADO.NET.
Title: Author Feedback   
Name: Joydip
Date: 2006-05-23 4:11:40 AM
Comment:
Rizwan, the samples are only code snippets and not complete code examples.
Title: working with different database   
Name: raja
Date: 2006-05-20 12:25:09 AM
Comment:
I want to transfer data from ms-access to sql server. The table design structure are same. please help me how to implement.its urgent.
Title: There is an error in your function   
Name: Rizwan Ullah
Date: 2006-05-18 4:14:19 AM
Comment:
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";
// here must be ilist type object below line is worng
ddListCountry.DataSource =sqlCommand.ExecuteReader();

ddListCountry.DataValueField ="id";
ddListCountry.DataTextField = "name";
ddListCountry.DataBind();
}
catch (Exception ex)
{
//Usual code
}
finally
{
sqlConnection.Close();
}
}
Title: An Insight into ADO.NET - Thank you   
Name: WIL
Date: 2006-05-17 1:55:16 PM
Comment:
Very well written - very lucid - a straightforward and understandable description of ADO.NET basics - I appreciate the time it took to do this well on behalf of those just learning.
Title: HI   
Name: Haider
Date: 2006-05-16 1:40:10 AM
Comment:
A very good article to quick start with ado.net wating for your forthcoming articles
Title: Author Response   
Name: Joydip
Date: 2006-05-15 10:53:03 AM
Comment:
Thanks Mike for your comments. Please look into my forthcoming articles, I would cover more and more aspects of ADO.Net with a lot of examples.
Title: hi   
Name: mike
Date: 2006-05-13 12:40:42 PM
Comment:
great intro to ado.net. can you plz describe DataView in more detail.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-06-24 3:07:35 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search