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

本文关键字：数据网 网格 视图 数据 填充 sql 查询 结果 使用 | 更新日期: 2023-09-27 18:26:57

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

这是我的代码：

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

这个代码出了什么问题？

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

这是您修复的代码。下一个忘记绑定源

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

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;

您不需要bindingSource1

只需设置dataGridView1.DataSource = table;

尝试将DataGridView绑定到DataTable:的DefaultView

dataGridView1.DataSource = table.DefaultView;

这被认为是最安全且容易出错的查询：

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

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

您可以尝试此示例，并始终检查您的连接字符串，您可以将此示例与绑定源一起使用，也可以将数据加载到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.");
                }
            }
        }
 }

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

 dataGridView1.DataSource = table.Tables[0];

如果您使用的是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);
}
}

晚了几年，但这是其他人最简单的情况。

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应该足够好。CCD_ 8也没有必要。

我认为这是一种专业的写作方式，但你可以将这段代码与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;
}

对于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);
相关文章：