如何将SQL查询结果加载到数据网格视图中？

Question

我声明了两个字符串变量：

string fname;
string lname;

当我在phpMyAdmin数据库中编写MySQL查询语句时：

SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN
project1.order_status ON workers.ID_WORKER = order_status.ID_WORKER 
INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER 
WHERE orders.ORDER_NUMBER = 'TEST' GROUP BY workers.FNAME, workers.LNAME

我有2个工人:

- "Adam Gax" 和

"Andrew Worm"

然后我想从这个查询中存储对象，并将数据加载到datagridview：

    string query1 = string.Format("SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " +
    "ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " +
    "WHERE orders.ORDER_NUMBER = '"+ NrOrder +"' GROUP BY workers.FNAME, workers.LNAME");

    SQLdata.connection.Open();
    using (var command = new MySqlCommand(query1, SQLdata.connection))
    {
        using (var reader1 = command.ExecuteReader())
        {
            while (reader1.Read())
            {
                fname = Convert.ToString(reader1[0]);
                lname = Convert.ToString(reader1[1]);
            }
        }
    }

我已经把while循环中的代码行分解成断点，并读取了所有的FNAME和LNAME。然后正确地加载了所有数据。接下来，我想将它们加载到datagridview中。

        SQLdata.connection.Close();
        sick_leaves x = new sick_leaves();
        x.FNAME = fname;
        x.LNAME = lname;
        return x;     

并将它们绑定在一起，就像这样：

        sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

        cu = calculate_sickness_leaves(txt_NrOrder.Text);
        var source = new BindingSource();
        source.DataSource = cu;
        dataGridView2.DataSource = source;

然后使用来自 Orders.cs 文件的数据：

public class sick_leaves
{
    public string FNAME { get; set; }
    public string LNAME { get; set; }
}

在datagridview中编译后，我只加载了一个工人：“Andrew Worm”。这应该是两个工人，所以它没有从sql查询中加载所有数据。

现在：如何将所有数据从sql查询加载到datagridview中？有什么建议吗？警告！我需要桌面应用程序的帮助。

编辑

我想保留代码结构来加载数据，因为我想要使用TimeSpan对象计算病假、请假时间。这样写是否可行？

我的代码：

GenerateOrder.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Windows.Forms.DataVisualization.Charting;
using iTextSharp.text;
using iTextSharp.text.pdf;
using System.IO;
using System.Diagnostics;

namespace ControlDataBase
{
    public partial class GenerateChartsOfOrders : Form
    {
        string fname;
        string lname;
        sick_leaves cu = new sick_leaves();
        public GenerateChartsOfOrders()
        {
            InitializeComponent();
        }        

        public void loaddata2()
        {
            string connect = "datasource=localhost;port=3306;username=root;password=";

            MySqlConnection connection = new MySqlConnection(connect);
            connection.Open();

            sick_leaves a = calculate_sickness_leaves(txt_NrOrder.Text);

            cu = calculate_sickness_leaves(txt_NrOrder.Text);
            var source = new BindingSource();
            source.DataSource = cu;
            dataGridView2.DataSource = source;

            connection.Close();
        }

        private sick_leaves calculate_sickness_leaves(string NrOrder)
        {
            string query1 = string.Format("SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " +
            "ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " +
            "WHERE orders.ORDER_NUMBER = '"+ NrOrder +"' GROUP BY workers.FNAME, workers.LNAME");

            SQLdata.connection.Open();
            using (var command = new MySqlCommand(query1, SQLdata.connection))
            {
                using (var reader1 = command.ExecuteReader())
                {
                    while (reader1.Read())
                    {
                        fname = Convert.ToString(reader1[0]);
                        lname = Convert.ToString(reader1[1]);
                    }
                }
            }

            SQLdata.connection.Close();
            sick_leaves x = new sick_leaves();
            x.FNAME = fname;
            x.LNAME = lname;
            return x;         
        }
    }
}

Orders.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

    namespace ControlDataBase
    {
        public class sick_leaves
        {
            public string FNAME { get; set; }
            public string LNAME { get; set; }
        }
    }

SQLData.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql;
using MySql.Data.MySqlClient;

namespace ControlDataBase
{
    class SQLdata
    {
        public static MySqlConnection connection = new MySqlConnection
        ("datasource=localhost;port=3306;username=root;password=");
    }
}

- Prochu1991

---

你可以在.NET中使用ObjectDataSource，并将其绑定到ASPxGridView。 - Syafiqur__

@Syafiqur__ 我可以在.NET中使用，但我需要在桌面应用程序方面寻求帮助。 - Prochu1991

代码有几个问题：

1. 你已经将 fname 和 lname 定义为表单字段。
2. 在 calculate_sickness_leaves 中，你在 while(reader1.Read()) 中设置了这些字段的值。
3. 最后从 calculate_sickness_leaves 返回一个单独的 sick_leaves 对象。

所以基本上，由于问题1和2，fname 和 lname 将始终包含你的表中最后一行的名和姓。同时，由于问题3，你的 DataGridView 将始终显示单个记录。 - Reza Aghaei

Answer 1

代码存在一些问题：

1. 已经将fname和lname定义为表单字段。
2. 在calculate_sickness_leaves中，您在while(reader1.Read())中设置了这些字段的值。
3. 最后从calculate_sickness_leaves返回单个对象。

因此，由于上述原因，fname和lname将始终包含表格的最后一行的名字和姓氏。

由于第三个问题，DataGridView将始终只显示一条记录。

要解决这个问题：

1. 删除fname和lname，因为它们不需要。
2. 将calculate_sickness_leaves的输出类型更改为IEnumerable<sick_leaves>。
3. 在while循环中，当从数据读取器读取字段值时，请创建sick_leaves的新实例并逐个返回。

注意事项

• 始终使用参数化查询来防止SQL注入。
• 处理可处置对象（例如连接）时，请始终使用using语句。
• 如果您有兴趣使用类型化实体对象，则可以查看MySQL Connector和Entity Framework。

示例

您可以找到很多关于如何将数据加载到DataTable或使用DataReader的示例。无论如何，我在这里分享两个更多的例子，展示您如何从MySql获取数据并转换为特定类型的列表。

在下面的示例中，我假设您有一个这样的Employee类：

public class Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

示例1 - 使用DataAdapter、DataTable和Select扩展方法

public IEnumerable<Employee> GetEmployees()
{
    string connectionString = "CONNECTION STRING";
    string commandText = "COMMAND TEXT";
    DataTable table = new DataTable();
    using (var adapter = new MySqlDataAdapter(commandText , connectionString))
        adapter.Fill(table);
    return table.AsEnumerable().Select(x => new Employee()
    {
        FirstName = x.Field<string>("FirstName"),
        LastName = x.Field<string>("LastName")
    });
}

例子2 - 使用DataReader和yield返回新的Employee

public IEnumerable<Employee> GetEmployees()
{
    string connectionString = "CONNECTION STRING";
    string commandText = "COMMAND TEXT";
    using (var connection = new MySqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new MySqlCommand(commandText, connection))
        {
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return new Employee()
                    {
                        FirstName = reader.GetFieldValue<string>(0),
                        LastName = reader.GetFieldValue<string>(1)
                    };
                }
            }
        }
    }
}

您可以像这样使用上述任一方法：

bindingSource.DataSource = GetEmployees();
dataGridView.DataSource = bindingSource;

- Reza Aghaei

---

@Syafiqur__ 是的，你可以使用 OleDb 对象，例如 OleDbConnection 和 OleDbCommand 从 Excel 表格中获取数据。请参考这篇文章。 - Reza Aghaei

是的。但我总是会收到错误消息 Microsoft.ACE.OLEDB.12.0............，尽管我已经安装了那个oledb。 - Syafiqur__

@Syafiqur__ 请看一下这篇帖子。（很可能是由于位数 x86/x64） - Reza Aghaei

Answer 2

这可能会有所帮助

private void GetData(string selectCommand)
    {
        try
        {
            // Specify a connection string.  
            // Replace <SQL Server> with the SQL Server for your Northwind sample database.
            // Replace "Integrated Security=True" with user login information if necessary.
            String connectionString =
                "Data Source=<SQL Server>;Initial Catalog=Northwind;" +
                "Integrated Security=True";

            // Create a new data adapter based on the specified query.
            dataAdapter = new SqlDataAdapter(selectCommand, connectionString);

            // Create a command builder to generate SQL update, insert, and
            // delete commands based on selectCommand. 
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable
            {
                Locale = CultureInfo.InvariantCulture
            };
            dataAdapter.Fill(table);
            bindingSource1.DataSource = table;

            // Resize the DataGridView columns to fit the newly loaded content.
            dataGridView1.AutoResizeColumns(
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
        }
        catch (SqlException)
        {
            MessageBox.Show("To run this example, replace the value of the " +
                "connectionString variable with a connection string that is " +
                "valid for your system.");
        }
    }

- Syafiqur__

Answer 3

C#数据访问层（仅有1个子程序的简化示例，位于此示例中的DAl.cs文件中）：

Using System.Data.SqlClient;

Public Class DAL
{
    Public Static void GetQueryResults(String cmdText)
    {
        SqlConnection oConn = New SqlConnection();
        oConn.ConnectionString = MainW.MyConnection;  // get connection string
        SqlCommand cmd = New SqlCommand(cmdText, oConn);
        DataSet ds = New DataSet();
        SqlDataAdapter da = New SqlDataAdapter(cmd);

        Try
        {
            oConn.Open();
            da.Fill(ds);       // retrive data
            oConn.Close();
        }
        Catch (Exception ex)
        {
            SysErrScreen errform = New SysErrScreen();
            errform.ChybaText.Text = ex.Message + Constants.vbCrLf + Constants.vbCrLf + cmdText;
            errform.ShowDialog();
            oConn.Close();
        }

        Return ds;
    }
}

在VB.NET中相同的写法：

Imports System.Data.SqlClient

Public Class DAL

Public Shared Function GetQueryResults(cmdText As String)
    Dim oConn As New SqlConnection
    oConn.ConnectionString = MainW.MyConnection  ' get connection string
    Dim cmd As New SqlCommand(cmdText, oConn)
    Dim ds As New DataSet()
    Dim da As New SqlDataAdapter(cmd)

    Try
        oConn.Open()
        da.Fill(ds)       ' retrive data
        oConn.Close()
    Catch ex As Exception
        Dim errform As New SysErrScreen
        errform.ChybaText.Text = ex.Message & vbCrLf & vbCrLf & cmdText
        errform.ShowDialog()
        oConn.Close()
    End Try

    Return ds
End Function

End Class

请注意，我在其他地方定义了一个ConnectionString（MainW.MyConnection），您可以为所有应用程序设置它。通常在启动时从某些设置（文件、应用程序变量）中检索它。

然后，使用它多次就很容易了（C#）：

Private void FillDGV()
{
    String cmdText = "SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " + 
"ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " + 
"WHERE orders.ORDER_NUMBER = '" + NrOrder + "' GROUP BY workers.FNAME, workers.LNAME\"; ";
        DataSet ds;
        ds = DAL.GetQueryResults(cmdText);
        DataTable dt;
        if (ds.Tables.Count > 0)
        {
            dt = ds.Tables(0);
            this.DataGridView1.DataSource = dt;   // fill DataGridView
        }
    }

VB.NET:

Private Sub FillDGV()
    Dim cmdText As String = "SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " +
        "ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " +
        "WHERE orders.ORDER_NUMBER = '" & NrOrder & "' GROUP BY workers.FNAME, workers.LNAME""; "
    Dim ds As DataSet
    ds = DAL.GetQueryResults(cmdText)
    Dim dt As DataTable
    If ds.Tables.Count > 0 Then
        dt = ds.Tables(0)
        Me.DataGridView1.DataSource = dt    ' fill DataGridView
    End If
End Sub

请注意，我在DataSet和DataGridView之间使用了DataTable作为数据对象。养成使用它的习惯是好的（除非使用其他更高级的方法），原因有很多。一个主要原因是，如果DataSet表为空，它不会删除您在DataGridView上定义的GUI列。您还可以在DataTable上更有效、更可靠地执行客户端数据操作，而不是在DataGridView上。

人们还可以考虑是否应该使用BindingSource而不是DataTable。它的实现与DataTable非常相似，因此，如果您使这个示例工作，就可以切换到BindingSource，如果您需要的话。

另一个要考虑的问题是使用参数化查询。如果您（或您的用户）在封闭的环境中运行您的桌面应用程序，则没问题。然而，在公开应用程序中，您可以确保会遭受某些SQL injection攻击。

- Oak_3260548

---

一个DataAdapter会为您打开和关闭连接，因此您不需要这样做。 - LarsTech

@LarsTech 嗯，看起来是个好点。实际上我已经有几年没有关注过代码中的那一部分了，因为它正在一个单独的类中休息... - Oak_3260548

Answer 4

当我从SQL数据库下载数据并在DataGridView中呈现时，这段代码对我有效：

Original Answer翻译成"最初的回答"

            string connectionString;
            String sql = "";
            SqlConnection cnn;

            sql = "SELECT workers.FNAME, workers.LNAME FROM project1.workers INNER JOIN project1.order_status " +
            "ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN project1.orders ON orders.ID_ORDER = order_status.ID_ORDER " +
            "WHERE orders.ORDER_NUMBER = '"+ NrOrder +"' GROUP BY workers.FNAME, workers.LNAME"";
            connectionString = @"datasource=localhost;port=3306;username=root;password=";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            SqlDataAdapter dataadapter = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dataadapter.Fill(ds, "workers");
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "workers";
            cnn.Close();

- TK-421

---

好的，但是你把那段代码放在哪里了？是在 public void loaddata2() 还是在 private sick_leaves calculate_sickness_leaves(string NrOrder) 中？ - Prochu1991

@Prochu1991，你的代码结构非常复杂。如果你只是想从数据库中呈现数据而不对其进行任何修改，那么为什么要有两个函数呢？因为你正在为每个人单独执行此函数，所以每次都会覆盖dataGridView并且只显示最后一个人。只需将我的代码放在一个函数中运行一次，它就会下载所有人并在dataGridView中显示它们。 - TK-421