Using ADO.NET to Develop Data Access Logic (DAL)

Dynamic Web sites usually use one or more data sources like a database or xml files. In ASP.NET 2.0 you can make use of ADO.NET to easily communicate with your data sources. This tutorial will explain you more about the data layer. There are several other tutorials for creating the business and the presentation layer on this site.

3 Tier application

Quite often you hear developers speak about 3 tier (or n tier) applications. This is a technique to develop your application in several layers to keep different things nicely separated. The basic idea of a 3 tier application is to separate your data, business logics and the presentation.

In the data layer you have CRUD (Create, Update, Delete) methods for your tables (or other data source). You might add some minor business logics in combination with those tables (e.g. max, count, ...) and usually a singleton class is used to provide connection to the business layer.

In the business layer you have all the domain classes with their variables, properties and methods. The variables contain the status of your business entity and are hidden to the outer world by using data hiding (private, protected). Public properties provide the data of these variables to client applications while methods implement the business logics.
It's common used to have a Facade in front of your business classes to provide communication with the client application or presentation layer. A facade combines all the methods that can be used from outside in one single class and makes it easier for others to use the business layer since it's not needed to know how everything works behind the facade.

The third layer of a 3 tier application is the presentation layer. This can be any type of user interface, for ASP.NET is this a Web application.

ADO.NET object model

ADO.NET has both connected and disconnected classes to handle data. The connected classes are located in a .NET data provider and are used for communication with a database and can be placed in two groups. At one side you have all the classes to set up a Connection, to handle a Command and to read the returned data (DataReader).
On the other side you have a DataAdapter. This adapter implements all four sql commands (Select, Insert, Update and Delete). With setting the sql code for a select (on a single table), the other three commands are automatically generated. This DataAdapter is quite often used to supply large quantities of data to controls like GridView or connect disconnected classes to a data source.

The disconnected classes can retrieve a DataSet from the connected classes or from an xml file. The DataTable implements a DataRowCollection, DataColumnCollection and a ConstrainCollection and acts like a database table (but then disconnected from the database). All sql commands can be executed on the disconnected classes. For more info about disconnected data, check ADO.NET - Working With Disconnected Data tutorial.

The .NET Framework has multiple data providers, mainly one for each common used type:

• SQL Server Data Provider: optimal access for SQL Server 7.0 and later
  implemented by System.Data.SqlClient namespace

• OLEDB Data Provider: all data sources with OLE DB provider (SQL Server before 7.0, Access, Oracle, ...)
  implemented by System.Data.OleDb namespace

• Oracle Data Provider: optimal access for Oracle 8.1 and later
  implemented by System.Data.OracleClient or ODP.NET (by Oracle)

• ODBC Data Provider: access by ODBC Driver (Excel, MySQL)
  implemented by System.Data.ODBC

• MySQL Data Provider: more info see their site

Setting up a connection with the database

Connecting with a database is done with the Connection class. Every Data Provider has his own version implemented. To setup the connection there is a connection string needed which mostly contains info about the server containing the database, name of the database and authentication data. Making a connection with the database costs loads of time. This is why most providers implement a connection pool. Every time you use Open() with the same connection string, a connection from the pool is returned. After a Close() the connection returns to the pool.
It is possible to place the connection string in the web.config file, but be careful for possible hackers and don't place your username/password in it.

<connectionStrings>
  <add name="Bieren" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|Bieren.mdf;Integrated Security=true;User Instance=True" />  
</connectionStrings>

Do not forget to close database connections yourself since they are not automatically closed when leaving the scope. This is best done in a finally clause to be sure it's always executed, even in case of an exception. Close connections as soon as possible, since they are expensive sources.
If you use the SqlConnection class which implements IDisposable you can open the connection with the using statement. This will close your connection automatically when leaving the scope. But if you plan to spend quite a lot time in the scope after the last sql statement (intensive calculations, ...) it's still suggested to close the connection yourself.

Executing a sql command

Executing a sql command is done with the Command class. This class needs at least his CommandText (query) and his Connection property set to be able to execute the command. This can be done by setting the properties one by one, or by adding both in the constructor. Parameters in the query (or stored procedure) have to be set by using the Parameters property which is a collection.

There are three possible ways to execute a command with the Command class. The first method is ExecuteReader, this method creates a DataReader with the result of the sql command. A DataReader is a forward-only and read-only stream data stream of database records and it's the fastest way to retrieve records. Don't forget to close the DataReader since a onnection object can only have one DataReader open. It's common used to implement 'mapper' methods to map your record to a business logic object. This is the method to use for your select statements .

public List<Brouwer> GetBrouwers()
{
    using (SqlConnection oConn = new SqlConnection(_connectionString)) {
        string strSql = "Select * from brouwers order by naam";
        SqlCommand oCmd = new SqlCommand(strSql, oConn);
        oConn.Open();
        // execute query
        SqlDataReader oReader = oCmd.ExecuteReader();
        List<Brouwer> brouwers = null;
        brouwers = GetBrouwerCollectionFromReader(oReader);
        oReader.Close();
        return brouwers;
    }
}     
 
