C#调用存储过程的通用方法

using System;
using System.ComponentModel;
using System.Collections;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.SessionState;
using System.Web;


namespace Utilities
{

    /// <summary> 
    /// DbAccess类,即进行数据库访问时需要调用的类 
    /// </summary> 
    public class DbaseBS
    {
        //取出数据库连接字符串
        public static readonly string conStr = ConfigurationSettings.AppSettings["ConnectionString"];

        /// <summary>
        /// 得到数据库连接对象
        /// </summary>
        /// <returns>数据库连接对象</returns>
        public static SqlConnection GetConObject()
        {
            return new SqlConnection(conStr);
        }

        /// <summary>
        /// 执行操作数据库的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>存储过程执行后所影响的行数</returns>
        public static int ExecuteNonQuery(string procName)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection con = GetConObject())
            {
                CreateCommand(cmd, con, null, procName, null);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                return val;
            }
        }

        /// <summary>
        /// 执行操作数据库的存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cmdParms">存储过程所使用的参数</param>
        /// <returns>存储过程执行后所影响的行数</returns>
        public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection con = GetConObject())
            {
                CreateCommand(cmd, con, null, procName, cmdParms);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
                return val;
            }
        }


        /// <summary>
        /// 执行读数据集操作，以DataReader的形式返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cmdParms">存储过程所使用的参数</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = GetConObject();

            try
            {
                CreateCommand(cmd, con, null, procName, cmdParms);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                Close(con);
                throw ex;
            }
        }

        /// <summary>
        /// 执行读数据集操作，以DataReader的形式返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader ExecuteReader(string procName)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = GetConObject();

            try
            {
                CreateCommand(cmd, con, null, procName, null);
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return dr;
            }
            catch (Exception ex)
            {
                cmd.Dispose();
                Close(con);
                throw ex;
            }
        }

        /// <summary>
        /// 执行读数据集操作，以DataSet的形式返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cmdParms">存储过程所使用的参数</param>
        /// <returns>DataSet对象</returns>
        public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = GetConObject();
            DataSet ds = new DataSet();

            try
            {
                CreateCommand(cmd, con, null, procName, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                Close(con);
            }
        }


        /// <summary>
        /// 执行读数据集操作，以DataSet的形式返回
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataReader对象</returns>
        public static DataSet ExecuteDataSet(string procName)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection con = GetConObject();
            DataSet ds = new DataSet();

            try
            {
                CreateCommand(cmd, con, null, procName, null);
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                Close(con);
            }
        }

        /// <summary>
        /// 创建数据库执行命令
        /// </summary>
        /// <param name="cmd">数据库执行命令对象</param>
        /// <param name="con">数据库连接对象</param>
        /// <param name="trans">数据库事务对象</param>
        /// <param name="procName">存储过程名称</param>
        /// <param name="cmdParms">存储过程所使用的参数数组</param>
        public static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms)
        {
            if (con.State != ConnectionState.Open)
                con.Open();

            cmd.Connection = con;
            cmd.CommandText = procName;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            cmd.CommandType = CommandType.StoredProcedure;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <param name="myConn">数据库连接对象</param>
        public static void Close(SqlConnection myConn)
        {
            if ((myConn != null) && (myConn.State == ConnectionState.Open))
            {
                myConn.Close();
            }
        }

    }
}