Next Page
Data Access in .Net using ADO.Net
If you are new to C# School
This is the 13th in the series of lessons of our C# School. The C# School is a kind of interactive learning platform where those who want to learn .NET with C# can find help and support. With one issue a week, describing some areas of the C# Programming Language with the Microsoft .Net Platform, this is not the same traditional passive tutorial where the author only writes and the reader only reads. There will be exercise problems at the end of each issue, which the reader is expected to solve after reading the issue. The solution to these problems will be provided in the next issue for testing purposes. There is also a dedicated
message board attached with the school, where you can ask questions about the article, and the author will respond to your question within 2/3 days. You can send your suggestions, feedback or ideas on how these lessons can be improved to either the Author (
farazrasheed@acm.org)or the WEBMASTER (
info@programmersheaven.com).
For previous lessons:
Lesson Plan
Today we will learn how our C# applications can interact with database systems. We will start out by looking at the architecture of ADO.Net and its different components. Later we will demonstrate data access in .Net through an application. Finally we will learn about stored procedures and explore the Data Grid control which is commonly used for viewing data.
Introducing ADO.Net
Most of today's applications need to interact with database systems to persist, edit or view data. In .Net, data access services are provided through ADO.Net components. ADO.Net is an object oriented framework that allows you to interact with database systems. We usually interact with database systems through SQL queries or stored procedures. The best thing about ADO.Net is that it is extremely flexible and efficient. ADO.Net also introduces the concept of a disconnected data architecture. In traditional data access components, you made a connection to the database system and then interacted with it through SQL queries using the connection. The application stays connected to the DB system even when it is not using DB services. This commonly wastes valuable and expensive database resources, as most of the time applications only query and view the persistent data. ADO.Net solves this problem by managing a local buffer of persistent data called a data set. Your application automatically connects to the database server when it needs to run a query and then disconnects immediately after getting the result back and storing it in the dataset. This design of ADO.Net is called a disconnected data architecture and is very much similar to the connectionless services of HTTP on the internet. It should be noted that ADO.Net also provides connection oriented traditional data access services.
Traditional Data Access Architecture
http://www.programmersheaven.com/articles/faraz/lesson13_img1.jpg
ADO.Net Disconnected Data Access Architecture
http://www.programmersheaven.com/articles/faraz/lesson13_img2.jpg
Another important aspect of disconnected architecture is that it maintains a local repository of data in the dataset object. The dataset object stores the tables, their relationship and their different constraints. The user can perform operations like update, insert and delete on this dataset locally, and the changes made to the dataset are applied to the actual database as a batch when needed. This greatly reduces network traffic and results in better performance.
Different components of ADO.Net
Before going into the details of implementing data access applications using ADO.Net, it is important to understand its different supporting components or classes. All generic classes for data access are contained in the System.Data namespace.
| Class | Description |
|---|
| DataSet | The DataSet is a local buffer of tables or a collection of disconnected record sets |
| DataTable | A DataTable is used to contain data in tabular form using rows and columns. |
| DataRow | Represents a single record or row in a DataTable |
| DataColumn | Represents a column or field of a DataTable |
| DataRelation | Represents the relationship between different tables in a data set. |
| Constraint | Represents the constraints or limitations that apply to a particular field or column. |
ADO.Net also contains some database specific classes. This means that different database system providers may provide classes (or drivers) optimized for their particular database system. Microsoft itself has provided the specialized and optimized classes for their SQL server database system. The name of these classes start with 'Sql' and are contained in the System.Data.SqlClient namespace. Similarly, Oracle has also provides its classes (drivers) optimized for the Oracle DB System. Microsoft has also provided the general classes which can connect your application to any OLE supported database server. The name of these classes start with 'OleDb' and these are contained in the System.Data.OleDb namespace. In fact, you can use OleDb classes to connect to SQL server or Oracle database; using the database specific classes generally provides optimized performance, however.
| Class | Description |
|---|
| SqlConnection, OleDbConnection | Represents a connection to the database system |
| SqlCommand, OleDbCommand | Represents SQL query |
| SqlDataAdapter, OleDbDataAdapter | A class that connects to the database system, fetches the record and fills the dataset |
| SqlDataReader, OleDbDataReader | A stream that reads data from the database in a connected design |
| SqlParameter, OleDbParameter | Represents a parameter to a stored procedure |
Next Page
