C# 执行带参数查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11905185/
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
Executing query with parameters
提问by Marc Spencer
I want to execute a .sqlscript from C#. Basically the script inserts a row into few different tables.
我想.sql从 C#执行脚本。基本上，脚本将一行插入到几个不同的表中。
The point is I have values in C# code that I need to pass to the .sqlquery. These values will be collected during program execution.
关键是我在 C# 代码中有值需要传递给.sql查询。这些值将在程序执行期间收集。
Here is the query that I want to execute from C# code:
这是我想从 C# 代码执行的查询：
INSERT INTO [DB].[dbo].[User]
?????????? ([Id]
?????????? ,[AccountId]
?????????? ,[FirstName]
?????????? ,[LastName]
?????????? ,[JobTitle]
?????????? ,[PhoneNumber]
??????????)
???? VALUES
?????????? ('00A640BD-1A0D-499D-9155-BA2B626D7B68'
?????????? ,'DCBA241B-2B06-48D7-9AC1-6E277FBB1C2A'
?????????? ,'Mark'
?????????? ,'Wahlberg'
?????????? ,'Actor'
,'9889898989'])
GO
The values will vary from time to time i.e., they are captured in C# code and need to be passed.
这些值会不时变化，即，它们在 C# 代码中捕获并需要传递。
Can anyone please help me do this..I am learning both C# and SQL. Thanks a lot.
任何人都可以帮我做这个..我正在学习 C# 和 SQL。非常感谢。
采纳答案by Paul Aldred-Bann
You could open yourself up to SQL injection attackshere, so best practice is to use parameters:
您可以在这里接受SQL 注入攻击，因此最佳实践是使用参数：
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
dbConn.Open();
using (SqlTransaction dbTrans = dbConn.BeginTransaction())
{
try
{
using (SqlCommand dbCommand = new SqlCommand("insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName], [JobTitle], [PhoneNumber] ) values ( @id, @accountid, @firstname, @lastname, @jobtitle, @phonenumber );", dbConn))
{
dbCommand.Transaction = dbTrans;
dbCommand.Parameters.Add("id", SqlType.VarChar).Value = id;
dbCommand.Parameters.Add("accountid", SqlType.VarChar).Value = accountId;
dbCommand.Parameters.Add("firstname", SqlType.VarChar).Value = firstName;
dbCommand.Parameters.Add("lastname", SqlType.VarChar).Value = lastName;
dbCommand.Parameters.Add("jobtitle", SqlType.VarChar).Value = jobTitle;
dbCommand.Parameters.Add("phonenumber", SqlType.VarChar).Value = phoneNumber;
dbCommand.ExecuteNonQuery();
}
dbTrans.Commit();
}
catch (SqlException)
{
dbTrans.Rollback();
throw; // bubble up the exception and preserve the stack trace
}
}
dbConn.Close();
}
This is a good article for beginners with ADO.Net
这是一篇适合ADO.Net 初学者的好文章
EDIT- Just as a bit of extra info, I've added a transactionto it so if the SQL command fails it will rollback.
编辑- 就像一些额外的信息，我已经向它添加了一个事务，所以如果 SQL 命令失败，它将回滚。
回答by Marcelo De Zen
You'll need the System.Data.SqlCommandclass.
你需要System.Data.SqlCommand上课。
Change the fixed values to named parameters. For example:
将固定值更改为命名参数。例如：
INSERT INTO [TABLE] (Column1) values (@Value1) // the @Value1 is the named parameter
INSERT INTO [TABLE] (Column1) values (@Value1) // @Value1 是命名参数
Example:
例子：
var connection = new SqlConnection("connectionstring");
var command = connection.CreateCommand();
command.CommandText = "insert...."; // sql command with named parameters
// set the named parameter values
command.Parameters["@Value1"] = "Mark wa...";
// execute
command.ExecuteNonQuery();
SqlCommand reference: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx
SqlCommand 参考：http: //msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx
回答by SpaceApple
using SqlCommand cmd= conn.CreateCommand())
{
cmd.CommandText = @"INSERT INTO TABLE (COLUMNS) VALUES (@Id, @account etc...
cmdUser.Parameters.Add(new SqlParameter("@User", SqlDbType.UniqueIdentifier) { Value = UserTypeID });
cmdUser.Parameters.Add(new SqlParameter("@Id", SqlDbType.UniqueIdentifier) { Value = ApprovalTypeID });
cmdUser.Parameters.Add(new SqlParameter("@AccountId", SqlDbType.UniqueIdentifier) { Value = UserID });
cmdUser.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 100) { Value = Name });
cmdUser.Parameters.Add(new SqlParameter("@JobTitle", SqlDbType.NVarChar, 100) { Value = Surname });
cmdUser.Parameters.Add(new SqlParameter("@PhoneNumber", SqlDbType.Bit) { Value = Active });
cmdUser.ExecuteNonQuery();
}
回答by Mazdak Shojaie
try
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("Insert_User", cn);
cmd.CommandType = CommandType.StoredProcedure;
if (cn.State != ConnectionState.Open)
cn.Open();
cmd.Parameters.Add("Id", SqlDbType.NVarChar).Value = "00A640BD-1A0D-499D-9155-BA2B626D7B68";
cmd.Parameters.Add("AccountId", SqlDbType.NVarChar).Value = "DCBA241B-2B06-48D7-9AC1-6E277FBB1C2A";
cmd.Parameters.Add("FirstName", SqlDbType.NVarChar).Value = "Mark";
cmd.Parameters.Add("LastName", SqlDbType.NVarChar).Value = "Wahlberg";
cmd.Parameters.Add("JobTitle", SqlDbType.NVarChar).Value = "Actor";
cmd.Parameters.Add("PhoneNumber", SqlDbType.NVarChar).Value = "9889898989";
return cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
and for stored procedure, in sql:
对于存储过程，在 sql 中：
create procedure [Insert_User]
(
@id as nvarchar(100),
@accid as nvarchar(100),
@fname as nvarchar(100),
@lname as nvarchar(100),
@jobtitle as nvarchar(100),
@phone as nvarchar(100)
)
INSERT INTO [DB].[dbo].[User]
([Id]
,[AccountId]
,[FirstName]
,[LastName]
,[JobTitle]
,[PhoneNumber]
)
VALUES
(@id
,@accid
,@fname
,@lname
,@jobtitle
,@phone])
also, you can use text boxes or other input type controls to set values. You can change dataType, as you wish, such as uniqueidentifier, int, etc. If one or more of values are set as identifire, eg. AccountID, remove them from query.
此外，您可以使用文本框或其他输入类型控件来设置值。你可以改变数据类型，如你所愿，如uniqueidentifier，int等。如果一个或多个值被设置为identifire，例如。AccountID，从查询中删除它们。
回答by Marc Gravell
Frankly, ADO.NET makes it hard to do things like this correctly. Tools like Dapperexist to make that easier:
坦率地说，ADO.NET 很难正确地做这样的事情。像Dapper这样的工具可以让这变得更容易：
dbConn.Execute(
@"insert into [DB].[dbo].[User] ( [Id], [AccountId], [FirstName], [LastName],
[JobTitle], [PhoneNumber] )
values ( @id, @accountId, @firstName, @lastName, @jobTitle, @phoneNumber )",
new { id, accountId, firstName, lastName, jobTitle, phoneNumber });
This will deal with all the parameterization for you, efficiently, effectively, and safely.
这将为您高效、有效和安全地处理所有参数化。
There are similar APIs for executing queries and populating objects.
有类似的 API 用于执行查询和填充对象。
