使用Datagridview更新数据库

我有一个dataGridView和窗体上的按钮。当我在dataGridView1中进行任何编辑、插入或删除时，下面的代码会执行以下神奇的

public partial class EditPermit : Form
{
     OleDbCommand command;
     OleDbDataAdapter da;
     private BindingSource bindingSource = null;
     private OleDbCommandBuilder oleCommandBuilder = null;
     DataTable dataTable = new DataTable();
    public EditPermit()
    {
        InitializeComponent();
    }
    private void EditPermitPermit_Load(object sender, EventArgs e)
    {
        DataBind();                           
    }
    private void btnSv_Click(object sender, EventArgs e)
    {
         dataGridView1.EndEdit(); //very important step
         da.Update(dataTable);
         MessageBox.Show("Updated");        
         DataBind(); 
    }
    private void DataBind()
    {
        dataGridView1.DataSource = null;
        dataTable.Clear();
        String connectionString = MainWindow.GetConnectionString(); //use your connection string please
        String queryString1 = "SELECT * FROM TblPermitType"; // Use your table please
        OleDbConnection connection = new OleDbConnection(connectionString);
        connection.Open();
        OleDbCommand command = connection.CreateCommand();
        command.CommandText = queryString1;
        try
        {
            da = new OleDbDataAdapter(queryString1, connection);
            oleCommandBuilder = new OleDbCommandBuilder(da);
            da.Fill(dataTable);
            bindingSource = new BindingSource { DataSource = dataTable }; 
            dataGridView1.DataSource = bindingSource;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

    }

我已经实现了直接从DataGridView插入/更新/删除MS SQL数据库中的数据的解决方案。为了完成这个任务，我使用了以下事件:rowvalidation和UserDeletingRow。

假设你有

SqlConnection _conn;

声明并初始化。下面是代码:

    private void dgv_RowValidating( object sender, DataGridViewCellCancelEventArgs e )
    {
        try
        {
            if (!dgv.IsCurrentRowDirty)
                return;
            string query = GetInsertOrUpdateSql(e);
            if (_conn.State != ConnectionState.Open)
                _conn.Open();
            var cmd = new SqlCommand( query, _conn );
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show( ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning );
        }
    }
    public string GetInsertOrUpdateSql( DataGridViewCellCancelEventArgs e )
    {
        DataGridViewRow row = dgv.Rows[e.RowIndex];
        int id = 0;
        int.TryParse( row.Cells["Id"].Value.ToString(), out id );
        DateTime dob;
        DateTime.TryParse( row.Cells["Dob"].Value.ToString(), out dob );
        string email = row.Cells["Email"].Value.ToString();
        string phone = row.Cells["Phone"].Value.ToString();
        string fio = row.Cells["Fio"].Value.ToString();
        if (id == 0)
            return string.Format( "insert into {0}  Values ('{1}','{2}','{3}','{4}')", "dbo.People", fio, dob.ToString( "dd-MM-yyyy" ), email, phone );
        else
            return string.Format( "update {0} set Fio='{1}', Dob='{2}', Email='{3}', Phone='{4}' WHERE Id={5}", "dbo.People", fio, dob.ToString( "dd-MM-yyyy" ), email, phone, id );
    }
    private void dgv_UserDeletingRow( object sender, DataGridViewRowCancelEventArgs e )
    {
        try
        {
            int id = 0;
            int.TryParse( e.Row.Cells["Id"].Value.ToString(), out id );
            string query = string.Format( "DELETE FROM {0} WHERE Id = {1}", "dbo.People", id );
            var cmd = new SqlCommand( query, _conn );
            if (_conn.State != ConnectionState.Open)
                _conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show( ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning );
        }
    }

我在家里有同样的项目，我没有源代码，但如果需要的话，我可以在这个周末的某个地方检查一下我到底做了什么，但我相信它是以下的一些:

由于您正在使用dataset和dataadapter，这可以很容易地实现。

只要你的DataGridView1有属性启用用户添加/删除/编辑行，你可以使用以下代码。

DataAdapter.Update(DataTable);
//in your code this would be:
da.Update(dt);

DataAdapter.Update()方法将自动生成任何插入/更新/删除命令，以更新与datagridview中当前数据比较的填充查询结果。
如果您喜欢修改这些命令(属性)，也可以设置这些命令(属性)，尽管这不是必需的。

这当然只在用户修改了DataGridView之后才起作用。将此代码添加到一个简单的按钮中，看看是否有任何运气。它肯定是这么简单的东西:)