SqlHelper基于存储过程

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using  System.Windows;

namespace DY.Common
{
    /// <summary>
    /// SqlServer 数据库操作类
    /// </summary>

    public class SqlClientDB
    {
        public static string SqlConStr; //数据库连接字符

        #region 无参数构造函数
        public SqlClientDB()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        #endregion

        #region 返回连接字符串
        public static SqlConnection SqlConn()
        {
            SqlConnection conn = new SqlConnection(SqlConStr);
            return conn;
        }
        #endregion

        #region 执行存储过程，返回整形，无参数
        /// <summary>
		/// 执行存储过程，包括Insert,Update,Delete，无参数
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <returns></returns>
		public static int ExecProcReturn(string procName)
        {
            return ExecProcReturn(procName, null);
        }
        #endregion

        #region  执行存储过程，包括Insert,Update,Delete，返回整形，有参数
        /// <summary>
		///  执行存储过程，包括Insert,Update,Delete，有参数
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">参数集</param>
		/// <returns></returns>
		public static int ExecProcReturn(string procName, SqlPrams prams)
        {
            SqlConnection conn = SqlConn();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = CreateCommand(procName, prams, conn);
                cmd.CommandTimeout = 80000;
                int count = cmd.ExecuteNonQuery();
                return count;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示");
                Err.GetErrPic(ex.Message);
                return 0;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，包括Insert，返回整形，有输出参数
        /// <summary>
		///  执行存储过程，包括Insert,Update,Delete，有输出参数
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">参数集</param>
		/// <returns></returns>
		public static int ExecProcReturn(string procName, SqlPrams prams, out string ID)
        {
            SqlConnection conn = SqlConn();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand cmd = CreateCommand(procName, prams, conn);
                cmd.CommandTimeout = 80000;
                int count = cmd.ExecuteNonQuery();
                ID = cmd.Parameters["@ID"].Value.ToString();
                return count;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示",
                    MessageBoxButton.OK,MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                ID = "";
                return 0;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，返回数据集，需要传递参数集
        /// <summary>
		/// 执行存储过程，返回数据集，需要传递参数集
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">参数集</param>
		/// <param name="TableName">返回的表名称（自定义）</param>
		/// <returns>返回数据集</returns>
		public static DataSet ExecProcReturnDataSet(string procName, SqlPrams prams, string TableName)
        {
            SqlConnection conn = SqlConn();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand Cmd = CreateCommand(procName, prams, conn);
                Cmd.CommandTimeout = 80000;
                SqlDataAdapter Da = new SqlDataAdapter(Cmd);
                DataSet ds = new DataSet();
                Da.Fill(ds, TableName);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示",
              MessageBoxButton.OK, MessageBoxImage.Error);
                //MessageBox.Show(ex.ToString(), "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，分页返回数据集
        /// <summary>
		/// 执行存储过程，返回数据集
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">参数集</param>
		/// <param name="startRecord">要查询的开始记录位置</param>
		/// <param name="maxRecords">要查询的最多记录数</param>
		/// <param name="TableName">返回数据集中的表名称（自定义）</param>
		/// <returns>数据集</returns>
		public static DataSet ExecProcReturnDataSet(string procName, SqlPrams prams, int startRecord, int maxRecords, string TableName)
        {
            SqlConnection conn = SqlConn();
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                SqlCommand Cmd = CreateCommand(procName, prams, conn);
                Cmd.CommandTimeout = 80000;
                SqlDataAdapter Da = new SqlDataAdapter(Cmd);
                DataSet ds = new DataSet();
                Da.Fill(ds, startRecord, maxRecords, TableName);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                //MessageBox.Show(ex.ToString(), "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行SQL语句，分页返回数据集
        /// <summary>
        /// 执行SQL语句，分页返回数据集
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
		public static DataSet ExecSqlReturnDataSet(string Sql, int startRecord, int maxRecords, string TableName)
        {
            SqlConnection con = SqlConn();
            try
            {
                SqlDataAdapter Da = new SqlDataAdapter(Sql, con);
                DataSet ds = new DataSet();
                Da.Fill(ds, startRecord, maxRecords, TableName);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 语句：" + Sql, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，返回数据集（带参数）
        /// <summary>
		/// 执行存储过程，返回数据集
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">参数集</param>
		/// <returns>数据集</returns>
		public static DataSet ExecProcReturnDataSet(string procName, SqlPrams prams)
        {
            SqlConnection conn = SqlConn();
            try
            {
                SqlCommand Cmd = CreateCommand(procName, prams, conn);
                Cmd.CommandTimeout = 80000;
                SqlDataAdapter Da = new SqlDataAdapter(Cmd);
                DataSet ds = new DataSet();
                Da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region  执行存储过程，返回数据表（带参数）
        /// <summary>
		/// 执行存储过程，返回数据表
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <param name="prams">存储过程参数集</param>
		/// <returns>返回表</returns>
		public static DataTable ExecProcReturnDataTable(string procName, SqlPrams prams)
        {
            SqlConnection conn = SqlConn();
            try
            {
                SqlCommand Cmd = CreateCommand(procName, prams, conn);
                Cmd.CommandTimeout = 80000;
                SqlDataAdapter Da = new SqlDataAdapter(Cmd);
                DataTable dt = new DataTable();
                Da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 存储过程名：" + procName, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataTable();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，返回数据表（不带参数）
        /// <summary>
		/// 执行存储过程，返回数据表
		/// </summary>
		/// <param name="procName">存储过程名称</param>
		/// <returns>返回数据表</returns>
		public static DataTable ExecProcReturnDataTable(string procName)
        {
            SqlConnection conn = SqlConn();
            try
            {
                SqlCommand Cmd = CreateCommand(procName, null, conn);
                Cmd.CommandTimeout = 80000;
                SqlDataAdapter Da = new SqlDataAdapter(Cmd);
                DataTable dt = new DataTable();
                Da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r " + ex.Source, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataTable();
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion

        #region 执行存储过程，返回数据集（不带参数）
        /// <summary>
        /// 执行存储过程，返回数据集
        /// </summary>
        /// <param name="procName">存储过程参数集</param>
        /// <returns>数据集</returns>
        public static DataSet ExecProcReturnDataSet(string procName)
        {
            return ExecProcReturnDataSet(procName, null);
        }
        #endregion

        #region 执行单条SQL语句，返回整形值，成功返回1，失败返回0
        /// <summary>
        /// 执行单条SQL语句，返回整形值，成功返回1，失败返回0
        /// </summary>
        /// <param name="Sql">字符串</param>
        /// <returns>返回int</returns>
		public static int ExecSqlReturn(string Sql)
        {
            SqlConnection con = SqlConn();
            try
            {
                SqlCommand Cmd = new SqlCommand(Sql, con);
                Cmd.CommandTimeout = 80000;
                con.Open();
                int count = Cmd.ExecuteNonQuery();
                return count;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 语句：" + Sql, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return 0;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 执行一组SQL语句，并封装到事务里，返回布尔值，成功返回true,失败返回false
        /// <summary>
        /// 执行一组SQL语句，并封装到事务里，返回布尔值，执行成功返回true,执行失败返回false
        /// </summary>
        /// <param name="Sql">字符串数组</param>
        /// <returns>返回布尔值</returns>
		public static bool ExecSqlReturn(string[] Sql)
        {
            SqlConnection con = SqlConn();
            SqlTransaction trans = null;
            try
            {
                con.Open();
                SqlCommand Cmd = con.CreateCommand();
                trans = con.BeginTransaction();
                Cmd.Connection = con;
                Cmd.Transaction = trans;
                Cmd.CommandTimeout = 80000;

                for (int i = 0; i < Sql.Length; i++)
                {
                    if (Sql[i] != "")  //从表格中生成的SQL语句，由于某些行不满足条件时数据元素可能为空
                    {
                        Cmd.CommandText = Sql[i];
                        Cmd.ExecuteNonQuery();
                    }
                }

                trans.Commit();

                return true;
            }
            catch (Exception e)
            {
                try
                {
                    trans.Rollback();
                    MessageBox.Show("错误原因:" + e.Message.Trim(), "系统提示", MessageBoxButton.OK, MessageBoxImage.Asterisk);
                    return false;
                }
                catch
                {
                    MessageBox.Show("错误原因:" + e.Message.Trim(), "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                    return false;
                }
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 执行SQL语句，返回数据集（参数带表名）
        /// <summary>
        /// 执行SQL语句，返回数据集（参数带表名）
        /// </summary>
        /// <param name="Sql">SQL语句</param>
        /// <param name="TableName">表名</param>
        /// <returns>数据集</returns>
		public static DataSet ExecSqlReturnDataSet(string Sql, string TableName)
        {
            SqlConnection con = SqlConn();
            try
            {
                SqlDataAdapter Da = new SqlDataAdapter(Sql, con);
                DataSet ds = new DataSet();
                Da.Fill(ds, TableName);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 语句：" + Sql, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 执行SQL语句，返回数据集
        /// <summary>
        /// 执行SQL语句，返回数据集
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
		public static DataSet ExecSqlReturnDataSet(string Sql)
        {
            SqlConnection con = SqlConn();
            try
            {
                SqlDataAdapter Da = new SqlDataAdapter(Sql, con);
                DataSet ds = new DataSet();
                Da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 语句：" + Sql, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataSet();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }
        #endregion

        #region 执行SQL语句，返回数据表
        /// <summary>
        /// 执行SQL语句，返回数据表
        /// </summary>
        /// <param name="Sql"></param>
        /// <returns></returns>
		public static DataTable ExecSqlReturnDataTable(string Sql)
        {
            SqlConnection con = SqlConn();
            try
            {
                SqlDataAdapter Da = new SqlDataAdapter(Sql, con);
                DataTable dt = new DataTable();
                Da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误原因:" + ex.Message.Trim() + "\r\n 语句：" + Sql, "系统提示", MessageBoxButton.OK, MessageBoxImage.Error);
                Err.GetErrPic(ex.Message);
                return new DataTable();
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
        }

        #endregion  以上执行SQL语句

        #region 构造一个SqlCommand类型的实例
        /// <summary>
        /// 构造一个SqlCommand类型的实例
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="prams">存储过程参数集</param>
        /// <param name="conn">数据库连接的一个实例</param>
        /// <returns>返回SqlCommand对象的一个实例</returns>
        private static SqlCommand CreateCommand(string procName, SqlPrams prams, SqlConnection conn)
        {
            try
            {
                SqlCommand cmd = new SqlCommand(procName, conn);
                cmd.CommandTimeout = 80000;
                cmd.CommandType = CommandType.StoredProcedure;
                if (prams != null)
                {
                    cmd.Parameters.Clear();
                    foreach (DictionaryEntry pram in prams.getSqlPrams)
                    {
                        cmd.Parameters.Add(pram.Value);
                    }
                }
                return cmd;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }


    /// <summary>
    /// 
    /// </summary>
    public class SqlPrams
    {
        #region 变量声明
        private Hashtable prams;//参数变量 
        #endregion

        #region 属性
        /// <summary>
        /// 取得sql参数集合
        /// </summary>
        public Hashtable getSqlPrams
        {
            get
            {
                return this.prams;
            }
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// SqlPrams构造函数
        /// </summary>
        public SqlPrams()
        {
            prams = new Hashtable();
        }
        #endregion

        #region 添加参数
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="pramName">参数名称</param>
        /// <param name="Value">参数值</param>
        /// <param name="Direction">输入输出类型</param>
        public void Add(string pramName, object Value, ParameterDirection Direction)
        {
            if (pramName == null)
            {
                return;
            }
            SqlParameter pram = new SqlParameter(pramName, Value);
            pram.Direction = Direction;
            pram.SqlDbType = SqlDbType.VarChar;

            pram.Size = 4000;
            if (prams.Contains(pramName))
            {
                prams[pramName] = pram;
            }
            else
            {
                prams.Add(pramName, pram);
            }
        }
        #endregion

        #region 添加image类型参数
        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="pramName">参数名称</param>
        /// <param name="Value">参数值</param>
        /// <param name="Direction">输入输出类型</param>
        public void AddImage(string pramName, byte[] imgdata, ParameterDirection Direction)
        {
            if (pramName == null)
            {
                return;
            }
            SqlParameter pram = new SqlParameter(pramName, imgdata);
            pram.Direction = Direction;
            pram.SqlDbType = SqlDbType.Image;
            if (prams.Contains(pramName))
            {
                prams[pramName] = pram;
            }
            else
            {
                prams.Add(pramName, pram);
            }
        }
        #endregion

        #region 添加输出参数
        /// <summary>
        /// 添加输出参数
        /// </summary>
        /// <param name="pramName">参数名称</param>
        /// <param name="Value">参数值</param>
        public void AddOutputPrams(string pramName, object Value)
        {
            this.Add(pramName, Value, ParameterDirection.Output);
        }
        #endregion

        #region 添加输入参数
        /// <summary>
        /// 添加输入参数
        /// </summary>
        /// <param name="pramName">参数名称</param>
        /// <param name="Value">参数值</param>
        public void AddInputPrams(string pramName, object Value)
        {
            this.Add(pramName, Value, ParameterDirection.Input);
        }
        #endregion

        #region 添加图片输入参数
        /// <summary>
        /// 添加图片输入参数
        /// </summary>
        /// <param name="pramName">参数名称</param>
        /// <param name="Value">参数值</param>
        public void AddImageInputPrams(string pramName, byte[] Value)
        {
            this.AddImage(pramName, Value, ParameterDirection.Input);
        }
        #endregion
    }
}