C# 使用 sql 查询结果填充 datagridview

Populate a datagridview with sql query results

提问by user2023203

I'm trying to present query results, but I keep getting a blank data grid. It's like the data itself is not visible

我正在尝试呈现查询结果，但我一直得到一个空白的数据网格。就像数据本身是不可见的

Here is my code:

这是我的代码：

 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;
}

What's wrong with this code?

这段代码有什么问题？

采纳答案by Don Thomas Boyle

Here's your code fixed up. Next forget 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];

回答by banging

You don't need bindingSource1

你不需要 bindingSource1

Just set dataGridView1.DataSource = table;

刚设置 dataGridView1.DataSource = table;

回答by Khan

Try binding your DataGridViewto the DefaultViewof the DataTable:

尝试结合您DataGridView对DefaultView的DataTable：

dataGridView1.DataSource = table.DefaultView;

回答by sayed hashim

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;

回答by Damion Willi

You may get a blank data grid if you set the data Source to a Dataset that you added to the form but is not being used. Set this to None if you are programatically setting your dataSource based on the above codes.

如果将数据源设置为添加到表单但未使用的数据集，则可能会得到一个空白数据网格。如果您根据上述代码以编程方式设置数据源，请将其设置为 None。

回答by Ramgy Borja

You may try this sample, and always check your Connection String, you can use this example with or with out bindingsource you can load the data to datagridview.

您可以尝试此示例，并始终检查您的Connection String，您可以使用此示例使用或不使用 bindingsource 您可以将数据加载到 datagridview。

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.");
                }

            }

        }

 }

回答by Mohammed Elrayh

you have to add the property Tables to the DataGridView Data Source

您必须将属性表添加到 DataGridView 数据源

 dataGridView1.DataSource = table.Tables[0];

回答by Miguel

if you are using mysql this code you can use.

如果您使用的是mysql，则可以使用此代码。

string con = "SERVER=localhost; user id=root; password=; database=databasename";
    private void loaddata()
{
MySqlConnection connect = new MySqlConnection(con);
connect.Open();
try
{
MySqlCommand cmd = connect.CreateCommand();
cmd.CommandText = "SELECT * FROM DATA1";
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
datagrid.DataSource = dt;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}

回答by Daniel Adenew

This is suppose to be the safest and error pron query :

这被认为是最安全和错误的查询：

    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();
                    }
                }

            }
        }