执行SQL命令并将结果输出到C#中的DataGridView [英] Execute SQL command and output result to DataGridView in C#

问题描述

我正在尝试使用C#通过OleDbConnection在Access数据库上执行SQL命令，并使用该信息来填充Windows窗体上的DataGridView.我已经打开了连接，声明了查询并执行了它，但是我找不到如何在Windows窗体(名为dataOutput)上将结果输出到DataGridView的方法.

I am trying to execute an SQL command on an Access Database over an OleDbConnection using C# and use that information to fill a DataGridView on a windows form. I have opened the connection, stated the query, and executed it, but I cannot find how to output the results to the DataGridView on the windows form (named dataOutput).

    private void Query()
    {
        string cmdText = "SELECT * FROM RetentionTable " +
            "WHERE [DateTime] BETWEEN '" + getDateTimeFrom("") + "' AND '" + getDateTimeTo("") + "'";

        string ConnectionPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RetentionDB.mdb";

        try
        {
            OleDbConnection cn = new OleDbConnection(ConnectionPath);
            DataSet objDataSet = new DataSet();
            OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

            if (cn.State.Equals(ConnectionState.Closed))
            {
                cn.Open();
            }

            OleDbCommand OleDbSearch = new OleDbCommand(cmdText, cn);
            OleDbSearch.ExecuteNonQuery();

            objDataAdapter.Fill(objDataSet);
            dataOutput.DataSource = objDataSet;
            cn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message.ToString());
            MessageBox.Show(ex.StackTrace.ToString());
        }

    }

据我所知，查询已正确执行，但是在尝试使用objDataAdapter.Fill时出现了问题.我想我不明白如何用查询的输出填充数据集.任何帮助将非常感激.谢谢！

From what I can see, the query is being executed correctly, but the issue comes when trying to use the objDataAdapter.Fill. I guess I am not understanding how to fill the DataSet with the output from the query. Any help would be much appreciated. Thanks!

推荐答案

一些评论:

1. 您的查询需要进行SQL注入.请改用参数化查询.
2. 您不需要打开/关闭连接； DataAdapter将为您做到这一点.
3. 您应该将OleDbConnection和OleDbCommand对象包装在using块中，以确保清理它们的资源.
4. 您无需在命令上调用ExecuteNonQuery或任何其他Execute...方法；
5. 您需要将命令分配给OleDbDataAdapter的SelectCommand属性，或将其传递给构造函数.

1. Your query is subject to SQL injection. Use a parameterised query instead.
2. You don't need to open/close the connection; the DataAdapter will do that for you.
3. You should wrap the OleDbConnection and OleDbCommand objects in a using block to ensure that their resources are cleaned up.
4. You don't need to call ExecuteNonQuery, or any other Execute... method on the command;
5. You need to assign the command to the SelectCommand property of the OleDbDataAdapter, or pass it to the constructor.

尝试这样的事情:

private void Query()
{
   const string ConnectionPath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=RetentionDB.mdb";

   try
   {
      using (var cn = new OleDbConnection(ConnectionPath))
      using (var cmd = new OleDbCommand("SELECT * FROM RetentionTable WHERE [DateTime] BETWEEN ? And ?"))
      {
         // Parameter names don't matter; OleDb uses positional parameters.
         cmd.Parameters.AddWithValue("@p0", getDateTimeFrom(""));
         cmd.Parameters.AddWithValue("@p1", getDateTimeTo(""));

         var objDataSet = new DataSet();
         var objDataAdapter = new OleDbDataAdapter(cmd);
         objDataAdapter.Fill(objDataSet);

         dataOutput.DataSource = objDataSet;
      }
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message.ToString());
      MessageBox.Show(ex.StackTrace.ToString());
   }
}

这篇关于执行SQL命令并将结果输出到C#中的DataGridView的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！