将dataGridView中的更改保存到SQL Server DB [英] Save changes from dataGridView to SQL Server DB

问题描述

我有一个带有dataGridView的表单，用于显示表出勤情况

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

...并在单击按钮后将更改保存到SQL Server数据库。

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

我尝试创建UPDATE查询：

I have tried creating UPDATE query:

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

但是我不确定如何将DGV中的值分配给参数。

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

将更改从dataGridView保存到SQL Server数据库的正确方法是什么？

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

请注意，在以下情况下我不使用DataSet或TableAdapter

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

推荐答案

您将要消耗 Row☎联系人 DataGridView 的c>方法，并将必要的信息缓存到 INSERT 数据中：

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

这篇关于将dataGridView中的更改保存到SQL Server DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！