我应该如何构建使用内联 SQL 从数据库生成数据的应用程序

本文关键字：数据库 SQL 应用程序 数据 何构建 构建 我应该 | 更新日期: 2023-09-27 18:37:10

我目前有一个数据访问层，它也使用Web服务公开了一些API。

接口调用

[WebMethod]
public List<GlobalStat> GetStats()
{
    List<GlobalStat> Stats = new List<GlobalStat>();
    string sql = @"
       A huge multi-line SQL query
    ";
    try
    {
        string ConString = Constants.connString;
        con = new SqlConnection(ConString);
        cmd = new SqlCommand(sql, con);
        con.Open();
        dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            GlobalStat stat = new GlobalStat();
            stat.Key = dr[0].ToString();
            stat.Value = int.Parse(dr[1].ToString());
            Stats.Add(stat);
        }
    }
    catch (Exception x)
    {
        Response.Write(x);
    }
    return Stats;
}

我有点担心SQL是如何编写的。

有很多东西硬编码到其中：数据库名称，表名称等。

为了解决这个问题，我只是创建一个单独的全局文件，将所有SQL命令放在一个地方，还是有更好的范例？我不是在应用程序内创建 SQL 表，但这些表驻留在不同的预构建数据库中。

我应该如何构建使用内联 SQL 从数据库生成数据的应用程序？

我应该如何构建使用内联 SQL 从数据库生成数据的应用程序

恕我直言，正确的方法是调用存储过程。在 C# 代码中，只需引用存储过程并传递正确类型的参数。在 C# 代码中构建的即席 SQL 为许多事情打开了大门 - 其中最重要的是 SQL 注入和计划缓存的低效使用。此外，如果不重新编译和重新部署应用程序，就很难重构查询。这对于某些更改（例如，当存储过程的接口更改时）是必需的，但对于许多其他典型的查询更改来说，这应该是必需的。

您提出了几个问题，而您向我们展示的代码又提出了更多问题。 您可能需要考虑的事项：

  • 尝试将以数据库为中心的活动限制在它们自己的类中。 除了处理从数据库中提取数据并将其转换为实际对象的实际类之外，没有其他人需要知道如何返回GlobalStats对象的行列表。没人。 如果其他人知道，那么你的类没有使用信息隐藏（因为我们使用的是面向对象的语言，你应该这样做）。

  • 如果对象实现了IDisposable，那么你应该用try {} finally {}块包装它，或者更好的是，用using语句包装它（参见下面的第二个示例）。

  • 连接字符串应仅供实际需要它的类访问（分离关注点的一部分）。 也许有一个包含该信息的基本 DataAccess 类？ 

    public abstract class DataAccess 
{
    protected const string ConnectionString = "YourConnectionStringHere";
}

然后，您的存储库可以从此类继承，并且您没有导致代码不必要耦合的全局静态常量。

  • 你的代码现在的方式（如果你的所有代码都是你向我们展示的一个例子），那么你可能遇到了SQL注入问题。这值得立即修复（我将在下面向您展示它的示例）。

    1. 使用参数化查询（如果您确实必须使用 C# 中的 SQL）。
    2. 分离您的关注点（从什么时候开始，Web API 调用需要关注数据库？
    3. 不要捕获无法处理的异常。

以下是我如何编写您正在编写的内容（请注意，此代码并不是真正打算使用的，它仅用于说明目的）：

[WebMethod]
public List<GlobalStat> GetStats()
{
    GlobalStatsRepository repository = new GlobalStatsRepository();
    List<GlobalStat> stats = repository.GetStats();
    return stats;
}

数据访问层

public class GlobalStatsRepository
{
    public List<GlobalStat> GetStats()
    {
         string sql = @"SELECT * from GlobalStats"; //no, not a good practice
        var stats = new List<GlobalStat>();
        try
        {
            string ConString = Constants.connString;
            conn = new SqlConnection(ConString);
            cmd = new SqlCommand(sql, conn);
            conn.Open();
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                GlobalStat stat = new GlobalStat();
                stat.Key = dr[0].ToString();
                stat.Value = int.Parse(dr[1].ToString());
                stats.Add(stat);
            }
        }
        catch (SQLDataReaderException ex)
        {
            logger.Log(ex);
            throw;
        }
    return stats;
    }
}

副优点查询示例

public List<GlobalStat> GetStatsById(int id)
{
    var stats = new List<GlobalStat>();
    string sql = @"SELECT * from GlobalStats WHERE Id = @Id";
    using (SqlConnection conn = new SqlConnection(ConString))
    {
         conn.Open();
         using (SQLCommand command = new SqlCommand(sql, conn))
         {         
             command.Parameters.Add(new SqlParameter("Id", id));
             SqlDataReader reader = command.ExecuteReader();
             while (reader.Read())
             {
                  GlobalStat stat = new GlobalStat();
                  stat.Key = dr[0].ToString();
                  stat.Value = int.Parse(dr[1].ToString());
                  stats.Add(stat);
             }
         }
     }
     return stats;
}

存储过程或 LINQ to SQL 是 C# 中两种流行的数据库访问范例。

有关 LINQ to SQL 的更多信息，请查看 Scott Gu 博客上的使用 Linq to SQL。

相关文章：