Wednesday, November 12, 2008

Introduction – ADO.NET

ADO.NET is a model intended primarily for disconnected data access. ADO.NET provides two strategies for you to work with this model.

1. Store data in a dataset, which is an in-memory cache of records you can work with while disconnected from the data source. To use a dataset, you use a data adapter to fill it from the data source. You then work with the data in the dataset.

2. Perform operations directly against the database. In this strategy, you use a data command object. You can then open a connection, execute the command to perform the operation, and then close the connection.

Each strategy has its own advantages. So you should choose a strategy based on what your data-access requirements are.

Advantages of Storing Data in Datasets

The advantages of the dataset model are:

· Data exchange with other applications - A dataset provides a powerful way to exchange with other components of your application and with other applications.

· Moving data between tiers in a distributed application - By keeping data in a dataset, you can easily move it between the presentation tier, business tier, and data tier of your applications.

· Working with multiple tables - A dataset can contain multiple tables. You can work with the tables individually or navigate between them as parent-child tables.

· Maintaining records for reuse - A dataset allows you to work with the same records repeatedly without re-querying the database.

· Manipulating data from multiple sources - The tables in a dataset can represent data from many different sources. Once the data is in the dataset, you can manipulate it and relate it as if it had come from a single source.

· Data binding - If you are working with forms, it is usually easier to bind controls to data in a dataset than it is to programmatically load data values into the control after executing a command.

· Ease of programming - When you work with a dataset, you can generate a class file that represents its structure as objects. This makes it easier, clearer, and less error-prone to program with, and is supported by Visual Studio tools such as IntelliSense, the Data Adapter Configuration wizard, and so on.

Advantage of performing Database Operations Directly

The advantages of performing database operations directly:

· More control over execution - By using commands, you get more direct control over how and when an SQL statement or stored procedure is executed and what becomes of the results or return values.

· Less overhead - By reading and writing directly in the database, you can bypass storing data in a dataset. Because the dataset requires memory, you can reduce some overhead in your application. This is especially true in situations where you intend to use the data only once. In that case, creating and filling a dataset might be an unnecessary step in displaying the data.

· Extra functionality - There are some operations, such as executing DDL commands, that you can run only by executing data commands.

· Less programming in some instances - In a few instances, particularly Web applications, there is some extra programming required to save the state of a dataset. If you use a data reader to read directly from the database, you avoid the extra steps required to manage the dataset.

Recommendations for Accessing Data

The following sections provide recommendations for which data-access strategy to use with specific types of applications.

Web Forms

Use data commands in general; use a data reader to fetch data. Because Web Forms pages and their controls and components are recreated each time the page makes a round trip, it often is not efficient to create and fill a dataset each time, unless you also intend to cache it between round trips.

Use datasets under the following circumstances:

· You need to perform extensive processing with each record you get from the database.

· You want to work with multiple separate tables or tables from different data sources.

· If your data processing involves interdependent records.

· If you want to perform XML operations such as XSLT transformations on the data.

· You are exchanging data with another application or a component such as an XML Web service.

· If you prefer the ease of programming provided by datasets.

XML Web Services

XML Web services are ASP.NET Web applications, and therefore use the same model as Web Forms pages: the XML Web service is created and discarded each time a call is made to it. This suggests that the data-access model for an XML Web service is largely the same as it is for Web Forms. However, XML Web services are often middle-tier objects, and an important part of their purpose is often to exchange data with other applications across the Web.

Use a dataset if:

· Your XML Web service sends and receives data.

· For any of the reasons listed above for Web Forms.

Use a data command if:

· The XML Web service is performing a non-query operation, such as a DDL command.

· The XML Web service is retrieving a scalar value.

· The XML Web service is calling a stored procedure to execute logic within the database.

Windows Forms

In general, in a Windows Form, use a dataset. Windows Forms are typically used on rich clients where the form is not created and discarded with each user operation, as with Web Forms. Windows Forms applications also traditionally offer data-access scenarios that benefit from maintaining a cache of records, such as displaying records one by one in the form.

Use a dataset if:

· You are using the Windows Forms data-binding architecture, which is specifically designed to work with datasets.

· You are working with the same records repeatedly, such as allowing a user to navigate between records.

· For any of the other reasons listed under Web Forms above.

Use a data command if:

· You are performing a non-query operation, such as a DDL command.

· You are getting a scalar value from the database

· You are getting read-only data to display in a form — for example, creating a report. Stated differently, if there is no need to keep the data available after accessing it, use a data command.

What is is data access architecture for the Microsoft .NET Framework.

Difference between ADO and

· ADO used connected data usage, while used disconnected data environment.

· ADO used OLE DB to access data and is COM-based, while uses XML as the format for transmitting data to and from your database and web application.

· In ADO, Record set, is like a single table or query result, while in Dataset, can contain multiple tables from any data source.

· In ADO, it is sometime problematic because firewall prohibits many types of request, while in there is no such problem because XML is completely firewall-proof

Difference between Dataset and ADO Recordset

· A DataSet can represent an entire relational database in memory, complete with tables, relations, and views.
A DataSet is designed to work without any continuing connection to the original data source.
Data in a DataSet is bulk-loaded, rather than being loaded on demand.
There's no concept of cursor types in a DataSet.
DataSets have no current record pointer You can use For Each loops to move through the data.
You can store many edits in a DataSet, and write them to the original data source in a single operation.
Though the DataSet is universal, other objects in ADO.NET come in different versions for different data sources. Terms

· Data Source: It can be a database, text file, excel spread sheet or an XML file.

· Data Provider: A set of libraries that is used to communicate with data source. Eg: SQL data provider for SQL, Oracle data provider for Oracle, OLE DB data provider for access, excel or mysql.

· SQL Connection: It establishes connection.

· SQL Command: It allows to manipulate database by executing stored procedure or sql statements.

· SQL DataReader: It provides a forward-only, read-only, connected recordset.

· DataSet: dataset is a disconnected, in-memory representation of data. It can contain multiple data table from different database.

· SQL DataAdapter: It populates dataset from data source. It contains a reference to the connection object and opens and closes the connection automatically when reading from or writing to the database.

· DataView: It provides a means to filter and sort data within a data table.

Difference between Dataset and DataReader Object

Points to be consider while choosing between the DataSet and DataReader objects.

DataSet object

DataReader object

Read/Write access

Read-only access

Supports multiple tables from different databases

Supports a single table based on a single SQL query of one database

Disconnected mode

Connected mode

Bind to multiple controls

Bind to a single control

Forward and backward scanning of data

Forward-only scanning of data

Slower access to data

Faster access to data

Greater overhead to enable additional features

Lightweight object with very little overhead

Supported by Visual Studio .NET tools

Must be manually coded

SQLDataReader makes exclusive use of connection

The SQLDataReader object makes exclusive use of its SQLConnection object as long as it is open. You are not able to execute any other SqlCommand objects on that connection as long as the SQLDataReader object is open. Therefore, you should always call SQLDataReader.close() as soon as you are done retrieving data.

Strongly Typed Dataset Object

Strongly typed Dataset object allows you to create early-bound data retrieval expression.

Advantage of Strongly Typed dataset

It is faster than late-bound data retrieval expression.

Its column name is shown in intellisense as you type code.

Improving Performance with Connection Pooling

Opening a connection is a database-intensive task. It can be one of the slowest operations that you perform in an ASP.NET page. Furthermore, a database has a limited supply of connections, and each connection requires a certain amount of memory overhead (approximately 40 kilobytes per connection).

If you plan to have hundreds of users hitting your Web site simultaneously, the process of opening a database connection for each user can have a severe impact on the performance of your Web site.

Fortunately, you can safely ignore these bad warnings if you take advantage of connection pooling. When database connections are pooled, a set of connections is kept open so that they can be shared among multiple users. When you request a new connection, an active connection is removed from the pool. When you close the connection, the connection is placed back in the pool.

Connection pooling is enabled for both OleDb and SqlClient connections by default.To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection

To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.

SQL Injection Problem

SQL injection is a strategy for attacking databases.

An ASP page asks the user for a name and a password, and then sends the following string to the database:
SELECT FROM users WHERE username = 'whatever' AND password = 'mypassword'

It seems safe, but it isn't. A user might enter something like this as her user name:
' OR 1>0 --

When this is plugged into the SQL statement, the result looks like this:
SELECT FROM users WHERE username = '' OR 1>0 -- AND password = ''

This injection comments out the password portion of the statement. It results in a list of all the names in the users table, so any user could get into your system.

The easiest way to prevent this sort of injection is to parse the SQL string and remove any occurrences of "--" before passing the statement.

Example 2:
You also have to beware of injections that contain semicolons because semicolons delimit SQL statements. Think about the implications of a user name like this:
' OR 1>0 ; DELETE Customers ; --

There are numerous ways a malicious user might penetrate your system using SQL injection and various defenses, but the simplest approach is to avoid dynamic SQL. Instead, use stored procedures everywhere. Thanks to the way SQL passes parameters, injections such as those above will produce errors, and the stored procedure will not execute.

Transaction Management in ADO.NET

A Database is a software system that defines a collection of predefined operations. Mainly it includes following operations

· Efficient management of large amount of persistent data in a persistent storage (database)

· Transaction Management which includes Concurrency Control, Atomicity and backup recovery procedure

· A DataModel which gives a separate level of abstraction

In this article I am concentrating of transaction management that is Concurrency Control in .NET environment.

A transaction is an abstract unit of concurrent computation that execute automatically. The effect of transaction does not interfere with other transactions that access the same data. Also a transaction happens with all of its effects (In this case you will commit the changes) or it doesn't happen none of its effects (In this case you will rollback the changes).

In the transaction control we generally define code in between a block where we perform mission critical operation. If all operations get completed successfully then that part is committed in the database otherwise what ever modification you might have done during the process is roll backed from the database so that it never affect other user's operations.

In .NET environment we can define transaction boundary by Transaction object.

