Helper 数据访问通用类

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

namespace Project.DAL {
 public class DBHelper {

  /// <summary>
  /// 数据库连接对象
  /// </summary>
  static SqlConnection conn;

  public static SqlConnection Conn {
   get {
    string connString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    if (conn == null) {
     conn = new SqlConnection(connString);
     conn.Open();
    } else if (conn.State == System.Data.ConnectionState.Closed) {
     conn.Open();
    } else if (conn.State == System.Data.ConnectionState.Broken) {
     conn.Close();
     conn.Open();
    }
    return conn;
   }
  }

  /// <summary>
  /// 更新sql Command
  /// </summary>
  /// <param name="safeSql">safeSql</param>
  /// <returns>更新影响的行数</returns>
  public static int ExecuteCommand(string safeSql) {
   SqlCommand cmd = new SqlCommand(safeSql, Conn);
   int result = cmd.ExecuteNonQuery();
   if (conn != null) {
    conn.Close();
   }
   return result;
  }

  /// <summary>
  /// 更新sql Command
  /// </summary>
  /// <param name="sql">sql</param>
  /// <param name="values">SqlParameter 参数</param>
  /// <returns>更新影响的行数</returns>
  public static int ExecuteCommand(string sql, params SqlParameter[] values) {
   SqlCommand cmd = new SqlCommand(sql, Conn);
   cmd.Parameters.AddRange(values);
   int count = cmd.ExecuteNonQuery();
   if (conn != null) {
    conn.Close();
   }
   return count;
  }

  /// <summary>
  /// 获得查询的第一行第一列的值
  /// </summary>
  /// <param name="safeSql"></param>
  /// <returns></returns>
  public static Object GetScalar(string safeSql) {
   SqlCommand cmd = new SqlCommand(safeSql, Conn);
   return cmd.ExecuteScalar();
   
  }

  /// <summary>
  /// 获得查询的第一行第一列的值
  /// </summary>
  /// <param name="safeSql"></param>
  /// <returns></returns>
  public static object GetScalar(string sql, params SqlParameter[] values) {
   SqlCommand cmd = new SqlCommand(sql, Conn);
   cmd.Parameters.AddRange(values);
   return cmd.ExecuteScalar();
  }

  /// <summary>
  /// 获得查询的SqlDataReader
  /// </summary>
  /// <param name="safeSql"></param>
  /// <returns></returns>
  public static SqlDataReader GetReader(string safeSql) {
   SqlCommand cmd = new SqlCommand(safeSql, Conn);
   SqlDataReader reader = cmd.ExecuteReader();
   return reader;
  }

  /// <summary>
  /// 获得查询的SqlDataReader
  /// </summary>
  /// <param name="sql"></param>
  /// <param name="values"></param>
  /// <returns></returns>
  public static SqlDataReader GetReader(string sql, params SqlParameter[] values) {
   SqlCommand cmd = new SqlCommand(sql, Conn);
   cmd.Parameters.AddRange(values);
   SqlDataReader reader = cmd.ExecuteReader();
   return reader;
  }

  /// <summary>
  /// 获得查询的DataTable
  /// </summary>
  /// <param name="safeSql"></param>
  /// <returns></returns>
  public static DataTable GetDataSet(string safeSql) {
   DataSet ds = new DataSet();
   SqlCommand cmd = new SqlCommand(safeSql, Conn);
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   da.Fill(ds);
   if (conn != null) {
    conn.Close();
   }
   return ds.Tables[0];
  }

  /// <summary>
  /// 获得查询的DataTable
  /// </summary>
  /// <param name="sql"></param>
  /// <param name="values"></param>
  /// <returns></returns>
  public static DataTable GetDataSet(string sql, params SqlParameter[] values) {
   DataSet ds = new DataSet();
   SqlCommand cmd = new SqlCommand(sql, Conn);
   cmd.Parameters.AddRange(values);
   SqlDataAdapter da = new SqlDataAdapter(cmd);
   da.Fill(ds);
   if (conn != null) {
    conn.Close();
   }
   return ds.Tables[0];
  }

 }
}