Click here to Skip to main content
15,867,453 members
Articles / Web Development / ASP.NET

A Beginner's Tutorial for Understanding ADO.NET

Rate me:
Please Sign up or sign in to vote.
4.79/5 (80 votes)
6 Apr 2012CPOL8 min read 432.8K   10.5K   175   41
A beginner's tutorial for understanding ADO.NET using a simple application.

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:

ADO.NET article image

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:

  1. Connecting to the database
  2. Passing the request to the database, i.e., a command like select, insert, or update.
  3. Getting back the results, i.e., rows and/or the number of rows effected.
  4. Storing the result and displaying it to the user.

This can be visualized as:

ADO.NET article image

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.

XML
<connectionStrings>
  <add name="MyConnectionString" 
     connectionString ="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PUBS.MDF; 
                        Integrated Security=True;User Instance=True" />
</connectionStrings>

Let us see how we can use the SqlConnection class to establish a connection with a database.

C#
private SqlConnection con = null;
con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

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:

  1. There are more objects that can/are used to store results but we will mainly be using these in this article.
  2. 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:

  1. Select Command - This will return a set of rows to the application.
  2. Insert Command - This will return the number of rows inserted.
  3. Delete Command - This will return the number of rows deleted.
  4. 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:

C#
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:

C#
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:

C#
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;   //This will specify that we are passing query from application
string query = "select * from Authors";
cmd.CommandText = query;

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:

C#
SqlCommand cmd = con.CreateCommand();
//This will specify that we are passing query from application
cmd.CommandType = CommandType.Text;
string query = "select * from Authors where authorId = '" + txtSearch.Text + "'";
cmd.CommandText = query;

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:

C#
SqlCommand cmd = con.CreateCommand();
//This will specify that we are passing query from application
cmd.CommandType = CommandType.Text;
string query = "select * from Authors where authorId = @id";
cmd.CommandText = query;

SqlParameter param = new SqlParameter("@id", txtSearch.Text);
cmd.Parameters.Add(param);

Using SqlParameters gives a cleaner, less error prone and SQL injection safe (comparative) code.

Executing the Select Command

Now let us see how we can retrieve the result of a Select command in the form of a DataTable.

C#
public DataTable ExecuteSelectCommand(string CommandName, CommandType cmdType)
{
    SqlCommand cmd = null;
    DataTable table = new DataTable();

    cmd = con.CreateCommand();

    cmd.CommandType = cmdType;
    cmd.CommandText = CommandName;

    try
    {
        con.Open();

        SqlDataAdapter da = null;
        using (da = new SqlDataAdapter(cmd))
        {
            da.Fill(table);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cmd.Dispose();
        cmd = null;
        con.Close();
    }

    return table;
}

public DataTable ExecuteParamerizedSelectCommand(string CommandName, 
                 CommandType cmdType, SqlParameter[] param)
{
    SqlCommand cmd = null;
    DataTable table = new DataTable();

    cmd = con.CreateCommand();

    cmd.CommandType = cmdType;
    cmd.CommandText = CommandName;
    cmd.Parameters.AddRange(param);

    try
    {
        con.Open();

        SqlDataAdapter da = null;
        using (da = new SqlDataAdapter(cmd))
        {
            da.Fill(table);
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cmd.Dispose();
        cmd = null;
        con.Close();
    }

    return table;
}

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

C#
public bool ExecuteNonQuery(string CommandName, CommandType cmdType, SqlParameter[] pars)
{
    SqlCommand cmd = null;
    int res = 0;

    cmd = con.CreateCommand();

    cmd.CommandType = cmdType;
    cmd.CommandText = CommandName;
    cmd.Parameters.AddRange(pars);

    try
    {
        con.Open();

        res = cmd.ExecuteNonQuery();            
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cmd.Dispose();
        cmd = null;
        con.Close();
    }

    if (res >= 1)
    {
        return true;
    }
    return false;
}

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:

ADO.NET article image

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.

History

  • 05 April 2012: First version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
India India

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.

  • Microsoft Certified Technology Specialist (MCTS): Web Applications Development with Microsoft .NET Framework 4
  • Microsoft Certified Technology Specialist (MCTS): Accessing Data with Microsoft .NET Framework 4
  • Microsoft Certified Technology Specialist (MCTS): Windows Communication Foundation Development with Microsoft .NET Framework 4

If you like my articles, please visit my website for more: www.rahulrajatsingh.com[^]

  • Microsoft MVP 2015

Comments and Discussions

 
PraiseThis is a very nice, short and to the point article. Pin
AKSHAY MISHRA24-Apr-17 20:59
professionalAKSHAY MISHRA24-Apr-17 20:59 
GeneralGreate artical Pin
sksaininet20-Jun-16 18:51
sksaininet20-Jun-16 18:51 
PraiseGreat Pin
Member 1249227229-Apr-16 1:59
Member 1249227229-Apr-16 1:59 
QuestionThank you Pin
Member 1176522114-Jun-15 10:00
Member 1176522114-Jun-15 10:00 
QuestionError executing a CREATE FUNCTION Pin
Member 1124494223-Apr-15 14:35
Member 1124494223-Apr-15 14:35 
QuestionDownload zipped code lacks any .sln, .proj files Pin
christopher_kenn19-Apr-15 3:19
christopher_kenn19-Apr-15 3:19 
QuestionAbout .net Pin
Member 1161738817-Apr-15 21:44
Member 1161738817-Apr-15 21:44 
Question.net Pin
Member 1141531230-Jan-15 7:01
Member 1141531230-Jan-15 7:01 
AnswerRe: .net Pin
pc_dev1-Jul-15 5:06
pc_dev1-Jul-15 5:06 
GeneralThank you for this article :) Pin
NavaneethaKrishnan A6-Nov-14 18:27
NavaneethaKrishnan A6-Nov-14 18:27 
QuestionMessage Closed Pin
8-Aug-14 6:17
Developer Hemu Patel8-Aug-14 6:17 
GeneralRe: Common Methods for ADO.NET Class Pin
PIEBALDconsult8-Aug-14 6:24
mvePIEBALDconsult8-Aug-14 6:24 
Questiongetting error Directory Listing -- /DatabaseAccessWithADONET/ Pin
REKA RAMALINGAM13-May-14 9:18
REKA RAMALINGAM13-May-14 9:18 
AnswerRe: getting error Directory Listing -- /DatabaseAccessWithADONET/ Pin
aarif moh shaikh2-Oct-14 2:43
professionalaarif moh shaikh2-Oct-14 2:43 
QuestionGridview Sorting? Pin
Mel Nutter15-Jul-13 21:03
Mel Nutter15-Jul-13 21:03 
GeneralMy vote of 5 Pin
Mel Nutter15-Jul-13 20:35
Mel Nutter15-Jul-13 20:35 
QuestionUpdate database Pin
saeedazam7866-Jan-13 22:08
saeedazam7866-Jan-13 22:08 
GeneralMy vote of 5 Pin
csharpbd21-Nov-12 19:04
professionalcsharpbd21-Nov-12 19:04 
GeneralMy vote of 4 Pin
VikZ917-Nov-12 16:12
VikZ917-Nov-12 16:12 
GeneralMy vote of 5 Pin
sushil Kumar Dhayal27-Sep-12 1:14
sushil Kumar Dhayal27-Sep-12 1:14 
AnswerRe: My vote of 5 Pin
Rahul Rajat Singh27-Sep-12 2:00
professionalRahul Rajat Singh27-Sep-12 2:00 
AnswerArticle of the Day on Microsoft's site Pin
Rahul Rajat Singh20-Sep-12 19:16
professionalRahul Rajat Singh20-Sep-12 19:16 
Questionnot bad Pin
BillW337-May-12 11:05
professionalBillW337-May-12 11:05 
GeneralMy vote of 3 Pin
Jack_32117-Apr-12 11:47
Jack_32117-Apr-12 11:47 
GeneralMy vote of 4 Pin
Dezfoul17-Apr-12 7:05
Dezfoul17-Apr-12 7:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.