ADO .NET


ADO .NET is a data access and manipulation protocol. Many applications need data access at one point of time making it a crucial component when working with applications. Data access is making the application interact with a database, where all the data is stored. Different applications have different requirements for database access.

Visual Basic .NET uses ADO .NET (Active X Data Object). Let's see why ADO .NET is superior to ADO.

how does ado.net objects fit in a N-Tier database application

How and where do you declare the ADO.Net objects? What are the functionalities which are available after the ADO.Net object is declared? To know all this, read erp .net books.

Evolution of ADO.NET

DAO (data access model) was created for local databases with the built-in Jet engine. Next came RDO (Remote Data Object) and ADO (Active Data Object) which were designed for Client Server architectures and ADO was superior of the two.

In ADO, all the data is contained in a recordset object which had problems when implemented on the network and penetrating firewalls. ADO is a connected data access, which means that when a connection to the database is established the connection remains open till the application is closed. Keeping the connection open for the lifetime of the application raises concerns about database security and network traffic. Also, open database connections use system resources to a maximum extent making the system performance less effective.

Why ADO.NET?

To overcome with some of the problems mentioned above, ADO .NET came into existence. ADO .NET addresses the above mentioned problems by maintaining a disconnected database access model which means, when an application interacts with the database, the connection is opened to serve the request of the application and is closed as soon as the request is completed. Likewise, if a database is Updated, the connection is opened long enough to complete the Update operation and is closed. By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance. Also, ADO .NET when interacting with the database uses XML and converts all the data into XML format for database related operations making them more efficient.

The ADO.NET Data Architecture

Data Access in ADO.NET relies on two components: DataSet and Data Provider.

DataSet

The dataset is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like an Oracle database or from a Microsoft Access database.

Data Provider

The Data Provider is responsible for providing and maintaining the connection to the database. A DataProvider is a set of related components that work together to provide data in an efficient and performance driven manner. The .NET Framework currently comes with two DataProviders: the SQLData Provider which is designed only to work with Microsoft's SQLServer 7.0 or later and the OleDb DataProvider which allows us to connect to other types of databases like Access and Oracle. Each DataProvider consists of the following component classes:

The Connection object which provides a connection to the database
The Command object which is used to execute a command
The DataReader object which provides a forward-only, read only, connected recordset
The DataAdapter object which populates a disconnected DataSet with data and performs update

Data access with ADO.NET can be summarized as follows:

A connection object establishes the connection for the application with the database. The command object provides direct execution of the command to the database. If the command returns more than a single value, the command object returns a DataReader to provide the data. Alternatively, the DataAdapter can be used to fill the Dataset object. The database can be updated using the command object or the DataAdapter.

Component classes that make up the Data Providers

The Connection Object

The Connection object creates the connection to the database. Visual Studio .NET provides two types of Connection classes: the SqlConnection object, which is designed specifically to connect to SQLServer 7.0 or later, and the OleDbConnection object, which can provide connections to a wide range of database types like Access and Oracle. The Connection string contains all of the information required to open a connection to the database.

The Command Object

The Command object is represented by two corresponding classes: SqlCommand and OleDbCommand. Command objects are used to execute commands to a database across a data connection. The Command objects can be used to execute stored procedures on the database, SQL commands, or return complete tables directly. Command objects provide three methods that are used to execute commands on the database:

ExecuteNonQuery: Executes commands that have no return values 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 forward-only, read-only, connected stream recordset from a database. Unlike other components of the Data Provider, DataReader objects cannot be directly instantiated. Rather, the DataReader is returned as the result of the Command object's ExecuteReader method. The SqlCommand.ExecuteReader method returns a SqlDataReader object, and the OleDbCommand.ExecuteReader method returns an OleDbDataReader object. The DataReader can provide rows of data directly to application logic when you do not need to keep the data cached in memory. Because only one row is in memory at a time, the DataReader provides the lowest overhead in terms of system performance but requires the exclusive use of an open Connection object for the lifetime of the DataReader.

The DataAdapter Object

The DataAdapter is the class at the core of ADO .NET's disconnected data access. It is essentially the middleman facilitating all communication between the database and a DataSet. The DataAdapter is used either to fill a DataTable or DataSet with data from the database with it's Fill method. After the memory-resident data has been manipulated, the DataAdapter can commit the changes to the database by calling the Update method. The DataAdapter provides four properties that represent database commands:

SelectCommand
InsertCommand
DeleteCommand
UpdateCommand

When the Update method is called, changes in the DataSet are copied back to the database and the appropriate InsertCommand, DeleteCommand, or UpdateCommand is executed.

namespaces that are included in ADO.Net

System.Data.SQLClient - Contains classes for connecting to Microsoft SQL Server.

System.Data.OLEDB - Contains classes for connecting to a data source that has an OLEDB provider.

System.Data.Odbc - Contains classes for connecting to a data source that has an ODBC driver.

System.Data.OracleClient - Contains classes for connecting to an Oracle database server.

ADO.Net classes

System.Data.SQLClient namespace contains the following three classes.

SqlConnection
SqlCommand
SqlDataReader


System.Data.OLEDB namespace contains the following three classes.

OleDbConnection
OleDbCommand
OleDbDataReader

We can use the following ado.net classes when working with datasets.

Dataadapter
Dataset
Datatable
Datarelation
Dataview

Other Classes in the Ado.net model.

Transaction class
Exception class
Error class


Performance with ADO.net

With ADO.Net, performance hits are no more an issue. ADO.Net passes extensible markup language from layer to layer seamlessly and has reduced processing overheads and increasing performce by more than two times.

Scalabilty with ADO.Net

ADO.Net handles disconnected data by focussing on the three tasks of get the data, process the data and get the data out. This focussed processing frees up valuable resources on the servers and allows more requests to be handled.

Interoperability

The transportation protocol used in ADO.net is XML and so any component that can handle XML can handle ADO.Net.

Summary

ADO.Net is a superior technology compared to ADO. Common tasks we can do using ado.net are retrieving records from a database table, inserting new records, updating records and deleting records. ADO.Net is used in any database application today. It is preferred over other technologies because of its performnce, scalability and interoperability.



cover image of azure cloud book

Develop Azure Applications

Azure Cloud is gaining popularity and is competing with Amazon Elastic Cloud and Google Cloud for a spot in the billion dollar Cloud computing space.

With this book learn how to use - Table (an Azure Table is different from a SQL Server Table), Queue, Blob storage services, use your local machine to create an Application and a Service, how Azure splits a Table to achieve Load Balancing, Scalability based on the Partition Key, AppFabric Service Bus, Access Control Service, role of Fabric Controller, features of Azure SQL Database and Content Delivery Network.

Many have mastered Cloud Programming and are enjoying the benefits. Why not you?

View Highlights » View Contents »
cover image of foundation database programming book

Create your Own Accounting Software

A foundation book to Master Accounting Software Development, VB.Net, C# and OOPs programming. Detailed explanation of the Accounting domain for programmers is included. SQL and ADO.Net is also covered in detail.

This book gives you a strong foundation in Application Development.

A thorough understanding of the contents will enable you to grasp the topics covered in the accompanying books quite easily. This is because, the accounting domain is dealt with great detail in this book.

The ideal book to get started with application development in the .Net world.

View Highlights » View Contents »

cover image of asp.net book

Create your Own WEB ERP Software using ASP.Net

This book opens you to the Web. Using ASP.Net, C# and SQL Server as the tools, you will learn to build a Multi Tier Web Application as recommended by Microsoft. You will learn implementing inline GridView control, Transaction Management for a layered Web Application, build Data Access component and a Business Layer component.

Opens you to a world of opportunities. You will be able to develop a Web ERP application including all the five modules and learn how to use the TreeView control and GridView control to implement transactions such as Purchase Invoice, Sales Order and Sales Invoice.

View Highlights » View Contents »
cover image of entity framework book

ASP.Net MVC 5 Application Development Using Entity Framework 6 and VS 2015

Entity frameworks is the latest buzz in .Net programming and is the data access technology for Microsoft OS based hand held devices and web applications. Learn to integrate EF into an ASP.Net Application and MVC application. Create, explore and query the Entity Data Model. Learn how to use ObjectDataSource control and GridView control in an ASP.Net Application.

Also, learn to build Three-Tier ASP.Net application, create a business-class, implement methods in the class to perform CRUD operations and bind a GridView to the ObjectDataSource control. Learn how to handle concurrency and managing Transactions.

View Contents »