|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Tuesday, August 25, 2009 9:35 AM
Points: 198,
Visits: 113
|
|
|
|
|
|
SSCarpal Tunnel
Group: General Forum Members
Last Login: Yesterday @ 4:47 AM
Points: 4,752,
Visits: 3,529
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, February 21, 2007 2:39 PM
Points: 6,
Visits: 1
|
|
.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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, June 01, 2009 3:04 AM
Points: 127,
Visits: 47
|
|
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
|
|
|
|
|
Forum 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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, May 03, 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;
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, October 29, 2009 11:54 AM
Points: 12,
Visits: 9
|
|
| This works great, but what about the direction? Does it not have to be specified?
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Thursday, October 29, 2009 11:54 AM
Points: 12,
Visits: 9
|
|
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
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Sunday, September 06, 2009 11:28 PM
Points: 6,
Visits: 55
|
|
| 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.
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Wednesday, April 02, 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
|
|
|
|
