What is ADO.Net?

Most of the today’s applications need to interact with database systems to persist, edit or view data. In .Net data access service is provided through ADO.Net (ActiveX Data Object in Dot 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. ADO.Net encapsulates our queries and commands to provide a uniform access to various database management systems.

ADO.Net is a successor of ADO (ActiveX Data Object). The prime features of ADO.Net are its disconnected data access architecture and XML integration.

What does it mean by disconnected data access architecture of ADO.Net?

ADO.Net introduces the concept of disconnected data architecture. In traditional data access components, you make a connection to the database system and then interact 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 the valuable and expensive database resource 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 data set. Your application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in dataset. This design of ADO.Net is called disconnected data architecture and is very much similar to the connection less services of http over the internet. It should be noted that ADO.Net also provides the connection oriented traditional data access services.

Traditional Data Access Architecture

ADO.Net Disconnected Data Access Architecture

Another important aspect of the disconnected architecture is that it maintains the local repository of data in the dataset object. The dataset object stores the tables, their relationship and different constraints. The user performs operations like update, insert, delete to this dataset locally and finally the changed dataset is stored in actual database as a batch when needed. This greatly reduces the network traffic and results in the better performance.

What does it mean by connected data access architecture of ADO.Net?

In the connected environment, it is your responsibility to open and close the database connection. You first establish the database connection, perform the interested operations to the database and when you are done, close the database connection. All the changes are done directly to the database and no local (memory) buffer is maintained.

What's the difference between accessing data with dataset or data reader?

The dataset is generally used when you like to employ the disconnected architecture of the ADO.Net. It reads the data into the local memory buffer and perform the data operations (update, insert, delete) locally to this buffer.

The data reader, on the other hand, is directly connected to the database management system. It passes all the queries to the database management system, which executes them and returns the result back to the application.

Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.
What are the performance considerations when using dataset?

Since no memory buffer is maintained by the data reader, it takes up fewer resources and performs more efficiently with small number of data operations. The dataset, on the other hand is more efficient when large number of updates are to be made to the database. All the updates are done in the local memory and are updated to the database in a batch. Since database connection remains open for the short time, the database management system does not get flooded with the incoming requests.

However, since the dataset stores the records in the local buffer in the hierarchical form, it does take up more resources and may affect the overall performance of the application.

How to select dataset or data reader?

The data reader is more useful when you need to work with large number of tables, database in non-uniform pattern and you need not execute the large no. of queries on few particular table.

When you need to work on fewer no. of tables and most of the time you need to execute queries on these fewer tables, you should go for the dataset.

It also depends on the nature of application. If multiple users are using the database and the database needs to be updated every time, you must not use the dataset. For this, .Net provides the connection oriented architecture. But in the scenarios where instant update of database is not required, dataset provides optimal performance by making the changes locally and connecting to database later to update a whole batch of data. This also reduces the network bandwidth if the database is accessed through network.

Disconnected data access is suited most to read only services. On the down side, disconnected data access architecture is not designed to be used in the networked environment where multiple users are updating data simultaneously and each of them needs to be aware of current state of database at any time (e.g., Airline Reservation System).
How XML is supported in ADO.Net?

The dataset is represented in the memory as an XML document. You can fill the dataset by XML and can also get the result in the form of XML. Since XML is an international and widely accepted standard, you can read the data using the ADO.Net in the XML form and pass it to other applications using Web Service. These data consuming application need not be the essentially Dot Net based. They may be written with Java, C++ or any other programming language and running on any platform.
What are the different components of ADO.Net?

The famous diagram of the ADO.Net architecture and its components is presented in the figure below:

All generic classes for data access are contained in the System.Data namespace. A short description of the core classes of ADO.Net is presented below:

Class
    

Description

DataSet
    

The DataSet is a local buffer of tables or a collection of disconnected record sets

DataTable
    

DataTable is used to contain the data in tabular form using rows and columns. DataRow Represents a single record or row in DataTable

DataRow
    

Represents a single record or row in DataTable

DataColumn
    

Represents a column or field of 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. The provider for such classes are called the Dot Net Framework Data Providers. Microsoft itself has provided the specialized and optimized classes for their SQL server database system. The name of these classes start with ‘Sql’ and these are contained in System.Data.SqlClient namespace. Similarly, Oracle has also provided its classes (driver) optimized for 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 System.Data.OleDb namespace. In fact, you can use OleDb classes to connect to SQL server or Oracle database but using the database specific classes generally provides optimized performance.

The core objects that make up a data provider are:

Class
    

Description

Connection
    

Represents a connection to the database system

Command
    

Represents SQL query or command to be executed at the database management system

DataAdapter
    

A class that connects to the database system, fetch the record and fill the dataset. It contains four different commands to perform database operations; Select, Update, Insert, Delete.

DataReader
    

A stream that reads data from the database in connected design

Parameter
    

Represents a parameter to a stored procedure

What is a dataset?

A dataset is the local repository of the data used to store the tables and disconnected record set. When using disconnected architecture, all the updates are made locally to dataset and then the updates are performed to the database as a batch.
What is a data adapter?

A data adapter is the component that exists between the local repository (dataset) and the physical database. It contains the four different commands (SELECT, INSERT, UPDATE and DELETE). It uses these commands to fetch the data from the DB and fill into the dataset and to perform updates done in the dataset to the physical database. It is the data adapter that is responsible for opening and closing the database connection and communicates with the dataset.
What is a database connection?

A database connection represents a communication channel between you application and database management system (DBMS). The application uses this connection to pass the commands and queries to the database and obtain the results of the operations from the database.
What is a database command?

A database command specifies which particular action you want to perform to the database. The commands are in the form of SQL (Structured Query Language). There are four basic SQL statements that can be passed to the database.
SQL SELECT Statement

This query is used to select certain columns of certain records from a database table.

SELECT * from emp

selects all the fields of all the records from the table name ‘emp’

SELECT empno, ename from emp

selects the fields empno and ename of all records from the table name ‘emp’

SELECT * from emp where empno < 100

selects all those records from the table name ‘emp’ that have the value of the field empno less than 100

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

selects all those records from the table name ‘article’ and ‘author’ that have same value of the field authorId
SQL INSERT Statement

This query is used to insert a record to a database table.

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

inserts a record to emp table and set its empno field to 101 and its ename field to ‘John Guttag’
SQL UPDATE Statement

This query is used to edit an already existing record 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 the existing record(s) from the database table

DELETE FROM emp WHERE empno = 101

deletes the record whose empno field is 101 from the emp table
What is a data reader?

The data reader is a component that reads the data from the database management system and provides it to the application. The data reader works in the connected manner; it reads a record from the DB, pass it to the application, then reads another and so on.
How do different components of ADO.Net interact with each other in disconnected architecture?

The Data Adapter contains in it the Command and Connection object. It uses the connection object to connect to the database, execute the containing command, fetch the result and update the DataSet.

How do different components of ADO.Net interact with each other in connected architecture?

Here, the Command object contains the Connection object. The Command object uses the containing connection (that must be opened) to execute the SQL query and if the SQL statement is SELECT, returns the DataReader object. The data reader object is the stream to the database which reads the resulting records from the DB and passes them to the application

What does it mean by Dot Net Framework Data Provider?

Dot Net Framework Data Provider is a set of classes that establishes the database communication between an application and the database management system based on the standards of ADO.Net framework. Different data providers provide specialized and optimized connectivity to particular database management system or to a particular class of DBMS. For example, the MS SQL Server data provider provides the optimized connectivity between dot net application and MS SQL Server DBMS while the OLEDB data provider provides the uniform connectivity between dot net application and the OLEDB databases.
What are the core objects that make up a dot net framework data provider?

The core objects that make up a data provider are:

Class
    

Description

Connection
    

Represents a connection to the database system

Command
    

Represents SQL query or command to be executed at the database management system

DataAdapter
    

A class that connects to the database system, fetch the record and fill the dataset. It contains four different commands to perform database operations; Select, Update, Insert, Delete.

DataReader
    

A stream that reads data from the database in connected design

Parameter
    

Represents a parameter to a stored procedure

What are the standard dot net framework data providers that are shipped with the Dot Net Framework 1.1?

The Dot Net Framework 1.1 is shipped with four different data providers:

    * Dot Net Framework data provider for Microsoft SQL Server DBMS
    * Dot Net Framework data provider for Oracle DBMS (available only in Framework 1.1)
    * Dot Net Framework data provider for OLEDB supporting DBMS
    * Dot Net Framework data provider for ODBC supporting data sources (available only in Framework 1.1)

Why should one use a specialized data provider when the data can be accessed with general data providers?

The specialized data providers (e.g., SQL Server and Oracle) are built specially for a particular kind of DBMS and works much more efficiently than the general data providers (e.g., OLEDB and ODBC). In practice, the specialized data providers are many times efficient than the general data providers.
What is the Dot Net Framework data provider for SQL Server?

The dot net framework data provider for SQL Server is the optimized data provider for Microsoft SQL Server 7 or later. It is recommended to use SQL Server data provider to access the SQL Server DB than general provider like OLEDB. The classes for this provider are present in the System.Data.SqlClient namespace.
What is the Dot Net Framework data provider for Oracle?

The dot net framework data provider for Oracle is the optimized data provider for Oracle DBMS. It is recommended to use Oracle data provider to access the Oracle DB than general provider like OLEDB. It supports the Oracle Client version 8.1.7 and later. The classes for this provider are present in the System.Data.OracleClient namespace. This provider is included in the .Net framework 1.1 and was not available in the Dot Net framework 1.0.
What is the Dot Net Framework data provider for OLEDB?

The dot net framework data provider for OLEDB provides connectivity with the OLEDB supported database management systems. It is the recommended middle tier for the SQL Server 6.5 or earlier and Microsoft Access Database. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.OleDBClient namespace.
What is the Dot Net Framework data provider for ODBC?

The dot net framework data provider for ODBC provides connectivity with the ODBC supported database management systems and data sources. It is a general data provider. You can also use it to connect with the SQL Server or Oracle Database Management Systems. The classes for this provider are present in the System.Data.ODBCClient namespace. This provider is included in the .Net framework 1.1 and was not available in the Dot Net framework 1.0.
What are the basic steps involved in data access with ADO.Net in disconnected environment?

Data access using ADO.Net involves the following steps:

    * Defining the connection string for the database server
    * Defining the connection (SqlConnection, OleDbConnection, etc) to the database using the connection string
    * Defining the command (SqlCommand, OleDbCommand, etc) or command string that contains the query
    * Defining the data adapter (SqlDataAdapter, OleDbDataAdapter, etc) using the command string and the connection object
    * Creating a new DataSet object
    * If the command is SELECT, filling the dataset object with the result of the query through the data adapter
    * Reading the records from the DataTables in the datasets using the DataRow and DataColumn objects
    * If the command is UPDATE, INSERT or DELETE, then updating the dataset through the data adapter
    * Accepting to save the changes in the dataset to the database

Which namespaces I need to add to my project for each of the standard data provider?

You need to add following namespaces for the specified data providers:

Data Provider
    

Namespace

MS SQL Server
    

System.Data.SqlClient

Oracle Database
    

System.Data.OracleClient

OLE DB Databases
    

System.Data.OleDBClient

ODBC Data Sources
    

System.Data.ODBCClient

How do I define a connection string for the database server?

For MS SQL Server, used with the SQL Server data provider, we can write the connection string like:
C# Version

// for Sql Server

string connectionString = "server=P-III; database=programmersheaven;" +_

"uid=sa; pwd=;";

VB.Net Version

' for Sql Server

Dim connectionString As String = "server=P-III; database=programmersheaven;" + _"uid=sa; pwd=;"

First of all we have defined the instance name of the server, which is P-III on my system. Next we defined the name of the database, user id (uid) and password (pwd). Since my SQL server doesn't have a password for the System Administrator (sa) user, I have left it blank in the connection string. (Yes I know this is very dangerous and is really a bad practice - never, ever use a blank password on a system that is accessible over a network)

For Oracle Database Server, used with the Oracle data provider, we can write the connection string like:
C# Version

string connectionString = "Data Source=Oracle8i;User Id=username;" +

"Password=pwd; Integrated Security=no;";

VB.Net Version

Dim connectionString As String = "Data Source=Oracle8i;User Id=username;" + _

"Password=pwd; Integrated Security=no;"

For MS Access Database, used with the OLE DB data provider, we can write the connection string like:
C# Version

// for MS Access

string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;" +

"data source = c:\\programmersheaven.mdb";

VB.Net Version

' for MS Access

Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" + _

"data source = c:\programmersheaven.mdb"

First we have defined the provider of the access database. Then we have defined the data source which is the address of the target database.

For MS SQL Server, used with the ODBC data provider, we can write the connection string like:
C# Version

string connectionString = "Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;";

VB.Net Version

Dim connectionString As String = "Driver={SQL Server};Server=FARAZ;Database=pubs;Uid=sa;Pwd=;"

How do I find the connection string for the database server?

Usually the connection string options are provided in the documentation, you can also find the connection strings on the internet. A good website for the connections strings is http://www.connectionstrings.com
How do I define the connection to database?

A connection is defined using the connection string. The Connection object is used by the data adapter or data reader to connect to and disconnect from the database. For SQL Server used with SQL Server data provider, the connection is created like this:
C# Version

SqlConnection conn = new SqlConnection(connectionString);

VB.Net Version

Dim conn As New SqlConnection(connectionString)

For Oracle Database Server used with Oracle data provider, the connection is created like this:
C# Version

OracleConnection conn = new OracleConnection(connectionString);

VB.Net Version

Dim conn As New OracleConnection(connectionString)

For OLE DB provider, the connection is created like this:
C# Version

OleDbConnection conn = new OleDbConnection(connectionString);

VB.Net Version

Dim conn As New OleDbConnection(connectionString)

For ODBC data provider, the connection is created like this:
C# Version

OdbcConnection conn = new OdbcConnection(connectionString);

VB.Net Version

Dim conn As New OdbcConnection(connectionString)

Here we have passed the connection string to the constructor of the connection object.
How do I create a command and supply the SQL query to ADO.Net? (Command object and command string)

First of all, you create a command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand) using the connection object (SqlConnection, OracleConnection, OleDbConnection, OdbcConnection) and set its CommandText property to the SQL query to execute.
C# Version

OdbcCommand cmd = conn.CreateCommand();

cmd.CommandText = "select * from authors";

VB.Net Version

Dim cmd As OdbcCommand

cmd = conn.CreateCommand()

cmd.CommandText = "select * from authors"

How do I define a data adapter?

The data adapter stores your command (query) and connection and using these connect to the database when asked, fetch the result of query and store it in the local dataset.

The DataAdapter class (SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter, OdbcDataAdapter) may be instantiated in three ways:

   1. by supplying the command string (SQL Select command) and connection string
   2. by supplying the command string (SQL Select command) and a connection object
   3. by supplying the command object (SqlCommand, OracleCommand, OleDbCommand, OdbcCommand)

For example, with SQL Server, the data adapter is created as
C# Version

// for Sql Server

SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, conn);

VB.Net Version

Dim da As New SqlDataAdapter(commandString, conn)

Here we have created a new instance of data adapter and supplied it command string and connection object in the constructor call.

For Access, the data adapter is created like
C# Version

// for MS Access

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandString, connectionString);

VB.Net Version

Dim da As New OleDbDataAdapter(commandString, connectionString)

Here we have created a new instance of data adapter and supplied it command string and connection string in the constructor call.
How do I get the result of my command and fill it to the dataset?

DataSet is a local and offline container of the data. The DataSet object is created simply like
C# Version

DataSet ds = new DataSet();

VB.Net Version

Dim ds As New DataSet()

Now we need to fill the DataSet with the result of the query. We will use the dataAdapter object for this purpose and call its Fill() method. This is the step where data adapter connects to the physical database and fetch the result of the query.
C# Version

dataAdapter.Fill(ds, "prog");

VB.Net Version

da.Fill(ds, "prog")

Here we have called the Fill() method of dataAdapter object. We have supplied it the dataset to fill and the name of the table (DataTable) in which the result of query is filled.

This is all we needed to connect and fetch the data from the database. Now the result of query is stored in the dataset object in the prog table which is an instance of DataTable. We can get a reference to this table by using the indexer property of dataset object’s Tables collection
C# Version

DataTable dataTable = ds.Tables["prog"];

VB.Net Version

Dim dataTable As DataTable

dt = ds.Tables("prog")

The indexer we have used takes the name of the table in dataset and returns the corresponding DataTable object. Now we can use the tables Rows and Columns collection to access the data in the table.
How do I read records from the data tables?

You can read the records from the data table using its Rows collection. With the Rows collection, you need to specify the row number and column name or number to access a particular field of the specified row. For example, if we have read the ‘Student’ table in our data table, we can access its individual fields as:
C# Version

DataTable dt = ds.Tables["student"];

string stId = dt.Rows[0]["StudentID"].ToString();

string stName = dt.Rows[0]["StudentName"].ToString();

string stDateOfBirth = dt.Rows[0][2].ToString();

VB.Net Version

Dim dt As DataTable

dt = ds.Tables("student")

Dim stId As String

stId = dt.Rows(0)("StudentID").ToString()

Dim stName As String

stName = dt.Rows(0)("StudentName").ToString()

Dim stDateOfBirth As String

stDateOfBirth = dt.Rows(0)(2).ToString()

Here we have retrieved various fields of the first record of the student table read in the dataset. As you can see, we can either specify the column name in string format or we can specify the column number in integer format. Also note that the field value is returned in the form of Object, so we need to convert it to the string before using it. Similarly, you need to cast variables of other data types before using them.
C# Version

int stAge = int.Parse(dt.Rows(0)("Age").ToString());

VB.Net Version

Dim stage As Integer

stAge = Integer.Parse(dt.Rows(0)("Age").ToString())

How do I save the changes, made in the dataset, to the database?

We update the dataset and table by calling the Update method of the data adapter. This saves the changes in the local repository of data: dataset. To save the changed rows and tables to the physical database, we call the AcceptChanges() method of the DataSet class.
C# Version

dataAdapter.Update(ds, "student");

