Data access in .Net

The .Net framework includes a rich set of classes called ADO.net mainly to provide data access services that are necessary for developing the components of data layer of an application. It caters to data management support required for retrieval and manipulation of all types of data like relational, XML and application-specific. Also, it offers consistent access to all types of data sources like Microsoft SQL Server and those which are exposed through ODBC and XML. The main enhancement from its earlier version, ADO is the support to XML.

Overview of ADO.net

The architecture of ADO.net is framed such that the four main fundamental units (as mentioned below) providing data management services are designed in a generic way to accommodate all types of data sources.

Dataset is the building block of ADO.net and represents the relational data in-memory from more than one table. It is designed to work in disconnected manner. The data that it holds include the relationship between tables, order, constraint, etc. Dataset is usually used with DataAdapter class to connect to the data source. It can be used in a strongly typed form to expose tables, rows and columns of a database.

Each Dataset contains many DataTable objects, each of which contains a DataColumnCollection. DataColumnCollection represents the schema of the table. The relationship between DataTables is represented by the DataRelation, group of which are maintained in DataRelationCollection. The two constraints that get created automatically for a new relation are the UniqueConstraint which checks uniqueness of values of column and the ForeignkeyConstraint for enforcing master-detail relationships between a single parent record linked to multiple child records.

DataView: exists for every table and is used for sorting and filtering the data obtained in the Dataset.

DataReader: used as a forward-only, read-only, cursor for faster streaming through data from the database. Hence, it is mostly used for data display or search operations in the application.

Thus, the entire database structure is represented in the objects in memory for quicker access to data sources. The classes used for achieving this generic access to data are included in the System.Data namespace.

In addition to the above, following are some key elements necessary for data access in ADO.net:

Data Provider acts as a bridge between an application and a data source. It helps to return query results from a data source, execute commands at a data source, and propagate changes in a Dataset to a data source.

Following are the namespaces in .Net for using different Data Providers:

System.Data.SqlClient – consists of objects to connect to MS SQL Server only through a TDS (Tabular Data Stream)

System.Data.Odbc - for connecting to all types ODBC data sources

System.Data.OleDb – for connecting to data sources through an OLE DB provider (for example, OLEDbConnection)

System.Data.OracleClient - for access to Oracle data sources

System.XML – for handling data stored in XML files

Connection: is used to link the data source with the required parameters and logon credentials. Different interfaces are provided in System.Data to connect to data sources like OleDBConnection, SqlConnection, etc.

Transaction: used when there are multiple updates that can occur within a transaction in an application. A Transaction object is returned when BeginConnection method of Connection object is called.
Command: By setting the property of a Command object and executing it, this object can be used for executing a simple query or a stored procedure or an entire table.

Using XML in ADO.net

ADO.net has provided extensive integration with XML by exposing interfaces to allow the following functionality related to XML data:

• Read and write XML contents in datasets from/ to files
• Load and update XML schema contents from/to XSD files
• Provide synchronous access to data in relational and hierarchical form using Dataset and XmlDataDocument classes

Working with ADO.net

In case of simple data manipulations like creation, update and deletion of single record, methods of Command object like ExecuteQuery() can be called along with the parameter of the required SQL command.

But recently, the number of multi-tiered applications has become more common due to the inherent benefits that it provides. In such scenarios, whether standalone or web-based type, data is sent back and forth between the tiers and hence the concern for performance arises. ADO.net provides a great support to solve this issue.

The Client gets the modified data in its Dataset object by calling GetChanges () method which in turn returns another dataset with the modified rows of the table. This data is passed across the wire for getting updated in the database. The new data caused by this update is fetched back (if anything created) to the client tier where it can get merged to the original dataset using Merge() method of the dataset. Hence, this type of incremental update can save many roundtrips and gain performance.

Tips for better access using ADO.net

• Open the connection before beginning the transaction and close the database connection after committing a transaction

• Use Connection pools for high performance applications so as to keep the connections to data source in use for minimum time

• Use the DataReader for faster, forward-only data access and when there is no need to cache the data

• Use the Dataset when there is a need for :

o data manipulation on multiple data sources (database, spreadsheet, XML file, etc.)
o data exchange between tiers
o data manipulation using XML operations
o data does not change often and hence maintained in a cache locally

• Since Dataset is disconnected from data source, care needs to be taken care of concurrency issues, if it is multi-user application with optimistic concurrency design

• In case, query statements for INSERT/UPDATE/DELETE are constant, use stored procedures by explicitly configuring the DataAdapter command properties for better performance

• In case your application may change to any type of database, use classes of System.Data.Common in the code to make it generic

Being an integral part of .Net framework, ADO.NET is used for developing data layer components representing the data from all data sources and middle-tier business objects that are used by all types of data-sharing applications, including desktop and distributed ones.

| All about Conceptual Analysis on .NET Remoting | Building desktop applications in .Net | Building Distributed Applications Efficiently Using .Net Remoting | C# (C Sharp) Unified Type System: Reference Types | C# (C Sharp) Unified Type System: Value Types | Data access in .Net | Delegates Vs Interfaces in C# (C Sharp) | How is Integration between Java and .NET Achieved | How is Versioning Achieved in C# (C Sharp) | Implementing Authorization and authentication in ASP.net | Implementing design patterns in .Net | List of Query Keywords in C# (C Sharp) |