protected Brouwer GetBrouwerFromReader(IDataRecord oReader)
{
    Brouwer brouwer = new Brouwer();
    brouwer.Brouwernr = (int)oReader["brouwernr"];
    brouwer.Naam = oReader.GetString(1);
    brouwer.Adres = (string)oReader["adres"];
    brouwer.Postcode = (string)oReader["postcode"];
    if( oReader["omzet"] != DBNull.Value)
        brouwer.Omzet = (int)oReader["omzet"];
    return brouwer;
}
 
protected  List<Brouwer> GetBrouwerCollectionFromReader(IDataReader oReader)
{
    List<Brouwer> brouwers = new List<Brouwer>();
    while( oReader.Read() )
        // read line by line and map to an object
        brouwers.Add(GetBrouwerFromReader(oReader));
 
    return brouwers;
}

The second method to execute a sql command is ExecuteNonQuery. This method executes the command and returns the number of rows affected. It should be used for insert, update and delete statements.

The last method is ExecuteScalar. This method is used for select statements that return only one value (max(), count(), ...) and allows you to retrieve the value in one single step. Note that you can also use the ExecuteReader method, but you will have to open and close the DataReader to get the value. Note that you can use ExecuteScalar instead of ExecuteNonQuery to retrieve the primary key from your last insert by adding select SCOPE_IDENTITY() to your command string.

You can add parameters to your sql command at runtime. This is be done with either placing the parameter in the command, or adding it to the Parameters collection property. Use an @ in front of your parameter name to minimalise chances on sql injection. Extra (regular expression) validation and MaxLength on your controls are extra safety checks. You don't want a malevolent person add this to your sql string if you ask a single number: "5; delete * from users".

public int InsertBrouwer(Brouwer brouwer)
{
    using (SqlConnection oConn = new SqlConnection(_connectionString)){
        string sqlString = "Insert into brouwers (naam, adres, postcode,omzet) "
            +"values(@naam,@adres,@postcode,@omzet); select SCOPE_IDENTITY()"; // select last id
        SqlCommand oCmd = new SqlCommand(sqlString, oConn);
        oCmd.Parameters.Add(new SqlParameter("@naam", SqlDbType.NVarChar, 50));
        oCmd.Parameters["@naam"].Value = brouwer.Naam;
        oCmd.Parameters.Add(new SqlParameter("@adres", SqlDbType.NVarChar, 50)).Value = brouwer.Adres;
        oCmd.Parameters.Add(new SqlParameter("@postcode", SqlDbType.SmallInt));
        oCmd.Parameters["@postcode"].Value = Int16.Parse(brouwer.Postcode);
        if (brouwer.Omzet != 0)
            oCmd.Parameters.Add(new SqlParameter("@omzet", brouwer.Omzet));
        else
            oCmd.Parameters.Add(new SqlParameter("@omzet", DBNull.Value));
         oConn.Open();
         int brouwernr = (int)(decimal)oCmd.ExecuteScalar();
         return brouwernr;
    }
}

Concurrency in ADO.NET

There are several ways to handle concurrency (concurrency is when multiple users changing same records). The first way is to use 'Last-in-wins' updating. This is the fastest way but only have to be used if the chances on a collision are very small. Another way to handle concurrency is Timestamp-based updating (optimistic locking). This requires an extra (timestamp) column with the time of the last update. If the timestamp did change since the last time you read this record, your update will fail.
The last method is match-all updating where you check in every single field of the record if it has changed. But this way you also get very inefficient queries.

Setting up a data access logic (DAL) class file

It's a nice habit to make a separate data access logic class for each different business entity. This DAL class will use ADO.NET and sql to access a database. Implementation for data in xml or other data sources are analogue. The DAL class will have to set up a connection, be able to execute commands and map the results to objects for the business layer. Have a look at the code supplied with this tutorial for an implementation of everything your learned in this tutorial. Also note that the business entities (domain classes) aren't necessarily an exact copy of a database table.

Databinding

To conclude this tutorial here's a small code snippet that shows you how to bind the retrieved data to a control. For more info about data binding and the controls, see Introduction To Server Controls tutorial.

if (!Page.IsPostBack)
{
    VullddlBrouwers();
    ddlGemeente.DataSource = BrouwersFacade.GetGemeenten();
    ddlGemeente.DataTextField = "Naam";
    ddlGemeente.DataValueField = "Postcode";
    ddlGemeente.DataBind();
    ddlGemeente.Items.Insert(0, new ListItem("--Select town--", "0"));
    pnlDetail.Visible = false;
}

You can downloadExample Data Access Logic Implementation Project, used in this tutorial.

This tutorial is written by Assesino

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |