Performing common data access tasks with ADO.NET
Enough review and introduction! Let's start something practical. Now we will build an application to demonstrate how common data access tasks are performed using ADO.NET.

We will use MS SQL server and MS Access database systems to perform the data access tasks. SQL Server is used because probably most of the time you will be using MS SQL server when developing .NET applications (And theres a free cut down version available from Microsoft). For SQL server, we will be using classes from the System.Data.SqlClient namespace. Access is used to demonstrate the OleDb databases. For Access we will be using classes from the System.Data.OleDb namespace. In fact, there is nothing different in these two approaches for developers and only two or three statements will be different in both cases. We will highlight the specific statements for these two using comments like:

' For SQL server
Dim dataAdapter As New SqlDataAdapter(commandString, conn)
' For Access
Dim dataAdapter As New OleDbDataAdapter(commandString, conn)

Field Name	Type	Description
artId	(Primary Key)Integer	The unique identifier for an article
title	String	The title of an article
topic	String	Topic or Series name of the article like 'Multithreading in Java' or 'VB.NET School'
authorId	(Foreign Key)Integer	Unique identity of author
lines	Integer	Number of lines in the article
dateOfPublishing	Date	The date the article was published

The 'ProgrammersHeaven' database also contains a table named 'Author' with the following fields:

Field Name	Type	Description
authorId	(Primary Key)Integer	The unique identity of the author
name	String	Name of the author

Accessing Data using ADO.NET
Data access using ADO.NET involves the following steps:

• Defining the connection string for the database server
• Defining the connection (SqlConnection or OleDbConnection) to the database using a connection string
• Defining the command (SqlCommand or OleDbCommand) or command string that contains the query
• Defining the Data Adapter (SqlDataAdapter or OleDbDataAdapter) using the command string and the connection object
• Creating a new DataSet object
• If the SQL command is SELECT, filling the DataSet object with the results of the query through the Data Adapter
• Reading the records from the DataTables in the DataSets using the DataRow and DataColumn objects
• If the SQL command is UPDATE, INSERT or DELETE. The dataset will be updated through the data adapter
• Accepting to save the changes in the DataSet to the database

Since we are demonstrating an application that uses both SQL Server and Access databases we need to include the following namespaces in our application:

Imports System.DataImports System.Data.OleDb     ' for Access database
Imports System.Data.SqlClient ' for SQL Server

Defining the connection string
The connection string defines which database server you are using, where it resides, your user name and password and optionally the database name.

For SQL Server we have written the following connection string:

' for Sql Server
Dim connectionString As String = "server=P-III; database=programmersheaven;" + _   
"uid=phuser; pwd=nicecoding;"

For Access, we have written the following connection string:

' for MS Access
Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + _
"data source = c:\programmersheaven.mdb"

Author's Note: Connection string details are vendor specific. A good source of connection strings for different databases is http://www.connectionstrings.com/

Defining a Connection
A connection is defined using the connection string. This object is used by the Data Adapter to connect to and disconnect from the database. For SQL Server, a connection is created like this:

' for Sql Server
Dim conn As New SqlConnection(connectionString)

And for Access, a connection is created like this:

' for MS Access
Dim conn As New OleDbConnection(connectionString)

Defining the command or command string
The command contains the query to be passed to the database. We are using a command string. We will see the command object (SqlCommand or OleDbCommand) later in the lesson. The command string we have used in our application is:

Dim commandString As String = "SELECT " + _ 
                      "artId, title, topic, " + _
                       "article.authorId as authorId, " + _
                       "name, lines, dateOfPublishing " + _
                       "FROM " + _
                       "article, author " + _
                       "WHERE " +  _
                       "author.authorId = article.authorId"

Dim commandString As String = "SELECT * from article"

We need to define the Data Adapter (SqlDataAdapter or OleDbDataAdapter). The Data Adapter stores your command (query) and connection. Using the connection and query the DaraAdapter connects to the database when asked, fetches the result of the query and stores it in a local dataset.

For SQL Server, a Data Adapter is created like this:

' for Sql Server
Dim dataAdapter As New SqlDataAdapter(commandString, conn)

' for MS Access
Dim dataAdapter As New OleDbDataAdapter(commandString, conn)

Creating and filling the DataSet
Finally, we need to create an instance of the DataSet. As we mentioned earlier, a DataSet is a local and offline container of data. The DataSet object is created simply as:

Dim ds As New DataSet()

dataAdapter.Fill(ds, "prog")

This is all we need to connect and fetch data from the database. Now the results of the query is stored in the dataset object in the prog table, which is an instance of the DataTable. We can get a reference to this table by using the indexer property of the DataSet object's Tables collection.

Dim dataTable As DataTable = ds.Tables("prog")