Intoduction of ADO.NET with Examples of Database Connectivity

ADO.NET is a set of classes that expose data access services to the .NET programmer. ADO.NET provides functionality to developers writing managed code similar to the functionality provided to native COM developers by ADO. ADO.NET provides consistent access to data sources such as Microsoft® SQL Server™, as well as data sources exposed through OLE DB and XML. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, manipulate, and update data.

ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an integral part of the .NET Framework, providing access to relational data, XML, and application data. ADO.NET supports a variety of development needs, including the creation of front-end database clients and middle-tier business objects used by applications, tools, languages, or Internet browsers. ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with various relational databases like SQL Server, Oracle, DB2, MYSQL etc. It enables developers to deal with data as business objects and entities. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects using C# or VB.NET.

Architecture of ADO.NET

• -         Before ADO.NET all are use ADO (ActiveX Data Objects)

• -         So now we discuss what the difference between ADO and ADO.NET is then we know the need of ADO.NET

• -         ActiveX Data Objects is Microsoft's newest high-level interface for data objects. ADO is designed to eventually replace Data Access Objects (DAO) and Remote Data Objects (RDO). Unlike RDO and DAO, which are designed only for accessing relational databases, ADO is more general and can be used to access all sorts of different types of data, including web pages, spreadsheets, and other types of documents.

• -         Together with OLE DB and ODBC, ADO is one of the main components of Microsoft's Universal Data Access (UDA) specification, which is designed to provide a consistent way of accessing data regardless of how the data are structured.

Difference between ADO and ADO.NET

 

	ADO	ADO.NET
Business Model	Connection-oriented Models used mostly	Disconnected models are used: Message-Like Models.
Disconnected Access	Provided by Record set	Provided by Data Adapter and Data set
XML Support	Limited	Robust Support
Connection Model	Client application needs to be connected always to data-server while working on the data using client-side cursors or a disconnected Record set	Client disconnected as soon as the data is processed Dataset is disconnected at all times
Data Passing	ADO objects communicate in binary mode.	ADO_NET uses XML tor passing the data.
Control of data access behaviors	Includes implicit behaviors that may not always be required in an application and that may therefore limit performance.	Provides well-defined, factored components with predictable behavior, performance and semantics.
Design-time support	Derives information about data implicitly at run time, based on metadata that is often expensive to obtain	Leverages known metadata at design time in order to provide better run-time performance and more consistent run-time behavior

Now we are discuss briefly on ADO.NET Architecture   :

-         ADO.NET implement a disconnected Architecture 

-         DO.NET is conceptually divided into consumers and data providers. The consumers are the applications that need access to the data, and the providers are the software components that implement the interface and thereby provide the data to the consumer.

-         Functionality exists in Visual Studio IDE to create specialized subclasses of the DataSet classes for a particular database schema, allowing convenient access to each field through strongly typed properties. This helps catch more programming errors at compile-time and enhances the IDE's Intellisense feature.

 

Diagram explanation:

1.     Firstly our application and database establish a connection with the help of SqlConnection class.

2.     DataAdapter fetch a data from a database with our command or command builder and store into DataSet.

3.     Data view fetch a data from data set and represent in a structured to a user.

E.g.

Connection:

                        The SqlConnection class handles database connections. It initiates a connection to your SQL database. This class is best used in a using resource acquisition statement. We call Open to query the database with SqlCommand.

DataAdapter:

            In ADO.NET, a DataAdapter functions as a bridge between a data source, and a disconnected data class, such as a DataSet. At the simplest level it will specify SQL commands that provide elementary CRUD (Create, Read, Update, and Delete) functionality with fill () method. At a more advanced level it offers all the functions required in order to create Strongly Typed DataSets, including DataRelations.

Data adapters are an integral part of ADO.NET managed providers, which are the set of objects used to communicate between a data source and a dataset. (In addition to adapters, managed providers include connection objects, data reader objects, and select objects.)

Adapters are used to exchange data between a data source and a dataset. In many applications, this means reading data from a database into a dataset, and then writing changed data from the dataset back to the database. However, a data adapter can move data between any source and a dataset. For example, there could be an adapter that moves data between a Microsoft Exchange server and a dataset.

 

CommandBuilder:

                        The DataAdapter is a part of the ADO.NET Data Provider. ADO.NET DataAdapter is used to manage four separate Command objects. The InsertCommand, the UpdateCommand, and the DeleteCommand properties of the SqlDataAdapter object update the database with the data modifications that are run on a DataSet object. The SqlCommand objects that are assigned to these properties can be created manually in code or automatically generated by using the SqlCommandBuilder object.

The SqlCommandBuilder opens the Connection associated with the DataAdapter and makes a round trip to the server each and every time it's asked to construct the action queries. It closes the Connection when it's done.

DataSet:

