向SQL Server中的存储过程添加参数之间的区别? [英] Difference between adding parameters to stored procedure in SQL Server?

问题描述

我想知道这两种表示法之间的区别.

I would like to know the difference between these 2 notations.

首先我有一个存储过程

CREATE PROCEDURE AddSomething( @zonename varchar(50), @desc varchar(255), @TheNewId int OUTPUT ) AS 
BEGIN 
   INSERT INTO a_zone(zonename, descr) VALUES(@zonename, @desc) 
   SELECT @TheNewId = SCOPE_IDENTITY()         
END

如果我以这种方式添加参数有什么区别

What is the difference if I add parameters in this manner

SqlCommand Cmd = new SqlCommand("AddSomething", oConn); 
Cmd.CommandType = CommandType.StoredProcedure; 
SqlParameter oParam1 = Cmd.Parameters.AddWithValue("@zonename", sName);
SqlParameter oParam2 = Cmd.Parameters.AddWithValue("@desc", description);

和

SqlCommand Cmd2 = new SqlCommand("AddSomething", oConn); 
Cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@zonename", SqlDbType.VarChar).Value = zonename.Text.Trim();
cmd2.Parameters.Add("@desc", SqlDbType.VarChar).Value = desc.Text.Trim();

推荐答案

以下是一些说明:

命令Add和AddWithValue之间的区别

Dim cmd as new SqlCommand("SELECT * FROM MyTable WHERE MyDate>@TheDate",conn)
cmd.Parameters.Add("@TheDate",SqlDbType.DateTime).Value="2/1/2007"

vs

cmd.Parameters.AddWithValue("@TheDate","2/1/2007")

"Add会强制从字符串到日期的转换，直到它进入参数为止.AddWithValue只需将字符串传递给SQL Server.

"Add forces the conversion from string to date as it goes into the parameter. AddWithValue would have simply passed the string on to the SQL Server.

使用Parameters.Add时-SqlDbType在编译时是已知的

When using Parameters.Add - the SqlDbType is known at compile time

使用Parameters.AddWithValue时，该方法必须对值进行装箱和拆箱以找出其类型.

When using Parameters.AddWithValue the method has to box and unbox the value to find out its type.

前者的其他好处是Add是更安全的代码 并将有助于抵御SQL注入攻击，从代码角度讲是安全的 如果您尝试传递与SqlDb类型不匹配的值 定义的-错误将被捕获在.Net代码中，您将没有 等待往返.

Additional benefits of the former is that Add is a bit more code safe and will assist against SQL injection attacks , code safe in terms that if you try to pass a value that doesn't match the SqlDb type defined - the error will be caught in .Net code and you will not have to wait for the round trip back.

• http://msdn.microsoft .com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
• http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx
• http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx
• http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx

修改:

获取输出参数的示例:

C#

cmd.Parameters.Add(new SqlParameter("@TheNewId", SqlDbType.Int, int.MaxValue));
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
int theNewID = (int)cmd.Parameters("@TheNewId").Value;

VB.Net

cmd.Parameters.Add(New SqlParameter("@TheNewId", SqlDbType.Int, Int32.MaxValue))
cmd.Parameters("@TheNewId").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Dim theNewID As Int32 = DirectCast(cmd.Parameters("@TheNewId").Value, Int32)

这篇关于向SQL Server中的存储过程添加参数之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！