Working With SqlParameter in .NET

Question

Working With SqlParameter in .NET

Vasant Raj

SSCommitted

Points: 1835
More actions
February 21, 2006 at 2:40 pm

#170709

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/workingwithsqlparameterinnet.asp

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135364 More actions · Answer 1

also keep in mind when defining your parameters, the better you allign with your sproc-parameter-datatype or column-datatype, the better your commandobject will be served.

Tell the system what you know !

you may want to generate your parameters using e.g. this query :

VB.Net support from your SQLServer DBA

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

DJR-254141 SSC Enthusiast Points: 170 More actions · Answer 2

.Net 2005 introduces a much easier way of doing this... a SqlDataSource .

The SqlDataSource object allows you to retrieve the parameter data automatically. Here's how: Put a SqlDataSource object on the Form, select the SelectQuery property and click the "..." button. This brings up a window, specify the Stored Procedure name or Query, it will then generate the parameter data (names, type, direction) and even allow you to specify a default value (note that this isn't the SP default value).

Then in code you can simply write (vb.net):

sqlDataSource.SelectParameters("iMemberID").DefaultValue = 1

sqlDataSource.Select(new DataSourceSelectArguments())

This works well for calling a Stored Proc.

Here's an example if you are selecting a recordset or need the output variables:

Dim f_oRst As IEnumerable

sqlDataSource.SelectParameters("iMemberID").DefaultValue = 1

f_oRst = sqlDataSource.Select(new DataSourceSelectArguments())

For Each f_oRecord As Data.Common.DbDataRecord In f_oRst

Trace.Write("Record No:" & CStr(f_oRecord(0)))

Using the SqlDataSource for Select, Update, Delete, and Insert simplifies a lot of code, while still allowing you to modify the script that it produces (see below). I typically like to have more strict and explicit code, however the nice integration of the SqlDataSource with Data binding controls such as the DataGrid makes it very compelling. I was amazed to see that my code behind pages are nearly empty now for data entry pages.

More detail:

After using the GUI wizard, the SqlDataSource will generate the db call and paramter information such as:

asp:SqlDataSource ID="sqlDataBusinessContact" runat="server" ConnectionString = RelevantYellow

InsertCommand="pro_usp_BusinessContact_SET" InsertCommandType="StoredProcedure"

SelectCommand="pro_usp_BusinessContact_GET" SelectCommandType="StoredProcedure"

UpdateCommand="pro_usp_BusinessContact_SET" UpdateCommandType="StoredProcedure"

UpdateParameters

asp : Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32"

asp : Parameter Direction="InputOutput" Name="iContactId" Type="Int32"

asp : Parameter Name="iMemberID" Type="Int32"

asp : Parameter Name="Title" Type="String"

...

David Rodecker

President, RelevantAds

@puyinc Right there with Babe Points: 791 More actions · Answer 3

How can I pass null values using SqlParameter???

I need to pass null value to a DateTime field in the database but don't know how to do it.

I tried using DBNull and other values but none of them worked.

Kindest Regards,

@puy Inc

Robert Varga-249427 SSC Rookie Points: 39 More actions · Answer 4

Hi Jose,

To set a null value you simply say:

Param.value = DBNull.Value

Cheers

Rob

pl-272086 SSC Journeyman Points: 81 More actions · Answer 5

There is no need to separately instantiate SqlParameter, you can simply do this:

sqlCommand.Parameters.Add("@myID", SqlDbType.Int).Value = 1001;

Roy Cross Mr or Mrs. 500 Points: 534 More actions · Answer 6

This works great, but what about the direction? Does it not have to be specified?

Roy Cross Mr or Mrs. 500 Points: 534 More actions · Answer 7

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

programmers SSC Journeyman Points: 76 More actions · Answer 8

Another very important reason for using parameters that I didn't see covered in the article is that of security. Using parameters provides a very important layer of defence to your application against SQL Injection attacks.

dave.baldwin SSC Journeyman Points: 88 More actions · Answer 9

The first line in the second example isn't legal without an explicit conversion or cast.

I'm pretty sure Raj meant to write...

string myID = "1001";

instead of...

int myID = "1001"; // this is an obvious compiler error

scott mcnitt Ten Centuries Points: 1028 More actions · Answer 10

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 Grasshopper Points: 18 More actions · Answer 11

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 SSC Guru Points: 125444 More actions · Answer 12

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 Grasshopper Points: 13 More actions · Answer 13

Patrick JJS

Grasshopper

Points: 13

November 9, 2007 at 12:02 pm

#749960

Good Article

Patrick

JJS

Joel Rea Old Hand Points: 339 More actions · Answer 14

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.