C# 使用 Datagridview 更新数据库

Updating Database Using Datagridview

提问by HOmer

I can add, edit, delete database using listbox. But I want to do it using DatagridView I already bind it to my database.

我可以使用列表框添加、编辑、删除数据库。但是我想使用 DatagridView 来做我已经将它绑定到我的数据库。

How do I add,edit,delete update my database in datagridview using codes?

如何使用代码在 datagridview 中添加、编辑、删除更新我的数据库？

These are my codes:

这些是我的代码：

namespace Icabales.Homer
{
    public partial class Form1 : Form
{
    SqlConnection cn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\users\homer\documents\visual studio 2010\Projects\Icabales.Homer\Icabales.Homer\Database1.mdf;Integrated Security=True;User Instance=True");
    SqlCommand cmd = new SqlCommand();
    SqlDataReader dr;
    SqlDataAdapter da;
    DataTable dt = new DataTable();
    public Form1()
    {
        InitializeComponent();
    }
    private void bindgrid()
    {
        string command = "select * from info";
        da = new SqlDataAdapter(command, cn);
        da.Fill(dt);
        dataGridView1.DataSource = dt;
    }
    private void Form1_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'database1DataSet.info' table. You can move, or remove it, as needed.
        this.infoTableAdapter.Fill(this.database1DataSet.info);
        cmd.Connection = cn;
        loadlist();
        bindgrid();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        if (txtid.Text != "" & txtname.Text != "")
        {
            cn.Open();
            cmd.CommandText = "insert into info (id,name) values ('" + txtid.Text + "' , '" + txtname.Text + "')";
            cmd.ExecuteNonQuery();
            cmd.Clone();
            MessageBox.Show("Record Inserted");
            cn.Close();
            txtid.Text = "";
            txtname.Text = "";
            loadlist();
        }
    }
    private void loadlist()
    {
        listBox1.Items.Clear();
        listBox2.Items.Clear();
        cn.Open();
        cmd.CommandText = "select * from info";
        dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                listBox1.Items.Add(dr[0].ToString());
                listBox2.Items.Add(dr[1].ToString());
            }
        }
        cn.Close();             
    }

    private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
    {
        ListBox l = sender as ListBox;
        if (l.SelectedIndex != -1)
        {
            listBox1.SelectedIndex = l.SelectedIndex;
            listBox2.SelectedIndex = l.SelectedIndex;
            txtid.Text = listBox1.SelectedItem.ToString();
            txtname.Text = listBox2.SelectedItem.ToString();
        }

    }

    private void button2_Click(object sender, EventArgs e)
    {
        if (txtid.Text != "" & txtname.Text != "")
        {
            cn.Open();
            cmd.CommandText = "delete from info where id = '"+txtid.Text+"'and name = '"+txtname.Text+"'";
            cmd.ExecuteNonQuery();
            cn.Close();
            MessageBox.Show("Record Deleted");
            loadlist();
            txtid.Text = "";
            txtname.Text = "";
        }
    }

    private void button3_Click(object sender, EventArgs e)
    {
        if (txtid.Text != "" & txtname.Text != "" & listBox1.SelectedIndex != -1)
        {
            cn.Open();
            cmd.CommandText = "update info set id='"+txtid.Text+"',name='"+txtname.Text+"'where id='"+listBox1.SelectedItem.ToString()+"' and name='"+listBox2.SelectedItem.ToString()+"'";
            cmd.ExecuteNonQuery();
            cn.Close();
            MessageBox.Show("Record Updated");
            loadlist();
            txtid.Text = "";
            txtname.Text = "";
        }
    }
}

}

}

回答by CuccoChaser

I have the same kind of project at home, I do not have the source code with me but if needed I can check somewhere this weekend to see what exactly I have done, but I believe it's some of the following:

我在家里有同样类型的项目，我没有源代码，但如果需要，我可以在本周末的某个地方查看我到底做了什么，但我相信它是以下一些内容：

Since you are using a datasetand a dataadapterthis can be achieved very easily.

由于您使用的是 adataset和 adataadapter这可以很容易地实现。

As long as your DataGridView1has the properties enabled for users to add/delete/edit rows you could use the following code.

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

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

The DataAdapter.Update()Method will auto generate any insert/update/delete commands needed to update the results of your fill query compared with the current data in your datagridview.
You can set those commands (properties) as well in case you prefer to modify them, though this is not necessary.

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

This only works ofcourse after a user has made changes to the DataGridView. Add this code to a simple button and see if you have any luck. It definitly was something this simple :)

这当然只有在用户对DataGridView. 将此代码添加到一个简单的按钮，看看您是否有运气。这绝对是这么简单的事情:)

回答by wamsow

I have a dataGridView and a button on a form. When I do any editing, insertion or deletion in the dataGridView1, the code below does the magic

我在表单上有一个 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);
        }


    }

回答by Sergey

I have implemented a solution that inserts/updates/deletes data in MS SQL database directly from DataGridView. To accomplish this I have used the following events: RowValidating and UserDeletingRow.

我已经实现了一个直接从 DataGridView 在 MS SQL 数据库中插入/更新/删除数据的解决方案。为此，我使用了以下事件：RowValidating 和 UserDeletingRow。

It is supposed that you have

假设你有

SqlConnection _conn;

declared and initialized. Here is the code:

声明并初始化。这是代码：

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

Hope this helps.

希望这可以帮助。