Updating Database Using Datagridview

问题:

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

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

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 = "";
        }
    }
}

}

回答1:

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

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


    }

回答2:

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.

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.

回答3:

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 dataset and a dataadapter this can be achieved very easily.

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

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.

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 :)