public Module Select(string ModuleID) { String sql = "select * from Module where Module_ID=@ModuleID"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@ModuleID", ModuleID), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); if (dt.Rows.Count > 0) { Module index = new Module(); index.ModuleId = dt.Rows[0]["Module_ID"].ToString(); index.ModuleName = dt.Rows[0]["Module_Name"].ToString(); index.ModuleUrl = dt.Rows[0]["Module_URL"].ToString(); index.IconUrl = dt.Rows[0]["Icon_Url"].ToString(); index.ParentId = dt.Rows[0]["Parent_Id"].ToString(); index.OrderId = dt.Rows[0]["Order_Id"].GetDecimal(); index.Notes = dt.Rows[0]["Notes"].ToString(); index.Status = dt.Rows[0]["STATUS"].GetDecimal(); return(index); } else { return(null); } }
public List <Module> Select(int pageSize, int start, Common.HashTableExp hash, out int total, String sqlWhere) { List <Module> list = new List <Module>(); sqlWhere = "1=1" + sqlWhere; #region 查询条件 if (hash["ModuleID"] != null) { sqlWhere += string.Format(" and Module_ID='{0}'", hash["ModuleID"]); } if (hash["ModuleName"] != null) { sqlWhere += string.Format(" and Module_Name='{0}'", hash["ModuleName"]); } if (hash["ModuleURL"] != null) { sqlWhere += string.Format(" and Module_URL='{0}'", hash["ModuleURL"]); } if (hash["IconUrl"] != null) { sqlWhere += string.Format(" and Icon_Url='{0}'", hash["IconUrl"]); } if (hash["ParentId"] != null) { sqlWhere += string.Format(" and Parent_Id='{0}'", hash["ParentId"]); } if (hash["OrderId"] != null) { sqlWhere += string.Format(" and Order_Id='{0}'", hash["OrderId"]); } if (hash["Notes"] != null) { sqlWhere += string.Format(" and Notes='{0}'", hash["Notes"]); } if (hash["Status"] != null) { sqlWhere += string.Format(" and STATUS='{0}'", hash["Status"]); } #endregion DataTable dt = SqlLiteHelper.GetTable("Module", "Module_ID,Module_Name,Module_URL,Icon_Url,Parent_Id,Order_Id,Notes,STATUS", pageSize, start, sqlWhere, "Order_Id", "asc", out total); for (int i = 0; i < dt.Rows.Count; i++) { Module index = new Module(); index.ModuleId = dt.Rows[i]["Module_ID"].ToString(); index.ModuleName = dt.Rows[i]["Module_Name"].ToString(); index.ModuleUrl = dt.Rows[i]["Module_URL"].ToString(); index.IconUrl = dt.Rows[i]["Icon_Url"].ToString(); index.ParentId = dt.Rows[i]["Parent_Id"].ToString(); index.OrderId = dt.Rows[i]["Order_Id"].GetInt32(); index.Notes = dt.Rows[i]["Notes"].ToString(); index.Status = dt.Rows[i]["STATUS"].GetDecimal(); list.Add(index); } return(list); }
public List <Module> Select(Common.HashTableExp hash, String sqlWhere) { List <Module> list = new List <Module>(); string sql = "select Module_ID,Module_Name,Module_URL,Icon_Url,Parent_Id,Order_Id,Notes,STATUS from Module where 1=1"; #region 查询条件 if (hash["ModuleID"] != null) { sql += string.Format(" and Module_ID='{0}'", hash["ModuleID"]); } if (hash["ModuleName"] != null) { sql += string.Format(" and Module_Name='{0}'", hash["ModuleName"]); } if (hash["ModuleURL"] != null) { sql += string.Format(" and Module_URL='{0}'", hash["ModuleURL"]); } if (hash["IconUrl"] != null) { sql += string.Format(" and Icon_Url='{0}'", hash["IconUrl"]); } if (hash["ParentId"] != null) { sql += string.Format(" and Parent_Id='{0}'", hash["ParentId"]); } if (hash["OrderId"] != null) { sql += string.Format(" and Order_Id='{0}'", hash["OrderId"]); } if (hash["Notes"] != null) { sql += string.Format(" and Notes='{0}'", hash["Notes"]); } if (hash["Status"] != null) { sql += string.Format(" and STATUS='{0}'", hash["Status"]); } #endregion sql += sqlWhere + " order by Order_Id asc"; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); for (int i = 0; i < dt.Rows.Count; i++) { Module index = new Module(); index.ModuleId = dt.Rows[i]["Module_ID"].ToString(); index.ModuleName = dt.Rows[i]["Module_Name"].ToString(); index.ModuleUrl = dt.Rows[i]["Module_URL"].ToString(); index.IconUrl = dt.Rows[i]["Icon_Url"].ToString(); index.ParentId = dt.Rows[i]["Parent_Id"].ToString(); index.OrderId = dt.Rows[i]["Order_Id"].GetDecimal(); index.Notes = dt.Rows[i]["Notes"].ToString(); index.Status = dt.Rows[i]["STATUS"].GetDecimal(); list.Add(index); } return(list); }
public DataTable GetCollectionByMonth() { String sql = @"select substr(time,1,7) time, (select ifnull(SUM(price),0) from V_BANK_CARD where substr(a.time,1,7)=substr(time,1,7) and Save_Type='1000200001') moneyInsert, (select ifnull(SUM(price),0) from V_BANK_CARD where substr(a.time,1,7)=substr(time,1,7) and Save_Type='1000200002') moneyOut from V_BANK_CARD a group by substr(time,1,7)"; return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
/// <summary> /// 根据月份汇总生活费 /// </summary> /// <param name="time">时间,如:2013-01</param> /// <returns></returns> public DataTable GetCollectionByDay(string time, String sqlWhere) { String sql = @"select TIME time,SUM(Price) price from Lifing_Cost "; if (!String.IsNullOrEmpty(time)) { sql += " where substr(time,1,7)='" + time + "' " + sqlWhere; } sql += " group by time order by time"; return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
/// <summary> /// 根据月份汇总生活费类型 /// </summary> /// <param name="time">时间,如:2013-01</param> /// <returns></returns> public DataTable GetCollectionByType(string time, String sqlWhere) { String sql = @"select COST_TYPE_NAME costTypeName,SUM(price) price from V_LIFING_COST"; if (!String.IsNullOrEmpty(time)) { sql += " where substr(time,1,7)='" + time + "' " + sqlWhere; } sql += " group by COST_TYPE_NAME"; return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
/// <summary> /// 查询所有的自定义分组名称 /// </summary> /// <returns></returns> public List <string> GetAllCusGroup() { String sql = "select CusGroup from Lifing_Cost where CusGroup is NOT NULL GROUP BY CusGroup"; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); List <String> list = new List <string>(); for (int i = 0; i < dt.Rows.Count; i++) { list.Add(dt.Rows[i][0].GetString()); } return(list); }
/// <summary> /// 查询所有的消费名称汇总,如:李子、桃子等 /// </summary> /// <returns></returns> public List <string> GetAllReasons() { List <String> list = new List <String>(); string sql = "select Reason from Lifing_Cost group by Reason"; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); for (int i = 0; i < dt.Rows.Count; i++) { list.Add(dt.Rows[i]["Reason"].GetString()); } return(list); }
public List <CostMonthCol> GetCollectionByMonth(string beginTime, string endTime, bool?isMark, bool?family, string costTypeIds, String userId) { String costSqlWhere = String.Empty; String incomeSqlWhere = String.Empty; #region 组装条件 if (!String.IsNullOrEmpty(beginTime)) { costSqlWhere += String.Format(" and time>='{0}'", beginTime); incomeSqlWhere += String.Format(" and time>='{0}'", beginTime); } if (!String.IsNullOrEmpty(endTime)) { costSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime); incomeSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime); } if (isMark != null) { costSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32()); incomeSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32()); } if (family != null) { costSqlWhere += String.Format(" and FamilyPay='{0}'", family.GetInt32()); incomeSqlWhere += String.Format(" and FamilyIncome='{0}'", family.GetInt32()); } if (!String.IsNullOrEmpty(costTypeIds)) { costSqlWhere += String.Format(" and Cost_Type_Id in({0})", costTypeIds); } //不是管理员 if (userId != "-1") { costSqlWhere += String.Format(" and Create_By='{0}'", userId); incomeSqlWhere += String.Format(" and Create_By='{0}'", userId); } #endregion String sql = String.Format(@"select a.PayTime Time,round(a.Pay,2) Pay,ifnull(round(b.Income,2),0) Income,round((ifnull(b.Income,0)-ifnull(a.Pay,0)),2) Balance from ( select strftime('%Y-%m',Time) PayTime,SUM(Price) Pay from Lifing_Cost where 1=1 {0} group by strftime('%Y-%m',Time) ) a left join ( select strftime('%Y-%m',Time) InTime,SUM(Price) Income from Income where 1=1 {1} group by strftime('%Y-%m',Time) ) b on a.PayTime=b.InTime order by a.PayTime desc", costSqlWhere, incomeSqlWhere); DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); return(Life.Model.Common <CostMonthCol> .ConvertToList(dt)); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List <Income> Select(HashTableExp hash, String sqlWhere) { List <Income> list = new List <Income>(); string sql = "select Id,TIME,Price,Note,Create_By,Create_Time,UpDate_By,UpDate_Time,FamilyIncome,IsMark,CusGroup from Income where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sql += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Time"] != null) { sql += string.Format(" and TIME='{0}'", hash["Time"]); } if (hash["Price"] != null) { sql += string.Format(" and Price='{0}'", hash["Price"]); } if (hash["Note"] != null) { sql += string.Format(" and Note='{0}'", hash["Note"]); } if (hash["CreateBy"] != null) { sql += string.Format(" and Create_By='{0}'", hash["CreateBy"]); } if (hash["CreateTime"] != null) { sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } if (hash["UpdateBy"] != null) { sql += string.Format(" and UpDate_By='{0}'", hash["UpdateBy"]); } if (hash["UpdateTime"] != null) { sql += string.Format(" and UpDate_Time='{0}'", hash["UpdateTime"]); } } #endregion sql += sqlWhere; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); list = Model.Common <Income> .ConvertToList(dt); return(list); }
/// <summary> /// 分页查询收入记录表 /// </summary> /// <param name="pageSize">每页多少条数据</param> /// <param name="start">排除多少条数据</param> /// <param name="hash">筛选条件</param> /// <param name="total">总共多少条</param> /// <returns>数据集合</returns> public List <Student> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere) { List <Student> list = new List <Student>(); #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and [id]='{0}'", hash["Id"]); } if (hash["Name"] != null) { sqlWhere += string.Format(" and [name]='{0}'", hash["Name"]); } if (hash["CardNo"] != null) { sqlWhere += string.Format(" and [card_no]='{0}'", hash["CardNo"]); } if (hash["BirthDay"] != null) { sqlWhere += string.Format(" and [birth_day]='{0}'", hash["BirthDay"]); } if (hash["Sex"] != null) { sqlWhere += string.Format(" and [sex]='{0}'", hash["Sex"]); } if (hash["Addr"] != null) { sqlWhere += string.Format(" and [addr]='{0}'", hash["Addr"]); } if (hash["Remark"] != null) { sqlWhere += string.Format(" and [remark]='{0}'", hash["Remark"]); } } sqlWhere = "1=1 " + sqlWhere; #endregion DataTable dt = SqlLiteHelper.GetTable("Student", "[id],[name],[card_no],[birth_day],[sex],[addr],[remark]", pageSize, start, sqlWhere, "name", "asc", out total); list = Model.Common <Student> .ConvertToList(dt); return(list); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List <Student> Select(HashTableExp hash, String sqlWhere) { List <Student> list = new List <Student>(); string sql = "select [id],[name],[card_no],[birth_day],[sex],[addr],[remark] from Student where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and [id]='{0}'", hash["Id"]); } if (hash["Name"] != null) { sqlWhere += string.Format(" and [name]='{0}'", hash["Name"]); } if (hash["CardNo"] != null) { sqlWhere += string.Format(" and [card_no]='{0}'", hash["CardNo"]); } if (hash["BirthDay"] != null) { sqlWhere += string.Format(" and [birth_day]='{0}'", hash["BirthDay"]); } if (hash["Sex"] != null) { sqlWhere += string.Format(" and [sex]='{0}'", hash["Sex"]); } if (hash["Addr"] != null) { sqlWhere += string.Format(" and [addr]='{0}'", hash["Addr"]); } if (hash["Remark"] != null) { sqlWhere += string.Format(" and [remark]='{0}'", hash["Remark"]); } } #endregion sql += sqlWhere; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); list = Model.Common <Student> .ConvertToList(dt); return(list); }
/// <summary> /// 汇总月份生活费消费信息 /// Sqlite 的ifnull函数和SqlServer的isnull函数功能一样 /// </summary> /// <returns></returns> public DataTable GetCollectionByMonth(String sqlWhere, String beginTime, String endTime, String isCalc) { if (!String.IsNullOrEmpty(beginTime)) { sqlWhere += String.Format(" and substr(time,1,7)>='{0}'", beginTime); } if (!String.IsNullOrEmpty(endTime)) { sqlWhere += String.Format(" and substr(time,1,7)<='{0}'", endTime); } String sql = String.Format(@"select substr(time,1,7) time,SUM(Price) price, ifnull((select SUM(price) from Income b where substr(a.time,1,7)=substr(b.time,1,7) {0}),0) interPrice from Lifing_Cost a where 1=1 {0} {1} group by substr(time,1,7) order by substr(time,1,7) asc", sqlWhere, isCalc); return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
/// <summary> /// 汇总月份生活费消费信息--图形汇总 /// </summary> /// <returns></returns> public DataTable GetCollection(String sqlWhere, String beginTime, String endTime) { if (!String.IsNullOrEmpty(beginTime)) { sqlWhere += String.Format(" and strftime('%Y-%m',Time)>='{0}'", beginTime); } if (!String.IsNullOrEmpty(endTime)) { sqlWhere += String.Format(" and strftime('%Y-%m',Time)<='{0}'", endTime); } String sql = String.Format(@"select strftime('%Y-%m',Time) time,SUM(price) price, ifnull((select SUM(price) from Income b where strftime('%Y-%m',a.Time)=strftime('%Y-%m',b.Time) {0} ),0) interPrice from Lifing_Cost a where 1=1 {0} group by strftime('%Y-%m',Time) order by time", sqlWhere); return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
public List <Module> SelectByUserId(string userId, string status) { String sql = String.Format(@"select distinct d.Module_ID,d.Module_Name,d.Module_URL,d.Icon_Url,d.Parent_Id,d.Order_Id,d.Notes,d.STATUS from User_To_Role a inner join Roles b on a.Role_Id=b.Role_Id inner join Role_To_Module c on b.Role_Id=c.Role_Id inner join Module d on c.Module_Id=d.Module_ID where a.User_Id='{0}'", userId); if (!String.IsNullOrEmpty(status)) { sql += " and d.STATUS=" + status; } sql += " order by d.Order_Id"; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); List <Module> list = Life.Model.Common <Module> .ConvertToList(dt); return(list); }
/// <summary> /// 分页查询临时信息存储表 /// </summary> /// <param name="pageSize">每页多少条数据</param> /// <param name="start">排除多少条数据</param> /// <param name="hash">筛选条件</param> /// <param name="total">总共多少条</param> /// <returns>数据集合</returns> public List <TempData> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere) { List <TempData> list = new List <TempData>(); sqlWhere = "1=1 " + sqlWhere; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Email"] != null) { sqlWhere += string.Format(" and Email='{0}'", hash["Email"]); } if (hash["Expires"] != null) { sqlWhere += string.Format(" and Expires='{0}'", hash["Expires"]); } if (hash["CreateTime"] != null) { sqlWhere += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } } #endregion DataTable dt = SqlLiteHelper.GetTable("Temp_Data", "Id,Email,Expires,Create_Time", pageSize, start, sqlWhere, "Id", "asc", out total); for (int i = 0; i < dt.Rows.Count; i++) { TempData index = new TempData(); index.Id = dt.Rows[i]["Id"].GetString(); index.Email = dt.Rows[i]["Email"].GetString(); index.Expires = dt.Rows[i]["Expires"].GetDateTime(); index.CreateTime = dt.Rows[i]["Create_Time"].GetDateTime(); list.Add(index); } return(list); }
/// <summary> /// 根据编号查询银行卡操作记录表 /// </summary> /// <param name="Id">编号</param> /// <returns>数据集合</returns> public BankCard Select(string Id) { String sql = "select * from Bank_Card where Id=@Id"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@Id", Id), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); List <BankCard> list = Life.Model.Common <BankCard> .ConvertToList(dt); if (list.Count > 0) { return(list[0]); } else { return(null); } }
/// <summary> /// 查询生活费的所有消费名称 /// </summary> /// <param name="key"></param> /// <returns></returns> public List <LifingCost> GetReasons(string key) { List <LifingCost> list = new List <LifingCost>(); string sql = "select Reason from Lifing_Cost group by Reason having Reason like '%@key%'"; SQLiteParameter[] parm = new SQLiteParameter[] { new SQLiteParameter("@key", key) }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); for (int i = 0; i < dt.Rows.Count; i++) { LifingCost index = new LifingCost(); index.Reason = dt.Rows[i]["Reason"].GetString(); list.Add(index); } return(list); }
/// <summary> /// 汇总生活费类型 /// </summary> /// <param name="time">时间,如:'2014-01','2014-12'</param> /// <param name="beginTime">开始统计的时间</param> /// <param name="endTime">结束统计的时间</param> /// <param name="isMark">特殊标识</param> /// <param name="family">家庭内收支</param> /// <param name="costTypeId">消费类型编号</param> /// <param name="userId">用户编号</param> /// <returns></returns> public DataTable GetCollectionType(string time, string beginTime, string endTime, bool?isMark, bool?family, string costTypeIds, string userId) { String costSqlWhere = String.Empty; #region 组装条件 if (!String.IsNullOrEmpty(time)) { costSqlWhere += String.Format(" and strftime('%Y-%m',Time) in ({0})", time); } if (!String.IsNullOrEmpty(beginTime)) { costSqlWhere += String.Format(" and time>='{0}'", beginTime); } if (!String.IsNullOrEmpty(endTime)) { costSqlWhere += String.Format(" and time<datetime('{0}','+1 hour')", endTime); } if (isMark != null) { costSqlWhere += String.Format(" and isMark='{0}'", isMark.GetInt32()); } if (family != null) { costSqlWhere += String.Format(" and FamilyPay='{0}'", family.GetInt32()); } if (!String.IsNullOrEmpty(costTypeIds)) { costSqlWhere += String.Format(" and Cost_Type_Id in({0})", costTypeIds); } //不是管理员 if (userId != "-1") { costSqlWhere += String.Format(" and Create_By='{0}'", userId); } #endregion String sql = String.Format(@"select COST_TYPE_NAME costTypeName,SUM(price) price from V_LIFING_COST where 1=1 {0} group by COST_TYPE_NAME", costSqlWhere); return(SqlLiteHelper.GetTable(sql, CommandType.Text)); }
/// <summary> /// 根据编号查询收入记录表 /// </summary> /// <param name="Id">编号</param> /// <returns>数据集合</returns> public Student Select(string Id) { String sql = "select * from Student where Id=@Id"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@Id", Id), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); if (dt.Rows.Count > 0) { List <Student> list = Life.Model.Common <Student> .ConvertToList(dt); if (list.Count > 0) { return(list[0]); } } return(null); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List <TempData> Select(HashTableExp hash, String sqlWhere) { List <TempData> list = new List <TempData>(); string sql = "select Id,Email,Expires,Create_Time from Temp_Data where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sql += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Email"] != null) { sql += string.Format(" and Email='{0}'", hash["Email"]); } if (hash["Expires"] != null) { sql += string.Format(" and Expires='{0}'", hash["Expires"]); } if (hash["CreateTime"] != null) { sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } } #endregion sql += sqlWhere; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); for (int i = 0; i < dt.Rows.Count; i++) { TempData index = new TempData(); index.Id = dt.Rows[i]["Id"].GetString(); index.Email = dt.Rows[i]["Email"].GetString(); index.Expires = dt.Rows[i]["Expires"].GetDateTime(); index.CreateTime = dt.Rows[i]["Create_Time"].GetDateTime(); list.Add(index); } return(list); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns>数据集合</returns> public List <Users> Select() { List <Users> list = new List <Users>(); String sql = "select Id,Login_Id,Login_Pwd,Name,Phone,Mail,Address,Age,Notes from Users"; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, null); for (int i = 0; i < dt.Rows.Count; i++) { Users index = new Users(); index.Id = dt.Rows[i]["Id"].ToString(); index.LoginId = dt.Rows[i]["Login_Id"].ToString(); index.LoginPwd = dt.Rows[i]["Login_Pwd"].ToString(); index.Name = dt.Rows[i]["Name"].ToString(); index.Phone = dt.Rows[i]["Phone"].ToString(); index.Mail = dt.Rows[i]["Mail"].ToString(); index.Address = dt.Rows[i]["Address"].ToString(); index.Age = dt.Rows[i]["Age"].GetInt32(); index.Notes = dt.Rows[i]["Notes"].ToString(); list.Add(index); } return(list); }
/// <summary> /// 根据编号查询角色表 /// </summary> /// <param name="RoleId">编号</param> /// <returns>数据集合</returns> public Roles Select(string RoleId) { String sql = "select * from Roles where Role_Id=@RoleId"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@RoleId", RoleId), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); if (dt.Rows.Count > 0) { Roles index = new Roles(); index.RoleId = dt.Rows[0]["Role_Id"].ToString(); index.RoleName = dt.Rows[0]["Role_Name"].ToString(); index.Notes = dt.Rows[0]["Notes"].ToString(); return(index); } else { return(null); } }
/// <summary> /// 根据编号查询角色模块对应表 /// </summary> /// <param name="Id">编号</param> /// <returns>数据集合</returns> public RoleToModule Select(string Id) { String sql = "select * from RoleToModule where Id=@Id"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@Id", Id), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); if (dt.Rows.Count > 0) { RoleToModule index = new RoleToModule(); index.Id = dt.Rows[0]["Id"].GetString(); index.RoleId = dt.Rows[0]["Role_Id"].GetString(); index.ModuleId = dt.Rows[0]["Module_Id"].GetString(); return(index); } else { return(null); } }
/// <summary> /// 分页查询角色模块对应表 /// </summary> /// <param name="pageSize">每页多少条数据</param> /// <param name="start">排除多少条数据</param> /// <param name="hash">筛选条件</param> /// <param name="total">总共多少条</param> /// <returns>数据集合</returns> public List <RoleToModule> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere) { List <RoleToModule> list = new List <RoleToModule>(); sqlWhere = "1=1 " + sqlWhere; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sqlWhere += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["RoleId"] != null) { sqlWhere += string.Format(" and Role_Id='{0}'", hash["RoleId"]); } if (hash["ModuleId"] != null) { sqlWhere += string.Format(" and Module_Id='{0}'", hash["ModuleId"]); } } #endregion DataTable dt = SqlLiteHelper.GetTable("Role_To_Module", "Id,Role_Id,Module_Id", pageSize, start, sqlWhere, "Id", "asc", out total); for (int i = 0; i < dt.Rows.Count; i++) { RoleToModule index = new RoleToModule(); index.Id = dt.Rows[i]["Id"].GetString(); index.RoleId = dt.Rows[i]["Role_Id"].GetString(); index.ModuleId = dt.Rows[i]["Module_Id"].GetString(); list.Add(index); } return(list); }
/// <summary> /// 查询所有银行卡的最后一条数据 time格式:2015-01-02 /// </summary> /// <returns></returns> public List <VBankCard> SelectCalc(String time) { String sql = String.Empty; List <VBankCard> list = new List <VBankCard>(); //查询所有银行卡 List <Diction> dics = new DLDiction().Select(new HashTableExp("ParentId", "1000100000"), null); foreach (var item in dics) { //查询最近一次的记录 String sqlWhere = String.Format("Bank_Type='{0}'", item.Id); if (!String.IsNullOrEmpty(time)) { sqlWhere += String.Format(" and time<='{0}'", time); } sql = String.Format("select * from V_Bank_Card where {0} order by time desc,create_time desc limit 1 offset 0", sqlWhere); DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); list = Life.Model.Common <VBankCard> .ConvertToList(dt); } return(list); }
/// <summary> /// 根据编号查询临时信息存储表 /// </summary> /// <param name="Id">编号</param> /// <returns>数据集合</returns> public TempData Select(string Id) { String sql = "select * from TempData where Id=@Id"; System.Data.SQLite.SQLiteParameter[] parm = new System.Data.SQLite.SQLiteParameter[] { new SQLiteParameter("@Id", Id), }; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text, parm); if (dt.Rows.Count > 0) { TempData index = new TempData(); index.Id = dt.Rows[0]["Id"].GetString(); index.Email = dt.Rows[0]["Email"].GetString(); index.Expires = dt.Rows[0]["Expires"].GetDateTime(); index.CreateTime = dt.Rows[0]["Create_Time"].GetDateTime(); return(index); } else { return(null); } }
/// <summary> /// 分页查询角色表 /// </summary> /// <param name="pageSize">每页多少条数据</param> /// <param name="start">排除多少条数据</param> /// <param name="hash">筛选条件</param> /// <param name="total">总共多少条</param> /// <returns>数据集合</returns> public List <Roles> Select(int pageSize, int start, HashTableExp hash, out int total, String sqlWhere) { List <Roles> list = new List <Roles>(); sqlWhere = "1=1 " + sqlWhere; #region 查询条件 if (hash["RoleId"] != null) { sqlWhere += string.Format(" and Role_Id='{0}'", hash["RoleId"]); } if (hash["RoleName"] != null) { sqlWhere += string.Format(" and Role_Name='{0}'", hash["RoleName"]); } if (hash["Notes"] != null) { sqlWhere += string.Format(" and Notes='{0}'", hash["Notes"]); } #endregion DataTable dt = SqlLiteHelper.GetTable("Roles", "Role_Id,Role_Name,Notes", pageSize, start, sqlWhere, "Role_Id", "asc", out total); for (int i = 0; i < dt.Rows.Count; i++) { Roles index = new Roles(); index.RoleId = dt.Rows[i]["Role_Id"].ToString(); index.RoleName = dt.Rows[i]["Role_Name"].ToString(); index.Notes = dt.Rows[i]["Notes"].ToString(); list.Add(index); } return(list); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List <RoleToModule> Select(HashTableExp hash, String sqlWhere) { List <RoleToModule> list = new List <RoleToModule>(); string sql = "select Id,Role_Id,Module_Id from Role_To_Module where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sql += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["RoleId"] != null) { sql += string.Format(" and Role_Id='{0}'", hash["RoleId"]); } if (hash["ModuleId"] != null) { sql += string.Format(" and Module_Id='{0}'", hash["ModuleId"]); } } #endregion sql += sqlWhere; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); for (int i = 0; i < dt.Rows.Count; i++) { RoleToModule index = new RoleToModule(); index.Id = dt.Rows[i]["Id"].GetString(); index.RoleId = dt.Rows[i]["Role_Id"].GetString(); index.ModuleId = dt.Rows[i]["Module_Id"].GetString(); list.Add(index); } return(list); }
/// <summary> /// 查询所有数据 /// </summary> /// <returns></returns> public List <LifingCost> Select(HashTableExp hash, String sqlWhere) { List <LifingCost> list = new List <LifingCost>(); string sql = "select Id,TIME,Reason,Price,Cost_Type_Id,Notes,Img_Url,Create_By,Create_Time,UpDate_By,UpDate_Time,IsMark,FamilyPay,CusGroup from Lifing_Cost where 1=1 "; #region 查询条件 if (hash != null) { if (hash["Id"] != null) { sql += string.Format(" and Id='{0}'", hash["Id"]); } if (hash["Time"] != null) { sql += string.Format(" and TIME='{0}'", hash["Time"]); } if (hash["Reason"] != null) { sql += string.Format(" and Reason='{0}'", hash["Reason"]); } if (hash["Price"] != null) { sql += string.Format(" and Price='{0}'", hash["Price"]); } if (hash["CostTypeId"] != null) { sql += string.Format(" and Cost_Type_Id='{0}'", hash["CostTypeId"]); } if (hash["Notes"] != null) { sql += string.Format(" and Notes='{0}'", hash["Notes"]); } if (hash["ImgUrl"] != null) { sql += string.Format(" and Img_Url='{0}'", hash["ImgUrl"]); } if (hash["CreateBy"] != null) { sql += string.Format(" and Create_By='{0}'", hash["CreateBy"]); } if (hash["CreateTime"] != null) { sql += string.Format(" and Create_Time='{0}'", hash["CreateTime"]); } if (hash["UpdateBy"] != null) { sql += string.Format(" and UpDate_By='{0}'", hash["UpdateBy"]); } if (hash["UpdateTime"] != null) { sql += string.Format(" and UpDate_Time='{0}'", hash["UpdateTime"]); } //格式2013-01 if (hash["YearMonth"] != null) { sqlWhere += string.Format(" and substr(time,1,7)='{0}'", hash["YearMonth"]); } } #endregion sql += sqlWhere; DataTable dt = SqlLiteHelper.GetTable(sql, CommandType.Text); list = Model.Common <LifingCost> .ConvertToList(dt); return(list); }