数据访问定义层中.NET 3.5 [英] Definition of a Data Access Layer in .NET 3.5

问题描述

我有被别人写在我的web项目中的以下code：

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 "";

我再有一个辅助类名为SqlHelper.cs它有这个方法：

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

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

是否把SqlHelper类构成DAL？什么是做事的正确方法？我应该创建你所发送的SQL到DAL类和刚拿到冠军回来（如SqlHelper.GetTitle（SQL））？

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

推荐答案

这code是刚刚的坏的。 SQL注入;数据表没有任何理由; StringBuilder的没有理由。在这里，它只是做了，用短小精悍（免费提供的NuGet）：

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() ?? "";
}

这是：

• 在注射安全（完全参数化）
• 直接（比如数据表没有不必要的层）
• 优化

这篇关于数据访问定义层中.NET 3.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！