ds.AcceptChanges();

VB.Net Version

da.Update(ds, "student")

ds.AcceptChanges()

Here ‘da’ is the reference to the data adapter object, ‘ds’ is the reference to the dataset, and ‘student’ is the name of table we want to update.

Note: For the next four FAQs, we will demonstrate sample applications. For these applications to work, you need following database and tables created in your database server. A database named ‘ProgrammersHeaven’ is created. It has a table named ‘Article’. The fields of the table ‘Article’ are

Field Name
    

Type
    

Description

artId (Primary Key)
    

Integer
    

The unique identity of article

Title
    

String
    

The title of the article

Topic
    

String
    

Topic or Series name of the article like ‘Multithreading in Java’ or ‘C# School’

authorId (Foreign Key)
    

Integer
    

Unique identity of author

Lines
    

Integer
    

No. of lines in the article

dateOfPublishing
    

Date
    

Date of publishing of the article

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 author

name
    

String
    

Name of author

How do I make my first “Hello, ADO.Net” Application in C#?

Let’s now create a demonstration application for accessing data. First create a windows form application and make the layout like the following snapshot

We have set the Name property of the text boxes (from top to bottom) as txtArticleID, txtArticleTitle, txtArticleTopic, txtAuthorId, txtAuthorName, txtNumOfLines and txtDateOfPublishing. Also we have set the ReadOnly property of all the text boxes to true as don’t want the user of application to change the text. The names of buttons (from top to bottom) are btnLoadTable, btnNext and btnPrevious. Initially we have disabled the Next and Previous buttons (by setting their Enabled property to false).

We have also defined three variables in the Form class as

public class ADOForm : System.Windows.Forms.Form

{

DataTable dataTable;

int currRec=0;

int totalRec=0;

The dataTable object will be used to reference the table returned as a result of the query. The currRec and totalRec integer variables are used to keep track of the current record and total records in the table.
Loading table

For LoadTable button, we have written the following event handler

private void btnLoadTable_Click(object sender, System.EventArgs e)

{

// for Sql Server

string connectionString ="server=P-III; database=programmersheaven;" +

"uid=sa; pwd=;";

// for MS Access

/*string connectionString ="provider=Microsoft.Jet.OLEDB.4.0;" +

"data source = c:\\programmersheaven.mdb";*/

// for Sql Server

SqlConnection conn = new SqlConnection(connectionString);

// for MS Access

//OleDbConnection conn = new OleDbConnection(connectionString);

string commandString = "SELECT " +

"artId, title, topic, " +

"article.authorId as authorId, " +

"name, lines, dateOfPublishing " +

"FROM " +

"article, author " +

"WHERE " +

"author.authorId = article.authorId";

// for Sql Server

SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, conn);

// for MS Access

//OleDbDataAdapter dataAdapter = new OleDbDataAdapter(commandString, conn);

DataSet ds = new DataSet();

dataAdapter.Fill(ds, "prog");

dataTable = ds.Tables["prog"];

currRec = 0;

totalRec = dataTable.Rows.Count;

FillControls();

btnNext.Enabled = true;

btnPrevious.Enabled = true;

}

