Save changes from dataGridView to SQL Server DB

Question

I have a form with dataGridView to display content of table Attendance

    TagID      SessionID       ScanningTime
    ---------------------------------------
    4820427      Test1       14/08/2013 18:12

I would like to add a record manually to the table to look like this....

    TagID      SessionID       ScanningTime
    ---------------------------------------
    4820000      Test1       14/08/2013 18:12
    0000001      Test2       15/08/2012 17:00

...and save changes to SQL Server database after button has been clicked.

I have tried creating UPDATE query:

command.Text = "UPDATE Attendance
SET TagID= @tagNo, SessionID= @sessionNo, ScanningTime= @scantime";

But I'm not sure how to assign values from DGV to parameters.

What is the correct way to save changes from dataGridView to SQL Server database?

Please note that I do not use DataSet or TableAdapter when working with DGV.

The correct way is open ended question but to answer your question the correct way would be to start writing some code and testing out if what you have written yields the expected results.. show some initiative / effort — MethodMan
@DJKRAZE thanks for your comment. I have updated my question — jaspernorth

Mike Perrenoud Mike Perrenoud · Accepted Answer · 2013-08-15T16:46:27

You're going to want to consume the RowsAdded method of the DataGridView and cache the necessary information to INSERT the data:

// new form field for caching
private List<DataGridViewRow> _addedRowsCache = new List<DataGridViewRow>();

private void dataGridView1_RowsAdded(object sender,
    DataGridViewRowsAddedEventArgs e)
{
    for (int i = e.RowIndex; i < e.RowIndex + e.RowCount; i++)
    {
        _addedRowsCache.Add(dataGridView.Rows[i]);
    }
}

and then when you're ready to submit the data to the database:

// new class field to store the INSERT sql
private string _insertSQL = "INSERT INTO tbl (field1, field2) VALUES (@field1, @field2)";

// this block goes inside the click event
if (_addedRowsCache.Count > 0)
{
    using (SqlConnection c = new SqlConnection(connString))
    {
        c.Open();

        foreach (DataGridViewRow r in _addedRowsCache)
        {
            using (SqlCommand cmd = new SqlCommand(sql, c))
            {
                // add any parameter values, I don't know where `val(n)`
                // comes from here. Maybe from the `DataBoundItem`
                // off the `DataGridViewRow`, or maybe from a `Cell`
                // out of the `Cells` collection of the `DataGridViewRow`
                cmd.Parameters.AddWithValue("@field1", val1);
                cmd.Parameters.AddWithValue("@field2", val2);

                cmd.ExecuteNonQuery();
            }
        }
    }
}

Save changes from dataGridView to SQL Server DB

1 Answers