web| stored Procedures | pages | Stored procedures first create stored procedures, in the following format:
creat PROCEDURE sp_customersbystate @region nvarchar (15)
As
Select Customerid,companyname from Customers
where region= @region ORDER by CompanyName
Return
Write program code:
In the C # code, we'll use the new class, System.Data.SqlClient.Parameter. Objects of this class are designed to represent parameters in stored procedures, so constructors need to know the name, data type, and size of the argument being discussed.
<%@ Import namespace= "System.Data"%>
<%@ Import namespace= "System.Data.SqlClient"%>
<body>
<form runat= "Server" method= "POST" >
Enter a State Code:
<asp:textbox id= "txtregion" runat= "Server"/>
<asp:button id= "btnsubmit" runat= "Server"
text= "Search"/>
<br/><br/>
<asp:datagrid id= "Dgoutput" runat= "Server"/>
</form>
</body>
<script language= "C #" runat= "Server" >
private void Submit (object sender, EventArgs e)
{
String strconnection = "Server=224numeca;database=northwind;user Id=sa;password=sa";
SqlConnection objconnection = new SqlConnection (strconnection);
SqlCommand objcommand = new SqlCommand ("Sp_customersbystate", objconnection);
Objcommand.commandtype = CommandType.StoredProcedure;
SqlParameter objparameter = new SqlParameter ("@region", SqlDbType.NVarChar, 15);
/* Creates a new parameter named @region and declared as Nvchar (15) that matches the declaration in the stored procedure. The second parameter of this version of the constructor always system.data.sqlDbType the members of the enumeration, which has 24 members, representing all the data types that you might need. */
OBJCOMMAND.PARAMETERS.ADD (Objparameter);
/* The second line parameter is added to the parameter collection of the command object, often forgetting the operation.
Objparameter.direction = ParameterDirection.Input;
/* Set the direction property of the Parameter object to determine whether it will be used to pass information to a stored procedure, or to receive information from it. ParameterDirection.Input is actually the default value for this property, but it is helpful to put it into code from the standpoint of maintenance and readability. */
Objparameter.value = Txtregion.text;
/* We set the Value property of the parameter to the Text property of the Txtregion text box. */
Objconnection.open ();
Objconnection.open ();
Dgoutput.datasource = Objcommand.executereader ();
Dgoutput.databind ();
Objconnection.close ();
}
</script>
