Definition of a Data Access Layer in .NET 3.5

问题:

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

回答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