If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.

If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.

If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

Let us discuss a simple block of transaction control. In this block I am taking SqlClient Managed Provider

string connectionString = ".........";

SqlConnection myConnection = new SqlConnection(connectionString);


// Start transaction.

SqlTransaction myTransaction = myConnection.BeginTransaction();

// Assign command in the current transaction.

SqlCommand myCommand = new SqlCommand();

myCommand.Transaction = myTransaction;




Database operations



Console.WriteLine("Records are modified in the database.");


catch(Exception e)




Console.WriteLine("Neither record was written to database.");






In Above Block

BeginTransaction method of the Connection object to mark the start of the transaction, which returns a Transaction object.

The newly created transaction object is assigned to CommandObject so that what ever the database operation is performed by that commandObject can be managed by Transaction Object.

If anything gets wrong the Transaction object will raise an Exception otherwise it will run through a normal process.

Call the Commit method of the Transaction object to complete the transaction if everything works fine otherwise call the Rollback method to cancel the transaction.

Concurrency Control

While doing certain modification in the database some time you need to lock the data so that no one can else perform modification in that data. There are two commonly known approaches for locking database they are optimistic locking and pessimistic locking.

Both these approaches are used to maintain concurrency in the database. Pessimistic concurrency locking is done at rows of the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, no one else can perform action until unless owner releases that lock. But this is not case with optimistic currency model. In optimistic concurrency model user does not lock row while reading it, while user only locks the row while updating changes to the database.

In .NET we use DataSet object for modifying changes in the database. The DataSet object uses optimistic concurrency model with the help of DataAdaptor. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are working in distributed environment.

In real time execution DataSet maintains the versions of data that means if anyone modify any data in the DataSet then it get maintain in the dataset as old version and new version. While updating modified data in the database if any of the concurrency conflict occur it raises Exception, which sets DataRow's HasError Boolean value. This we can easily handle with DataAdaptor event and with our own programming logic.

Here I am giving a simple code sample, which explains you how can you manage, concurrency control in .NET environment

string connectionString = ".......................";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter myAdaptor = new SqlDataAdapter("SELECT Name, City FROM Employee ORDER BY EmpID", myConnection);

// Add the RowUpdated event handler.

myAdaptor.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

DataSet supplierData = new DataSet();

myAdaptor.Fill(supplierData, "Supplier");

// Modify the DataSet contents.



myAdaptor.Update(supplierData, "Supplier");

foreach (DataRow myRow in supplierData.Tables["Supplier"].Rows)


if (myRow.HasErrors)

Console.WriteLine(myRow[0] + "\n" + myRow.RowError);


protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)


if (args.RecordsAffected == 0)


args.Row.RowError = "Optimistic Concurrency Violation Encountered";

args.Status = UpdateStatus.SkipCurrentRow;



Explanation of Code:

In this code you have SqlDataAdaptor, which is retrieving supplier record from a database and filling it in a DataSet supplierData. After that you have performed certain modification in that data via DataSet. After modifying data we have used dataAdaptor to update that changes in the database.

So what is new in this code? You might have noticed that in this code we have defined a event handler on dataAdaptor's RowUpdated event. This event will be fired when row is updated in the database and in that event handler mechanism we can define different status to argument so that further action can be take place.

In the main code I have specified code to write all those rows in which error has occurred during modification.

There are different type of status is available for SqlRowUpdatedEventArgs by which you can direct the updating process. Those status are as follows

Status Description

Continue - Continue the update operation.

ErrorsOccurred - Abort the update operation and throw an exception.

SkipCurrentRow - Ignore the current row and continue the update operation.

SkipAllRemainingRows - Abort the update operation but do not throw an exception.

So far we haven't used explicit transaction handling and therefore auto-commit has been enabled. This means that all commands have been executed in their own transaction, and that might not be what we want. Fortunately, transaction handling is quite easy and straightforward in ADO.NET using the OdbcTransaction class.

A transaction is started by calling the OdbcConnection.BeginTransaction(isolationlevel) that returns an instance of OdbcTransaction. The different isolation levels are available in System.Data.IsolationLevel. For example, System.Data.IsolationLevel.Serializable can be used.

All SQL statements that we want to be part of the transaction have to be associated with the transaction object. This can be done by passing the transaction through the constructor of the OdbcCommand or by setting the Transaction property. When we are done with the work we simply call OdbcTransaction.Commit() or OdbcTransaction.Rollback() to commit or roll back the transaction.

OdbcConnection con = new OdbcConnection("...");

//Start the transaction
OdbcTransaction trans =
OdbcCommand command = new OdbcCommand(sql, con, trans);

//An alternative but equivalent approach
OdbcCommand command2= new OdbcCommand(sql2, con);

//Execute and commit
catch(OdbcException oe)
//Show errors
catch(OdbcException oe2)
//Show errors

As you can see, we simply commit when we are done and if an OdbcException is thrown we do a roll back. Note that the trans.RollBack() method can throw an OdbcEvent itself and is therefore surrounded by its own try-catch.


My Blog List