01 - Introduction to ADO.NET

Most of the applications need to handle data whether it is in the form of text file, a spreadsheet or in the database. Smooth data access is crucial part of any application, so to access this data the applications need to implement the functionality where we can interact with various databases like Microsoft SQL Server, Oracle and MS Access.

In .NET Technology this functionality is provided through ADO.NET, It can be used with all .NET Framework supportive languages like VB.NET, VC++, VF#, VC#.NET etc.

Understanding ADO.NET

ADO.NET is a part of .NET framework architecture. It is used by .NET applications to communicate with a database for retrieving, accessing and updating data.

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

Name

Description

ConnectionString property

Provides information about data source and database name.

Open( )

Opens a database connection with the property settings specified by the ConnectionString property

Close( )

Closes the connection to the database

ConnectionString Parameters

Parameter

Description

Provider

Set or return the name of the provider for the connection

Initial Catalog

Specify the name of the database

Data Source

Specify the name of server to be used when a connection is open.

User ID

Specify server login account

Password

Specify the login password for the server login account

Integrated Security

Determine whether or not the connection needs to be a secure connection. When false, user id and password are specified in the connection. When true current windows account credentials are used for authentication.

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:

Control

Description

ListBox

DataSource property

Used to bind control to a Data source

DisplayMember property

Used to bind the control to a specific data element.

ComboBox

This control is the combination of TextBox and DropDown List

DataGridView

This control can display data from multiple records and multiple columns.

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:

Like us on Facebook