public ActionResult GetUserList() { Users users = new Users(); string strWhere = " 1=1 "; string KeyWord = DNTRequest.GetString("KeyWord"); string RoleID = DNTRequest.GetString("RoleID"); int rows = DNTRequest.GetInt("rows", 10); if (KeyWord != "") { strWhere += " and (Uname like '%" + KeyWord + "%') or ( UId in (select UId from .[dbo].Users where truename like '%" + KeyWord + "%')) "; } if (RoleID != "") { strWhere += " and UId in (select UId from .[dbo].Users where roleId=" + RoleID.ToString() + ")"; } int count = 0; //string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc"; //string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere; DataSet ds = bll.GetUserList(); // DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count); List <VURser> list = TBToList <VURser> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <VURser> >(); grid.total = count; grid.rows = list; return(Json(grid, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 普通无分页查询 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <returns></returns> public static List <T> GetEntitiesListByDB <T>(string sql) { IRDBHelper helper = null; List <T> list = new List <T>(); try { string strCon = ManageConfig.instance.GetConnectionStrings("SHHConnection"); helper = RDBFactory.CreateDbHelper(strCon, DatabaseType.MSSQL); DataTable dataTable = helper.ExecuteDatatable("table", sql, true); list = TBToList.ToList <T>(dataTable); return(list); } catch (Exception ex) { LogAPI.Debug(ex); return(list); } finally { if (helper != null) { helper.DisConnect(); helper = null; } } }
public ActionResult GetRoleList(int?page) { page = page == null ? 1 : page; //string sql = "select m.*,d.ItemText StatusName from tb_Role m left join tb_Doption d on d.MoptionID=2 and d.ItemValue=m.Status where 1=1 "; string KeyWord = DNTRequest.GetString("KeyWord"); string Status = DNTRequest.GetString("Status"); int rows = DNTRequest.GetInt("rows", 10); //if (KeyWord != "") //{ // sql += " and (m.RoleName like '%" + KeyWord + "%') "; //} //if (Status != "") //{ // sql += " and (m.Status =" + Status + ")"; //} int count = 0; DataSet ds = ubll.GetList(); //DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, page.Value, rows, "RoleID desc"), out count); List <Role> list = TBToList <Role> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <Role> >(); grid.total = count; grid.rows = list; //JavaScriptSerializer js = new JavaScriptSerializer(); return(Json(grid, JsonRequestBehavior.AllowGet)); }
public List <Share_County> GetCountyByCode(string code) { string sql = "select top 1 * from Share_County where code=@code"; SqlParameter[] parameters = { new SqlParameter("@code", code) }; DataTable dt = SqlHelper.GetDataTable(CommandType.Text, sql, parameters); IList <Share_County> IList = new TBToList <Share_County>().ToList(dt); return(IList.ToList()); }
/// <summary> /// RDBHelper 查询分页 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql">sql语句</param> /// <param name="skip">页数</param> /// <param name="take">每页显示条数</param> /// <param name="count">总数</param> /// <returns></returns> public static List <T> GetEntitiesListByDB <T>(string sql, int skip, int take, out int count) { IRDBHelper helper = null; List <T> list = new List <T>(); try { string strCon = ManageConfig.instance.GetConnectionStrings("SHHConnection"); helper = RDBFactory.CreateDbHelper(strCon, DatabaseType.MSSQL); if (helper != null) { string countSQL = string.Format("select count(1) as NUM from({0})", sql); DataTable dt = helper.ExecuteDatatable("table", countSQL, true); if (dt != null && dt.Rows.Count > 0) { count = int.Parse(dt.Rows[0]["NUM"].ToString()); } else { count = 0; LogAPI.Debug("查询总数失败!"); } //分页查询 string strBegin = "select * from (select a.* ,rownum rn from ( "; string strEnd = string.Format(")a where rownum < {0} ) where rn>{1} ", take, skip); string querySQL = strBegin + sql + strEnd; DataTable dataTable = helper.ExecuteDatatable("table", querySQL, true); list = TBToList.ToList <T>(dataTable); } else { count = 0; LogAPI.Debug("数据库打开失败!"); } return(list); } catch (Exception ex) { count = 0; LogAPI.Debug("查询受理列表异常:"); LogAPI.Debug(ex); return(list); } finally { if (helper != null) { helper.DisConnect(); helper = null; } } }
public ActionResult GetGoodsList() { int count = 0; //string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc"; //string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere; DataSet ds = bll.GetGoodsList(); List <Goods> list = TBToList <Goods> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <Goods> >(); grid.total = count; grid.rows = list; return(Json(grid, JsonRequestBehavior.AllowGet)); }
public ActionResult GetSOList() { int count = 0; //string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc"; //string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere; DataSet ds = bll.GetsOrdermin(); // DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count); List <VOrderSminxi> list = TBToList <VOrderSminxi> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <VOrderSminxi> >(); grid.total = count; grid.rows = list; return(Json(grid, JsonRequestBehavior.AllowGet)); }
public EasyUIGridModel GetAttachLst(FileUploadQueryForm form, EasyUIGridSetting gridSetting) { string sql = string.Format(@"SELECT CID,PNODE as SLBH,CNAME,to_char(CREATEDATE,'yy-mm-dd hh24:mi:ss') CREATEDATE FROM (Select ROWNUM AS ROWNO, T.* from (select CID,PNODE,CNAME,CREATEDATE from WFM_ATTACHLST where {0}) T WHERE ROWNUM <= {1}) TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO > {2}", form.GetWhere(), gridSetting.PageIndex * gridSetting.PageSize, (gridSetting.PageIndex - 1) * gridSetting.PageSize); string connectStr = ConfigurationManager.ConnectionStrings["bdcggkConnection"].ToString(); DataSet ds = DBHelper.GetDataSet(connectStr, sql); TBToList <AttachModel> list = new TBToList <AttachModel>(); IList <AttachModel> tags = list.ConvertToModel(ds.Tables[0]); string countSql = string.Format("Select count(1) from WFM_ATTACHLST where {0}", form.GetWhere()); int count = DBHelper.GetScalar(connectStr, countSql); EasyUIGridModel ret = new EasyUIGridModel(gridSetting.PageIndex, count, tags); return(ret); }
public EasyUIGridModel GetWriteBackResult(WriteBackResultQueryForm form, EasyUIGridSetting gridSetting) { string sql = string.Format(@"SELECT SLBH,DJLX,SFTS,PUSHDATA,PUSHDATE,PUSHRESULT,WBERRINFO,CGERRINFO,PID,ADDRESSTYPE FROM (Select ROWNUM AS ROWNO, T.* from (select * from FC_SPFHX_TAG where {0} order by pushdate desc) T WHERE ROWNUM <= {1}) TABLE_ALIAS WHERE TABLE_ALIAS.ROWNO > {2} ", form.GetWhere(), gridSetting.PageIndex * gridSetting.PageSize, (gridSetting.PageIndex - 1) * gridSetting.PageSize, gridSetting.SortBy.PropertyName, gridSetting.SortBy.SortType); if (gridSetting.SortBy.PropertyName != "") { sql += " order by " + gridSetting.SortBy.PropertyName + " " + gridSetting.SortBy.SortType; } DataSet ds = DBHelper.GetDataSet(sql); TBToList <SPFHX_TAG> list = new TBToList <SPFHX_TAG>(); IList <SPFHX_TAG> tags = list.ConvertToModel(ds.Tables[0]); string countSql = string.Format("Select count(1) from FC_SPFHX_TAG where {0}", form.GetWhere()); int count = DBHelper.GetScalar(countSql); EasyUIGridModel ret = new EasyUIGridModel(gridSetting.PageIndex, count, tags); return(ret); }
public ActionResult GetProductTypeList(int id = 0, string KeyWord = "") { int count = 0; string sql = "SELECT [id] ,[code] ,[name],[ParentId],[AddTime] FROM [" + DBName + "].[dbo].[tb_ProductType] "; string strWhere = " Where 1=1"; if (KeyWord != "") { strWhere += " and ( id in ( select id from [" + DBName + "].[dbo].[tb_ProductType] where code like '%" + KeyWord + "%' or name like '%" + KeyWord + "%' ) "; strWhere += " or id in ( select ParentId from [" + DBName + "].[dbo].[tb_ProductType] where code like '%" + KeyWord + "%' or name like '%" + KeyWord + "%' ) ) "; } //sql = sql + strWhere; //DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 1, 10000, "id desc"), out count); //List<MenuPage> list = TBToList<MenuPage>.ConvertToList(ds.Tables[0]).ToList(); ////List<tb_ProductType> list = ef.tb_ProductType.Where(p => p.MenuID > 0).ToList(); //List<MenuPage> listJson = new List<MenuPage>(); //foreach (tb_ProductType model in list.Where(p => p.ParentId == 0).OrderBy(p => p.id)) //{ // MakeMenuJson(list, model, id); // listJson.Add(model); //} //return Json(listJson, JsonRequestBehavior.AllowGet); GoodsBLL bn = new GoodsBLL(); //string sql = @"select p.Uname,p.Sex,p.roleId,p.truename,p.UId from Users p order by UId desc"; //string sql = @"select * ,(select top 1 truename from [dbo].Users where UId=m.UId) as truename, (select roleName from Role where roleId in(select top 1 roleId from Users where UId=m.UId)) as roelName from .[dbo].Users m where " + strWhere; DataSet ds = bn.GetMenu(); // DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, 7, rows, "UId desc"), out count); List <MenuPage> list = TBToList <MenuPage> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <MenuPage> >(); grid.total = count; grid.rows = list; return(Json(grid, JsonRequestBehavior.AllowGet)); }
public ActionResult GetListtb_Store(int?page) { string KeyWord = DNTRequest.GetString("KeyWord"); int rows = DNTRequest.GetInt("rows", 10); string strWhere = " 1=1 "; if (KeyWord != "") { strWhere += " and (Name like '%" + KeyWord + "%' or code like '%" + KeyWord + "%') "; } int count = 0; DataSet ds = gb.GetMenushop(); //string sql = @"select * from [" + DBName + @"].[dbo].[tb_Store] where " + strWhere; //DataSet ds = DataPageHelper.GetDataPage(DataPageHelper.GetPageSql(sql, page.Value, rows, "id desc"), out count); List <VUShop> list = TBToList <VUShop> .ConvertToList(ds.Tables[0]).ToList(); var grid = new EasyuiDataGrid <List <VUShop> >(); grid.total = count; grid.rows = list; return(Json(grid, JsonRequestBehavior.AllowGet)); }