Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Working With SqlParameter in .NET Expand / Collapse
Author
Message
Posted Friday, November 9, 2007 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 41, Visits: 371
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.
Post #420504
Posted Friday, November 9, 2007 7:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 1:58 PM
Points: 2, Visits: 59
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
Post #420510
Posted Friday, November 9, 2007 7:59 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
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.



Post #420544
Posted Friday, November 9, 2007 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 5, 2010 10:27 AM
Points: 3, Visits: 9
Good Article

Patrick
JJS
Post #420663
Posted Friday, November 9, 2007 12:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 27, 2010 10:56 AM
Points: 9, 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.
Post #420668
Posted Friday, November 9, 2007 11:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
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.
Post #420766
Posted Monday, November 12, 2007 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, December 27, 2010 10:56 AM
Points: 9, 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.
Post #421135
Posted Wednesday, December 5, 2007 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 8, 2010 11:05 PM
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.
Post #429708
Posted Wednesday, December 5, 2007 8:10 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 22, 2014 1:22 PM
Points: 8,369, Visits: 734
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"]...



Post #429794
Posted Wednesday, December 5, 2007 4:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 4:01 PM
Points: 6, Visits: 159
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);
Post #429958
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse