添加参数存储过程在SQL Server 2005的区别 [英] Difference between adding parameters to stored procedure in SQL Server 2005

问题描述

我想知道这两个符号之间的差异。

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

首先我有一个存储过程的

First of all I have a stored procedure

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();

请帮我

由于预期

推荐答案

下面是一些解释：

命令之间的区别添加和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")

从字符串到日期的加入势力的转换，因为它进入的参数。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 - 在编译时获知的s qlDbTypeis

When using Parameters.Add - the S qlDbTypeis known at compile time

在使用 Parameters.AddWithValue 的方法有框和拆箱的价值，找出它的类型。

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

前的额外的好处是，添加是多一点code安全   并协助对SQL注入攻击，code安全的条件   如果你试图传递一个值不匹配的SQLDB类型   定义 - 错误就会陷入净code，你会不会有   等待往返行程背面

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

修改

例如获得一个输出参数：

example to get an Output-Parameter:

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

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 2005的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！