ADO.NET

ADO.NET Overview

ADO.NET is design to provide data access. ADO.NET supports disconnected database access model which means that connection is open long enough to perform data operation and is closed. When data is requested by the application, connection is opened, required data is loaded to the application and connection gets close. So in ADO.NET, connection is only available when it is required. This model reduces system resource usage and thus enhances performance.
ADO.NET is shipped with the Microsoft .NET Framework. It is used to access relational data sources, XML, and application data.ADO.NET uses XML for data transaction between client application and database.

To access data using ADO.NET's DataAdapter objects

• Create connection using connection object.
• Create Dataset object using DataAdapter object.
• Load data into Dataset using fill method of DataAdapter.
• Display Data using Dataset object.
• Close connection

ADO.NET vs ADO

• ADO.NET is considered as evolution version of ADO.
• ADO.NET works with both connected as well as disconnected fashion whereas ADO works with connected architecture.
• ADO.NET provides disconnected access through DataSet while ADO provides disconnected access using recordset.
• ADO.NET use XML to transfer data between objects while ADO use binary format to transfer data.
• ADO.NET is shipped with .Net Framework and implemented using .Net methodology whereas ADO relies on COM.

ADO.NET Data Architecture

ADO.NET has four layers.

* The Physical Data Store

This can be any database, XML files or OLE.

*The DataSet

The dataset stores relevant portion of the database on the local machine. It allows data to be loaded in the memory of the machine and is disconnected from the database. The dataset can manipulate disconnected data in the memory. It contains one or more DataTable objects. The DataTable objects contain DataColumns, DataRows and Constraint collections. The DataSet contains a DataRelations collection which allow you to create associations between rows in one table and rows in another table.

*The Data Provider

A data provider provides a set of components that helps to extract data from database. The components are as follows:

Connection object

Command object

DataReader

DataAdapter

*The Dataview

This determines presentation of a table in the dataset. This is mainly used to sort or filter data in the dataset.

Type of database connection in ADO.NET

SQL Connection

This object connects to the SQL Server.

Oracle Connection

This object is used to connect oracle database.

Oledb connection

This object is used to connect MS Access or MySQL(third party database).
Apart from these connections, ADO.NET also allows access to XML files using Dataset object's ReadXML and WriteXML method.

Components of data providers in ADO.NET

The Connection Object

The Connection object represents the connection to the database. The Connection object has ConnectionString property that contains all the information, required to connect to the database.

The Command Object

The command object is used to execute stored procedures and command on the database. It contains methods to execute command on the database such as ExecuteNonQuery, ExecuteScalar and ExecuteReader.

ExecuteNonQuery

Executes commands that return no records, such as INSERT, UPDATE, or DELETE

ExecuteScalar

Returns a single value from a database query

ExecuteReader

Returns a result set by way of a DataReader object

The DataReader Object

The DataReader object provides a connected, forward-only and read-only recordset from a database. The Command.ExecuteReader method creates and returns a DataReader object. Since it is connected to the database throughout its lifetime, it requires exclusive use of connection object.

The DataAdapter Object

The DataAdapter object acts a communication bridge between the database and a dataset. It fills the dataset with data from the database. The dataset stores the data in the memory and it allows changes. The DataAdapter's update method can transmit the changes to the database.

The DataAdapter Object's properties.
SelectCommand-Contains the command text or object that selects the data from the database.
InsertCommandCont-ains the command text or object that inserts a row into a table.
DeleteCommand-Contains the command text or object that deletes a row from a table.
UpdateCommand-Contains the command text or object that updates the values of a database.

Define connected and disconnected data access in ADO.NET

You have connected data access through the DataReader objects of data provider. This object requires exclusive use of the connection object. It can provide fast and forward-only data access. It doesn't allow editing. Disconnected data access is achieved through the DataAdapter object. This object establishes connection, executes the command, load data in the DataSet. The dataset works independent of database. It contains data in the memory and can edit the data. The changes in the data can be transmitted to the database using Update method of DataAdapter object.

Describe CommandType property of a SQLCommand in ADO.NET.

A SQLCommand has CommandType property which can take Text, Storedprocedure or TableObject as value. If it is set to Text, the command executes SQL string that is set to CommandText property. When set to StoredProcedure, the command runs the stored procedure of the database. If the property is set to TableObject, the command returns the entire content of the table indicated by the CommandText property.

Define Dataview component of ADO.NET.

A DataView object allows you work with data of DataTable of DataSet object. It is associated with a DataTable and sits on the top of DataTable. It is used to filter and sort data. Data sorting is accomplished by setting the Sort property and data filter by setting the RowFilter property.

What are the ways to create connection in ADO.NET?

There are two ways to create connection supported by ADO.NET.

Steps to create connection in codeCreate instance of connection object.Set the ConnectionString property.

Steps to create connection using designerDrag connection object from the Data tab of the toolbox.Set the ConnectionString property using properties window.

ADO.NET transaction Processing
Steps to use transaction object
Open a database connection.Create the transaction object using BeginTransaction method of connection object.Create command object by using transaction object as the parameter. Execute the commands and check for the error.If no error, commit the changes to the database or restore the database state.Close the connection.

sample code

//Connection
SqlConnection sqlConn = new SqlConnection(@"Data Source=LCHNS1403\SQLEXPRESS;Initial Catalog=ODS_Tracking718;uid=sa;pwd=Windows123;");
//Command
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
//if incase of stored procedure
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SpName";
sqlCmd.Parameters.Add("parm1");
sqlCmd.Parameters.Add("parm2");
//SQl DataAdapter
SqlDataAdapter sqlDadp = new SqlDataAdapter();
sqlDadp.SelectCommand = sqlCmd;
DataTable sqlDT = new DataTable();
sqlDadp.Fill(sqlDT);
//Grid Bind
datagridname.DataSource = sqlDt;
datagridname.DataBind();
}