Thought of Genius

假設我們已經建立一個預存程序如下(四個輸入參數、一個輸出參數，執行查詢，並回傳查詢筆數)：
<br />-- Create store procedure<br />CREATE PROCEDURE usp_ComplexQuery<br /> @CustomerID nchar(5),<br /> @CompanyName nvarchar(40),<br /> @City nvarchar (15),<br /> @Country nvarchar(15),<br /> @STR nvarchar(1024) OUTPUT<br />AS<br /> /*.....procedure content.....*/<br /> SELECT * FROM Customers<br /> RETURN @@rowcount<br />GO<br />
我們可以撰寫下列程式碼來執行預存程序並處理參數：
<br />public class NorthwindAdapter {<br /> private SqlConnection m_Connection = new SqlConnection(Properties.Settings.Default.NorthwindConStr);<br /> private string _SqlStr;<br /> private int _ReturnValue;<br /><br /> public string SqlStr {<br /> get { return this._SqlStr; }<br /> set { this._SqlStr = value; }<br /> }<br /> public int ReturnValue {<br /> get { return this._ReturnValue; }<br /> set { this._ReturnValue = value; }<br /> }<br /><br /> public DataTable usp_ComplexQuery(string customerid, string customername, string city, string country) {<br /> string cmdstr = @"usp_ComplexQuery";<br /> using (SqlDataAdapter da = new SqlDataAdapter(cmdstr, m_Connection)) {<br /> da.SelectCommand.CommandType = CommandType.StoredProcedure;<br /> da.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5).Value = customerid;<br /> da.SelectCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40).Value = customername;<br /> da.SelectCommand.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value = city;<br /> da.SelectCommand.Parameters.Add("@Country", SqlDbType.NVarChar, 15).Value = country;<br /> da.SelectCommand.Parameters.Add("@STR", SqlDbType.NVarChar, 1024).Direction = ParameterDirection.Output;<br /> da.SelectCommand.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;<br /> DataTable dt = new DataTable();<br /> da.Fill(dt);<br /> this.SqlStr = da.SelectCommand.Parameters["@STR"].Value.ToString();<br /> this.ReturnValue = Convert.ToInt32(da.SelectCommand.Parameters["@ReturnValue"].Value);<br /> return dt;<br /> }<br /> }<br />}<br />