Introduction to ADO.NET

Introduction

This is a set of questions and answers related to ADO.NET. All the code is in Beta 2. The questions by no means cover the entire capabilities of ADO.NET. Specifically, I have tried to answer following questions :

• What is ADO.NET?
• Is ADO.NET different than ADO?
• What are managed providers?
• What are the data access namespaces in .NET?
• What are main objects in ADO.NET ?
• How do I establish connection with a database?
• How do I use Command object to execute action queries?
• How do I use command object to execute Select queries?
• How do I use command object to execute stored procedure?
• What is DataAdapter and Dataset?
• How do i populate a dataset?
• How do I navigate dataset?
• How do I add new record?
• How do I change data ?
• How do I delete a row?
• How do I filter data once arrived in data set?
• How do I handle errors ?
• How do I handle transactions?

What is ADO.NET?

ADO.NET is next generation of data access technology from Microsoft target at .NET platform. ADO.NET is built with distributed and internet applications in mind. ADO.NET provides strong support for XML and disconnected data processing.

Is ADO.NET different than ADO?

ADO.NET is much different than ADO. In order to achieve disconnected data access programmers have to use different techniques like disconnected recordsets, RDS etc. ADO object model is very small as compared to ADO.NET. ADO.NET provides number of specialized objects to handle very specific tasks. Microsoft has taken care to closely map properties and methods of ADO.NET objects with existing ADO counterparts. As per Microsoft ADO.NET is not a replacement for ADO but an enhancement in the overall data access technology. You can use both ADO and ADO.NET in your application.

What are managed providers?

A managed provider is analogous to ODBC driver or OLEDB provider. It performs operation of communicating  with the database. ADO.NET currently provides two distinct managed providers. The SQL Server managed provider is used with SQL server and is a very efficient way of communicating with SQL Server. OLEDB managed provider is used to communicate with any OLEDB compliant database like Access or Oracle. The data access APIs for both the providers are found in separate namespaces.

What are the data access namespaces in .NET?

Following are the most common data access namespaces :

• System.Data
• System.Data.OleDb
• System.Data.SQLClient
• System.Data.SQLTypes
• System.Data.XML

What are main objects in ADO.NET?

Following are the main objects in ADO.NET :

• OleDbConnection / SQLConnection
• OleDbCommand / SQLCommand
• OleDbDataReader / SQLDataReader
• OleDbDataAdapter / SQLDataAdapter
•   OleDbParameter / SQLParameter
• DataSet
• DataTable
• DataView
• DataRow
• DataColumn

Most of the objects mentioned above can be created as 'stand-alone' objects via code. In the discussion below we will see all the examples with OLEDB objects. The usage for SQL server objects is same except name changes. Also, for simplicity I have omitted the repetitive code (like  opening a database connection) in later examples.

How do I establish connection with a database?

The way you connect to a database is very similar to that used in ADO. You use connection object to accomplish this. Consider following code fragment :

Dim cnn as OleDbConnection
cnn=new OleDbConnection("OLEDB_connection_string") 

cnn.open()
'use connection here

cnn.close()

How do I use Command object to execute action queries?

Using command object is similar to that from ADO.

Dim cmd as OleDbCommand
Dim cnn as
OleDbConnection

cnn=new OleDbConnection("OLEDB_connection_string") 

cnn.open()

cmd=

newOleDbCommand("my_action_query",cnn)        

cmd.ExecuteNonQuery()
cmd.CommandText=
"new_query"

How do I use command object to execute Select queries?

You can collect records returned by a command object using DataReader object.

Dim dr as OleDbDataReader
Dim cmd as OleDbCommand
Dim cnn as OleDbConnection

cnn=new OleDbConnection("OLEDB_connection_string") 

cnn.open()

cmd=new OleDbCommand("my_select_query",cnn)

dr=cmd.ExecuteReader()

do while dr.read()

   response.write(dr("field1"))

loop

dr.close()

How do I use command object to execute stored procedure?

You can execute a stored procedure in the same way as any query. Following example shows how to execute parameterize stored procedure.

Dim cmd as OleDbCommand

Dim p as OleDbParameter
Dim cmd as OleDbCommand
Dim cnn as OleDbConnection

cnn=new OleDbConnection("OLEDB_connection_string") 

cnn.open()

cmd=new OleDbCommand("my_stproc_name",cnn)

p=new OleDbParameter("name",type,width)

p.value="myvalue"
cmd.parameters.Add(p)
'add all the parameters in the same way

   cmd.ExecuteNonQuery()

What is DataAdapter and Dataset?

Dataset is an in-memory disconnected representation of data from actual database. Dataset can be thought of as a collection of recordsets. Each such recordset is called as a DataTable. Note that data table can be based on JOIN queries. Dataset is much more powerful than a simple collection of DataTables. You can also put relations and constraints within various datatables of a dataset. DataSet and its constituent parts like DataTable, DataRow can also be created programmatically.

Since dataset is totally disconnected from the database there must be some means of communication between the dataset and the database. DataAdapter is used for that purpose. The main functions performed by DataAdapter are :

• Populate the dataset by fetching data from database
• Updating changes made to the dataset back to the database

Changes made to the dataset are not written to the database unless explicitly updated via DataAdapter.

How do i populate a dataset?

To populate a dataset you will use DataAdapter's Fill method. One DataAdapter can be used to populate several tables of one or many datasets.

Dim dscmd as OleDbDataAdapter

Dim ds as Dataset

dscmd=new OleDbDataAdapter("sql_select_statement",cnn)

ds=new DataSet()
dscmd.Fill(ds,"data_table_name")

How do I navigate a dataset?

Dim row as DataRow

For each row in ds.Tables("data_table_name").Rows

    row.Columns("field1").toString()

Next

How do I add new record to a dataTable?

Dim row as DataRow

row=ds.Tables("recordset_name_in_ds").NewRow()

row("field1")="value1"

row("field1")="value1"

.

.
ds.Tables("recordset_name_in_ds").Rows.Add(row)

dscmd.Update(ds,"data_table_name")

Here, note that you are issuing update on data adapter rather than dataset itself because dataset is actually a disconnected view of data and you must have valid connection information to update changes to original database.

How do I change data from a DataTable?

Dim row as DataRow

'i can be any number in below statement
row=      ds.Tables("data_table_name").Rows(i)

row("field1")="value1"

row("field1")="value1"

dscmd.update(ds,"data_table_name")

How do I delete a row from a DataTable?

ds.Tables("data_table_name").Rows(i).Delete

dscmd.update(ds,"data_table_name")

How do I filter data once arrived in dataTable?

You can filter data based on some conditions using datatables object's Select method.

Dim rows() as DataRow
rows=ds.Tables(0).Select("condition_similar_to_where_clause")

How do I handle errors ?

There can be various ways in which you can handle errors. Using  try..catch is most common. ADO.NET also provides a mechanism by which you can set row level error messages.

Dim cnn as OleDbConnection
Dim cmd as OleDbDataAdapter
Dim ds as Dataset
Dim errors as DataRow() 

       cnn=new OleDbConnection("connection_string") 
cnn.open()
ds=new dataset() 
	cmd=    new
OleDbDataAdapter("select_query",cnn)
cmd.Fill(ds,"data_table_name") 

ds.Tables("data_table_name").Rows(0).RowError =
"Some  error msg" 

ds.Tables("data_table_name").Rows(1).RowError = "Some
other  error msg" 

if ds.tables("users").HasErrors then
	errors = ds.tables("data_table_name").GetErrors()
	' do something with erroraneous rows
end if
 

In above code you can set row level error messages depending on certain business validation. This will allow us to use common error handling routines. Also, note that the RowError property is also set by ADO.NET if there is any real database error.

How do I handle transactions?

ADO.NET offers OleDbTransaction object to control a transaction. This object is returned by BeginTransaction method of OleDbConnection.

Dim tran as OleDbTransaction
Dim cnn as OleDbConnection

cnn=new OleDbConnection("connection_string")
try 

      cnn.open() 
      tran=
  cnn.BeginTransaction() 
      cmd=new OleDbCommand("some_sql",cnn) 

  cmd.Transaction=
 tran 
      cmd.ExecuteNonQuery() 

      cmd.commandtext=
 "some_other_sql" 
      cmd.ExecuteNonQuery() 

      tran.Commit() 

catch myException as Exception 

      tran.Rollback() 

end try