C# 从数据网格视图中删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11193385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete row from datagridview
提问by Gary Yee
I having problem on deleting a row of data returned by a search query. I wish the user can select whichever row of data and click on the delete button [button1_click] to delete it from DB. This is a windows form application.
我在删除搜索查询返回的一行数据时遇到问题。我希望用户可以选择任意一行数据，然后单击删除按钮 [button1_click] 将其从数据库中删除。这是一个 windows 窗体应用程序。
Hope you can advise me. Thanks a lot. Below is my code
希望你能给我建议。非常感谢。下面是我的代码
public partial class Search : Form
{
public Search()
{
InitializeComponent();
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Project\DB_Booking.mdb;";
DataTable ds = new DataTable();
using (var cn = new OleDbConnection(strConn))
{
cn.Open();
using (var cmd = new OleDbCommand("SELECT * FROM staff", cn))
{
using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
adp.Fill(ds);
comboBox1.DataSource = ds;
comboBox1.ValueMember = "sname";
comboBox1.SelectedIndex = 0;
}
}
}
private void btn_search_bystaffname_Click(object sender, EventArgs e)
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Project\DB_Booking.mdb;";
DataTable dt = new DataTable();
using (var cn = new OleDbConnection(strConn))
{
cn.Open();
using (var cmd = new OleDbCommand("SELECT * FROM booking WHERE sname = @sname", cn))
{
//cmd.Parameters.AddWithValue("@bdate", dtp_search_date.Value.Date);
cmd.Parameters.AddWithValue("@sname", comboBox1.SelectedValue);
using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd))
oda.Fill(dt);
GridView1.DataSource = dt;
GridView1.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[0].HeaderText = "Booking ID";
GridView1.Columns[1].HeaderText = "Client Name";
GridView1.Columns[2].HeaderText = "Booking Date";
GridView1.Columns[3].HeaderText = "Booking Time";
GridView1.Columns[4].HeaderText = "Client Contact";
GridView1.Columns[5].HeaderText = "Staff Name";
this.GridView1.DefaultCellStyle.Font = new Font("Times New Roman", 12);
this.GridView1.DefaultCellStyle.ForeColor = Color.Blue;
this.GridView1.DefaultCellStyle.BackColor = Color.Beige;
this.GridView1.DefaultCellStyle.SelectionForeColor = Color.Yellow;
this.GridView1.DefaultCellStyle.SelectionBackColor = Color.Black;
this.GridView1.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
}
}
}
private void btn_search_bydate_Click(object sender, EventArgs e)
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Project\DB_Booking.mdb;";
DataTable dt = new DataTable();
using (var cn = new OleDbConnection(strConn))
{
cn.Open();
using (var cmd = new OleDbCommand("SELECT * FROM booking WHERE bdate = @bdate", cn))
{
cmd.Parameters.AddWithValue("@bdate", dtp_search_date.Value.Date);
cmd.Parameters.AddWithValue("@sname", comboBox1.SelectedValue);
using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd))
oda.Fill(dt);
GridView1.DataSource = dt;
GridView1.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter;
GridView1.Columns[0].HeaderText = "Booking ID";
GridView1.Columns[1].HeaderText = "Client Name";
GridView1.Columns[2].HeaderText = "Booking Date";
GridView1.Columns[3].HeaderText = "Booking Time";
GridView1.Columns[4].HeaderText = "Client Contact";
GridView1.Columns[5].HeaderText = "Staff Name";
this.GridView1.DefaultCellStyle.Font = new Font("Times New Roman", 12);
this.GridView1.DefaultCellStyle.ForeColor = Color.Blue;
this.GridView1.DefaultCellStyle.BackColor = Color.Beige;
this.GridView1.DefaultCellStyle.SelectionForeColor = Color.Yellow;
this.GridView1.DefaultCellStyle.SelectionBackColor = Color.Black;
this.GridView1.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
this.GridView1.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
GridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
DataGridViewRow row = GridView1.SelectedRows[0];
GridView1.Rows.Remove(row);
}
}
}
回答by JohnP
Here's how I've been doing it in my application (My unique identifier for the mysql table is in cell zero):
这是我在我的应用程序中的做法（我的 mysql 表的唯一标识符在单元格零中）：
Oh, and dtcommand is a class instance for a database command class I use for common db stuff.
哦，dtcommand 是我用于常见数据库内容的数据库命令类的类实例。
int userDeleteIndex;
if (int.TryParse(datagridview.Rows[rowIndex].Cells[0].Value.ToString(), out userDeleteIndex))
{
if (MessageBox.Show("Delete " + recordidentifyingdata + "? ", "Delete " + userDeleteIndex.ToString(), MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
string updateUserSql = "DELETE FROM table WHERE user_id = " + userDeleteIndex.ToString() + "; ";
dtCommand.UpdateTable(updateUserSql);
InitializeUserDataView();
// Initalize userdataview refreshes the datagridview with the updated info
}
catch (Exception err)
{
Error trapping goes here
}
Here's the database update section from my class:
这是我班上的数据库更新部分：
public int UpdateTable(string updateString, string MySqlConnectionString)
{
int returnValue = 0;
MySqlConnection connection = new MySqlConnection(MySqlConnectionString);
MySqlCommand command = new MySqlCommand(updateString, connection);
try
{
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception err)
{
WriteErrorLog("Unable to update table: " + err.ToString() +
" - Using SQL string: " + updateString + ".");
//MessageBox.Show("An error has occured while updating the database.\n" +
//"It has been written to the file: " + errorFile + ".", "Database Error");
returnValue = -1;
}
finally
{
connection.Close();
}
return (returnValue);
}
回答by Conrad Frix
I'm assuming this is the method you want to change
我假设这是你想要改变的方法
private void button1_Click(object sender, EventArgs e)
{
GridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
DataGridViewRow row = GridView1.SelectedRows[0];
GridView1.Rows.Remove(row);
}
This GridView1.Rows.Remove(row);will only remove the item from the DaDataGridViewRowCollection and will not remove it from the database.
这GridView1.Rows.Remove(row);只会从 DaDataGridViewRowCollection 中删除该项目，而不会从数据库中删除它。
To remove it from the database you can do one of the following
要将其从数据库中删除，您可以执行以下操作之一
Remove it from `DataTable' and then use a DataAdapter and call update.
Directly delete it from the database using a DELETE SQL statement through a OleDbCommand.
从“DataTable”中删除它，然后使用 DataAdapter 并调用更新。
通过 OleDbCommand 使用 DELETE SQL 语句直接从数据库中删除它。
If you choose option one you'd be well served by making dta Field on your Form. This is because you can only access it now via ((DataRow)row.DataBoundItem).Tableor GridView1.DataSourcein the button1_Click event. Also making the DataAdapter a field would make this easier
如果您选择选项一，您可以通过dt在表单上创建一个字段来获得良好的服务。这是因为您现在只能通过((DataRow)row.DataBoundItem).Table或GridView1.DataSource在 button1_Click 事件中访问它。同时使 DataAdapter 成为一个字段会使这更容易
e.g.
例如
GridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
DataGridViewRow row = GridView1.SelectedRows[0];
DataRow dRow = (DataRow)row.DataBoundItem;
dt.Rows.Remove(dRow);
Adapter.Update(dt);
As an aside you can choose to do only the dt.Rows.Remove(dRow);in the button1_Click and defer the Adapter.Update(dt)until later in a Save button.
dt.Rows.Remove(dRow);顺便说Adapter.Update(dt)一句，您可以选择仅在 button1_Click 中执行，然后在 Save 按钮中推迟执行。
If you go with option two you'll need to remove it from the DataTable or refresh the DataTable
如果您使用选项二，则需要将其从 DataTable 中删除或刷新 DataTable
e.g.
例如
GridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
DataGridViewRow row = GridView1.SelectedRows[0];
OleDbCommand cmd = new OleDbCommand(
using (var cn = new OleDbConnection(strConn))
{
cn.Open();
// not 100% this delete syntax is correct for Access
using (var cmd = new OleDbCommand("DELETE booking WHERE [Booking ID] = @BookingId", cn))
{
cmd.Parameters.AddWithValue("@BookingId", dRow["Booking Id"]);
cmd.ExecuteNonQuery();
}
}
// Do this to update the in-memory representation of the Data
DataRow dRow = (DataRow)row.DataBoundItem;
dt.Rows.Remove(dRow);
// Or just refresh the datatable using code similar as your search methods
