C＃将datagridview数据写入SQL表 [英] C# write datagridview data into a SQL table

问题描述

我在SQL数据库中有一个四色表。前三个列的信息由另一个来源提供。 coloum 4默认设置为null。

然后，我使用一个可以使用以下代码填充来自sql tabase的信息的数据表的获胜表单：

  public DataTable populateFormList（）
 {
 
 
 SqlConnection con = new SqlConnection（Properties .Settings.Default.sqlConnectionString）; 
 SqlCommand cmd = new SqlCommand（SELECT * FROM of_formlist_raw，con）; 
 con.Open（）; 
 
 SqlDataReader reader = cmd.ExecuteReader（）; 
 
 DataTable dt = new DataTable（）; 
 dt.Load（reader）; 
 
 return dt; 
} 
 
 datagridview2.DataSource = populateFormList（）; 
 datagridview2.Refresh（）; 
  

现在，在获取我的数据时可以正常使用。

然后，用户可以对列4中的空值进行更改。

如何轻松地将这些更改从datatable写回SQL表？ p>

换句话说，一旦屏幕上的datatable具有附加值，那么如何才能将更新的信息存储在最初从其获取的SQL数据中？

谢谢。

解决方案

尝试这样的东西，只需通过（DataTable）datagridview2.DataSource 作为数据表：

  private static void BulkInsertToSQL（DataTable dt，string tableName）
 {
 
 using（SqlConnection con = new SqlConnection（_DB））
 {
 SqlBulkCopy sbc = new SqlBulkCopy（con）; 
 sbc.DestinationTableName = tableName; 
 
 //如果您的DB col名称与您的数据表列名不匹配100％
 //然后将源数据表列名称与目标DB cols 
 sbc相关联.ColumnMappings.Add（DBAttributeName1，DTColumnName1）; 
 sbc.ColumnMappings.Add（DBAttributeName2，DTColumnName2）; 
 sbc.ColumnMappings.Add（DBAttributeName3，DTColumnName3）; 
 sbc.ColumnMappings.Add（DBAttributeName4，DTColumnName4）; 
 
 con.Open（）; 
 
 sbc.WriteToServer（dt）; 
 con.Close（）; 
} 
 
 
} 
  

I have a four coloum table in an SQL database. The info for the first three colums is supplied by another source. coloum 4 is set to null by default.

I then have a win form with a datatable that populates with the information from the sql tabase using the following code:

    public DataTable populateFormList()
    {


            SqlConnection con = new SqlConnection(Properties.Settings.Default.sqlConnectionString);
            SqlCommand cmd = new SqlCommand("SELECT * FROM of_formlist_raw", con);
            con.Open();

            SqlDataReader reader = cmd.ExecuteReader();

            DataTable dt = new DataTable();
            dt.Load(reader);

            return dt;
}

datagridview2.DataSource = populateFormList();
datagridview2.Refresh();

Now that works fine in obtaining my data.

The user can then make changes to the null values in column 4.

How can I easily write these changes from the datatable back into the SQL table?

In other words, once the on screen datatable has aditional values, how can I then store the updated information back in the SQL data from which it was originally obtained from?

Thanks.

解决方案

Try something like this and just pass (DataTable)datagridview2.DataSource as the data table:

private static void BulkInsertToSQL(DataTable dt, string tableName)
        {

                using (SqlConnection con = new SqlConnection(_DB))
                {
                    SqlBulkCopy sbc = new SqlBulkCopy(con);
                    sbc.DestinationTableName = tableName;

                    //if your DB col names don’t match your data table column names 100%
                    //then relate the source data table column names with the destination DB cols
                    sbc.ColumnMappings.Add("DBAttributeName1", "DTColumnName1");
                    sbc.ColumnMappings.Add("DBAttributeName2", "DTColumnName2");
                    sbc.ColumnMappings.Add("DBAttributeName3", "DTColumnName3");
                    sbc.ColumnMappings.Add("DBAttributeName4", "DTColumnName4");

                    con.Open();

                    sbc.WriteToServer(dt);
                    con.Close();
                }


        }

这篇关于C＃将datagridview数据写入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！