如何使用C＃立即更新数据库表？ [英] How to UPDATE a database table instantly using C# ?

问题描述

我编写了以下代码来更新项目的课程表。但它不起作用。请建议我，我做错了什么..



我尝试过：



private void更新（）

{

试试

{

string ConnectionString = @Data Source = D3ll\SQLEXPRESS; Initial Catalog = GMS; Integrated Security = True;

SqlConnection Connection = new SqlConnection（ConnectionString） ;

Connection.Open（）;

string Query =UPDATE Courses SET Title ='+ TitleTextBox.Text +'，Fee ='+ FeeTextBox.Text +'，Description ='+ DescriptionTextBox.Text +'WHERE CourseID ='+ CourseIDTextBox.Text +';

SqlCommand Command = new SqlCommand（Query，Connection）;

Command.ExecuteNonQuery（）;

Connection.Close（）;

}

catch（Exception ex）

{

MessageBox.Show（哎呀！出了点问题！）;

}

}

I have written the following code to update my project's "Courses" table. But its not working. Please suggest me, whats wrong I'm doing..

What I have tried:

private void Updating()
{
try
{
string ConnectionString = @"Data Source= D3ll\SQLEXPRESS; Initial Catalog=GMS; Integrated Security=True";
SqlConnection Connection = new SqlConnection(ConnectionString);
Connection.Open();
string Query = "UPDATE Courses SET Title='" + TitleTextBox.Text + "', Fee='" + FeeTextBox.Text + "', Description='" + DescriptionTextBox.Text + "' WHERE CourseID='" + CourseIDTextBox.Text + "'";
SqlCommand Command = new SqlCommand(Query, Connection);
Command.ExecuteNonQuery();
Connection.Close();
}
catch (Exception ex)
{
MessageBox.Show("Oops!! Something is wrong!!");
}
}

推荐答案

大量的东西！
第一个也是最大的一个是：永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏整个数据库。请使用参数化查询。



第二个是：我们不知道 - 我们无法访问您的数据，所以我们无法分辨。

但是你这样做，你有工具可以帮助你找出问题所在。

首先在线上设一个断点：

Loads of things!
The first and biggest, is this: never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

The second is: we don't know - we don't have access to your data, so we can't tell.
But you do, and you have tools to help you work out what the problems is.
Start by putting a breakpoint on the line:

MessageBox.Show("Oops!! Something is wrong!!");

并更改一行：

And change the line:

Command.ExecuteNonQuery();

To：

To:

int count = Command.ExecuteNonQuery();

并设置断点这一行：

And put a breakpoint on this line as well:

Connection.Close();

现在在调试器中运行你的应用程序，并查看它到达了哪个断点。

如果它到达MessageBox，那么看看 ex ，它是Message，InnerException，看看他们究竟说了什么。

如果它到达Connection.Close，那么看看 count 中的内容。是1吗？还是0？返回值是命令影响的行数，因此如果它是一个然后它工作，你需要查看其他地方。如果它为零，则可能意味着您的WHERE子句与任何行都不匹配 - 所以开始查看传递给SQL的值。



我们不能为你做任何事情，但你应该很简单，看看你得到了什么。

Now run your app in the debugger, and see which breakpoint it reaches.
If it gets the to MessageBox, then look at ex and it's Message, InnerException and see exactly what they say.
If it gets to the Connection.Close, then look at what is in count. Is it 1? Or is it 0? The return value is the number of rows the command affected, so if it's one then it worked and you need to look elsewhere. If it's zero, then it probably means that your WHERE clause didn't match any rows - so start looking at the values you pass to SQL.

We can't do any of that for you, but it should be pretty simple for you to do, and see what you get.

我已经通过以下代码成功解决了这个问题;

I have successfully resolved that by following code;

private void Updating()
        {
            try
            {
                string connectionString = @"Data Source= D3ll\SQLEXPRESS; Initial Catalog=GMS; Integrated Security=True";
                SqlConnection Connection = new SqlConnection(connectionString);
                SqlCommand Command = new SqlCommand();
                Command.Connection = Connection;
                Connection.Open();
                Command.CommandText = "UPDATE Courses SET Title = @ttl, Fee = @fe, Description= @dscptn WHERE CourseID = @id";

                Command.Parameters.AddWithValue("@ttl", TitleTextBox.Text);
                Command.Parameters.AddWithValue("@fe", FeeTextBox.Text);
                Command.Parameters.AddWithValue("@dscptn", DescriptionTextBox.Text);
                Command.Parameters.AddWithValue("@id", CourseIDTextBox.Text);
                int NEW = Command.ExecuteNonQuery();
                Connection.Close();

                MessageBox.Show("!! (" + NEW + ") new Course Information has been UPDATED successfully!!");

                if (NEW > 0)
                    ClearText();

            }
            catch (Exception ex)
            {
                MessageBox.Show("Oops!! Something is wrong!!");
            }
        }

这篇关于如何使用C＃立即更新数据库表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！