ADO.NET FAQ

What is ADO.Net?

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.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.

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 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 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.

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).

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?

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'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.

How to get the count of records in the Database table using the DataSet?

VB.NET
ds.Tables(0).Rows.Count

C#
ds.Tables[0].Rows.Count ;

How to check if the Dataset has records?

VB.NET
if ds.Tables(0).Rows.Count= 0 then
'No record
else
'Record Found
end if

C#
if (ds.Tables[0].Rows.Count == 0 )
{
//No record
}
else
{
//Record Found
}

How to retrieve value of a field in a dataset?

VB.NET
ds.Tables("TableName").Rows(0)("ColumnName")

C#
ds.Tables["TableName"].Rows[0]["ColumnName"];

where TableName and ColumnName could be also integer (not in quotes then) to indicate you refer to the table's or column's index position. Rows(0) indicates the first and only row in DataTable's Rows collection

How to filter the data in the DataView and display it in some DataControl?

VB.NET
Dim thefilter as string = "fieldname='' "
dbDataView.RowFilter = thefilter
Repeater1.DataSource = dbDataView
Repeater.DataBind()

C#
string thefilter = "fieldname='' ";
dbDataView.RowFilter = thefilter;
Repeater1.DataSource = dbDataView;
Repeater.DataBind();

How to truncate the data in the column?

VB.NET
Protected function TruncateData( Byval strNotes as string)
If strNotes.Length > 20 then
Return strNotes.Substring(0,20) + "..."
Else
return strnotes
End function

C#
protected string TruncateData( string strNotes )
{
if (strNotes.Length > 20)
{
return strNotes.Substring(0,20) + "...";
}
else
{
return strNotes;
}
}

How to find the null fields in the datareader?

VB.NET
If dbReader("fieldname").Tostring= DBnull.Value.ToString()
'Empty field value
Else
'Display value
End if

C#
if (dbReader["fieldname").ToString() == DBNull.Value.ToString() )
{
//Empty field value
}
else
{
//display Value
}

How to query the database to get all the Table names?

SELECT * FROM information_schema.tables where Table_type='BASE TABLE'

A field with bit data type value when displayed on a web page shows true/ false how to display a bit value as 1/0?

VB.NET
'Using DataReader
While dr.Read()
Response.Write((dr("ProductName") + " "))
Response.Write((Convert.ToInt16(dr("discontinued")) + " "))
End While

C#
//Using DataReader
while (dr.Read ())
{
Response.Write (dr["ProductName"] + " ");
Response.Write (Convert.ToInt16 ( dr["discontinued"]) + " ");
}

How to get the count of items in a dataReader?

VB.NET

Dim mycn As New SqlConnection("server=localhost;uid=sa;password=;database=northwind;")

Dim mycmd As New SqlCommand("Select * from Products", mycn)
mycn.Open()
Dim dr As SqlDataReader = mycmd.ExecuteReader
Dim i As Integer
While dr.Read
i += 1
End While
Response.Write("Count of Records : " & i)

C#

SqlConnection mycn =new SqlConnection("server=localhost;uid=sa;password=;database=northwind;");

SqlCommand mycmd = new SqlCommand ("Select * from Products", mycn);
mycn.Open();
SqlDataReader dr = mycmd.ExecuteReader();
int i=0;
while(dr.Read())
{
i+=1;
}
Response.Write("Count of Records : " + i.ToString());

How to filter xml data and display data in the DataGrid?

VB.NET
Dim ds As New DataSet
ds.ReadXml(Server.MapPath("data1.xml"))
Dim dv As New DataView
dv = ds.Tables(0).DefaultView
dv.RowFilter = "prodId='product2-00'"
Me.DataGrid1.DataSource = dv
Me.DataBind()

C#
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("data1.xml"));
DataView dv = new DataView();
dv = ds.Tables[0].DefaultView;
dv.RowFilter = "prodId='product2-00'";
this.DataGrid1.DataSource = dv;
this.DataBind();

Why do I get the error message "ExecuteReader requires an open and available Connection. The connection's current state is Closed"?

This error is caused if you have not opened the connection. Before you read the data using DataReader open the Connection

I get the error message "Keyword not supported: 'provider'", when using Sql Server why?

If you are using SqlConnection then the connection string should be as follows:
server=localhost;uid=sa;password=;database=northwind
i.e
server=;uid=;password=;database="

For SqlConnection we do not provide a Provider . Provider is used in cases where OleDbConnection is used.

I get the error message "Cast from type DBNull to type String is not valid." when I try to display DataReader values on form?

VB.NET
If dbReader("fieldname").ToString= DBnull.Value.ToString()
'Empty field value
Else
'Display value
End if

C#
if (dbReader["fieldname").ToString() == DBNull.Value.ToString() )
{
//Empty field value
}
else
{
//display Value
}

What is the significance of CommandBehavior.CloseConnection?

To avoid having to explicitly close the connection associated with the command used to create either a SqlDataReader or and OleDbDataReader, pass the CommandBehavior.CloseConnection argument to the ExecuteReader method of the Connection. i.e

VB.NET
dr= cmd.ExecuteReader(CommandBehavior.CloseConnection)

C#
dr= cmd.ExecuteReader(CommandBehavior.CloseConnection);

The associated connection will be closed automatically when the Close method of the Datareader is called. This makes it all the more important to always remember to call Close on your datareaders.

How to loop through a Dataset to display all records?

VB.NET
'Fill Dataset
Dim dc As DataColumn
Dim dr As DataRow
For Each dr In ds.Tables(0).Rows
For Each dc In ds.Tables(0).Columns
Response.Write(dr(dc.ColumnName).ToString())
Next
Next

C#
//Fill the DataSet
foreach (DataRow dr in ds.Tables[0].Rows)
{
foreach( DataColumn dc in ds.Tables[0].Columns)
{
Response.Write(dr[dc.ColumnName].ToString());
}
}

What is connection pooling?

Connection pooling enables an application to use a connection from a pool of connections that do not need to be re-established for each use. Once a connection has been created and placed in a pool, an application can reuse that connection without performing the complete connection creation process.

When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released.

When is the connection pool created ?

When a connection is opened for the first time a connection pool is created and the pool is determined by the exact match of the connection string in the connection. Each connection pool is associated with a distinct connection string. When a new connection is requested, if the connection string is not an exact match to an existing pool, a new pool is created.

When is the connection pool destroyed ?

When last connection in the pool is closed the pool is destroyed.

What happens when all the connections in the connection pool are consumed and a new connection request comes?

If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when you call Close or Dispose on the Connection.

How can I enable connection pooling ?

For .Net applications it is enabled by default. Well, to make sure the same we can use the Pooling=true; in the connection string for the SQLConnection Object.

How can I disable connection pooling?

ADO.NET Data Providers automatically use connection pooling turned on. If you want to turn this functionality off:

In an SQLConnection object, Add this to the connection string:

Pooling=False;

In An OLEDBConnection object, add this:

OLE DB Services=-4;

This way, the OLE DB data provider will mark your connection so that it does not participate in connection pooling.

What is a stored procedure?

A stored procedure is a precompiled executable object that contains one or more SQL statements. A stored procedure may be written to accept inputs and return output.

posted by Vijay Kumar @ 10/24/2006 11:41:00 PM