Features of ADO.NET

Ado.net features are:

Disconnected data architecture

ADO.NET supports disconnected architecture; applications connect to database only while retrieving and updating data. After the data is retrieved, the connection with the database is closed. When the database needs to be updated, the connection is re-established. The main advantage in this case is system resources can be utilized in optimal manner. So the database can cater to the needs of several applications simultaneously because interaction is for a shorter duration.

Data cached in datasets

A DataSet is the most common method of accessing data in ADO.NET because it implements disconnected architecture. Data is retrieved from database and stored in DataSets.

Scalability

ADO.NET supports scalability by working with datasets. Database operations are performed on the dataset instead of on the database. Resource can be saved and the database can meet the increasing demands of users more efficiently.

Data transfer in XML format

XML is the fundamental format for data transfer in ADO.NET. Data is transferred from a database into a dataset and from a dataset to another component by using XML. It is beneficial because XML is a standard format for exchanging information between different types of applications.

ADO.NET object model:

In ado.net object model the data present in database is retrieved through a data provider, which is a set of components that includes connection, command, data adapter and data reader objects.It provides the facility of data retrievel as well as data updation. An application accesses data through a DataSet or DataReader object.

The main components of Ado.net object model are:

1. Data provider
2. Dataset

Data provider:

Used to establish a connection to a database, retrieving data, store data in dataset and update the data. It depends on the type of data source that is being accessed.

Mainly the following data providers are being used:

· SQL SERVER

It is used to work specifically with Microsoft SQL Server. System.Data.dll assembly implements SQL Server .NET Framework data provider in the System.Data.SqlClient namespace

· OLE DB

It uses native OLE DB through COM interoperability to enable data access. This data provider works with the OLE DB provider, Oracle OLE DB provider and Jet OLE DB provider. The System.Data.dll assembly implements OLE DB .NET Framework data provider in the System.Data.OleDb namespace.

· ODBC

It uses native ODBC Driver Manager through COM interoperability to enable data access. To use this type of provider, we must use an ODBC driver. The System.Data.ODBC.dll assembly implements ODBC .NET Framework data provider.

· ORACLE

It enables data access to Oracle data sources through the Oracle Client connectivity software. Oracle client software must be installed on the system before using this provider to connect to an Oracle data source. The System.Data.OracleClient.dll assembly implements the oracle .NET Framework data provider in the System.Data.OracleClient namespace.

The key components of data provider are:

· Connection

Used to establish a connection with a data source (Database)

· Command

Used to retrieve, insert, delete or modify data in data source.

· DataReader

Used to retrieve data from a data source in a read-only and forward-only mode.

· DataAdapter

Used to transfer data to and from a database. A data adapter retrieves data from a database into a dataset

Dataset:

It is a part of disconnected environment. It is a disconnected, cached set of records that are retrieved from a database. It contains a collection of one or more DataTable objects. It present in the System.Data namespace.

The key components of DataSet are:

· DataTableCollection

It contains all the tables retrieve from the database.

· DataRelationCollection

It contains relationship and links between tables in a dataset.

· DataTable

It represents a table in the database Collection of a dataset.

· DataRowCollection

It contains all the rows in a datatable.

· DataColumnCollection

It contains all the columns in a datatable.

For the data retrieval and data updation, following steps are required:

1. Create Connection object

2. Create Command object

3. Open Connection object

4. Execute SQL statements

5. Close the Connection object

Creating a Connection object

SqlConnection class

ConnectionString Parameters

For eg.

// creating SqlConnection class object

SqlConnection myconnection=new SqlConnection( );

// creating ConnectionString to the EMPLOYEE database

myconnection.ConnectionString=”Data Source=SQLSERVER01;Initial Catalog=EMPLOYEE; User ID=sa; Password=abc#123456”;

Creating a Command Object

After a connection has been established with the data source, we can execute commands and return results from the data source using a command object.

// Create an instance of SqlCommand class

SqlCommand cmd=new SqlCommand( “select * from empdetails”, myconnection);

Opening the Connection Object

The Open( ) method uses the information in the ConnectionString property of the Connection object to connect to the data source and establish a connection.

// open ConnectionString

myconnection.Open( );

Executing SQL statements in the command object

To execute query passed in the command object we should call the method.

// create an object of SqlDataReader class

SqlDataReader myreader= cmd.ExecuteReader( );

Closing the connection

After executing the query Connection can be closed using Close( ) method

// close ConnectionString

myconnection.Close( );

All the steps can be summarized as follows:

SqlConnection myconnection=new SqlConnection( );
myconnection.ConnectionString=”Data Source=SQLSERVER01;Initial Catalog=EMPLOYEE; User ID=sa; Password=abc#123456”;
SqlCommand cmd=new SqlCommand( “select  * from empdetails”, myconnection);
myconnection.Open( );
SqlDataReader myreader= cmd.ExecuteReader( );
myconnection.Close( );

ADO.NET Object Model

Data Binding

The binding of data with the controls of Windows Forms is known as Data Binding. Data binding is the ability to bind some elements of a data source with the controls of an application.
On the basis of the number of bound values that can be displayed through a control of a Windows Form, It can be divided in two types:
Simple data binding
Complex data binding

Simple Data binding

It is the process of binding a control, such as text box, label to a single value in a dataset. The dataset value can be bound to the control by using the properties of the control. We can bind the value of a column in the dataset to a TextBox control by setting the Text property under the DataBinding category of the TextBox control.

Complex Data Binding

It is the process of binding a component, such as DataGridView, ListBox to display multiple values from a dataset. It is the ability to bind a control to more than one data element and more than one record in a database.

Controls of Windows Form those are used for Complex Data Binding:

Navigating between records

For every data source that is bound to a Windows form control, there exists a BindingNavigator control. This control handles the binding to the data source by keeping a pointer to the current item in the record list.

Relationship between BindingNavigator control and Windows form: