Working With SqlParameter in .NET

  • Vasant Raj

    SSCommitted

    Points: 1835

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

  • Johan Bijnens

    SSC Guru

    Points: 134286

    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[/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[/url] :alien: but most of the time this is me :hehe:

  • DJR-254141

    SSC Enthusiast

    Points: 170

    .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

    Right there with Babe

    Points: 791

    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

    Hi Jose,

    To set a null value you simply say:

    Param.value = DBNull.Value

    Cheers

    Rob

  • pl-272086

    SSC Journeyman

    Points: 81

    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

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

  • Roy Cross

    Mr or Mrs. 500

    Points: 534

    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

    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

    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

    SSC Eights!

    Points: 998

    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

    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

    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

    Good Article

    Patrick

    JJS

  • Joel Rea

    Old Hand

    Points: 339

    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.

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

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