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.
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.
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:
and write XML contents in datasets from/ to files
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
the connection before beginning the transaction and close the database
connection after committing a transaction
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.
to our mailing list and receive new articles Note
: We never rent, trade, or sell my email lists to Visit
.NET Programming Tutorial Homepage ______________________________________________________
to our mailing list and receive new articles
: We never rent, trade, or sell my email lists to
Visit .NET Programming Tutorial Homepage
| 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) |