基于ado.net访问sql server数据库的sqlhelper

 /// <summary>
    ///  访问SQL Server的通用数据访问类
    /// </summary>
    class SQLHelper
    {
        //定义连接字符串
        private static readonly string connString =
            ConfigurationManager.ConnectionStrings["connString"].ToString();
        /// <summary>
        /// 执行增删改操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Update(string sql)
         {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
             try
              {
                conn.Open();
                return  cmd.ExecuteNonQuery();
               }
              catch(Exception  ex)
              {
                //将异常信息写入日志
                throw ex;
               }
              finally//最终一定会被执行的代码
             {
                conn.Close();
                }

          }

        /// <summary>
        /// 执行单一结果查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                //将异常信息写入日志
                throw ex;
            }
            finally//最终一定会被执行的代码
            {
                conn.Close();
            }

        }

        /// <summary>
        /// 执行一个结果集的查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回一个SqlDataReader对象</returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                //将异常信息写入日志
               if(conn.State==ConnectionState.Open)
                {
                    conn.Close();
                }
                throw ex;
            }
            //finally//最终一定会被执行的代码
            //{
            //    conn.Close();
            //}

        }

        /// <summary>
        /// 执行返回数据集的查询 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            //创建数据适配器对象
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);//使用数据数据适配器，填充数据集
                return ds;
            }
            catch (Exception ex)
            {
                //将异常信息写入日志
                throw ex;
            }
            finally//最终一定会被执行的代码
            {
                //连接对象必须要关闭
                conn.Close();
            }
        }
        /// <summary>
        /// 获取数据库的时间
        /// </summary>
        /// <returns></returns>
        public  static  DateTime GetServerTime()
        {
            return Convert.ToDateTime(GetSingleResult("select  getdate()"));
        }

        /// <summary>
        /// 启用事务执行多条sql语句
        /// </summary>
        /// <param name="sqlList">sql语句集合</param>
        /// <returns></returns>
        public  static  bool UpdateByTran(List<string> sqlList)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            try
            {
                conn.Open();
                cmd.Transaction = conn.BeginTransaction();//开启事务
                foreach(string itemSql in sqlList)
                {
                    cmd.CommandText = itemSql;
                    cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();//提交事务（真正保存到数据库)
                return true;
            }
            catch (Exception ex)
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction.Rollback();//回滚事务
                }
                throw new Exception("调用事务方法 public static bool UpdateByTran(List<string> sqlList)出错 :"+ex.Message);
            }
            finally
            {
                if (cmd.Transaction != null)
                {
                    cmd.Transaction = null;//清除事务

                }
                conn.Close();
            }
        }

    }