创建数据库查询方法

Question

我不确定我是否被迷惑了，但我想做的是创建一个返回查询结果的方法，以便我可以重用连接代码。据我了解，查询返回一个对象，但如何将该对象传递回来？我想将查询作为字符串参数发送到方法中，并让它返回结果，以便我可以使用它们。这就是我所拥有的，这是在黑暗中进行的尝试，它显然不起作用。这个例子是我尝试用查询结果填充列表框；工作表名称是员工，字段/列是名称。我得到的错误是“Complex DataBinding 接受 IList 或 IListSource 作为数据源。”。有什么想法吗？

 public Form1()
        {
            InitializeComponent();
            openFileDialog1.ShowDialog();
            openedFile = openFileDialog1.FileName;

            lbxEmployeeNames.DataSource = Query("Select [name] FROM [Employees$]");


        }

        public object Query(string sql)
        {
            System.Data.OleDb.OleDbConnection MyConnection;
            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
            string connectionPath;

            //build connection string
            connectionPath = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openedFile + "';Extended Properties=Excel 8.0;";

            MyConnection = new System.Data.OleDb.OleDbConnection(connectionPath);
            MyConnection.Open();
            myCommand.Connection = MyConnection;

            myCommand.CommandText = sql;
            return myCommand.ExecuteNonQuery();


        }

Answer 1

在学习与数据库交互时，每个程序员必须做两件基本的事情：关闭连接和参数化查询。这些项目与运行 sql 语句和接收结果的实际过程是分开的，但它们仍然是绝对必要的。出于某种原因，互联网上的大多数教程都只是掩盖了它们，甚至完全错误，也许是因为对于任何有能力编写教程的人来说，这都是第二天性。我的目标是向您展示如何构建整个流程，包括这些额外的基础知识，以一种更容易做到这一点的方式，并且每次都能做到正确。

要做的第一件事是认识到将数据访问代码隐藏在一个方法中是不够的：我们实际上想为此构建一个单独的类（甚至类库）。通过创建一个单独的类，我们可以将实际的连接方法在该类中私有化，以便只有该类中的其他方法可以连接到数据库。这样，我们就设置了一个看门人，强制程序中的所有数据库代码都通过批准的通道运行。对于我上面谈到的两个问题，让守门人代码正确，你的整个程序也将始终正确。所以我们的开始是这样的：

public class DataLayer
{
   private DbConnection GetConnection()
   {
        //This could also be a connection for OleDb, ODBC, Oracle, MySQL, 
        // or whatever kind of database you have.
        //We could also use this place (or the constructor) to load the 
        // connection string from an external source, like a
        // (possibly-encrypted) config file
        return new SqlConnection("connection string here");
   }
}

到目前为止，我们还没有真正解决引言中的任何一个基本问题。到目前为止，我们所做的只是编写代码，以便我们以后能够实施良好的实践。那么让我们开始吧。首先，我们将担心如何强制关闭您的连接。我们通过添加一个方法来运行查询、返回结果并确保完成后关闭连接：

private DataTable Query(string sql)
{
     var result = new DataTable();
     using (var connection = GetConnection())
     using (var command = new SqlCommand(sql, connection)
     {
         connection.Open();
         result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
     }
     return result;
}

您可以添加其他类似的方法来返回标量数据或根本不返回数据（例如更新/插入/删除）。暂时不要太执着于这段代码，因为它仍然是损坏的。我稍后会解释原因。现在，我要指出的是，这个方法仍然是私有的。我们还没有完成，因此我们不希望此代码可供程序的其他部分使用。

我想强调的另一件事是 using 关键字。此关键字是在 .Net 和 C# 中声明变量的有效方法。 using 关键字在变量声明下创建一个作用域块。在作用域块的末尾，您的变量被释放。请注意，其中包括三个重要部分。首先，这实际上只适用于数据库连接等非托管资源；内存仍然以通常的方式收集。第二个是即使抛出异常，变量也会被释放。这使得该关键字适合与时间敏感或严格约束的资源（例如数据库连接）一起使用，而无需在附近使用单独的 try/catch 块。最后一部分是关键字利用 .Net 中的 IDisposable 模式。您现在不需要了解有关 IDisposable 的所有信息：只需知道数据库连接实现（认为：继承）IDisposable 接口，因此可以使用 using 块。

您不必在代码中使用 using 关键字。但如果不这样做，处理连接的正确方法如下所示：

SqlConnection connection;
try
{
   connection = new SqlConnection("connection string here");
   SqlCommand command = new SqlCommand("sql query here", connetion);

   connection.Open();
   SqlDataReader reader = command.ExecuteReader(); 
   //do something with the data reader here
}
finally
{
    connection.Close();
}

即使这仍然是简单的版本。您还需要在finally 块中进行额外检查，以确保您的连接变量有效。 using 关键字是表达这一点的更简洁的方式，它确保您每次都能得到正确的模式。我想在这里展示的是，如果您只是调用 connection.Close()，而没有任何保护来确保程序实际到达该行，那么您就失败了。如果您的 sql 代码在没有 try/finally 或 using 保护的情况下引发异常，您将永远无法到达 .Close() 调用，从而可能使连接保持打开状态。经常这样做，您就可以将自己锁定在数据库之外！

现在让我们构建一些公共的东西：您可以实际从其他代码中使用的东西。正如我之前暗示的那样，您为应用程序编写的每个 sql 查询都将进入它自己的方法。下面是一个简单查询的示例方法，用于从 Employee 表中获取所有记录：

public DataTable GetEmployeeData()
{
    return Query("SELECT * FROM Employees");
}

哇，这很简单...单行函数调用，我们就得到了从数据库返回的数据。我们确实取得了进展。不幸的是，我们仍然缺少一块拼图：你看，想要返回整个表的情况很少见。通常，您需要以某种方式过滤该表，并可能将其与另一个表连接。让我们更改此查询以返回名为“Fred”的虚构员工的所有数据：

public DataTable GetFredsEmployeeData()
{
     return Query("SELECT * FROM Employees WHERE Firstname='Fred'");
}

仍然很简单，但这违背了我们要实现的目标的精神。您不想为每个可能的员工姓名构建另一个方法。你想要更多这样的东西：

public DataTable GetEmployeeData(string FirstName)
{
    return Query("SELECT * FROM Employees WHERE FirstName='" + FirstName + "'");
}

呃哦。现在我们有一个问题。存在令人讨厌的字符串连接，只是等待有人过来并将文本 ';Drop tableEmployees;-- （或更糟）输入到应用程序的 FirstName 字段中。处理这个问题的正确方法是使用查询参数，但这就是棘手的地方，因为前面几段我们构建了一个只接受完成的 sql 字符串的查询方法。

很多人想要编写一个像 Query 方法一样的方法。我认为几乎每个数据库程序员在其职业生涯的某个阶段都会受到这种模式的诱惑，不幸的是，在您添加一种接受 sql 参数数据的方法之前，这完全是错误的。幸运的是，有多种不同的方法可以解决这个问题。最常见的是在方法中添加一个参数，让我们传入要使用的sql数据。为了实现这一点，我们可以传递一个 SqlParameter 对象数组、一个键/值对集合，甚至只是一个对象数组。其中任何一个就足够了，但我认为我们可以做得更好。

我花了很多时间研究不同的选项，并缩小了我认为最简单、最有效、（更重要的是）最准确和可维护的 C# 选项的范围。不幸的是，它确实要求您了解 C# 中一项更高级语言功能的语法：匿名方法/lambda（实际上是：委托，但我很快就会展示 lambda）。此功能允许您在另一个函数中定义一个函数，用变量保留它，将其传递给其他函数，然后在闲暇时调用它。这是一个有用的功能，我将尝试演示。以下是我们如何修改原始 Query() 函数以利用此功能：

private DataTable Query(string sql, Action<SqlParameterCollection> addParameters)
{
    var result = new DataTable();
    using (var connection = GetConnection())
    using (var command = new SqlCommand(sql, connection)
    {
        //addParameters is a function we can call that was as an argument
        addParameters(command.Parameters);

        connection.Open(); 
        result.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
    }
    return result;
}

请注意新的 Action 参数。不要介意 < > 部分。如果您不熟悉泛型，您现在可以假装它是类名称的一部分。重要的是，这种特殊的 Action 类型允许我们将一个函数（在本例中为将 SqlParameterCollection 作为参数的函数）传递给另一个函数。这是从我们的 GetEmployeeData() 函数中使用时的样子：

public DataTable GetEmployeeData(string firstName)
{
    return Query("SELECT * FROM Employees WHERE FirstName= @Firstname", 
    p => 
    {
        p.Add("@FirstName", SqlDbType.VarChar, 50).Value = firstName;
    });
}  

这一切的关键是 Query() 函数现在有一种方法可以将传递给其父 GetEmployeeData() 函数的 firstName 参数连接到sql 字符串中的 @FirstName 表达式。这是使用 ADO.Net 和 SQL 数据库引擎中内置的功能来完成的。最重要的是，它的发生方式可以防止任何 SQL 注入攻击的可能性。同样，这种奇怪的语法并不是发送参数数据的唯一有效方法。您可能会更舒服地发送您迭代的集合。但我确实认为这段代码很好地保持了参数代码靠近查询代码，同时也避免了额外的工作构建，然后迭代（重建）参数数据。

我将（最后！）用两个简短的项目来结束。第一个是不带参数调用新查询方法的语法：

public DataTable GetAllEmployees()
{
    return Query("SELECT * FROM Employees", p => {});
}

虽然我们也可以将其作为原始 Query() 函数的重载提供，但在我自己的代码中我不喜欢这样做，因为我想与其他人进行通信开发人员应该寻求参数化他们的代码，而不是偷偷摸摸地使用字符串连接。

其次，这个答案中概述的代码仍未完成。还有一些重要的弱点需要解决。一个例子是，使用数据表而不是数据读取器会强制您将每个查询的整个结果集一次性加载到内存中。我们可以采取一些措施来避免这种情况。我们还没有讨论插入、更新、删除或更改，也没有解决如何组合复杂的参数情况，例如，我们可能想要添加代码来过滤姓氏，但前提是数据姓氏过滤器实际上可以从用户处获得。虽然这可以很容易地适应所有这些场景，但我认为此时我已经完成了最初的目标，所以我将把它留给读者。

总之，请记住您必须做的两件事：通过finally块关闭连接，并参数化您的查询。希望这篇文章能让您做好这件事。

Answer 2

嘿！试试这个，
如果您只想将所有员工的姓名显示到列表框中，这应该可行。
我刚刚编辑了你的代码中的一些行...

Form1()
{
    InitializeComponent();
    openFileDialog1.ShowDialog();
    openedFile = openFileDialog1.FileName;

    lbxEmployeeNames.DataSource = Query("Select [name] FROM [Employees$]");
    lbxEmployeeNames.DisplayMember = "name"; // The column you want to be displayed in your listBox.
}

// Return a DataTable instead of String.
public DataTable Query(string sql)
{
    System.Data.OleDb.OleDbConnection MyConnection;
    string connectionPath;

    //build connection string
    connectionPath = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + openedFile + "';Extended Properties=Excel 8.0;";

    MyConnection = new System.Data.OleDb.OleDbConnection(connectionPath);
    MyConnection.Open();
    System.Data.OleDb.OleDbDataAdapter myDataAdapter = new System.Data.OleDb.OleDbDataAdapter(sql, MyConnection);
    DataTable dt = new DataTable();
    myDataAdapter.Fill(dt);
    return dt;
}

Answer 3

尝试 ExecuteReader< /a> 相反。它返回一个对象，然后可以像文件一样读取该对象以获得结果：

OleDbDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while(myReader.Read()) 
{
   Console.WriteLine(myReader.GetString(0));
}

Answer 4

您发布的代码的巨大问题是无法正确参数化查询。您必须在调用函数之前进行字符串连接，这会让您容易受到 SQL 注入攻击。您需要在代码中找到一种方法来允许查询参数与 sql 字符串分开。

示例中的其他一些问题包括未正确关闭连接（如果查询引发异常，连接将处于挂起状态）和调用错误的 ADO 方法。

我已经投入了大量的工作来实现这一点，我认为我有一些接近理想模式的东西，可以在我对另一个问题的回答中很好地确定：
SQL Server 插入、更新、选择的最快方法

基本上，当您调用 ADO 函数来实际运行查询时，您会得到一个 DbDataReader.我使用迭代器块将该数据读取器转换为 IEnumerable。与 linq 和其他代码以及 Action配合得很好。鼓励正确的查询参数化。因此，您将连接代码抽象为这样的方法：

private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
    using (var cn = new SqlConnection(ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
                yield return rdr;
            rdr.Close();
        }
    }
}

并在实际查询的代码中使用它，如下所示：

public IEnumerable<IDataRecord> GetSomeDataById(int MyId)
{
    return Retrieve(
        "SELECT * FROM [MyTable] WHERE ID= @MyID", 
       p =>
       {
          p.Add("@MyID", SqlDbType.Int).Value = MyId;
       }
     );
}

请注意，这可以让您正确参数化查询，将始终正确关闭和处置您的连接对象，让您能够在 3 层或服务架构中的每个层之间进行管道传输（使其速度更快），并且以最小的代码开销实现这一点。