C#中的SQL更新语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15246182/
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
SQL update statement in C#
提问by Андрей Про
I have table "Student"
我有表 “学生”
P_ID LastName FirstName Address City
1 Hansen Ola
2 Svendson Tove
3 Petterson Kari
4 Nilsen Johan
...and so on
How do i change edit code in C#
我如何更改 C# 中的编辑代码
string firstName = "Ola";
string lastName ="Hansen";
string address = "ABC";
string city = "Salzburg";
string connectionString = System.Configuration.ConfigurationManager
.ConnectionStrings["LocalDB"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = "INSERT INTO Student (LastName, FirstName, Address, City)
VALUES (@ln, @fn, @add, @cit)";
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
to edit entry where Lastnamefield has lastname value and FirstNamefield has firstname value.
要编辑条目，其中姓字段姓价值和名字字段名字值。
I dont want to use like this
我不想这样使用
UPDATE Persons SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob'
and i edited my original statement to
我将我的原始声明编辑为
command.CommandText = "UPDATE Student(LastName, FirstName, Address, City)
VALUES (@ln, @fn, @add, @cit) WHERE LastName='" + lastName +
"' AND FirstName='" + firstName+"'";
but the statement is not getting executed, why is it throwing SQL exception ? Is there nay solution to it ?
但是语句没有被执行，为什么会抛出 SQL 异常？有什么解决办法吗？
采纳答案by K D
This is not a correct method of updating record in SQL:
这不是在 SQL 中更新记录的正确方法：
command.CommandText = "UPDATE Student(LastName, FirstName, Address, City) VALUES (@ln, @fn, @add, @cit) WHERE LastName='" + lastName + "' AND FirstName='" + firstName+"'";
You should write it like this:
你应该这样写：
command.CommandText = "UPDATE Student
SET Address = @add, City = @cit Where FirstName = @fn and LastName = @add";
Then you add the parameters same as you added them for the insert operation.
然后添加与为插入操作添加的参数相同的参数。
回答by Habib
I dont want to use like this
我不想这样使用
That is the syntax for Updatestatement in SQL, you have to use that syntax otherwise you will get the exception.
这是UpdateSQL 中语句的语法，您必须使用该语法，否则您将收到异常。
command.Text = "UPDATE Student SET Address = @add, City = @cit Where FirstName = @fn AND LastName = @ln";
and then add your parameters accordingly.
然后相应地添加您的参数。
command.Parameters.AddWithValue("@ln", lastName);
command.Parameters.AddWithValue("@fn", firstName);
command.Parameters.AddWithValue("@add", address);
command.Parameters.AddWithValue("@cit", city);
回答by Roger Far
If you don't want to use the SQL syntax (which you are forced to), then switch to a framework like Entity Framework or Linq-to-SQL where you don't write the SQL statements yourself.
如果您不想使用 SQL 语法（您被迫使用），那么请切换到诸如 Entity Framework 或 Linq-to-SQL 之类的框架，在这些框架中您不必自己编写 SQL 语句。
回答by Rashad Maqsood
There is always a proper syntax for every language. Similarly SQL(Structured Query Language) has also specific syntax for update query which we have to follow if we want to use update query. Otherwise it will not give the expected results.
每种语言都有合适的语法。同样，SQL（结构化查询语言）也有特定的更新查询语法，如果我们想使用更新查询，我们必须遵循这些语法。否则它不会给出预期的结果。
回答by user3576577
string constr = @"Data Source=(LocalDB)\v11.0;Initial Catalog=Bank;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(constr);
DataSet ds = new DataSet();
con.Open();
SqlCommand cmd = new SqlCommand(" UPDATE Account SET name = Aleesha, CID = 24 Where name =Areeba and CID =11 )";
cmd.ExecuteNonQuery();
回答by robin
String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
+ "WHERE supplier_id = " + textBox1.Text;
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
MessageBox.Show("update successful");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
回答by robin
private void button4_Click(object sender, EventArgs e)
{
String st = "DELETE FROM supplier WHERE supplier_id =" + textBox1.Text;
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
MessageBox.Show("刪除成功");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
private void button6_Click(object sender, EventArgs e)
{
String st = "SELECT * FROM suppliers";
SqlCommand sqlcom = new SqlCommand(st, myConnection);
try
{
sqlcom.ExecuteNonQuery();
SqlDataReader reader = sqlcom.ExecuteReader();
DataTable datatable = new DataTable();
datatable.Load(reader);
dataGridView1.DataSource = datatable;
//MessageBox.Show("LEFT OUTER成功");
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
回答by amir nikanjam
command.Text = "UPDATE Student
SET Address = @add, City = @cit
Where FirstName = @fn and LastName = @add";
回答by Diego Venancio
Please, never use this concat form:
请不要使用这种 concat 形式：
String st = "UPDATE supplier SET supplier_id = " + textBox1.Text + ", supplier_name = " + textBox2.Text
+ "WHERE supplier_id = " + textBox1.Text;
use:
用：
command.Parameters.AddWithValue("@attribute", value);
Always work object oriented
始终面向对象工作
Edit: This is because when you parameterize your updates it helps prevent SQL injection.
编辑：这是因为当您参数化更新时，它有助于防止 SQL 注入。
