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.
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.
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:
- 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.
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.
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:
Used to establish a connection with a data source (Database)
Used to retrieve, insert, delete or modify data in data source.
Used to retrieve data from a data source in a read-only and forward-only mode.
Used to transfer data to and from a database. A data adapter retrieves data from a database into a 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:
It contains all the tables retrieve from the database.
It contains relationship and links between tables in a dataset.
It represents a table in the database Collection of a dataset.
It contains all the rows in a datatable.
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
Provides information about data source and database name.
Opens a database connection with the property settings specified by the ConnectionString property
Closes the connection to the database
Set or return the name of the provider for the connection
Specify the name of the database
Specify the name of server to be used when a connection is open.
Specify server login account
Specify the login password for the server login account
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.
// 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
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
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
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:
Used to bind control to a Data source
Used to bind the control to a specific data element.
This control is the combination of TextBox and DropDown List
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: