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

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

SELECT empno, ename from emp

SELECT * from emp where empno < 100

SELECT * from article, author where article.authorId = author.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')

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

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

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

DELETE FROM emp WHERE empno = 101

• 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

User Comments

Jim From Australia (Report as abusive)	Great I am a VB6 programmer & this article was right to the point with easy to understand examples. Just what I needed for a quick rundown on .net database access.
	View all   Rate and comment this article