melo 唐

SP:

ALTER PROC [dbo].[usp_showLowInventory_04]
@multiple int --@total int output
as

--這裡select 會直接將找到資料回傳給ADO.NET
select 供應商名稱,類別名稱,產品編號,產品名稱,庫存量,安全存量,
(安全存量*@multiple - 庫存量) as 建議進貨量
from 產品資料 as P, 產品類別 as C, 供應商 as S
where P.類別編號 = C.類別編號 and
P.供應商編號 = S.供應商編號 and
庫存量 < 安全存量
order by S.供應商編號

前端程式:

<form id="form1" runat="server">
<div>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Button"
onclick="Button1_Click" />
<br />
<br />
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
<br />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
<br />

</div>
</form>

後端程式:

public partial class StoredProcedure : System.Web.UI.Page
{

protected void Page_Load(object sender, EventArgs e)
{

}


protected void Button1_Click(object sender, EventArgs e)
{
//前端textbox輸入一數字,此數字當做初始傳遞參數給對應方法
DataSet dsBudgetInfo = QueryBudgetInfo(Convert.ToInt32(TextBox1.Text));

//step4 顯示資料,接收回傳之dataset
if (dsBudgetInfo.Tables[0].Rows.Count > 0)
{
TextBox2.Text = dsBudgetInfo.Tables[0].Rows[0]["供應商名稱"].ToString();
TextBox3.Text = dsBudgetInfo.Tables[0].Rows[0]["類別名稱"].ToString();
TextBox4.Text = dsBudgetInfo.Tables[0].Rows[0]["產品編號"].ToString();
}

GridView1.DataSource = dsBudgetInfo;
GridView1.DataBind();
}

public DataSet QueryBudgetInfo(int multipleInput)
{
//step2 宣告sqlcommand 和 要執行的SP:usp_showLowInventory_04
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_showLowInventory_04";

//Step2 設定輸入參數
cmd.Parameters.Clear();
//傳入SP之參數,注意"multiple"要和SP中傳入參數同樣名稱,不然對應不到
cmd.Parameters.AddWithValue("multiple", multipleInput);

DataSet dsBugetInfo = QuerySqlCommand(cmd);
return dsBugetInfo;
}

public DataSet QuerySqlCommand(SqlCommand cmd)
{
//Step1 打開連線字串
SqlConnection Conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["CH13範例資料庫ConnectionString"].ConnectionString);
Conn.Open();
cmd.Connection = Conn;

//Step3 值型SQLCommand由DataAdapter放入DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
//利用dataAdapter 塞入資料到 dataset中的datatable "DataTable"
da.Fill(ds, "DataTable");
da.Dispose();
//將dataset 傳回 step 4
return ds;
}

}

執行結果: