Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working With SqlParameter in .NET


Working With SqlParameter in .NET

Author
Message
Vasant Raj
Vasant Raj
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 137
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/workingwithsqlparameterinnet.asp
ALZDBA
ALZDBA
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9927 Visits: 8866

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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
DJR-254141
DJR-254141
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 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)))
Next


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
@puyinc
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 49

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
Robert Varga-249427
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: 1

Hi Jose,

To set a null value you simply say:

Param.value = DBNull.Value

Cheers

Rob


pl-272086
pl-272086
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1

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

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


Roy Cross
Roy Cross
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 33
This works great, but what about the direction? Does it not have to be specified?
Roy Cross
Roy Cross
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 33
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
programmers
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 179
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
dave.baldwin
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 38
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
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