用 SQL 查询结果填充数据网格视图

Question

我试图展示查询结果，但是我一直得到一个空数据网格，就像数据本身不可见。

这是我的代码：

 private void Employee_Report_Load(object sender, EventArgs e)
 {
     string select = "SELECT * FROM tblEmployee";
     Connection c = new Connection();
     SqlDataAdapter dataAdapter = new SqlDataAdapter(select, c.con); //c.con is the connection string
     SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

     DataTable table = new DataTable();
     table.Locale = System.Globalization.CultureInfo.InvariantCulture;
     dataAdapter.Fill(table);
     bindingSource1.DataSource = table;

     dataGridView1.ReadOnly = true;        
     dataGridView1.DataSource = bindingSource1;
}

这段代码有什么问题？

- user2023203

---

将数据绑定到 dataGridView。 - Dawood Ahmed

Answer 1

这是你修复后的代码。下一步忘记BindingSource。

 var select = "SELECT * FROM tblEmployee";
 var c = new SqlConnection(yourConnectionString); // Your Connection String here
 var dataAdapter = new SqlDataAdapter(select, c); 

 var commandBuilder = new SqlCommandBuilder(dataAdapter);
 var ds = new DataSet();
 dataAdapter.Fill(ds);
 dataGridView1.ReadOnly = true; 
 dataGridView1.DataSource = ds.Tables[0];

- Don Thomas Boyle

---

完全可以使用“填充”DataTable，不需要使用DataSet。 - banging

它确实可以工作，但是DataSet只是多余的开销，没有任何好处。 - banging

@DonThomasBoyle 在第二个示例代码中，使用的类是 SqlServerCe 而不是 SqlServer。这可能会令人困惑。 - Chris

在我的情况下，就好像数据本身是不可见的。 - user2023203

Answer 2

String strConnection = Properties.Settings.Default.BooksConnectionString;
SqlConnection con = new SqlConnection(strConnection);

SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = con;
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "Select * from titles";
SqlDataAdapter sqlDataAdap = new SqlDataAdapter(sqlCmd);

DataTable dtRecord = new DataTable();
sqlDataAdap.Fill(dtRecord);
dataGridView1.DataSource = dtRecord;

- sayed hashim

Answer 3

你不需要使用 bindingSource1

只需要设置 dataGridView1.DataSource = table;

- banging

---

你在 datagridview 中看到列吗？如果是，那么你的查询没有返回任何行！！ - banging

我在SQL Server 2008中检查了我的查询，它确实返回了员工表的所有数据。 - user2023203

Answer 4

尝试将DataGridView绑定到DataTable的DefaultView：

dataGridView1.DataSource = table.DefaultView;

- Khan

Answer 5

这应该是最安全和最不易出错的查询：

    public void Load_Data()
        {
            using (SqlConnection connection = new SqlConnection(DatabaseServices.connectionString)) //use your connection string here
            {
                var bindingSource = new BindingSource();
                string fetachSlidesRecentSQL = "select top (50) * from dbo.slides order by created_date desc";
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(fetachSlidesRecentSQL, connection))
                {
                    try
                    {
                       SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

                        DataTable table = new DataTable();
                        dataAdapter.Fill(table);
                        bindingSource.DataSource = table;
                        recent_slides_grd_view.ReadOnly = true;
                        recent_slides_grd_view.DataSource = bindingSource;
                    }
                    catch (SqlException ex)
                    {
                       MessageBox.Show(ex.Message.ToString(), "ERROR Loading");
                    }
                    finally
                    {
                        connection.Close();
                    }
                }

            }
        }

- Nickromancer

Answer 6

你需要将属性Tables添加到DataGridView数据源中

 dataGridView1.DataSource = table.Tables[0];

- Mohammed Elrayh

---

已经有被接受的答案，而且提供的信息比你提供的要多得多。这里非常冗余。 - L. Guthardt

Answer 7

您可以尝试此示例，并始终检查您的连接字符串，您可以使用此示例来加载数据到DataGridView中，可以使用或不使用BindingSource。

private void Employee_Report_Load(object sender, EventArgs e)
{
        var table = new DataTable();

        var connection = "ConnectionString";

        using (var con = new SqlConnection { ConnectionString = connection })
        {
            using (var command = new SqlCommand { Connection = con })
            {

                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                con.Open();

                try
                {
                    command.CommandText = @"SELECT * FROM tblEmployee";
                    table.Load(command.ExecuteReader());

                    bindingSource1.DataSource = table;

                    dataGridView1.ReadOnly = true;
                    dataGridView1.DataSource = bindingSource1;

                }
                catch(SqlException ex)
                {
                    MessageBox.Show(ex.Message + " sql query error.");
                }

            }

        }

 }

- Ramgy Borja

Answer 8

多年以后，这里提供最简单的方法，以便其他人参考。

String connectionString = @"Data Source=LOCALHOST;Initial Catalog=DB;Integrated Security=true";

SqlConnection cnn = new SqlConnection(connectionString);
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblEmployee;", cnn);

DataTable data = new DataTable();
sda.Fill(data);

DataGridView1.DataSource = data;

使用DataSet并不是必须的，DataTable就足够了。而SQLCommandBuilder也是不必要的。

- Red Magda

Answer 9

针对Oracle：

        var connString = new ConfigurationBuilder().AddJsonFile("AppSettings.json").Build()["ConnectionString"];
        OracleConnection connection = new OracleConnection();
        connection.ConnectionString = connString;
        connection.Open();          
        var dataAdapter = new OracleDataAdapter("SELECT * FROM TABLE", connection);
        var dataSet = new DataSet();
        dataAdapter.Fill(dataSet);

- ozanmut

Answer 10

我认为从一开始就以专业的方式编写是正确的，但你可以在MySQL中使用这段代码，我认为它们都是相同的：

1/

using System.Data; AND using MySql.Data.MySqlClient;

2/

MySqlConnection con = new MySqlConnection("datasource=172.16.2.104;port=3306;server=localhost;database=DB_Name=root;password=DB_Password;sslmode=none;charset=utf8;");
        MySqlCommand cmd = new MySqlCommand();

3/

public void SetCommand(string SQL)
{
    cmd.Connection = con;
    cmd.CommandText = SQL;

}
private void FillGrid()
{
    SetCommand("SELECT * FROM `transport_db`ORDER BY `id` DESC LIMIT 15");
    DataTable tbl = new DataTable();
    tbl.Load(cmd.ExecuteReader());
    dataGridView1.DataSource = tbl;
}

- Beshoy Nady