如何删除行数据网格和更新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屋！