In the start, we have created the connection, data adapter and filled the dataset object which we have discussed earlier. It should be noted that we have commented the code for OleDb provider (MS-Access) and are using the SQL Server specific code. If you like to use Access database, you can simply comment the SQL server code and de-comment the Access code.

Next we assigned the data table resulted from query to the dataTable object which we declared at class level, assigned zero to currRec variable and assigned the number of rows in the dataTable to totalRec variable

dataTable = ds.Tables["prog"];

currRec = 0;

totalRec = dataTable.Rows.Count;

Then we called the FillControls() method which fills the controls (text boxes) on the form with the current record of table “prog”. Finally we have enabled the Next and Previous Button
Filling the controls on the Form

The FillControls() method in our program fills the controls on the form with the current record of the data table. The method is defined as

private void FillControls()

{

txtArticleId.Text = dataTable.Rows[currRec]["artId"].ToString();

txtArticleTitle.Text = dataTable.Rows[currRec]["title"].ToString();

txtArticleTopic.Text = dataTable.Rows[currRec]["topic"].ToString();

txtAuthorId.Text = dataTable.Rows[currRec]["authorId"].ToString();

txtAuthorName.Text = dataTable.Rows[currRec]["name"].ToString();

txtNumOfLines.Text = dataTable.Rows[currRec]["lines"].ToString();

txtDateOfPublishing.Text = dataTable.Rows[currRec]["dateOfPublishing"].ToString();

}

Here we have set the Text property of the text boxes to the string values of the corresponding fields of current record. We have used the Rows collection of the dataTable and using its indexer we have got the DataRow representing the current record. We then accessed the indexer property of this DataRow using the column name to get the data in the respective field. If this explanation looks weird to you, you can simplify the above statements as

DataRow row = dataTable.Rows[currRec]; // getting current row

object data = row["artId"];// getting data in the artId field

string strData = data.ToString();// converting to string

txtArticleId.Text = strData;// display in the text box

which is equivalent to

txtArticleId.Text = dataTable.Rows[currRec]["artId"].ToString();

Hence when you start the application and press the LoadTable button, you will see the following output

How do I make my first “Hello, ADO.Net” Application in VB.Net?

Let's now create a demonstration application for accessing data. First create a windows form application and make the layout shown the following snapshot.

We have set the Name property of the text boxes (from top to bottom) as txtArticleID, txtArticleTitle, txtArticleTopic, txtAuthorId, txtAuthorName, txtNumOfLines and txtDateOfPublishing. Also we have set the ReadOnly property of all the text boxes to true as don't want the user to change the text. The names of the buttons (from top to bottom) are btnLoadTable, btnNext and btnPrevious. Initially we have disabled the Next and Previous buttons (by setting their Enabled property to false). We have also defined three variables in the Form class:

Public Class ADOForm

Inherits System.Windows.Forms.Form' Private global members to be used in various methods

Private dataTable As dataTable

Private currRec As Integer = 0

Private totalRec As Integer = 0...

The dataTable object will be used to reference the table returned as a result of the query. The currRec and totalRec integer variables are used to keep track of the current record and total number of records in the table. Loading table For the LoadTable button, we have written the following event handler

Private Sub btnLoadTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadTable.Click

' for Sql Server

Dim connectionString As String = "server=P-III; database=programmersheaven;" + _

"uid=sa; pwd=;"

' for MS Access

'Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" +

'"data source = c:\\programmersheaven.mdb"

' for Sql Server

Dim conn As New SqlConnection(connectionString)

' for MS Access

'Dim conn As New OleDbConnection(connectionString)

Dim commandString As String = "SELECT " + _

"artId, title, topic, " + _

"article.authorId as authorId, " + _

"name, lines, dateOfPublishing " + _

"FROM " + _

"article, author " + _

"WHERE " + _

"author.authorId = article.authorId"

' for Sql Server

Dim dataAdapter As New SqlDataAdapter(commandString, conn)

' for MS Access

'Dim dataAdapter As New OleDbDataAdapter(commandString, conn)

Dim ds As New DataSet()

dataAdapter.Fill(ds, "prog")

dataTable = ds.Tables("prog")

currRec = 0

totalRec = dataTable.Rows.Count

FillControls()

btnNext.Enabled = True

btnPrevious.Enabled = True

End Sub

First we created the connection, data adapter and filled the dataset object, all of which we have discussed earlier. It should be noted that we have commented out the code for the OleDb provider (MS-Access) and are using SQL Server specific code. If you would like to use an Access databases, you can simply comment the SQL server code out and de-comment the Access code. Next, we have assigned the data table resulting from the query to the dataTable object which we declared at the class level, assigned zero to currRec variable and assigned the number of rows in the dataTable to the totalRec variable:

dataTable=ds.Tables("prog")currRec=0totalRec=dataTable.Rows.Count

Then we called the FillControls() method, which fills the controls (text boxes) on the form with the current record of the table "prog". Finally we enabled the Next and Previous Buttons.
Filling the controls on the Form

The FillControls() method in our program fills the controls on the form with the current record of the data table. The method is defined as follows:

Private Sub FillControls()

txtArticleId.Text=dataTable.Rows(currRec)("artId").ToString()

txtArticleTitle.Text=dataTable.Rows(currRec)("title").ToString()

txtArticleTopic.Text=dataTable.Rows(currRec)("topic").ToString()

txtAuthorId.Text=dataTable.Rows(currRec)("authorId").ToString()

txtAuthorName.Text=dataTable.Rows(currRec)("name").ToString()

txtNumOfLines.Text=dataTable.Rows(currRec)("lines").ToString()

txtDateOfPublishing.Text=dataTable.Rows(currRec)("dateOfPublishing").ToString()

End Sub

Here we have set the Text property of the text boxes to the string values of the corresponding fields of the current record. We have used the Rows collection of the dataTable and using its indexer we have got the DataRow representing the current record. We have then accessed the indexer property of this DataRow using the column name to get the data in the respective field. If this explanation looks weird to you, you can simplify the above statements to:-

Dim row As DataRow = dataTable.Rows(currRec) ' getting current row

Dim data As Object = row("artId")' getting data in the artId field

Dim strData As String = data.ToString()' converting to string

txtArticleId.Text = strData' display in the text box

which is equivalent to

txtArticleId.Text = dataTable.Rows(currRec)("artId").ToString()

Hence when you start the application and press the LoadTable button, you will see the following output:

How do I navigate through the records?

Navigating through the records is again very easy. For the Next button, we have written the following simple event handler

Private Sub btnNext_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnNext.Click

currRec += 1

If currRec >= totalRec Then

currRec = 0

End If

FillControls()

End Sub

Here we first increment the integer variable currRec and check if it has crossed the last record (using the totalRec variable) in the table. If it has, then we move the current record to the first record. We then call the FillControls() method to display the current record on the form. Similarly the event handler for the Previous button looks like this:

Private Sub btnPrevious_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnPrevious.Click

currRec -= 1

If currRec < totalRec Then

currRec = totalRec - 1

End If

FillControls()

End Sub

Here we decrement the currRec variable and check if has crossed the first record and if it has then we move it to the last record. Once again, we call the FillControls() method to display the current record. Now you can navigate through the records using the Next and Previous buttons.

How do I update tables in the dataset?

Updating a table in ADO.Net is very interesting and easy. You need to follow these steps to update, insert and delete records:

    * The Data Adapter class (SqlDataAdapter) has properties for each of the insert, update and delete commands. First of all we need to prepare the command (SqlCommand) and add it to the data adapter object. The commands are simple SQL commands with parameters. You may use the Visual Studio .Net designer to easily create these commands.
    * Secondly we need to add parameters to these commands. The parameters are simply the names of the data table fields involved in the particular command. Visual Studio .Net also build it for you in its Data Adapter configuration wizard.
    * The two steps described above are done only once in the application. For each insert, update and delete; we insert, update and delete the corresponding data row (DataRow) of the data table (DataTable) object.
    * After any update we call the Update() method of the data adapter class by supplying to it, the dataset and table name as parameters. This updates our local dataset.
    * Finally we call the AcceptChanges() method of the dataset object to store the changes in the dataset to the physical database.

How do I use a CommandBuilder object to prepare the update commands in my dataset?

Each data provider has a command builder object that prepares the update, insert and delete commands for you. You can use these (SqlCommandBuilder, OracleCommandBuilder, OleDbCommandBuilder, OdbcCommandBuilder) objects to generate commands automatically using the Select command you specified when defining the data adapter. In the following code, we have created and set the update, insert and delete commands using the SqlCommandBuilder object
C# Version

SqlConnection conn = new SqlConnection("server=FARAZ; database=programmersheaven; uid=sa; pwd=;");

string cmdStr = "select * from article";

SqlDataAdapter da = new SqlDataAdapter(cmdStr, conn);

DataSet ds = new DataSet();

da.Fill(ds, "Article");

SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da);

da.InsertCommand = cmdBuilder.GetInsertCommand();

da.UpdateCommand = cmdBuilder.GetUpdateCommand();

da.DeleteCommand = cmdBuilder.GetDeleteCommand();

VB.Net Version

Dim conn As New SqlConnection("server=FARAZ; database=programmersheaven; uid=sa; pwd=;")

Dim cmdStr As String

cmdStr = "select * from article"

Dim da As New SqlDataAdapter(cmdStr, conn)

Dim ds As New DataSet

da.Fill(ds, "Article")

Dim cmdBuilder As New SqlCommandBuilder(da)

da.InsertCommand = cmdBuilder.GetInsertCommand()

da.UpdateCommand = cmdBuilder.GetUpdateCommand()

da.DeleteCommand = cmdBuilder.GetDeleteCommand()

What are the general steps for updating the records in dataset?

    * The Data Adapter class (SqlDataAdapter) has properties for each of the insert, update and delete commands. First of all we need to prepare the command (SqlCommand) and add it to the data adapter object.
    * Secondly we need to add parameters to these commands.
    * The two steps described above are done only once in the application. For each insert, update and delete; we insert, update and delete the corresponding data row (DataRow) of the data table (DataTable) object.
    * After any update we call the Update() method of the data adapter class by supplying to it, the dataset and table name as parameters. This updates our local dataset.
    * Finally we call the AcceptChanges() method of the dataset object to store the changes in the dataset to the physical database.

How do I update the dataset with the updates in records?

You can update the dataset by calling the Update() method of the data adapter.
C# Version

DataTable dt = ds.Tables["Article"];

dt.Rows[2]["lines"] = 600;

da.Update(ds, "Article");
VB.Net Version

Dim dt = ds.Tables("Article")

dt.Rows(2)("lines") = 700

da.Update(ds, "Article")

Here ‘da’ and ‘ds’ are the references of the DataAdapter and DataSet objects respectively.
How do I update the physical database with the changes in the dataset?

You can update the physical database by calling the AcceptChanges() method of the data set.
C# Version

DataTable dt = ds.Tables["Article"];

dt.Rows[2]["lines"] = 600;

da.Update(ds, "Article");

ds.AcceptChanges();
VB.Net Version

Dim dt = ds.Tables("Article")

dt.Rows(2)("lines") = 700

da.Update(ds, "Article")

ds.AcceptChanges()

Here ‘da’ and ‘ds’ are the references of the DataAdapter and DataSet objects respectively.
How do I update a record in the table using ADO.Net dataset?

Once you have the UpdateCommand prepared in the data adapter, you can update individual records simply by updating the field values in the data table’s rows. The above code demonstrate how we can update the ‘lines’ field of the third record of the table ‘Article’
C# Version

DataTable dt = ds.Tables["Article"];

dt.Rows[2]["lines"] = 600;

da.Update(ds, "Article");

ds.AcceptChanges();
VB.Net Version

Dim dt = ds.Tables("Article")

dt.Rows(2)("lines") = 700

da.Update(ds, "Article")

ds.AcceptChanges()
How do I insert a record in the table using ADO.Net dataset?

To insert a record in the data table, you create an object of the DataRow using the DataTable object. Then you set the appropriate field values and finally add it to the DataTable’s Rows collection.
C# Version

DataTable dt = ds.Tables["Article"];

// Insert

DataRow dr = dt.NewRow();

dr[0] = 4;

dr[1] = "MFC Programming";

dr[2] = "VC++ MFC Library";

dr[3] = 3;

dr[4] = 3000;

dr[5] = DateTime.Parse("8/14/1999");

dt.Rows.Add(dr);

da.Update(ds, "Article");

ds.AcceptChanges();
VB.Net Version

Dim dt As DataTable

dt = ds.Tables("Article")

' Insert

Dim dr As DataRow

dr = dt.NewRow()

dr(0) = 4

dr(1) = "MFC Programming"

dr(2) = "VC++ MFC Library"

dr(3) = 3

dr(4) = 3000

dr(5) = DateTime.Parse("8/14/1999")

dt.Rows.Add(dr)

da.Update(ds, "Article")

ds.AcceptChanges()