基于MVC的 DBHelper

基于MVC的 DBHelper

public static class DBHelper
{
    /// <summary>
    /// // 从配置文件中读取连接字符串
    /// </summary>
    public static string Constr = ConfigurationManager.ConnectionStrings["UserInforGer"].ConnectionString;
    
    /// <summary>
    /// 方法  增删改  返回受影响的行数 insert delete update
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteNonQuery(string sql)
    {
        using (SqlConnection conn = new SqlConnection(Constr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteNonQuery();
        }
    }
    /// <summary>
    /// 方法 查询返回首行首列  返回object类型 select count(1)from 表
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql)
    {
        using (SqlConnection conn = new SqlConnection(Constr))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteScalar();
        }
    }

    /// <summary>
    /// 方法 查询返回多行多列（表格）  DataTable   select * from 表
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable GetDataTable(string sql)
    {
        using (SqlConnection conn = new SqlConnection(Constr))
        {
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            return dt;
        }
    }

    /// <summary>
    /// 获取数据并返回集合
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static List<T> GetDataList<T>(string sql)
    {
        var dt = GetDataTable(sql);
        return JsonConvert.DeserializeObject<List<T>>(JsonConvert.SerializeObject(dt));
    }
}