---

Data Access in .Net using ADO.Net

If you are new to VB.Net School
This is the 13th in the series of lessons in the VB.NET School. The VB.NET School is a kind of interactive learning platform where those who want to learn .NET with VB.NET can find help and support. With one issue a week, describing some areas of the VB.NET 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 click here

Lesson Plan
In this lesson we will learn how VB.NET 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 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 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


ADO.NET Disconnected Data Access Architecture

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 relationships and their different constraints. The user can perform operations like update, insert and delete on this local dataset. 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 of the 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 recordsets
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 DataSet.
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 names 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 a query
SqlDataAdapter, OleDbDataAdapter	A class that connects to the database system, fetches the records 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

A review of basic SQL queries
Here we present a brief review of four basic SQL queries.

SQL SELECT Statement
This query is used to select certain columns of certain records from one or more database tables.

SELECT * from emp

selects all the fields of all the records from the table named 'emp'

SELECT empno, ename from emp

selects the fields empno and ename for all of the records from the table named 'emp'

SELECT * from emp where empno < 100

selects all records from the table named 'emp' where the value of the field empno is less than 100

SELECT * from article, author where article.authorId = author.authorId

selects all records from the tables named 'article' and 'author' that have the same value of the field authorId

SQL INSERT Statement
This query is used to insert a record into a database table.

INSERT INTO emp(empno, ename) values(101, 'John Guttag')

inserts a record in to the emp table and sets its empno field to 101 and its ename field to 'John Guttag'

SQL UPDATE Statement
This query is used to modify existing records in a database table.

UPDATE emp SET ename = 'Eric Gamma' WHERE empno = 101

updates the record whose empno field is 101 by setting its ename field to 'Eric Gamma'

SQL DELETE Statement
This query is used to delete existing record(s) from a database table.

DELETE FROM emp WHERE empno = 101

deletes the record whose empno field is 101 from the emp table

• Note that its not good practice to allow users to actually delete records from your database. This is open to abuse an human error. A more safer method is to flag a field with an end date. I.e When a user "deletes" a record, what really happens is this.

UPDATE emp SET enddate = GetNow(date) WHERE empno = 101

To remove this record from the users reach in future quieries.

Select * FROM emp WHERE enddate = Null

---

School Home