SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working With SqlParameter in .NET


Working With SqlParameter in .NET

Author
Message
scott mcnitt
scott mcnitt
SSC Veteran
SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)SSC Veteran (264 reputation)

Group: General Forum Members
Points: 264 Visits: 437
For those of us coming from the old ADO world, do the parameter names have to exactly match the sproc parm names? In ADO the ordinal position mattered not the name.
Anna-449784
Anna-449784
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 97
One other thing to note, Parameters.Add() has been deprecated in 2.0 and replaced with Parameters.AddWithValue();

http://weblogs.asp.net/JackieG/archive/2005/03/22/395517.aspx
Antares686
Antares686
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: Moderators
Points: 27388 Visits: 802
Roy Cross (5/9/2007)
Found it. For anyone else who may be interested...

' Add the input parameter and set its properties.
Command.Parameters.Add("@SKU", SqlDbType.VarChar).Value = SKU
Command.Parameters.Add("@Price", SqlDbType.Float).Value = Price
Command.Parameters.Add("@RC", SqlDbType.TinyInt).Direction = ParameterDirection.Output



Remember .Add has multiple overrides one of which is accepting a SQLParameter object, I will commonly do this (mostly from habit more than anything).

cmdSQL.Parameters.Add(new SqlParameter("@Employee_ID",SqlDbType.VarChar,10,ParameterDirection.Input,false,0,0,"Employee_ID",DataRowVersion.Proposed,txtEmployeeID.Text.ToString()));


But commonly overlooked point of parameters are dynamic SQL statements. You can build the SQL string using @[Name] for the parameter position and can use parameters to control data validation as well as protection against injection attacks. Which if you plan to use dynamic SQL this is what I suggest you do versus build in a Stored Procedure.

This is a nice simple article but really lacks the impact of describing SqlParameter as it should.



Patrick JJS
Patrick JJS
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
Good Article

Patrick
JJS
Joel Rea
Joel Rea
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 33
Anna (11/9/2007)
One other thing to note, Parameters.Add() has been deprecated in 2.0 and replaced with Parameters.AddWithValue();

http://weblogs.asp.net/JackieG/archive/2005/03/22/395517.aspx
Not quite true. The specific overload of Parameters.Add(paramName As String, value As Object) has been deprecated because it’s too easy for the value parameter to be mistaken for an enumerated (and thus Integer typed) SqlType if, for instance, your desired value were an integer. .AddWithValue avoids this ambiguity because its second parameter will always be the value, not the type.

All other overloads of the .Add method, including the nifty .Add().Value = value syntax, remain as non-deprecated.
Vasant Raj
Vasant Raj
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 137
scott mcnitt (11/9/2007)
For those of us coming from the old ADO world, do the parameter names have to exactly match the sproc parm names? In ADO the ordinal position mattered not the name.


The order does not matter in .NET but the names do. So, if the sproc has a paramter @test, then from .NET it is required to give @test as parameter name. Not doing so will result in runtime error.
Joel Rea
Joel Rea
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 33
It should be noted that the “@” symbol is a syntax flag for parameters in the SQL CommandString, and is not part of the actual Parameter Name. The .Add methods will accept the name with or without the “@” symbol for convenience, but it’s not technically proper to include it since it isn’t really part of the name.
Halim Dahlan
Halim Dahlan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 9
Hi,

I'm new to ASP.NET and ADO.NET and was just trying out SqlParameter. How do I use SqlParameter to get a return code along with other parameters (input). I have a stored procedure that accepts some parameters to insert new record in a table.

I need to get the return code whether my insert execution is successful or not. So how do I get the return code?

Thanks in a million.
Antares686
Antares686
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: Moderators
Points: 27388 Visits: 802
Halim Dahlan (12/5/2007)
Hi,

I'm new to ASP.NET and ADO.NET and was just trying out SqlParameter. How do I use SqlParameter to get a return code along with other parameters (input). I have a stored procedure that accepts some parameters to insert new record in a table.

I need to get the return code whether my insert execution is successful or not. So how do I get the return code?

Thanks in a million.


Sorry I don't usually use myself but as I recall if you have a return code the parameter collection of the SqlCommand object will contain it in the 0 item or @ret item so something like this in C# (assuming your SqlCommand object is named cmdSQL)

cmdSQL.Parameters[0]...

or

cmdSQL.Parameters["@ret"]...



programmers
programmers
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 179
Try the following code, it should get you started:

SqlCommand cmd = new SqlCommand("usp_Table_Action");
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("@ReturnCode", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(param);
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search