DBHelper

    public class DBHelper
    {
        private static string strConn = ConfigurationManager.ConnectionStrings["strConn"].ConnectionString;
        private static SqlConnection conn = new SqlConnection(strConn);
        /// <summary>
        /// 获取数据库连接对象
        /// </summary>
        public static SqlConnection Conn
        {
            get
            {
                return conn;
            }
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        public static void ConnOpen()
        {
            if (conn.State == System.Data.ConnectionState.Closed)
                conn.Open();
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        public static void ConnClose()
        {
            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();
        }
        /// <summary>
        /// 对数据进行增删查改
        /// </summary>
        /// <param name="strsql">sql 语句</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string strsql)
        {
            int i = 0;
            try
            {
                SqlCommand comm = new SqlCommand();
                comm.CommandText = strsql;//command需要执行的sql 语句
                comm.Connection = conn;//连接对象
                ConnOpen();//打开连接
                i = comm.ExecuteNonQuery();//command方法，它的返回值类型为int型。多用于执行增加，删除，修改数据。返回受影响的行数。当select操作时，返回-1

            }
            catch (Exception e)
            {

            }
            finally
            {
                ConnClose();//执行完command之后 进行关闭
            }
            return i;
        }
        /// <summary>
        /// 带参数进行增删查改
        /// </summary>
        /// <param name="strsql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string strsql, SqlParameter[] param)
        {
            int i = 0;
            try
            {
                SqlCommand cmd = new SqlCommand(strsql, conn);
                cmd.Parameters.AddRange(param);
                ConnOpen();
                i = cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            finally
            {
                ConnClose();
            }
            return i;


        }

        //带参数的查询
        public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] param)
        {
            SqlDataReader dr = null;
            try
            {
                SqlCommand comm = new SqlCommand(strSql, conn);
                comm.Parameters.AddRange(param);
                ConnOpen();
                dr = comm.ExecuteReader();
            }
            catch (Exception e)
            {
            }
            finally
            {
                //在阅读器没有读取数据前不能关闭数据库连接，否则将读取不到数据
            }
            return dr;
        }

        /// <summary>
        /// 查询是否有该数据
        /// </summary>
        /// <param name="strSql">数据操作的Sql语句</param>
        /// <returns></returns>
        public static bool Exists(string strSql, SqlParameter[] param)
        {
            bool bReturn = false;
            try
            {
                SqlCommand comm = new SqlCommand(strSql, conn);
                comm.Parameters.AddRange(param);
                ConnOpen();
                bReturn = (int)comm.ExecuteScalar() > 0;
            }
            catch (Exception e)
            {
            }
            finally
            {
                ConnClose();
            }
            return bReturn;
        }
        /// <summary>
        /// 查询是否有该数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int ExecuteScalar(string sql, params SqlParameter[] param)
        {
            int i = 0;
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.Parameters.AddRange(param);
            try
            {
                ConnOpen();
                i = Convert.ToInt32(comm.ExecuteScalar().ToString());
            }
            catch (Exception)
            {

            }
            finally
            {
                comm.Dispose();
            }
            return i;
        }

        public static DataSet GetAll(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;

        }

        public static DataSet GetAll(string sql, SqlParameter[] param)
        {
            SqlCommand comm = new SqlCommand(sql, conn);
            comm.Parameters.AddRange(param);
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;

        }
        /// <summary>
        /// dataset 存储过程进行数据修改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataSet ExcuteDatasetProc(string sql, params SqlParameter[] param )
        {
           
                SqlCommand comm = new SqlCommand(sql, conn);
                comm.Parameters.AddRange(param);
                comm.CommandType = System.Data.CommandType.StoredProcedure;
                ConnOpen();
                using (SqlDataAdapter da = new SqlDataAdapter(comm))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    comm.Parameters.Clear();
                    ConnClose(); 
                    return ds;
                }
          
        }

     
        /// <summary>
        /// 操作存储过程
        /// </summary>
        /// <param name="strSql">数据操作的Sql语句</param>
        /// <returns></returns>
        public static int ExecuteProcedure(string strSql, SqlParameter[] param)
        {
            int i = 0;
            try
            {
                SqlCommand comm = new SqlCommand(strSql, conn);
                comm.Parameters.AddRange(param);
                comm.CommandType = System.Data.CommandType.StoredProcedure;
                ConnOpen();
                i = comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
            }
            finally
            {
                ConnClose();
            }
            return i;
        }
        /// <summary>
        /// 执行存储过程查询数据
        /// </summary>
        /// <param name="strSql">存储过程名称</param>
        /// <param name="param">存储过程参数</param>
        /// <returns></returns>
        public static SqlDataReader ExecuteDataReaderProcedure(string strSql, SqlParameter[] param)
        {
            SqlDataReader dr = null;
            try
            {
                SqlCommand comm = new SqlCommand(strSql, conn);
                comm.Parameters.AddRange(param);
                comm.CommandType = System.Data.CommandType.StoredProcedure;
                ConnOpen();
                dr = comm.ExecuteReader();
            }
            catch (Exception e)
            {
            }
            finally
            {
                //在阅读器没有读取数据前不能关闭数据库连接，否则将读取不到数据
            }
            return dr;
        }
    }