SQLhelper相助类

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;namespace Utility{    public class SQLHelper    {        #region 通用方法        // 数据连接池         private SqlConnection con;        private string _message;        string _Message = string.Empty;        public string Message        {            get { return _message; }            set { _message = value; }        }        private CommandType _SQLCommandType = CommandType.Text;        /// <summary>        /// 命令类型        /// </summary>        public CommandType SQLCommandType        {            get { return this._SQLCommandType; }            set { this._SQLCommandType = value; }        }        /// <summary>        /// 返回数据库连接字符串        /// </summary>        /// <returns></returns>        public static String GetSqlConnection()        {            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnStrPool"].ConnectionString;            return connString;        }        #endregion        #region 执行sql字符串        /// <summary>        /// 执行不带参数的SQL语句        /// </summary>        /// <param name="StrSql"></param>        /// <returns></returns>        public static int ExecuteSql(String StrSql)        {            String ConnStr = GetSqlConnection();            using (SqlConnection conn = new SqlConnection(ConnStr))            {                SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;                cmd.CommandText = StrSql;                conn.Open();                cmd.ExecuteNonQuery();                conn.Close();                return 1;            }        }        /// <summary>        /// cyj 2012 7 4        /// 验证数据是否存在        /// </summary>        /// <param name="sql"></param>        /// <param name="param"></param>        /// <returns></returns>        public bool ValiExist(string sql, SqlParameter[] param)        {            String ConnStr = GetSqlConnection();            using (SqlConnection con = new SqlConnection(ConnStr))            {                con.Open();                SqlCommand cmd = new SqlCommand(sql, con);                if (param != null)                    cmd.Parameters.AddRange(param);                using (SqlDataReader dr = cmd.ExecuteReader())                {                    if (dr.Read())                    {                        con.Close();                        return true;                    }                    else                    {                        con.Close();                        return false;                    }                }            }        }        /// <summary>        /// 执行不带参数的SQL语句(替换上面的方法。暂时保留上面的方法，是为了与旧系统能兼容)         /// 作者：Duke        /// 时间：2012.2.6        /// 改写目的：程序出错时，不崩溃，并能给出友好的提示信息        /// 改写方法：        /// 1、取消静态方法（不方便出错信息等属性读取）        /// 2、加入try结构        /// 3、封闭类的消息属性         /// </summary>        /// <param name="StrSql"></param>        /// <returns></returns>        public int ExecuteSqlNoPa(String StrSql)        {            String ConnStr = GetSqlConnection();            using (SqlConnection conn = new SqlConnection(ConnStr))            {                SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;                cmd.CommandText = StrSql;                try                {                    conn.Open();                    return cmd.ExecuteNonQuery();                }                catch (SqlException ex)                {                    this.Message = ex.Message;                    return 0;                }                finally                {                    conn.Close();                }            }        }        /// <summary>        /// 执行带参数的SQL语句        /// 作者：Duke        /// 时间：2012.2.2        /// 改写目的：程序出错时，不崩溃，并能给出友好的提示信息        /// 改写方法：        /// 1、取消静态方法（不方便出错信息等属性读取）        /// 2、加入try结构        /// 3、封闭类的消息属性        /// </summary>        /// <param name="StrSql"></param>        /// <param name="param"></param>        /// <returns></returns>        public  int ExecuteSql(String StrSql, SqlParameter[] param)        {            String ConnStr = GetSqlConnection();            using (SqlConnection conn = new SqlConnection(ConnStr))            {                SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;                cmd.CommandText = StrSql;                cmd.Parameters.AddRange(param);                try                {                    conn.Open();                    return cmd.ExecuteNonQuery();                }                catch (SqlException ex)                {                                       this.Message = ex.Message;                    return 0;                }                finally                {                    conn.Close();                }            }        }        /// <summary>        /// 返回DataReader        /// </summary>        /// <param name="StrSql"></param>        /// <returns></returns>         public static SqlDataReader ExecuteReader(String StrSql)        {            String ConnStr = GetSqlConnection();            SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的             try            {                SqlCommand cmd = new SqlCommand();                cmd.Connection = conn;                cmd.CommandText = StrSql;                conn.Open();                return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection             }            catch //(Exception ex)             {                return null;            }            finally            {                conn.Close();            }        }        /// <summary>        /// 获取单个值        /// </summary>        /// <param name="selectSQL"></param>        /// <returns></returns>        public object ExecScalar(string selectSQL)        {            SqlConnection conn = new SqlConnection();            conn.ConnectionString = GetSqlConnection();            SqlCommand command = new SqlCommand(selectSQL, conn);            try            {                conn.Open();                return command.ExecuteScalar();            }            catch (SqlException ex)            {                Message = ex.Message;                return null;            }            finally            {                conn.Close();            }        }        /// <summary>        /// 执行SQL语句并返回数据表        /// </summary>        /// <param name="StrSql"></param>        /// <returns></returns>        public static DataTable ExecuteDt(String StrSql)        {            String ConnStr = GetSqlConnection();            using (SqlConnection conn = new SqlConnection(ConnStr))            {                SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);                DataTable dt = new DataTable();                conn.Open();                da.Fill(dt);                conn.Close();                return dt;            }        }        /// <summary>        /// 执行SQL语句并返回DataSet        /// </summary>        /// <param name="StrSql"></param>        /// <returns></returns>        public static DataSet ExecuteDs(String StrSql)        {            String ConnStr = GetSqlConnection();            using (SqlConnection conn = new SqlConnection(ConnStr))            {                SqlDataAdapter da = new SqlDataAdapter(StrSql, conn);                DataSet ds = new DataSet();                conn.Open();                da.SelectCommand.CommandTimeout = 180;                da.Fill(ds);                conn.Close();                return ds;            }        }        /// <summary>        /// 获取结果集（含参数）        /// </summary>        /// <param name="selectSQL">Select语句或者存储过程名称</param>        /// <param name="paramList">参数列表</param>        /// <returns></returns>        public DataTable Select(string selectSQL, System.Data.SqlClient.SqlParameter[] paramList)        {            SqlConnection conn = new SqlConnection();            conn.ConnectionString = GetSqlConnection();            SqlCommand command = new SqlCommand(selectSQL);            command.CommandType = this.SQLCommandType;            command.Parameters.AddRange(paramList);            command.Connection = conn;            SqlDataAdapter adapter = new SqlDataAdapter(command);            DataTable ds = new DataTable();            try            {                conn.Open();                adapter.Fill(ds);            }            catch (SqlException ex)            {                Message = ex.Message;            }            finally            {                conn.Close();            }            return ds;        }        #endregion        #region 操作存储过程        /// <summary>        /// 调用存储过程（有参数）        /// </summary>        /// <param name="name"></param>        /// <param name="paramList"></param>        /// <returns>-1:失败</returns>        public int CallProcedure(string name, System.Data.Common.DbParameter[] paramList)        {            SqlConnection conn = new SqlConnection();            conn.ConnectionString = GetSqlConnection();            SqlCommand command = new SqlCommand(name);            command.CommandType = System.Data.CommandType.StoredProcedure;            command.Parameters.AddRange(paramList);            command.Connection = conn;            try            {                conn.Open();                return command.ExecuteNonQuery();            }            catch (SqlException ex)            {                Message = ex.Message;                return -1;            }            finally            {                conn.Close();            }        }        /// <summary>        /// 调用存储过程（无参数）        /// </summary>        /// <param name="name"></param>        /// <param name="paramList"></param>        /// <returns>-1:失败</returns>        public int CallProcedure(string name)        {            SqlConnection conn = new SqlConnection();            conn.ConnectionString = GetSqlConnection();            SqlCommand command = new SqlCommand(name);            command.CommandType = System.Data.CommandType.StoredProcedure;            command.Connection = conn;            try            {                conn.Open();                return command.ExecuteNonQuery();            }            catch (SqlException ex)            {                this._Message = ex.Message;                return -1;            }            finally            {                conn.Close();            }        }        /// <summary>        /// 运行存储过程(已重载)         /// </summary>        /// <param name="procName">存储过程名</param>        /// <returns></returns>        public int RunProc(string procName)        {            SqlCommand cmd = CreateCommand(procName, null);            cmd.ExecuteNonQuery();            this.Close();            return (int)cmd.Parameters["ReturnValue"].Value;        }        /// <summary>        /// 运行存储过程(已重载)        /// </summary>        /// <param name="procName">存储过程的名字</param>        /// <param name="prams">存储过程的输入参数列表</param>        /// <returns>存储过程的返回值</returns>        public int RunProc(string procName, SqlParameter[] prams)        {            SqlCommand cmd = CreateCommand(procName, prams);            cmd.ExecuteNonQuery();            this.Close();            return (int)cmd.Parameters[0].Value;        }        /// <summary>        /// 运行存储过程(已重载)        /// </summary>        /// <param name="procName">存储过程名</param>        /// <param name="dataReader">结果集</param>        public void RunProc(string procName, out SqlDataReader dataReader)        {            SqlCommand cmd = CreateCommand(procName, null);            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);        }        /// <summary>        /// 运行存储过程(已重载)        /// </summary>        /// <param name="procName">存储过程的名字</param>        /// <param name="prams">存储过程的输入参数列表</param>        /// <param name="dataReader">结果集</param>        public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)        {            SqlCommand cmd = CreateCommand(procName, prams);            dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);        }        /// <summary>        /// 创建Command对象用于访问存储过程        /// </summary>        /// <param name="procName">存储过程的名字</param>        /// <param name="prams">存储过程的输入参数列表</param>        /// <returns>Command对象</returns>        private SqlCommand CreateCommand(string procName, SqlParameter[] prams)        {            // 确定连接是打开的             Open();            //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );             SqlCommand cmd = new SqlCommand(procName, con);            cmd.CommandType = CommandType.StoredProcedure;            // 添加存储过程的输入参数列表             if (prams != null)            {                foreach (SqlParameter parameter in prams) cmd.Parameters.Add(parameter);            }            // 返回Command对象             return cmd;        }        /// <summary>        /// 创建输入参数        /// </summary>        /// <param name="ParamName">参数名</param>        /// <param name="DbType">参数类型</param>        /// <param name="Size">参数大小</param>        /// <param name="Value">参数值</param>        /// <returns>新参数对象</returns>        public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)        {            return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);        }        /// <summary>        /// 创建输出参数        /// </summary>        /// <param name="ParamName">参数名</param>        /// <param name="DbType">参数类型</param>        /// <param name="Size">参数大小</param>        /// <returns>新参数对象</returns>        public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)        {            return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);        }        /// <summary>        /// 创建存储过程参数        /// </summary>        /// <param name="ParamName">参数名</param>        /// <param name="DbType">参数类型</param>        /// <param name="Size">参数大小</param>        /// <param name="Direction">参数的方向(输入/输出)</param>        /// <param name="Value">参数值</param>        /// <returns>新参数对象</returns>        public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)        {            SqlParameter param;            if (Size > 0)            {                param = new SqlParameter(ParamName, DbType, Size);            }            else            {                param = new SqlParameter(ParamName, DbType);            }            param.Direction = Direction;            if (!(Direction == ParameterDirection.Output && Value == null))            {                param.Value = Value;            }            return param;        }        #endregion        #region 数据库连接和关闭        public void OpenAdd()        {            Open();        }        /// <summary>        /// 打开连接池        /// </summary>        private void Open()        {            // 打开连接池             if (con == null)            {                //这里不仅需要using System.Configuration;还要在引用目录里添加                 con = new SqlConnection(GetSqlConnection());                con.Open();            }        }        /// <summary>        /// 关闭连接池        /// </summary>        public void Close()        {            if (con != null)                con.Close();        }        /// <summary>        /// 释放连接池        /// </summary>        public void Dispose()        {            // 确定连接已关闭             if (con != null)            {                con.Dispose();                con = null;            }        }        #endregion    }}