Introduction

This article aims at understanding the various concepts and classes available for data access in ADO.NET. This article is meant for absolute beginners and discusses
various techniques of data access using ADO.NET.

Background

ADO.NET is a set of classes that comes with the Microsoft .NET framework to facilitate data access from managed languages. ADO.NET has been in existence for a long time and
it provides a comprehensive and complete set of libraries for data access. The strength of ADO.NET is firstly that it lets applications access
various types of data using the same methodology. If I know how to use ADO.NET to access
a SQL Server database then the same methodology
can be used to access any other type of database (like Oracle or MS Access) by just using
a different set of classes. Secondly, ADO.NET provides
two models for data access: a connected model where I can keep the connection with the database and perform data access, and
another way is to get all the data in ADO.NET objects that let us perform data access on disconnected objects.

Note: Many developers and development houses are now using ORMs to perform data access instead of using ADO.NET. ORMs provide a lot of data access functionality
out of the box and relieves users from writing mundane data access code again and again. Still, I think that knowing and understanding ADO.NET is crucial as a .NET
developer as it gives a better understanding of the data access methodologies. Also, there are many development houses that are still using ADO.NET.

Let us try to visualize ADO.NET data access using the following diagram:

The diagram above shows that ADO.NET can be used with any kind of application, i.e., it can be used from a
Windows Forms application, an ASP.NET application, or
from a WPF and/or Silverlight application. Also, the data store underneath can
be any data store, SQL Server, Access, or Oracle. It is just a matter
of using the right set of classes specific to that data store and the methodology will remain the same.

Using the code

Let us try to understand a few ADO.NET classes and methodologies by writing a small web application. This application uses
a sample database
from Microsoft (subset of the Pubs database) and we will use this database for understanding
the various classes and methods of ADO.NET. We will be using
ADO.NET classes specific to SQL Server but once it is understood, the basic philosophy remains the same and can be applied with any data store.

Before jumping into the code, we will have to understand some of the important objects of ADO.NET. In a typical scenario requiring data access, we need to perform
four major tasks:

Connecting to the database

Passing the request to the database, i.e., a command like select, insert, or update.

Getting back the results, i.e., rows and/or the number of rows effected.

Storing the result and displaying it to the user.

This can be visualized as:

So now we need to understand how we can achieve these functionalities using ADO.NET.

The Connection

The ADO.NET Connection class is used to establish a connection to the database. The Connection class uses a ConnectionString to identify the database server
location, authentication parameters, and other information to connect to the database. This ConnectionString is typically stored in the web.config.

Now we have a connection ready with our database. Whenever we want to retrieve data, we just need to open the connection, perform the operation, and close the connection.

Storing the Result

Before we can jump to understanding how we can execute commands on a database, we first need to understand how we can store the results
and these results can be displayed to the user. To get the hang of how we can store the results, we need to understand
a few ADO.NET objects.

DataReader - A DataReader is an object that can be used to access the results sequentially from
a database. The DataReader is used to get
forward only sequential results as the query executes. This is used with the Command object (we will see the usage shortly).

Dataset - The Dataset can be thought of as an in-memory representation of a database. A
DataSet is a disconnected data access object. The result of the
query can be stored in a Dataset. The DataSet contains
DataTables. The DataTables contain DataRow and
DataColumns. A DataSet or a DataTable can be used with
a Command and a DataAdapter object to store query results.

DataAdapter - A DataAdapter object is used to fill
a DataSet/DataTable with query results. This can be thought
of as the adapter between the
connected and disconnected data models. A Command object will be used to execute the query and a
DataAdapter will use this Command object and fill
the query results coming from the database into a DataSet/DataTable.

Note:

There are more objects that can/are used to store results but we will mainly be using these in this article.

The usage and implentation of these objects are in the next section, as understanding the
Command object is required before that.

The Command

Once we have the connection ready, the next step would be to tell the database about what operation we need to perform on the database.
This can be done using the Command object. We will be using SqlCommand to tell the database about the operation we need to perform.
The typical commands on a database will be:

Select Command - This will return a set of rows to the application.

Insert Command - This will return the number of rows inserted.

Delete Command - This will return the number of rows deleted.

Update Command - This will return the number of rows updated.

Note: We are only talking about data manipulation commands in this article.

All these commands expect SQL syntax. This SQL can either be passed from the application or can be written in
the form of Stored Procedures and executed using a SqlCommand.

Using a Stored Procedure with a Command

If we want to use a Stored Procedure with a Command object then we need to specify it as:

cmd = con.CreateCommand();
// This will specify that we are passing the stored procedures name
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandName; // This will be the stored procedures name

If the Stored Procedure is expecting some parameters then we can pass these parameters by creating instances of SqlParameter objects as:

SqlCommand cmd = con.CreateCommand();
// This will specify that we are passing the stored procedures name
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = CommandName; // This will be the stored procedures name
SqlParameter param = new SqlParameter("@id", txtSearch.Text);
cmd.Parameters.Add(param);

Passing a SQL query from an application using a Command

If we want to pass a SQL query from our application then we can use the SqlCommand as:

There is one important thing to understand here and that is SqlParameters. Many a times we will need to pass parameters in our
SQL query.
This can be done in two ways: we can create a query using string concatenation like:

This is not recommended as this approach is error prone and is vulnerable to SQL
Injection attacks. So whenever we need to pass
parameters to a query the preferred way is using SqlParameters. The same query can be written as:

Executing Update, Delete, and Insert Commands

Commands like insert, update, delete are executed by calling the ExecuteNonQuery method of
SqlCommand. Let us see how we can write a simple
function that will execute these commands. These commands can be used by passing
a query from the application or by invoking Stored Procedures (same as we saw above).

Using the sample application

The first thing to notice in the application is that it contains a class that is responsible for all the ADO.NET logic. The class
DataAccess (file: DataAccess.cs) contains all the ADO.NET classes and methods. All the pages use this class. This class can be reused in any
application with some minor application specific changes. The class diagram for the class is:

The sample application contains four pages:

Authors.aspx

Titles.aspx

AddAuthors.aspx

AddTitles.aspx

The author pages, i.e., Authors.aspx and AddAuthors.aspx, use Stored Procedures to perform the operations whereas the title pages pass all
the queries from the application to the database. We have mainly implemented Select
and Insert commands but Update and Delete can be implemented on the same lines as
Insert.

Some things worth mentioning about the application are:

This should in no way be treated as a design reference for the data access layer.
This is only to demonstrate ADO.NET logic.

The code is written in such a way as to provide a clear understanding from a beginner's perspective, i.e., experienced programmers will find a lot of possible optimizations in the code.

No client side or server side validations have been provided as that was not the scope of this article.

There is no design (architecture wise and look wise) in this application.

Points of interest

Since ADO.NET has been in existence for so many years, many people will think that this article is very late and probably useless. But the idea behind writing
this article is to target those developers who are still in the early stages of their software development. Also,
the presence of ORMs has made ADO.NET obsolete for
many development houses but still knowing how ADO.NET works could be really helpful in improving data access understanding.

Share

About the Author

I Started my Programming career with C++. Later got a chance to develop Windows Form applications using C#. Currently using C#, ASP.NET & ASP.NET MVC to create Information Systems, e-commerce/e-governance Portals and Data driven websites.

My interests involves Programming, Website development and Learning/Teaching subjects related to Computer Science/Information Systems. IMO, C# is the best programming language and I love working with C# and other Microsoft Technologies.

Excellent article, when I use the function command to execute the contents of a script sql I have a problem because it does not recognize the GO instruction, and when I remove the GO function in my script I get an error when I create a function, an error CREATE FUNCTION must be the first statement in a query batch.
This error is common or is incorrect run my sql script with instruction comand?
Or any advice?
Regards

I downloaded the code project file and extracted/unzipped them but there not seem to be any solution or project files with which to open in Visual Studio. There are only .aspx and .aspx.cs, and .cs files in the zip. Can you offer any guide as to the using your code sample?

HiYour article is Excellent.i am new to database.i want to ask a question that how can we Subtract the values in update.For example a stock column contain 20,we give input as 5,then how can we subtract 5 from stock column and then stock column updated to 15.Thanks in advance

Using SQL Parameters is also very efficient because of the cached execution plan saved on the database. When a database receives a query it has to parse the SQL and create an execution plan (which is cached) before running the query. So further uses of this query can re-use the cached execution plan which saves time and resources on the database, but this is only possible with querys using SQL Parameters - as the query text doesn't change, only the parameter values do.e.g."Insert into tblNames (FirstName, Surname) Values (@FirstName, @Surname)"This query could be used hundreds of times, but would only have one cached execution plan.