主要完成数据库的一些公共类的设置

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

namespace BOTO.DBUtility
{
    public class SqlHelper
    {
        #region 执行ExecuteNonQuery
        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="cmdType">Sql语句类型</param>
        /// <param name="cmdText">Sql语句或存储过程名</param>
        /// <param name="cmdParms">Parm数组</param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, string sysConn, params SqlParameter[] cmdParms)
        {
            int val = -1;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(sysConn))
            {
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
                try
                {
                    conn.Open();
                    val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                catch
                {
                    val = -1;
                }
                finally
                {
                    conn.Close();
                }
            }
            return val;
        }
        #endregion

        #region 获得SqlParameter实例
        /// <summary>
        /// 获得SqlParameter实例
        /// </summary>
        /// <param name="ParamName">字段名</param>
        /// <param name="Value">赋值</param>
        /// <returns>返回一个SqlParameter实例</returns>
        public static SqlParameter MakeParam(string ParamName, object Value)
        {
            return new SqlParameter(ParamName, Value);
        }
        #endregion

        #region 获得DateSet实例
        /// <summary>
        /// 获得DateSet实例
        /// </summary>     
        /// <param name="cmdType">Sql语句类型</param>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdParms">Parm数组</param>  
        /// <returns>返回DateSet实例</returns>
        public static DataSet ExecuteDataSet(CommandType cmdType, string cmdText, string sysConn, params SqlParameter[] cmdParms)
        {
            SqlConnection conn = new SqlConnection(sysConn);
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
            da.SelectCommand.CommandType = cmdType;
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                        da.SelectCommand.Parameters.Add(parm);
                }
                da.Fill(ds, "NewTable");
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
                da.Dispose();
                conn.Close();
            }
            return ds;
        }
        #endregion

        #region 获得DateSet数据集
        /// <summary>
        /// 获得数据集
        /// </summary>     
        /// <param name="ProcedureName">存储过程名</param>
        /// <returns>返回IList实例</returns>
        public static DataSet GetDataSetDetail(string ProcedureName, string sysConn)
        {
            SqlConnection conn = new SqlConnection(sysConn);
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(ProcedureName, conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds, "NewTable");
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
                da.Dispose();
                conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 获得数据集
        /// </summary>     
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="ht">表示层传递过来的哈希表对象</param> 
        /// <returns>返回IList实例</returns>
        public static DataSet GetDataSetDetail(string ProcedureName, string sysConn, Hashtable ht)
        {
            // ＝＝＝获得数据库源，返回DataSet为数据源＝＝＝
            DataSet ds = new DataSet();

            // 处理传递过来的参数
            SqlParameter[] Parms = new SqlParameter[ht.Count];
            IDictionaryEnumerator et = ht.GetEnumerator();
            int i = 0;
            while (et.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp = MakeParam("@" + et.Key.ToString(), et.Value);
                Parms[i] = sp;
                i = i + 1;
            }
            ds = ExecuteDataSet(CommandType.StoredProcedure, ProcedureName, sysConn, Parms);
            return ds;
        }
        #endregion

        #region 获得SqlDataReader实例
        /// <summary>
        /// 获得SqlDataReader实例
        /// </summary>     
        /// <param name="cmdType">Sql语句类型</param>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdParms">Parm数组</param> 
        /// <returns>返回SqlDataReader实例</returns>
        public static SqlDataReader ExecuteDataReader(CommandType cmdType, string cmdText, string sysConn, params SqlParameter[] cmdParms)
        {
            SqlConnection conn = new SqlConnection(sysConn);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
            SqlDataReader sdr = null;
            try
            {
                conn.Open();
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                cmd.Dispose();
                sdr.Close();
                conn.Close();
            }
            return sdr;
        }
        #endregion

        #region 获得OutPut实例
        /// <summary>
        /// 获得OutPut实例
        /// </summary>     
        /// <param name="cmdType">Sql语句类型</param>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="inputLen">输入参数的长度</param>
        /// <param name="sysConn">链接字符串</param>
        /// <param name="cmdParms">Parm数组</param>  
        /// <returns></returns>
        public static IList ExecuteOutPut(CommandType cmdType, string cmdText, int inputLen, string sysConn, params SqlParameter[] cmdParms)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();
            int count = 0;//控件循环次数
            SqlConnection conn = new SqlConnection(sysConn);
            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = cmdType;
            try
            {
                conn.Open();
                int len = cmdParms.Length;
                if (cmdParms != null)
                {
                    foreach (SqlParameter parm in cmdParms)
                    {
                        if (count < inputLen)
                        {
                            cmd.Parameters.Add(parm);
                        }
                        else
                        {
                            break;
                        }
                        count++;
                    }
                    switch (len - inputLen)
                    {
                        case 1:
                            SqlParameter rst11 = cmd.Parameters.AddWithValue(cmdParms[inputLen].ParameterName, cmdParms[inputLen].Value);
                            rst11.Direction = ParameterDirection.Output;
                            rst11.Size = 50;

                            cmd.Prepare();
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            Ilst.Add(rst11.Value);
                            break;
                        case 2:
                            SqlParameter rst21 = cmd.Parameters.AddWithValue(cmdParms[len - 2].ParameterName, cmdParms[len - 2].Value);
                            rst21.Direction = ParameterDirection.Output;
                            rst21.Size = 50;

                            SqlParameter rst22 = cmd.Parameters.AddWithValue(cmdParms[len - 1].ParameterName, cmdParms[len - 1].Value);
                            rst22.Direction = ParameterDirection.Output;
                            rst22.Size = 50;

                            cmd.Prepare();
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            Ilst.Add(rst21.Value);
                            Ilst.Add(rst22.Value);
                            break;
                        case 3:
                            SqlParameter rst31 = cmd.Parameters.AddWithValue(cmdParms[len - 3].ParameterName, cmdParms[len - 3].Value);
                            rst31.Direction = ParameterDirection.Output;
                            rst31.Size = 50;

                            SqlParameter rst32 = cmd.Parameters.AddWithValue(cmdParms[len - 2].ParameterName, cmdParms[len - 2].Value);
                            rst32.Direction = ParameterDirection.Output;
                            rst32.Size = 50;

                            SqlParameter rst33 = cmd.Parameters.AddWithValue(cmdParms[len - 1].ParameterName, cmdParms[len - 1].Value);
                            rst33.Direction = ParameterDirection.Output;
                            rst33.Size = 50;

                            cmd.Prepare();
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                            Ilst.Add(rst31.Value);
                            Ilst.Add(rst32.Value);
                            Ilst.Add(rst33.Value);
                            break;
                        default:
                            break;

                    }
                }

            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return Ilst;
        }
        #endregion

        #region 获得输出单个数据库源
        /// <summary>
        /// 获得输出单个数据库源
        /// </summary>     
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="alParam">字段,字段参数名</param>
        /// <param name="alValue">字段,字段参数值</param>
        /// <returns>返回IList实例</returns>
        public static IList GetParamOutPut(string ProcedureName, string sysConn, Hashtable htInPut, Hashtable htOutPut)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();

            // 处理传递过来的参数          
            SqlParameter[] Parms = new SqlParameter[htInPut.Count + htOutPut.Count];
            IDictionaryEnumerator etInPut = htInPut.GetEnumerator();
            IDictionaryEnumerator etOutPut = htOutPut.GetEnumerator();
            int i = 0;
            //作哈希表循环
            while (etInPut.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp1 = MakeParam("@" + etInPut.Key.ToString(), etInPut.Value);
                Parms[i] = sp1;
                i++;
            }
            while (etOutPut.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp2 = MakeParam("@" + etOutPut.Key.ToString(), etOutPut.Value);
                Parms[i] = sp2;
                i++;
            }
            Ilst = ExecuteOutPut(CommandType.StoredProcedure, ProcedureName, htInPut.Count, sysConn, Parms);

            return Ilst;
        }
        #endregion

        #region 获得单个子数据库源
        /// <summary>
        /// 获得单个子数据库源
        /// </summary>     
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="ht">表示层传递过来的哈希表对象</param> 
        /// <returns>返回IList实例</returns>
        public static IList GetParamDetail(string ProcedureName, string sysConn, Hashtable ht)
        {
            // ＝＝＝获得数据库源，返回IList为数据源＝＝＝
            IList Ilst = new ArrayList();

            // 处理传递过来的参数
            SqlParameter[] Parms = new SqlParameter[ht.Count];
            IDictionaryEnumerator et = ht.GetEnumerator();
            int i = 0;
            while (et.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp = MakeParam("@" + et.Key.ToString(), et.Value);
                Parms[i] = sp;
                i = i + 1;
            }
            using (DataSet ds = ExecuteDataSet(CommandType.StoredProcedure, ProcedureName, sysConn, Parms))
            {
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    Ilst.Add(ds.Tables[0].Rows[0][j]);
                }
            }
            return Ilst;
        }
        #endregion

        #region 数获得全部数据库源
        /// <summary>
        /// 不带参数获得全部数据库源
        /// </summary>     
        /// <param name="ProcedureName">存储过程名</param>       
        /// <returns>返回SqlDataReader实例</returns>
        public static SqlDataReader GetDataReaderDetail(string ProcedureName, string sysConn)
        {
            // ＝＝＝获得数据库源，返回SqlDataReader为数据源＝＝＝
            SqlDataReader sdr = null;

            // 定义空参数
            SqlParameter[] Parms = null;

            sdr = ExecuteDataReader(CommandType.StoredProcedure, ProcedureName, sysConn, Parms);
            return sdr;
        }

        /// <summary>
        /// 带参数获得全部数据库源
        /// </summary>    
        /// <param name="ProcedureName">存储过程名</param>
        /// <param name="ht">表示层传递过来的哈希表对象</param>  
        /// <returns>返回SqlDataReader实例</returns>
        public static SqlDataReader GetDataReaderDetail(string ProcedureName, string sysConn, Hashtable ht)
        {
            // ＝＝＝获得数据库源，返回SqlDataReader为数据源＝＝＝
            SqlDataReader sdr = null;

            // 处理传递过来的参数
            SqlParameter[] Parms = new SqlParameter[ht.Count];
            IDictionaryEnumerator et = ht.GetEnumerator();
            int i = 0;
            while (et.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp = MakeParam("@" + et.Key.ToString(), et.Value);
                Parms[i] = sp;
                i = i + 1;
            }
            sdr = ExecuteDataReader(CommandType.StoredProcedure, ProcedureName, sysConn, Parms);
            return sdr;
        }
        #endregion

        #region 插入、修改、删除记录操作
        /// <summary>
        /// 插入、修改、删除记录操作
        /// </summary>      
        /// <param name="cmdType">sql语句类型</param>
        /// <param name="str_Sql">sql语句</param>
        /// <param name="ht">表示层传递过来的哈希表对象</param>
        public static int ExecuteSQL(string ProcedureName, string sysConn, Hashtable ht)
        {
            int rst = -1;
            SqlParameter[] Parms = new SqlParameter[ht.Count];
            IDictionaryEnumerator et = ht.GetEnumerator();
            int i = 0;
            //作哈希表循环
            while (et.MoveNext())
            {
                System.Data.SqlClient.SqlParameter sp = MakeParam("@" + et.Key.ToString(), et.Value);
                Parms[i] = sp;
                i++;
            }
            rst = ExecuteNonQuery(CommandType.StoredProcedure, ProcedureName, sysConn, Parms);
            return rst;
        }
        #endregion

    }
}