|
 |
aspx thread: Stored Proc + Parameters
Message #1 by "Alvin Ling" <alvin.ling@i...> on Tue, 29 Jan 2002 14:22:49 -0500
|
|
In classic ASP, adding parameters to a command object/stored procedure
was a pretty clean one-liner:
With objCmd
.ActiveConnection = objConn
.CommandText = "my_stored_proc"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@param1", adVarChar,
adParamInput, 30, strParam1)
.Parameters.Append .CreateParameter("@param2", adVarChar,
adParamInput, 20, strParam2)
.Parameters.Append .CreateParameter("@output1", adVarChar,
adParamOutput, 20)
.Execute()
strOutput1 = .Parameters("@output1")
End With
Can someone help with a .NET equivalent?
I've been searching through the .NET documentation but haven't found a
simple way of specifying the parameter, datatype, direction and value
(for input variables) like in classic ASP.
Alvin
Message #2 by "Albert Davis" <albertdavis@h...> on Tue, 29 Jan 2002 14:56:27 -0500
|
|
MSDN has an article about using the Command object for executing a stored
proc @ (watch for broken link):
http://www.msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp
Al
>From: "Alvin Ling" <alvin.ling@i...>
>Reply-To: "ASP+" <aspx@p...>
>To: "ASP+" <aspx@p...>
>Subject: [aspx] Stored Proc + Parameters
>Date: Tue, 29 Jan 2002 14:22:49 -0500
>
>In classic ASP, adding parameters to a command object/stored procedure
>was a pretty clean one-liner:
>
>With objCmd
> .ActiveConnection = objConn
> .CommandText = "my_stored_proc"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@param1", adVarChar,
>adParamInput, 30, strParam1)
> .Parameters.Append .CreateParameter("@param2", adVarChar,
>adParamInput, 20, strParam2)
> .Parameters.Append .CreateParameter("@output1", adVarChar,
>adParamOutput, 20)
> .Execute()
> strOutput1 = .Parameters("@output1")
>End With
>
>Can someone help with a .NET equivalent?
>
>I've been searching through the .NET documentation but haven't found a
>simple way of specifying the parameter, datatype, direction and value
>(for input variables) like in classic ASP.
>
>
>Alvin
>
>
>
>
>
_________________________________________________________________
Join the world?s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
Message #3 by "Graham Dobson" <grahamdo@a...> on Tue, 29 Jan 2002 15:00:58 -0500
|
|
This is actually stolen from Donny Mack and Doug Seven's Programming Data
Driven Web Applications with ASP.NET
I'm gonna use C# cos it's sooo much cooler than vb
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
void Page_Load()
{
SqlConnection dotNetConnection;
SqlCommand dotNetCommand;
SqlParameter dotNetParam;
dotNetConnection = new
SqlConnection("Server=kstation;database=Northwind;uid=sa;pwd=;");
dotNetCommand = new
SqlCommand("GetCustomerBycountry",dotNetConnection);
dotNetCommand.CommandType = CommandType.StoredProcedure;
dotNetParam = dotNetCommand.Parameters.Add(new
SqlParameter("@country",SqlDbType.VarChar,50));
dotNetParam.Direction = ParameterDirection.Input;
dotNetParam.Value = "USA";
dotNetConnection.Open();
SqlDataReader dotNetDataReader = dotNetCommand.ExecuteReader();
dotNetDataGrid.DataSource = dotNetDataReader;
dotNetDataGrid.DataBind();
dotNetConnection.Close();
}
</script>
<html>
<head>
<title>Donny and Dougs ASP.NET code</title>
</head>
<body bgcolor="Ivory" text="Indigo">
<form runat="server" method="post">
<asp:DataGrid runat="server" id="dotNetDataGrid"/>
</form>
</body>
</html>
You have to create a new stored procedure in SQL Server enterprise manager
CREATE PROCEDURE [GetCustomerByCountry]
@country varchar (50)
AS
SELECT * FROM Customers WHERE Country = @country
Tested. It works. Rather ugly web page but demonstrates the use of
paramaterized stored procedures under ADO.NET.
----- Original Message -----
From: "Alvin Ling" <alvin.ling@i...>
To: "ASP+" <aspx@p...>
Sent: Tuesday, January 29, 2002 2:22 PM
Subject: [aspx] Stored Proc + Parameters
> In classic ASP, adding parameters to a command object/stored procedure
> was a pretty clean one-liner:
>
> With objCmd
> .ActiveConnection = objConn
> .CommandText = "my_stored_proc"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@param1", adVarChar,
> adParamInput, 30, strParam1)
> .Parameters.Append .CreateParameter("@param2", adVarChar,
> adParamInput, 20, strParam2)
> .Parameters.Append .CreateParameter("@output1", adVarChar,
> adParamOutput, 20)
> .Execute()
> strOutput1 = .Parameters("@output1")
> End With
>
> Can someone help with a .NET equivalent?
>
> I've been searching through the .NET documentation but haven't found a
> simple way of specifying the parameter, datatype, direction and value
> (for input variables) like in classic ASP.
>
>
> Alvin
>
>
>
>
>
>
Message #4 by "D" <yezh99@e...> on Thu, 31 Jan 2002 16:50:25
|
|
BTW, u can look at the IBuySpy.com Store source code. it is a very good
example for how to use stored procedure.
Good luck,
|
|
|
View Cart
