SQLServerCentral / Discuss Content Posted by Raj Vasant / Article Discussions / Article Discussions by Author / Working With SqlParameter in .NET / Latest Posts

RE: Working With SqlParameter in .NET

Halim Dahlan — Wed, 05 Dec 2007 20:52:56 GMT

Hey thanks a lot [b]programmer[/b]. It worked.

RE: Working With SqlParameter in .NET

programmers — Wed, 05 Dec 2007 16:14:42 GMT

Try the following code, it should get you started:SqlCommand cmd = new SqlCommand("usp_Table_Action");cmd.CommandType = CommandType.StoredProcedure;SqlParameter param = new SqlParameter("@ReturnCode", SqlDbType.Int);param.Direction = ParameterDirection.ReturnValue;cmd.Parameters.Add(param);

RE: Working With SqlParameter in .NET

Antares686 — Wed, 05 Dec 2007 08:10:09 GMT

[quote][b]Halim Dahlan (12/5/2007)[/b][hr]Hi,I'm new to ASP.NET and ADO.NET and was just trying out SqlParameter. How do I use SqlParameter to get a return code along with other parameters (input). I have a stored procedure that accepts some parameters to insert new record in a table.I need to get the return code whether my insert execution is successful or not. So how do I get the return code?Thanks in a million.[/quote]Sorry I don't usually use myself but as I recall if you have a return code the parameter collection of the SqlCommand object will contain it in the 0 item or @ret item so something like this in C# (assuming your SqlCommand object is named cmdSQL)cmdSQL.Parameters[0]...orcmdSQL.Parameters["@ret"]...

RE: Working With SqlParameter in .NET

Halim Dahlan — Wed, 05 Dec 2007 03:35:30 GMT

Hi,I'm new to ASP.NET and ADO.NET and was just trying out SqlParameter. How do I use SqlParameter to get a return code along with other parameters (input). I have a stored procedure that accepts some parameters to insert new record in a table.I need to get the return code whether my insert execution is successful or not. So how do I get the return code?Thanks in a million.

RE: Working With SqlParameter in .NET

Joel Rea — Mon, 12 Nov 2007 10:18:47 GMT

It should be noted that the “@” symbol is a syntax flag for parameters in the SQL CommandString, and is [i]not[/i] part of the actual Parameter Name. The .Add methods will accept the name with or without the “@” symbol for convenience, but it’s not technically proper to include it since it isn’t really part of the name.

RE: Working With SqlParameter in .NET

Vasant Raj — Fri, 09 Nov 2007 23:12:13 GMT

[quote][b]scott mcnitt (11/9/2007)[/b][hr]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.[/quote]The order does not matter in .NET but the names do. So, if the sproc has a paramter @test, then from .NET it is required to give @test as parameter name. Not doing so will result in runtime error.

RE: Working With SqlParameter in .NET

Joel Rea — Fri, 09 Nov 2007 12:13:01 GMT

[quote][b]Anna (11/9/2007)[/b][hr]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[/quote]Not [i]quite[/i] true. The [i]specific overload[/i] of [b]Parameters.Add([/b][i]paramName As String[/i][b],[/b] [i]value As Object[/i][b])[/b] has been deprecated because it’s too easy for the [i]value[/i] parameter to be mistaken for an enumerated (and thus [i]Integer[/i] typed) [i]SqlType[/i] if, for instance, your desired value were an integer. [b].AddWithValue[/b] avoids this ambiguity because its second parameter will always be the value, not the type.All other overloads of the [b].Add[/b] method, including the nifty [b].Add([/b]…[b]).Value = [/b] [i]value[/i] syntax, remain as non-deprecated.

RE: Working With SqlParameter in .NET

Patrick JJS — Fri, 09 Nov 2007 12:02:33 GMT

Good Article Patrick JJS

RE: Working With SqlParameter in .NET

Antares686 — Fri, 09 Nov 2007 07:59:09 GMT

[quote][b]Roy Cross (5/9/2007)[/b][hr]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[/quote]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.

RE: Working With SqlParameter in .NET

Anna-449784 — Fri, 09 Nov 2007 07:16:00 GMT

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

RE: Working With SqlParameter in .NET

scott mcnitt — Fri, 09 Nov 2007 06:57:45 GMT

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.

RE: Working With SqlParameter in .NET

dave.baldwin — Fri, 09 Nov 2007 06:41:17 GMT

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

RE: Working With SqlParameter in .NET

programmers — Thu, 08 Nov 2007 23:01:35 GMT

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.

RE: Working With SqlParameter in .NET

Roy Cross — Wed, 09 May 2007 06:20:00 GMT

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

RE: Working With SqlParameter in .NET

Roy Cross — Tue, 08 May 2007 08:14:00 GMT

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

RE: Working With SqlParameter in .NET

pl-272086 — Wed, 29 Mar 2006 03:01:00 GMT

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

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

RE: Working With SqlParameter in .NET

Robert Varga-249427 — Wed, 22 Mar 2006 15:14:00 GMT

Hi Jose,

To set a null value you simply say:

Param.value = DBNull.Value

Cheers

Rob

RE: Working With SqlParameter in .NET

@puyinc — Wed, 22 Mar 2006 08:01:00 GMT

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.

RE: Working With SqlParameter in .NET

DJR-254141 — Wed, 22 Mar 2006 03:44:00 GMT

.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))) NextUsing 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 RodeckerPresident, RelevantAds

RE: Working With SqlParameter in .NET

ALZDBA — Wed, 22 Mar 2006 02:35:00 GMT

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

Working With SqlParameter in .NET

Vasant Raj — Tue, 21 Feb 2006 14:40:00 GMT

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