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 Tuesday, February 21, 2006 2:40 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
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/workingwithsqlparameterinnet.asp
Post #260410
Posted Wednesday, March 22, 2006 2:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 7,004, Visits: 8,448

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #267489
Posted Wednesday, March 22, 2006 3:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 13, 2011 4:53 PM
Points: 6, 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
Post #267500
Posted Wednesday, March 22, 2006 8:01 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 14, 2010 12:32 AM
Points: 127, 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
Post #267586
Posted Wednesday, March 22, 2006 3:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 22, 2006 2:20 PM
Points: 1, Visits: 1

Hi Jose,

To set a null value you simply say:

Param.value = DBNull.Value

Cheers

Rob

Post #267767
Posted Wednesday, March 29, 2006 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 3, 2006 5:51 AM
Points: 3, Visits: 1

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

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

 

Post #269283
Posted Tuesday, May 8, 2007 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 21, 2014 9:34 AM
Points: 12, Visits: 32
This works great, but what about the direction?  Does it not have to be specified?
Post #364025
Posted Wednesday, May 9, 2007 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 21, 2014 9:34 AM
Points: 12, Visits: 32
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
Post #364330
Posted Thursday, November 8, 2007 11:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 5:57 PM
Points: 6, Visits: 160
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.
Post #420370
Posted Friday, November 9, 2007 6:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 2, 2008 6:35 AM
Points: 24, 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





Post #420497
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse