如何使用c#使用datagridview单元格和datagridview事件在数据库中插入，删除，选择记录
本文关键字：datagridview 插入 删除 记录 选择 数据库 事件 何使用 使用 单元格 | 更新日期: 2023-09-27 17:51:08
我设计了一个只有DataGridView控件的windows窗体应用程序。我以编程方式从数据库绑定数据。我还编写了用于从DataGridView单元更新数据库记录的代码。但我不知道如何从这些Cell插入一个新的记录到数据库。你能帮我吗?
这是我到目前为止的代码:
private void Form1_Load(object sender, EventArgs e)
{
try
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd = new SqlCommand("select * from student1", con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
DataSet ds = new DataSet();
da.Fill(ds, "student1");
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
string s;
int x, y;
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
//s=dataGridView1.CurrentCell.ToString();
//x = dataGridView1.CurrentCellAddress.X;
//y = dataGridView1.CurrentCellAddress.Y;
}
private void dataGridView1_CancelRowEdit(object sender, QuestionEventArgs e)
{
x = -1;
y = -1;
}
private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
x = dataGridView1.CurrentCellAddress.X;
y = dataGridView1.CurrentCellAddress.Y;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd;
if (e.ColumnIndex == 0)
{
cmd = new SqlCommand("Select * from student1", con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
MessageBox.Show("invalid column selected");
}
else
{
s = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
int i = -1;
i = (int)Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
if (e.ColumnIndex == 1)
cmd = new SqlCommand("update student1 set name='" + s + "' where id='" + i + "'", con);
else
cmd = new SqlCommand("update student1 set email='" + s + "' where id='" + i + "'", con);
da.UpdateCommand = cmd;
cmd.ExecuteNonQuery();
MessageBox.Show("Information updated Successfully");
}
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
dataGridView1.Refresh();
}
下面是通过数据网格视图向DB添加新记录的一个很好的代码示例。方法略有不同，但应该可以工作:
http://www.codeguru.com/csharp/.net/net_data/datagrid/article.php/c13041/Add-Edit-and-Delete-in-DataGridView.htm这里如何对insert、update和delete的数据使用DataGridView希望能有所帮助
private void dataGridView1_RowValidating(object sender, DataGridViewCellCancelEventArgs e) //or CellValidating Event
{
try
{
if (dataGridView1.IsCurrentRowDirty) //use IsCurrentCellDirty if you choose CellValidating Event
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd;
string studentId = dataGridView1[0, e.RowIndex].EditedFormattedValue.ToString();
string lastname = dataGridView1[1, e.RowIndex].EditedFormattedValue.ToString();
string firstname = dataGridView1[2, e.RowIndex].EditedFormattedValue.ToString();
string myQry;
//I use int.Parse to Convert the string to int,
if (int.Parse(studentId) == 0) //Get Primary key (hint null, 0, or -1 value to insert the data)
{
//Insert
myQry = @"insert intro student1 (lastname, firstname) Values ('" + lastname + "', '" + firstname + "')";
}
else
{
//Update
myQry = @"update student1 set lastname = '" + lastname + "', firstname = '" + firstname + "'" + " Where studentId = '" + studentId + "'";
}
cmd = new SqlCommand(myQry, con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
private void dataGridView1_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Delete)
{
if (dataGridView1.SelectedRows.Count > 0)
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd;
foreach (var item in dataGridView1.Rows.Cast<DataGridViewRow>().Where(c => c.IsNewRow == false))
{
string delQry = @"delete from student1 where studentId = '" + item.Cells[0].EditedFormattedValue.ToString() + "'";
cmd = new SqlCommand(delQry, con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
}
con.Close();
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace datagridview
{
public partial class Form1 : Form
{
SqlConnection con = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();
public Form1()
{
InitializeComponent();
}
private void btnshow_Click(object sender, EventArgs e)
{
try
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd = new SqlCommand("select * from student1", con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
DataSet ds = new DataSet();
da.Fill(ds, "student1");
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
private void Form1_Load(object sender, EventArgs e)
{
try
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd = new SqlCommand("select * from student1", con);
da.SelectCommand = cmd;
cmd.ExecuteNonQuery();
DataSet ds = new DataSet();
da.Fill(ds, "student1");
dataGridView1.DataSource = ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
string s,s1,s2;
int x, y;
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
}
private void dataGridView1_CancelRowEdit(object sender, QuestionEventArgs e)
{
x = -1;
y = -1;
}
private void dataGridView1_CellBeginEdit(object sender, DataGridViewCellCancelEventArgs e)
{
x = dataGridView1.CurrentCellAddress.X;
y = dataGridView1.CurrentCellAddress.Y;
}
private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
try
{
con.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con.Open();
SqlCommand cmd;
int i = -1,j;
if (e.ColumnIndex == 0)
{
j = (int)Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
s1 = "";
s2 = "";
cmd = new SqlCommand("insert into student1 values('" +j+ "','" + s1 + "','" + s2 + "')", con);
da.InsertCommand = cmd;
cmd.ExecuteNonQuery();
MessageBox.Show("id inserted successfully");
}
else
{
s = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value;
i = (int)Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
if (e.ColumnIndex == 1)
cmd = new SqlCommand("update student1 set name='" + s + "' where id='" + i + "'", con);
else
cmd = new SqlCommand("update student1 set email='" + s + "' where id='" + i + "'", con);
da.UpdateCommand = cmd;
cmd.ExecuteNonQuery();
MessageBox.Show("Information updated Successfully");
}
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
dataGridView1.Refresh();
}
private void dataGridView1_AllowUserToAddRowsChanged(object sender, EventArgs e)
{
}
private void dataGridView1_KeyUp(object sender, KeyEventArgs e)
{
if (e.KeyCode == Keys.Delete)
{
try
{
SqlConnection con2 = new SqlConnection();
SqlDataAdapter da2 = new SqlDataAdapter();
con2.ConnectionString = "Data Source=CHANDU-PC;Initial Catalog=Class;Integrated Security=true;MultipleActiveResultSets=true;";
con2.Open();
x = (int)Convert.ToInt32(dataGridView1.CurrentRow.Cells[0].Value);
SqlCommand cmd2 = new SqlCommand("delete student1 where id='" + x + "'", con2);
da2.DeleteCommand = cmd2;
cmd2.ExecuteNonQuery();
MessageBox.Show("Information deleted Successfully");
}
catch (Exception ex)
{
MessageBox.Show("Exception caught : " + ex.Message.ToString());
}
finally
{
con.Close();
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}