/// <summary> /// 返回指定时间段的总收入 /// </summary> /// <param name="dt1"></param> /// <param name="dt2"></param> /// <returns></returns> public MODEL.Cost GetCountForPay(DateTime dt1, DateTime dt2) { string sql = "SELECT * from " + strTable + " WHERE cDate BETWEEN '" + dt1.ToString("yyyy-MM-dd") + "' AND '" + dt2.ToString("yyyy-MM-dd") + "'"; System.Data.DataTable dt = SqlHelper.GetTable(sql); MODEL.Cost model = new MODEL.Cost(); if (dt.Rows.Count > 0) { BuildSql.LoadModelData(dt.Rows[0], model, strTable); } return(model); }
/// <summary> /// /// </summary> /// <typeparam name="T">返回数据的类型</typeparam> /// <param name="table">数据表对象</param> /// <param name="paseSize">页容量</param> /// <param name="currentPage">当前页数</param> /// <param name="sqlWhere">分页条件</param> /// <returns></returns> public static List <T> SimplePager <T>(string table, string paseSize, string currentPage, string sqlWhere = "") where T : new() { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "select top " + paseSize + " * from ( select ROW_NUMBER() OVER (order by " + columnNames.Split(',')[0] + " desc) as RowNumber,* from " + table + " " + sqlWhere + " " + ") A where RowNumber>" + paseSize + "*(" + currentPage + "-1) "; DataTable dt = SqlHelper.GetTable(strSql); List <T> list = new List <T>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { T obs = new T(); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }
/// <summary> /// 返回泛型集合对象 /// </summary> /// <typeparam name="T">返回数据的类型</typeparam> /// <param name="table">数据表</param> /// <param name="desc">排序方法</param> /// <param name="sqlWhere">条件,默认为空</param> /// <returns></returns> public static List <T> GetListByWhere <T>(string table, string desc = "desc", string sqlWhere = "") where T : new() { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc; DataTable dt = SqlHelper.GetTable(strSql); List <T> list = new List <T>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { T obs = new T(); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }
/// <summary> /// 返回泛型集合对象 /// </summary> /// <param name="table">数据表</param> /// <param name="obj">实体对象</param> /// <param name="desc">排序方式</param> /// <param name="sqlWhere">条件,默认为空 </param> /// <returns>List<object>集合</object></objct></returns> // public List<MODEL.User> GetAllList() //{ // MODEL.User model = new MODEL.User(); // List<object> objs = BuildSql.GetListByWhere("dbo.[User]", model); // List<MODEL.User> list = new List<MODEL.User>(); // foreach (object item in objs) // { // if (item is MODEL.User) // { // MODEL.User mol = (MODEL.User)item; // list.Add(mol); // } // } // return list; //} public static List <object> GetListByWhere(string table, object obj, string desc = "desc", string sqlWhere = "") { string columnParas = string.Empty; string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true); string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc; DataTable dt = SqlHelper.GetTable(strSql); Type t = obj.GetType(); List <object> list = new List <object>(); if (dt.Rows.Count > 0) { foreach (DataRow dr in dt.Rows) { object obs = Activator.CreateInstance(t); BuildSql.LoadModelData(dr, obs, table); list.Add(obs); } } return(list); }