C# write datagridview data into a SQL Server table

Question

I have a four column table in a SQL Server database. The info for the first three columns is supplied by another source. Column 4 is set to null by default.

I then have a win form with a datatable that populates with the information from the SQL Server database 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 Server table?

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

Thanks.

What do you means database back into sql table? (close connection, close reader after using it) — Min Min
– Min Min, Commented Apr 20, 2012 at 14:49

Dan · Accepted Answer · 2012-04-20 15:05:30Z

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


        }

LaGrandMere · Accepted Answer · 2012-04-20 15:00:28Z

0

2 options, with or without TableAdapter.

I would recommend to read this in MSDN for TableAdapter

They're using BindingSources too, which are excellent components, easy-to-use.

Without TableAdapter, read this, the "Update Records Using Command Objects" part.

edited Apr 20, 2012 at 15:00

answered Apr 20, 2012 at 14:52

LaGrandMere

10.4k1 gold badge36 silver badges42 bronze badges

1 Comment

tripbrock Over a year ago

The 4th coloum is already there as stated, so I can just used table atapater and it should write the data-table information back to the sql database?

Collectives™ on Stack Overflow

C# write datagridview data into a SQL Server table

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related