The ADO.NET DataSet contains DataTableCollection and their DataRelationCollection. It represents a collection of data retrieved from the Data Source (Data base). We can use Dataset in combination with DataAdapter class. The DataSet object offers a disconnected data source architecture. The Dataset can work with the data it contain, without knowing the source of the data coming from. That is, the Dataset can work with a disconnected mode from its Data Source. It gives a better advantage over DataReader, because the DataReader is working only with the connection oriented Data Sources.

            The Dataset contains the copy of the data we requested. The Dataset contains more than one Table at a time. We can set up Data Relations between these tables within the DataSet. The data set may comprise data for one or more members, corresponding to the number of rows.

            The DataAdapter object allows us to populate DataTables in a DataSet. We can use Fill method of the DataAdapter for populating data in a Dataset. The DataSet can be filled either from a data source or dynamically. A DataSet can be saved to an XML file and then loaded back into memory very easily.

DataTable:

A DataSet is made up of a collection of tables, relationships, and constraints. In ADO.NET, DataTable objects are used to represent the tables in a DataSet. A DataTable represents one table of in-memory relational data; the data is local to the .NET-based application in which it resides, but can be populated from a data source such as Microsoft SQL Server using a DataAdapter.

a.     Columns and Constraints:

                        The schema, or structure of a table is represented by columns and constraints. You define the schema of a DataTable using DataColumn objects as well as ForeignKeyConstraint and UniqueConstraint objects. The columns in a table can map to columns in a data source, contain calculated values from expressions, automatically increment their values, or contain primary key values.

b.    Rows:

                        In addition to a schema, a DataTable must also have rows to contain and order data. The DataRow class represents the actual data contained in a table. You use the DataRow and its properties and methods to retrieve, evaluate, and manipulate the data in a table. As you access and change the data within a row, the DataRow object maintains both its current and original state.

c.     Relations:

                        You can create parent-child relationships between tables using one or more related columns in the tables. You create a relationship between DataTable objects using a DataRelation. DataRelation objects can then be used to return the related child or parent rows of a particular row.

v What is the difference between a DataSet and a DataTable in .NET

1.     DataSet is connectionless. Whenever you want fetch data from database.

2.     DataSet connects indirectly to the database and create a virtual database in local system. Then disconnected from database.

3.      DataSet is easily read and write data from virtual database.

4.     A DataTable object represents a single table in the database. It has a name rows and columns.

5.     There is not much difference between dataset and DataTable, dataset is just the collection of DataTables.      
   

DataView:

The DataView is class which provides different views of the data stored in a DataTable. That is we can customize the views of data from a DataTable. DataView can be used to sort, filter, and search the data in a DataTable, additionally we can add new rows and modify the content in a DataTable.

Introduction of Connection Oriented Architecture:

The architecture of ADO.net, in which connection must be opened to access the data retrieved from database is called as connected architecture. Connected architecture was built on the classes Connection, Command, DataReader and Transaction. 

Connected architecture is when you constantly make trips to the database for any CRUD (Create, Read, Update and Delete) operation you wish to do. This creates more traffic to the database but is normally much faster as you should be doing smaller transactions.

In Connection Oriented Data Access Architecture the application makes a connection to the Data Source and then interact with it through SQL requests using the same connection. In these cases the application stays connected to the database system even when it is not using any Database Operations.

            In Connection Oriented Data Access, when you read data from a database by using a DataReader object, an open connection must be maintained between your application and the Data Source. Unlike the DataReader, the DataSet is not connected directly to a Data Source through a Connection object when you populate it. It is the DataAdapter that manages connections between Data Source and Dataset by fill the data from Data Source to the Dataset and giving a disconnected behavior to the Dataset. The DataAdapter acts as a bridge between the Connected and Disconnected Objects.

Connection:

                        The SqlConnection class handles database connections. It initiates a connection to your SQL database. This class is best used in a using resource acquisition statement. We call Open to query the database with SqlCommand.

Command:
                        The command object is one of the basic components of ADO .NET.

The Command Object uses the connection object to execute SQL queries.

The queries can be in the Form of Inline text, Stored Procedures or direct Table access.

An important feature of Command object is that it can be used to execute queries and Stored Procedures with Parameters.

If a select query is issued, the result set it returns is usually stored in either a DataSet or a DataReader object.

DataReader:

            The DataReader object is used for accessing data from the data store. You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory. After creating an instance of the Command object, you create a DataReader by calling Command. ExecuteReader to retrieve rows from a data source.

            The DataReader is a good choice when retrieving large amounts of data because the data is not cached in memory. You should always call the Close method when you have finished using the DataReader object. If your Command contains output parameters or return values, they will not be available until the DataReader is closed.

• Difference between DataReader and DataAdapter

Transaction:

Transactions are a key feature for maintaining data integrity when modifying data. A database would have a series of properties known as ACID.

Transactions are useful for several other things. First, they provide a way to rollback a group of SQL statements if a single one should.

Secondly, they provide a way to isolate the data your transaction is working on so that you don’t have to worry about surprises.

 Data Provider:

The ADO.NET Data Provider model provides a common managed interface in the .NET Framework for connecting to and interacting with a data store. The ADO.NET Entity Framework builds on top of the ADO.NET Data Provider model to allow for use of the Entity Framework with any data source for which a supported provider is available.

 

Click here to Continue