Definition of a Data Access Layer in .NET 3.5

Question

I have the following code that was written by someone else in my web project:

    StringBuilder sql = new StringBuilder("");

    // Define sql
    sql.Append("SELECT title ");
    sql.Append("FROM MyTable ");
    sql.Append(string.Format("WHERE id = {0} AND Var = '{1}'", myId, myVar));

    DataTable dtGroups = SqlHelper.GetDataTable(sql.ToString());

    if (dtGroups.Rows.Count > 0)
    {

        foreach (DataRow dr in dtGroups.Rows)
        {
            return dr["title"].ToString();
        }
    }

    return "";

I then have a helper class called SqlHelper.cs which has this method:

  public static DataTable GetDataTable(string sql) {
        return GetDataTable(sql, MyConnectionString);
    }

Does the SqlHelper class constitute a DAL? What is the proper way of doing things? Should I create a DAL classes that you will send the sql to and just get the title returned (like SqlHelper.GetTitle(sql))?

Pericranium

I would generally put the DAL layer in it's own project. Any code that performs the basic interactions with a data source (creating, reading, updating, deleting) should be put in the DAL layer. That would include all of the code you listed. – Keyhole

The main thing that code constitutes is a huge SQL injection vulnerability. Seriously: use parameters. And DataTable throughout? It is a poorly implemented approach built on a poor concept... – Carman

Answer 1

That code is just bad. SQL injection; DataTable for no reason; StringBuilder for no reason. Here it is done simply, using "dapper" (freely available on NuGet):

using(var conn = GetSomeConnection()) { // <== todo
    return conn.Query<string>(
        "select title from MyTable where id=@id and Var=@var",
        new { id = myId, var = myVar }).FirstOrDefault() ?? "";
}

This is:

• injection safe (fully parameterised)
• direct (no unnecessary layers like DataTable)
• optimised

Carman

yes i am in the process of fixing the parameters too (i forgot to mention ignore that for now). But how do i implement a data access layer, say using dapper (which I need to do as per my requirements) – Pericranium

well i was hoing to learn more myself instead of asking him how he wants it? any best practices and examples would help – Pericranium