ADO.NET is an improved version of Microsoft's ADO that can be executed to perform database operations in a managed environment. It is an object oriented data access technology from Microsoft. It comprises of a collection of classes and namespaces that you can use to perform database operations in both connected and disconnected modes. This is the first part of a series of articles on ADO.NET that discuss the components and features of ADO.NET and how to write programs to perform CRUD operations using ADO.NET.
Here is a list of the major features of ADO.NET:
The ADO.NET architecture comprises of the following major components:
DataSet: An in-memory disconnected representation
of database
DataProvider: This encapsulates the protocols used
to interact with databases
The DataSet is an in-memory, disconnected representation of the database and contains one or more tables and constraints. It can cache data locally and has the ability to track changes to data. The MSDN states, "The ADO.NET DataSet is the core component of the disconnected architecture of ADO.NET. The DataSet is explicitly designed for data access independent of any data source. As a result it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. 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."
Here is a list of the data providers in use:
The ADO.NET Data Provider is in turn comprised of the following core objects:
Note that the DataReader is a connected, forward only, read only stream of data that is retrieved from the database. It 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.
Note that you have specific connection, command and data reader objects depending on the database that you are using. As an example, you have the following types of connection objects in ADO.NET:
Similarly, you have the following types of command objects:
And, you have the following types of data readers too:
The command object makes use of the following methods to send and execute the SQL statements.
Note that the ExecuteNonQuery method returns an integer representing the number of rows affected in the operation.
For disconnected data access in ADO.NET, you use DataAdapters and DataSets. While the former is a bridge between the DataSet and the underlying database, the later is an in-memory representation of data.
The most important methods of the DataAdapter class are:
The Fill method is used to populate a DataSet instance with data from the database. The Update method is used to update the database with changes based on the data in a DataSet. In essence, the Update method of the DataAdapter class commits the changes back to the database.
The DataAdapter provides the following commands:
Let’s dig into some code now. Here is the code that illustrates how you can insert data into the database by sending the data for the insert command as parameters to the stored procedure.
Note: To compile and execute the examples given in this chapter, you will require the System.Data.SqlClient namespace to be included in the examples.
SqlConnection sqlConnection = new SqlConnection();
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
SqlCommand sqlCommand = new SqlCommand();
try
{
sqlConnection.Open();
sqlCommand.CommandText = "Insert into StudentMaster (Name, Address, Phone) _
values(" + "'"+txtName.Text +"'"+ "," + "'"+txtAddress.Text + "'"+", _
" + "'"+txtPhone.Text +"'"+ ")";
sqlCommand.Connection = sqlConnection;
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex);
}
finally
{
sqlConnection.Close();
}
Note that the connection string to connect to the database is specified in the web.config file. Here is how the connection string looks like.
<connectionStrings><add name="DBConnectionString"
connectionString="Data Source=.;Initial Catalog=Student;
User ID=sa;Password=sa" providerName="System.Data.SqlClient"/>
</connectionStrings>
You can use the DataAdapter to read data. In this case, you need to populate a Dataset instance with data retrieved from a call to the Fill method on the DataAdapter instance. Here is the code.
SqlConnection sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
SqlDataAdapter sqlDataAdapter =
new SqlDataAdapter("Select * from StudentMaster ", sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
}
catch (Exception ex)
{
//Write your error handling code here
}
finally
{
sqlConnection.Close();
}
You can update data by making a call to the Update method of the DataAdapter. In the example below, we will use a SqlCommandBuilder and the DataAdapter to update a record in the StudentMaster table. Here is the code:
SqlConnection sqlConnection = new SqlConnection();
try
{
sqlConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString.Trim();
sqlConnection.Open();
SqlDataAdapter sqlDataAdapter = new
SqlDataAdapter("Select * from StudentMaster ", sqlConnection);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet);
SqlCommandBuilder sqlCom = new SqlCommandBuilder(sqlDataAdapter);
DataRow dataRow =dataSet.Tables[0].NewRow();
dataRow[0] = “Jini”;
dataRow[1] = "Kolkata";
dataSet.Tables[0].Rows.Add(dataRow);
dataAdapter.Update(dataSet);
}
catch (Exception e)
{
//Write your error handling code here
}
finally
{
sqlConnection.Close();
}
Conclusion
In the first part of this series of articles on ADO.NET, we have had a look at what ADO.NET is, its features and how we can use it to perform CRUD operations in our applications. In the next part in this series, we will take a look at how we can use ADO.NET for binding data to the ASP.NET data controls. We will also discuss how we can use ADO.NET for performing transactional operations.