从 C# 代码向 SQL Server 插入数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11030183/
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
Insert data into SQL Server from C# code
提问by titi
I have a table student(id, name). Then I have one textbox, for entering the name, when click on submit button, it inserts the data into the database. So how can I insert only to name, not id because id is auto increment?
我有一张桌子student（id, name）。然后我有一个文本框，用于输入名称，当单击提交按钮时，它将数据插入到数据库中。那么我怎样才能只插入到名称而不是 id 因为 id 是自动递增的？
I tried this
我试过这个
insert into student(id, name) values(,name)
but it is not insert to my table.
但它没有插入我的桌子。
This is my code :
这是我的代码：
protected void Button1_Click(object sender, EventArgs e)
{
string test = txtName.Text;
SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Person.mdf;Integrated Security=True;User Instance=True");
string sql = "insert into student(name) values ('test')";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
}
finally
{
conn.Close();
}
}
采纳答案by David M
INSERT INTO student (name) values ('name')
Omit the idcolumn altogether, it will be populated automatically. To use your variable, you should parameterise your SQL query.
id完全省略该列，它将自动填充。要使用您的变量，您应该参数化您的 SQL 查询。
string sql = "INSERT INTO student (name) values (@name)";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = test;
cmd.ExecuteNonQuery();
You should never attempt to do this by constructing a SQL string containing the input value, as this can expose your code to SQL injection vulnerabilities.
您永远不应该尝试通过构造包含输入值的 SQL 字符串来执行此操作，因为这可能会将您的代码暴露给 SQL 注入漏洞。
回答by Asif Mushtaq
You don't need to mention the ID in first part.
您无需在第一部分提及 ID。
insert into student(name) values('name')
回答by Rajesh Subramanian
Try the following query,
尝试以下查询，
insert into student(name) values(name)
SQL Server internally auto increments the id column when u insert the data since u said it is auto increment. If it is not working, the u have to check the identity column in the db.
当您插入数据时，SQL Server 会在内部自动增加 id 列，因为您说它是自动增加的。如果它不起作用，您必须检查数据库中的标识列。
回答by Damith
You better use parameters when you insert data.
插入数据时最好使用参数。
try
{
string sql = "insert into student(name) values (@name)";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@name", test); // assign value to parameter
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
}
回答by Nikhil D
string sql = "INSERT INTO student (name) values (@name)";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = test;
cmd.ExecuteNonQuery();
回答by Ganesh Kamath
I was facing this problem and after trying various solution found at stack overflow, i could summarize the experience as follows: commands executed in command shell of mssql like:
我遇到了这个问题，在尝试了在堆栈溢出时找到的各种解决方案后，我可以总结如下经验：在 mssql 的命令 shell 中执行的命令，如：
insert into table_name (val1,val2,val3,val4) VALUES ("val1","val2",0,"val4")
insert into table_name (val1,val2,val3,val4) VALUES ("val1","val2",0,"val4")
go
go
or
或者
insert into table_name VALUES ("val1","val2",0,"val4")
insert into table_name VALUES ("val1","val2",0,"val4")
go
work when typed directly in the mssql database prompt,
直接在 mssql 数据库提示中键入时工作，
But when it is required to use the the insert statement from c#, it is required to be kept in mind that string needs to be surrounded by an additional pair of single quites, around the strings, like in:
但是当需要使用 c# 中的 insert 语句时，需要记住字符串需要被一对额外的单个字符串包围，围绕字符串，例如：
SqlConnection cnn;
string connetionString = "Data Source=server_name;Initial Catalog=database_name;User ID=User_ID;Password=Pass_word";
cnn = new SqlConnection(connetionString);
SqlCommand myCommand = new SqlCommand("insert into table_name (val1,val2,val3,val4) VALUES ('val1','val2',0,'val4');", cnn);
//or
//SqlCommand myCommand = new SqlCommand(insert into table_name VALUES ('val1','val2',0,'val4');", cnn);
cnn.Open();
myCommand.ExecuteNonQuery();
cnn.Close();
the problem here is that most people, like myself, try to use <\"> in the place of double quotes <">that is implemented as in the above command line case, and SQL executor fails to understand the meaning of this.
这里的问题是，像我这样的大多数人都尝试使用 <\"> 代替双引号 <"> ，这是在上述命令行案例中实现的，而 SQL 执行程序无法理解其含义。
Even in cases where a string needs to be replace, ensure that strings are surrounded by single quotation, where a string concatination looks like a feasible solution, like in:
即使在需要替换字符串的情况下，也要确保字符串被单引号包围，其中字符串连接看起来是一个可行的解决方案，例如：
SqlCommand myCommand = new SqlCommand("insert into table_name (val1,val2,val3,val4) VALUES ('"+val1+"','val2',0,'val4');", cnn);
回答by Ram
use the key word "identity" to auto increment the id column
使用关键字“身份”自动增加 id 列
Refer : http://technet.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
参考：http: //technet.microsoft.com/en-us/library/aa933196(v=sql.80).aspx
create table table_name( id int PRIMARY KEY IDENTITY )
and you no need to mention the "id" in the insert query
并且您无需在插入查询中提及“id”
