📄 storedprocedure.aspx
字号:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
<body>
<SCRIPT language="C#" runat="Server">
SqlConnection conn;
void Page_Load(Object src, EventArgs E)
{
conn = new SqlConnection("data source=(local)\\NETSDK;initial catalog=conf;integrated security=SSPI;persist security info=True");
displayCategories();
displayProducts();
displayOrderCount();
}
//the ProductCategoryList storedprocedure has no parameters and returns records
//display the returned records in a datagrid
void displayCategories()
{
SqlDataAdapter cmd;
DataSet ds;
//call the ProductCategoryList stored procedure
cmd = new SqlDataAdapter("ProductCategoryList", conn);
cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
//fill dataset with results of stored procedure
ds = new DataSet();
cmd.Fill(ds, "Categories");
//bind dataset to datagrid
dgCategories.DataSource = ds.Tables["Categories"].DefaultView;
dgCategories.DataBind();
}
//the ProductsByCategory storedprocedure has an input parameter which is the categoryID
//asnd returns all items from that category
//read the input parameter from a text box and display the results in a datagrid
void displayProducts()
{
SqlDataAdapter cmd;
DataSet ds;
SqlParameter workParam;
//call the ProductCategory stored procedure
cmd = new SqlDataAdapter("ProductsByCategory", conn);
cmd.SelectCommand.CommandType = CommandType.StoredProcedure;
//add the CategoryID input parameter from the txtCatID textbox
workParam = new SqlParameter("@CategoryID", SqlDbType.Int);
workParam.Direction = ParameterDirection.Input;
workParam.Value = Convert.ToInt32(txtCatID.Text,10);
cmd.SelectCommand.Parameters.Add (workParam);
//run the stored procedure and fill a dataset with the results
ds = new DataSet();
cmd.Fill(ds, "Products");
//bind the dataset to a datagrid
dgProducts.DataSource = ds.Tables["Products"].DefaultView;
dgProducts.DataBind();
}
//the OrdersCount storedprocedure has an input parameter which is the customerID
//and an output parameter which is the number of orders for that customer.
//read the input parameter from a text box and display the output value in a label
void displayOrderCount()
{
SqlCommand cmd;
SqlParameter workParam;
//call OrdersCount stored procedure
cmd = new SqlCommand("OrdersCount",conn);
cmd.CommandType = CommandType.StoredProcedure;
//add the CustomerID input parameter from txtCustID textbox
workParam = new SqlParameter("@CustomerID", SqlDbType.Int);
workParam.Direction = ParameterDirection.Input;
workParam.Value = Convert.ToInt32(txtCustID.Text,10);
cmd.Parameters.Add(workParam);
//add the ItemCount output parameter
workParam = new SqlParameter("@ItemCount", SqlDbType.Int);
workParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(workParam);
//open the connection so you can call execute on the SelectCommand
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
//display the output parameter in a SPAN element
spnOrderCount.InnerHtml = cmd.Parameters["@ItemCount"].Value.ToString();
}
</SCRIPT>
<h2>Categories</h2>
<asp:datagrid id="dgCategories" runat="server"/>
<br><br>
<form runat="server">
<P>Enter category: <asp:textbox id="txtCatID" runat="server" Text="14"/>
<asp:button runat="server" text="Get Products"/>
<h2>Products in Category</h2>
<P><asp:datagrid id="dgProducts" runat="server"/>
<br><br>
<h2>Number of Current Orders for a Customer</h2>
<P>Customer ID <asp:textbox id="txtCustID" runat="server" Text="31"/>
<asp:button runat="server" text="Get Order Count"/>
<br>has <span id="spnOrderCount" runat="server"></span> outstanding order(s)
</form>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -