16.1 Introduction to ADO.NET
ADO.NET is a collection of classes that exposes data access services for the .NET framework. It provides a rich set of components for creating distributed data sharing applications. It contains a set of libraries that helps the user to interact with the data sources.
Advantages of ADO.NET
1) The performance of ADO.NET is much faster than ADO
2) It contains SQL Server Data Provider which is optimized for interaction with SQL Server
3) It provides a good support for XML data
4) It provides disconnected operation model through the use of Datasets
5) It has rich object model providing class inheritance and interface implementation
ADO.NET contains data providers that allow user to interact with different type of data sources and databases. There are several data providers in ADO.NET. Each has a unique feature associated with it. These providers are light in weight and create minimum layer between data source and code.
The table containing the list of providers is as shown below:
Data Provider | Description |
Data Provider for SQL Server | It provides data access for Microsoft SQL Server. The System.Data.SqlClient namespace is used |
Data Provider for OLEDB | It is used for data sources using OLE DB. The System.Data.OleDb namespace is used |
Data Provider for ODBC | It is used for data sources using ODBC. The System.Data.Odbc namespace is used |
Data Provider for Oracle | It is used Oracle data sources. The System.Data.OracleClient namespace is used |
EntityClient Provider | It provides data access for Entity Data Model. The System.Data.EntityClient namespace |
Data Provider for SQL Server Compact 4.0 | It provides data access for Microsoft SQL Server 4.0. The System.Data.SqlServerCe namespace is used |
Advantages of using Data Providers in ADO.NET
1) It provides high performance of client-server access over RDBMS
2) It contains trigger providing powerful means for maintaining business rules at database level
3) It provides referential integrity that supports primary/foreign key definition
4) It provides data access through table or server side control
5) It is easy to convert the .NET framework to other database engines
6) It contains full server based transaction to eliminate database corruption
7) It has database security functionality and encryption support
Architecture of ADO.NET
Data processing is based on the connection based model. But as the disconnected architecture provides better scalability and fast execution, they are preferred over the connected one. The ADO.NET architecture consists of two main components as .NET framework and Dataset. The architecture diagram of ADO.NET is as shown below:
The .NET framework contains data providers for data manipulation, forward only and read only access to the data. The connection object is used for data connectivity to a data source. The command object is used for accessing the database commands for returning data to the user. It can be used to run stored procedures in an application.
The Data Reader is used for high performance stream of data source. Data Adapter uses the command objects for the execution of the SQL commands at the data source. The changes that are made to the data are sent back to the Dataset.
Dataset is designed for data access independent of the data source. It can be used with many data sources and with XML data. It can be used to manage data local to the application. The Dataset contains a collection of one or more Data Table objects. These objects consists of rows, columns, primary key and foreign key constraints.
16.2 DataSet Class
The DataSet class is used for representing a subset of the database. It is defined in the System.Data namespace. It is used for disconnected representation of results sets from the Data Source. It provides more flexibility while dealing with the result sets.
The DataSet contains rows, columns, primary keys, constraints, and relation with Data Table objects. It has a collection of DataTable objects and can be related to each other with Data Relation objects.
The list of properties for DataSet are as shown below:
Property | Description |
Container | It gets the container for the component |
CaseSensitive | It gets or sets value indicating whether string comparisons with Data Table objects |
DataSetName | It gets or sets the name of the current DataSet |
DefaultViewManager | It gets a custom view of the data contained into the DataSet to allow filtering, searching and navigating data using a custom DataViewManager |
DesignMode | It gets a value indicating the component is currently in design mode |
Events | It gets a list of event handlers that are attached to this component |
ExtendedProperties | It gets a collection of customized user information associated with Data Set |
HasErrors | It gets a value indicating the errors in any DataTable objects within the DataSet |
Tables | It gets the collection of tables contained in the DataSet |
Relations | It gets a collection of relation that links tables and allow navigation from parent tables to child tables |
IsInitialized | It gets a value that indicates whether the DataSet is initialized |
The list of methods of DataSet are as shown below:
Methods | Description |
AcceptChanges | It commits all the changes made in the DataSet when it was loaded or when the last changes were accepted |
BeginInit | It begins the initialization of a DataSet used on a form or by other component |
Clear | It clears the DataSet of any data by removing all rows in the table |
Clone | It copies the structure of the DataSet including Data Table schemas, relations and constraints |
Copy | It copies the structure and data from the DataSet |
CreateDataReader() | It returns the DataTableReader with one result set per Data Table |
Dispose() | It release all the resources used by the MarshalByValueComponent |
EndInit | It ends the initialization od a DataSet that is used on a form |
Finalize | It allows the object to try to free resources and perform cleanup operations before the garbage collection |
GetChanges() | It gets a copy of the DataSet containing all changes made in the last load |
GetDataSetSchema | It gets a copy of XmlSchemaSet for the DataSet |
GetHashCode | It serves as a default hash function |
HasChanges() | It gets a value indicating whether DataSet has changes, including new, deleted or modified rows |
InferXmlSchema(String, String[]) | It applies the XML Schema from the specified Stream into DataSer |
InferXmlSchema(TextReader, String[]) | It applies the XML Schema from the specified XmlReader to the DataSet |
IsBinarySerialized | It inspects the format of the serialized representation of the DataSet |
Load(IDataReader, LoadOption, String[]) | It fills a DataSet with values from a data source using supplied IDataReader using array of strings |
MemberwiseClone | It creates a shallow copy of the current Object |
Merge(DataRow[]) | It merges an array of DataRow objects into the current DataSet |
Merge(DataSet, Boolean) | It merges a DataSet and its schema into current DataSet |
Merge(DataTable, Boolean, MissingSchemaAction) | It merges a DataTable and its schema into current DataSet preserving or removing changes into the DataSet |
OnRemoveRelation | It occurs when a DataRelation object is removed from the DataTable |
ReadXml(Stream) | It reads XML Schema and data into DataSet using System.IO.Stream |
Reset | It clears all tables and removes all relations, contraints, and tables from the DataSet |
ShouldSerializeRelations | It gets a value indicating the Relations property should be persisted |
ToString | It returns a string containing the name of the component |
WriteXml(Stream) | It writes the current data for the DataSet using the System.IO.Stream |
WriteXml(TextWriter) | It writes the current data for the DataSet using the TextWriter |
WriteXml(TextWriter, XmlWriteMode) | It writes a current data and schema for the DataSet using the TextWriter and XmlWrite mode |
WriteXmlSchema(XmlWriter) | It writes the DataSet structure as an XMl schema to the XmlWriter object |
The DataSet can be used along with the Data Adapter in an application. The syntax for the creation of DataSet and using in an application is as shown below:
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql,connection);
da.Fill(ds);
16.3 DataTable Class
The DataTable class is used when the tables of the database are to be represented. The list of some DataTable properties is as shown below:
Property | Description |
CaseSensitive | It indicates that the string comparisons are case – sensitive |
ChildRelations | It gets the collection of child relations for this DataTable |
Columns | It gets the collection of columns belonging to the table |
Constraints | It gets the collection of constraints maintained by the table |
DataSet | It gets the DataSet to the corresponding table |
DisplayExpression | It gets or sets the expression returning a value to represent the table |
Events | It gets the event handlers list attached to the component |
Namespace | It gets or sets the namespace for the XML representation of the data store |
PrimaryKey | It gets or sets an array of columns functioning as primary key for the table |
Rows | It gets the collection of rows belonging to the table |
TableName | It gets or sets the name of the DataTable |
The list of some of methods of the DataTable are as shown below:
Methods | Description |
AcceptChanges | It contains all the changes when the last AcceptChanges was called |
Clear | It clears the DataTable of all data |
Clone | It clones the structure of the DataTable containing constraints and schemas |
Copy | It copies the structure and data for the DataTable |
Dispose() | It releases all the resources used by the MarshalByValueComponent |
EndLoadData | It turns on notifications, constraints, indes maintenance after the data is loaded |
Finalize | It allows an object to free resources and perform cleanup operations before garbage collection |
GetChanges() | It gets a copy of DataTable where all the changes are made by the user |
ImportRow | It copies the DataRow into DataTable |
Load(IDataReader) | It fills a DataTable with values from a data source using IDataReader |
Merge(DataTable) | It merges the DataTable with the current DataTable |
NewRow | It creates a new row with the schema similar to the DataTable |
Reset | It resets the DataTable to the original state |
Select() | It gets an array of all DataRow objects |
WriteXml(String) | It writes the current contents of the DataTable as XML using the specified file |
The syntax of creating DataTable in an application is as shown below:
DataTable dt = new DataTable();
DataColumn dc = new DataColumn();
dc.ColumnName = “Srno”;
dc.DataType = typeof(int);
dt.Columns.Add(dc);
DataColumn dc1 = new DataColumn();
dc.ColumnName = “Name”;
dc.DataType = typeof(string);
dt.Columns.Add(dc1);
dt.Rows.Add(new object [ ] { “1”, “John”});
16.4 DataRow Class
The DataRow is used to represent rows in the DataTable. Some of the properties of DataRow are as shown below:
Property | Description |
HasErrors | It gets a value indicating that row has errors |
Item[DataColumn] | It gets or sets the data stored in the specified DataColumn |
Item[String] | It gets or sets the data stored in the column specified by the name |
ItemArray | It gets or sets all the values for this row through an array |
RowError | It gets or sets the custom error description for a row |
Table | It gets the DataTable for DataSet |
The syntax for creating DataRow is as shown below:
DataRow dr =new dt.NewRow(); dr[“Name”]=”Mark”; dr[“Course”]=”MBA”; dt.Rows.Add(dr);
16.5 DataAdapter and DataReader
DataAdapter is used as a ADO.NET data provider. It is used as communication between DataSet and DataSource. It is used in disconnected architecture. The list of DataAdapter properties is as shown below:
Property | Description |
DeleteCommand | It is used for deleting records from the data source |
InsertCommand | It is used to insert records in a data source |
SelectCommand | It is used to select records from a stored procedure |
UpdateCommand | It is used to update records in the data source |
TableMappings | It represents the collection of mappings between actual data source table |
The list of methods for the DataAdapter is as shown below:
Methods | Description |
Fill | It is used to fill data into DataSet using DataAdapter |
Update | It is used to update data to the data source |
FillSchema | It adds a DataTable to the DataSet |
DataReader is used in forward only, read only retrieval of data from data sources. They cannot be called directly in the code. User must use ExecuteReader method of the Command object to retrieve the code. Some of the properties of the DataReader are as shown below:
Property | Description |
Connection | It gets the Connection associated with DataReader |
FieldCount | It gets the number of columns |
HasRows | It gets a value indicating the DataReader has one or more rows |
IsClosed | It retrieves a Boolean value the DataReader instance is closed |
Item[Int32] | It gets the value of the specified column in the original format |
RecordsAffected | It gets the number of rows changed, inserted, deleted by execution |
16.6 DbCommand and DBConnection objects
DbCommand object is used to send the SQL command to the data source. It can be used for insert, update, retrieve and delete command. User can modify the schema information at the database. Some of the DbCommand properties are as listed below:
Property | Description |
CommandText | It gets or sets the text command against the data source |
CommandType | It identifies the CommandText property |
Connection | It gets or sets the DbConnection used by the DbCommand |
Parameters | It gets the collection of DbParameter objects |
Site | It gets or sets the ISite of the component |
Transaction | It gets or sets the DbTransaction which the DbCommand object executes |
UpdatedRowSource | It gets or sets the command results applied to the DataRow |
Some of the methods for the DbCommand objects are as shown below:
Methods | Description |
Cancel | It attempts to cancel the execution of the DbCommand |
CreateDbParameter | It creates a new instance of DbParameter object |
Dispose() | It releases all the resources used by the component |
ExecuteDbDataReader | It executes the command text against the connection |
ExecuteNonQuery | It executes SQL statement against the connection object |
ExecuteReader() | It executes the CommandText against the Connection object |
GetService | It returns an object representing a service object for the instance |
DbConnection objects are used for establishing connection with the DataSet. Some of the properties for the connection object are as shown below:
Property | Description |
CanRaiseEvents | It gets a value indicating the component can raise an event |
ConnectionString | It gets or sets string used to open the connection |
ConnectionTimeout | It gets the time to wait for establishing the connection |
Database | It gets the name of the current database after the connection is opened |
DataSource | It gets the name of the database server to which the user can connect |
Site | It gets or sets the ISite of the component |
ServerVersion | It gets a string representing the version of the server |
State | It gets a string that describes the state of the connection |
Some of the methods for the DbConnection object are as shown below:
Methods | Description |
BeginDbTransaction | It starts the database transaction |
ChangeDatabase | It changes the current database for an open connection |
Close | It closes the connection to the database |
CreateCommand | It creates and returns the DbCommand object |
Dispose() | It releases the resources used by the component |
GetHashCode | It serves as a default hash function |
GetSchema() | It returns the schema information for the data source |
Open | It opens the database connection with the settings specified by the ConnectionString object |
ToString | It returns the string containing the name of the component |
Example of ADO.NET in ASP.NET
The code behind file is as shown below:
protected void Page_Load(object sender, EventArgs e)
{
If(!PostBack)
{
DataSet ds = CreateData();
GridView1.DataSource = ds.Tables[“Country”];
GridView1.DataBind();
}
}
private DataSet CreateData()
{
DataSet ds1 = new DataSet();
DataTable Country = CreateCountryTable();
ds1.Tables.Add(Country);
return ds1;
}
private DataTable CreateCountryTable()
{
DataTable Country = new DataTable(“Country”);
AddNewColumn(Country, “System.String”, “CountryName”);
AddNewColumn(Country, “System.String”, “CountryCapital”);
AddNewColumn(Country, “System.String”, “CountryState”);
AddNewRow(Country, “United Kingdom”, “London”, “Cambridgeshire”);
AddNewRow(Country, “Egypt”, “Cairo”, “North Africa”);
AddNewRow(Country, “SriLanka”, “Columbo”, “SouthAsia”);
return Country;
}
private void AddNewColumn(DataTable table, string columnType, string columnName)
{
DataRow dr = table.NewRow();
dr[“CountryName”] = name;
dr[“CountryCapital”] = capital;
dr[“CountryState”] = state;
table.Rows.Add(dr);
}
The source code is as shown below:
<html xmlbs=”http://www.w3.org/1999/xhtml”> <head runat=”server”> <title></title> </head> <body> <form is=”form1” runat=”server”> <div> <asp:GridVew ID=”GridView1” runat=”server” AutoGenerateColumns=”false”> </asp:GridView> </div> </form> </body> </html>
The output is:
