/// <summary> /// Get an object entity /// </summary> public ModulesEntity Get(int ModuleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from Modules "); strSql.Append(" where ModuleID=@ModuleID "); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ModuleID", DbType.Int32, ModuleID); ModulesEntity model = null; using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { model = ModulesEntity.ReaderBind(dataReader); } } return(model); } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } }
public List <ModulesEntity> GetModulesList(int roleID) { StringBuilder strSql = new StringBuilder(); strSql.Append("IF(@RoleID=0)"); strSql.Append(" BEGIN"); strSql.Append(" SELECT m.*"); strSql.Append(" FROM [Modules] m "); strSql.Append(" WHERE [Status] =0 OR [Status] =1"); strSql.Append(" ORDER BY Orders DESC ,ModuleID DESC"); strSql.Append(" END "); strSql.Append("ELSE"); strSql.Append(" BEGIN"); strSql.Append(" SELECT m.*"); strSql.Append(" FROM [Modules] m,RoleModules rm"); strSql.Append(" WHERE rm.RoleID=@RoleID AND m.ModuleID=rm.ModuleID"); strSql.Append(" ORDER BY Orders DESC ,ModuleTitle ASC"); strSql.Append(" END"); List <ModulesEntity> list = new List <ModulesEntity>(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "RoleID", DbType.Int32, roleID); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { list.Add(ModulesEntity.ReaderBind(dataReader)); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(list); }
public List <ModulesEntity> GetAllModules(int parentID, int page, int pageCount) { int start = page * pageCount + 1 - pageCount; int end = page * pageCount; StringBuilder strSql = new StringBuilder(); strSql.Append(@"SELECT * FROM ("); strSql.Append("SELECT ROW_NUMBER() OVER(Order BY ParentID ASC, Orders DESC,ModuleTitle ASC) AS INDEX_ID,* "); strSql.Append("FROM [Modules] "); strSql.Append(@"WHERE [ParentID]=@ParentID AND ([Status] =0 OR [Status] =1 ) ) NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End; "); List <ModulesEntity> list = new List <ModulesEntity>(); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "ParentID", DbType.Int32, parentID); db.AddInParameter(dbCommand, "Strat", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { while (dataReader.Read()) { list.Add(ModulesEntity.ReaderBind(dataReader)); } } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(list); }