C#調用存儲過程的通用類

using?System;
using?System.Collections.Generic;
using?System.Text;
using?System.Data.SqlClient;
using?System.Collections;
using?System.Data;

//?摘要：數據訪問助手。
//?作者：ZhiQiao
//?日期：2008/07/02

namespace?ZhiQiao.DataAccessHelper
{
????//?存儲過程調用助手。
????public?class?StoreProcedure
????{
????????//?存儲過程名稱。
????????private?string?_name;
????????//?數據庫連接字符串。
????????private?string?_conStr;

????????//?構造函數
?????????//?sprocName:?存儲過程名稱；
?????????//?conStr:?數據庫連接字符串。
????????public?StoreProcedure(string?sprocName,?string?conStr)?{
????????????_conStr?=?conStr;
????????????_name?=?sprocName;
????????}
????????
????????//??執行存儲過程，不返回值。
?????????//??paraValues:?參數值列表。
?????????//??return:?void
????????public?void?ExecuteNoQuery(params?object[]?paraValues)?{
????????????using?(SqlConnection?con?=?new?SqlConnection(_conStr))?{
????????????????SqlCommand?comm?=?new?SqlCommand(_name,?con);
????????????????comm.CommandType?=?CommandType.StoredProcedure;

????????????????AddInParaValues(comm,?paraValues);

????????????????con.Open();
????????????????comm.ExecuteNonQuery();
????????????????con.Close();
????????????}
????????}
????????
????????//?執行存儲過程返回一個表。
?????????//?paraValues:?參數值列表。
?????????//?return:?DataTable
????????public?DataTable?ExecuteDataTable(params?object[]?paraValues)?{
????????????SqlCommand?comm?=?new?SqlCommand(_name,?new?SqlConnection(_conStr));
????????????comm.CommandType?=?CommandType.StoredProcedure;
????????????AddInParaValues(comm,?paraValues);

????????????SqlDataAdapter?sda?=?new?SqlDataAdapter(comm);
????????????DataTable?dt?=?new?DataTable();
????????????sda.Fill(dt);

????????????return?dt;
????????}
???????
????????//?執行存儲過程，返回SqlDataReader對象，
?????????//?在SqlDataReader對象關閉的同時，數據庫連接自動關閉。
?????????//?paraValues:?要傳遞給給存儲過程的參數值類表。
?????????//?return:?SqlDataReader
????????public?SqlDataReader?ExecuteDataReader(params?object[]?paraValues)?{
????????????SqlConnection?con?=?new?SqlConnection(_conStr);
????????????SqlCommand?comm?=?new?SqlCommand(_name,?con);
????????????comm.CommandType?=?CommandType.StoredProcedure;
????????????AddInParaValues(comm,?paraValues);
????????????con.Open();
????????????return?comm.ExecuteReader(CommandBehavior.CloseConnection);
????????}

????????//?獲取存儲過程的參數列表。
????????private?ArrayList?GetParas()?{
????????????SqlCommand?comm?=?new?SqlCommand("dbo.sp_sproc_columns_90",?
???????????????????????new?SqlConnection(_conStr));
????????????comm.CommandType?=?CommandType.StoredProcedure;
????????????comm.Parameters.AddWithValue("@procedure_name",?(object)_name);
????????????SqlDataAdapter?sda?=?new?SqlDataAdapter(comm);

????????????DataTable?dt?=?new?DataTable();
????????????sda.Fill(dt);

????????????ArrayList?al?=?new?ArrayList();
????????????for?(int?i?=?0;?i?<?dt.Rows.Count;?i++)?{
????????????????al.Add(dt.Rows[i][3].ToString());
????????????}
????????????return?al;
????????}

????????//?為?SqlCommand?添加參數及賦值。
????????private?void?AddInParaValues(SqlCommand?comm,?params?object[]?paraValues)?{
????????????comm.Parameters.Add(new?SqlParameter("@RETURN_VALUE",?SqlDbType.Int));
????????????comm.Parameters["@RETURN_VALUE"].Direction?=?
???????????????????????????ParameterDirection.ReturnValue;
????????????if?(paraValues?!=?null)?{
????????????????ArrayList?al?=?GetParas();
????????????????for?(int?i?=?0;?i?<?paraValues.Length;?i++)?{
????????????????????comm.Parameters.AddWithValue(al[i?+?1].ToString(),?
?????????????????????????paraValues[i]);
????????????????}
????????????}
????????}
????}
}