A Beginner's Tutorial for Understanding ADO.NET

• Download source - 347 KB

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:

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:

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.

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

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:

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:

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:

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:

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.

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

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:

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.