Working With SqlParameter in .NET

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

  • 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

  • .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

  • 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

  • Hi Jose,

    To set a null value you simply say:

    Param.value = DBNull.Value

    Cheers

    Rob

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

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

     

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

  • 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

  • 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.

  • 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

  • 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.

  • 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

  • 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.

  • Good Article

    Patrick

    JJS

  • 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 20 total)

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