如何删除行数据网格和更新SQL数据库C# [英] How to delete row datagrid and update SQL database C#
问题描述
我真的很困惑,这里没有书或书本都对用户友好,足以说明如何使用C#从数据网格对象中删除数据
I am really stuck and non of the books or tread here are user-friendly enough to explain how to delete data from data grid object using C#
从各种书籍和四个线程中我设法设法将其编码,但是无法执行sql命令,我有一个表名为"SpellingList",两列为ID,另一列为Words,我想要做的就是从数据网格中删除一行.
from varies of books and fourm threads I have managed to get this coded but it fails to executes the sql command , I have One table call 'SpellingList' and two columns one is ID and the other is Words, all I want to do is delete a row from the datagrid.
有人能指出我正确的方向吗?一旦我解决了这个问题,我将上传一些简单易学的基本教程,以便其他新手可以按照此示例进行操作.
Can someone point me to the correct direction , once I solved this , I shall upload some basic easy to follow tutorial so the rest of the newbie can follow this example.
我已使用此线程如何删除所选的DataGridViewRow并更新连接的数据库表?
以及MCSD认证工具包考试70-483第392页,所有尝试均失败.
and MCSD Certificaiton Toolkit Exam 70-483 book Page 392 , all attempts have failed.
如果我注释掉if(dataGridView1.SelectedRows.Count> 0)行,则这里有一个陷阱22,我得到了索引超出范围错误.
there is a catch 22 here if I comment out if(dataGridView1.SelectedRows.Count > 0) line , I get the index out of range error .
private void btnDelete_Click(object sender, EventArgs e)
{
// My SQL connectionString
SqlConnection myConnection = new SqlConnection("Data Source=Epic-LaptopWR;Initial Catalog=words;Integrated Security=True");
myConnection.Open();
foreach (DataGridViewCell oneCell in dataGridView1.SelectedCells)
{
if(dataGridView1.SelectedRows.Count > 0)
{
int selectedIndex = dataGridView1.SelectedRows[0].Index;
// gets the RowID from the first column in the grid
int rowID = int.Parse(dataGridView1[0, selectedIndex].Value.ToString());
// your code for deleting it from the database
string sql = "DELETE FROM SpellingList WHERE Words =" + selectedIndex;
SqlCommand deleteRecord = new SqlCommand();
deleteRecord.Connection = myConnection;
deleteRecord.CommandType = CommandType.Text;
deleteRecord.CommandText = sql;
SqlParameter RowParameter = new SqlParameter();
RowParameter.ParameterName = "@Words";
RowParameter.SqlDbType = SqlDbType.Int;
RowParameter.IsNullable = false;
RowParameter.Value = rowID;
deleteRecord.Parameters.Add(RowParameter);
deleteRecord.Connection.Open();
deleteRecord.ExecuteNonQuery();
if (oneCell.Selected)
dataGridView1.Rows.RemoveAt(oneCell.RowIndex);
deleteRecord.Connection.Close();
wordsDataSet.GetChanges();
// booksDataset1.GetChanges();
spellingListTableAdapter.Fill(wordsDataSet.SpellingList);
}// end of if
// then your code for refreshing the DataGridView
}// end of for each statement
myConnection.Close();
}// end of delete Button
推荐答案
我们可以简化很多工作.并修复一些明显的错误.
We could simplify a lot. And fix some evident bugs.
ID字段是表的关键字段,因此您应该使用它来查找要删除的行,然后,一个有用的参数需要在要执行的命令文本中使用参数占位符>
The ID field is the key field of your table and thus you should use it to find the row to delete, then, a parameter, to be useful, needs a parameter placeholder in the command text that you want to execute
private void btnDelete_Click(object sender, EventArgs e)
{
// No row selected no delete....
if(dataGridView1.SelectedRows.Count == 0)
return; // show a message here to inform
// Prepare the command text with the parameter placeholder
string sql = "DELETE FROM SpellingList WHERE ID = @rowID";
// Create the connection and the command inside a using block
using(SqlConnection myConnection = new SqlConnection("...."))
using(SqlCommand deleteRecord = new SqlCommand(sql, myConnection))
{
myConnection.Open();
int selectedIndex = dataGridView1.SelectedRows[0].Index;
// gets the RowID from the first column in the grid
int rowID = Convert.ToInt32(dataGridView1[0, selectedIndex].Value);
// Add the parameter to the command collection
deleteRecord.Parameters.Add("@rowID", SqlDbType.Int).Value = rowID;
deleteRecord.ExecuteNonQuery();
// Remove the row from the grid
dataGridView1.Rows.RemoveAt(selectedIndex);
}
}
如您所见,连接和命令是可抛弃的对象,应将其括在using语句中,以确保在发生异常情况下也能正确关闭和处置.
As you can see, connections and commands are disposable objects and should be enclosed in a using statement to ensure proper closing and disposing also in case of exceptions.
最后,只需使用行的索引从网格中删除该行,并在单个命令中删除该行即可.您无需遍历单元格即可将其一一删除.
Finally, removing the row from the grid should be done simply using the index of the row, removing the line in a single command. You don't need to loop over the cells to delete them one by one.
这篇关于如何删除行数据网格和更新SQL数据库C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!