public static List <Student> GetFullList() { List <Student> rList = new List <Student>(); DataTable dt = null; string sql = "SELECT StudentId,StudentName,Age,Email FROM Students ORDER BY StudentId"; try { if (DbHandler.IsMssqlConnected) { dt = DbHandler.MSSQL.SelectDataTable(sql); } else { dt = DbHandler.SQLite.SelectDataTable(sql); } if (dt == null) { return(null); } foreach (DataRow dr in dt.Rows) { Student o = Mapping(dr); if (o != null) { rList.Add(o); } } } finally { DbHandler.DisposeDataTable(ref dt); } return(rList); }
/// Get single record by ID. public static Student GetRecordById(int id) { DataTable dt = null; string sql = "SELECT StudentId,StudentName,Age,Email FROM Students WHERE StudentId=@StudentId"; try { if (DbHandler.IsMssqlConnected) { dt = DbHandler.MSSQL.SelectDataTable(sql, new SqlParameter("@StudentId", SqlDbType.Int) { Value = id }); } else { dt = DbHandler.SQLite.SelectDataTable(sql, new System.Data.SQLite.SQLiteParameter("@StudentId", DbType.Int32) { Value = id }); } if (dt.Rows.Count < 1) { return(null); } return(Mapping(dt.Rows[0])); } finally { DbHandler.DisposeDataTable(ref dt); } }
public static Models.Role GetUnit(int RoleId) { DataTable dt = null; try { string sql = "SELECT r.*,u1.DisplayName AS CreatedByDisplayName,u2.DisplayName AS UpdatedByDisplayName" + " FROM Roles r LEFT JOIN Users u1 ON r.CreatedBy=u1.UserId" + " LEFT JOIN Users u2 ON r.UpdatedBy=u2.UserId" + " WHERE r.RoleId=@RoleId"; dt = DbHandler.IsMssqlConnected ? DbHandler.MSSQL.SelectDataTable(sql, new SqlParameter("@RoleId", SqlDbType.Int) { Value = RoleId }) : DbHandler.SQLite.SelectDataTable(sql, new System.Data.SQLite.SQLiteParameter("@RoleId", DbType.Int32) { Value = RoleId }); if ((dt?.Rows.Count ?? 0) < 1) { return(null); } return(Mapping(dt.Rows[0])); } finally { DbHandler.DisposeDataTable(ref dt); } }
public static List <Models.AppFuncLevel> GetAll() { DataTable dt = null; try { string sql = "SELECT * FROM AppFuncLevels ORDER BY AppFuncLevelId"; dt = DbHandler.IsMssqlConnected ? DbHandler.MSSQL.SelectDataTable(sql) : DbHandler.SQLite.SelectDataTable(sql); if ((dt?.Rows.Count ?? 0) < 1) { return(null); } List <Models.AppFuncLevel> rList = new List <Models.AppFuncLevel>(); foreach (DataRow dr in dt.Rows) { Models.AppFuncLevel o = Mapping(dr); if (o != null) { rList.Add(o); } } return(rList); } finally { DbHandler.DisposeDataTable(ref dt); } }
public static IEnumerable <Models.AppFunction> GetAllParentList() { List <Models.AppFunction> rList; DataTable dt = null; try { rList = new List <Models.AppFunction>() { new Models.AppFunction() { AppFunctionId = 0, UniqueName = "system", DisplayName = "System", AppFuncLevelId = 0, ParentId = 0, SequentialNum = 0, IsDisabled = false, IsNavItem = false } }; string sql = "SELECT * FROM AppFunctions WHERE AppFuncLevelId<3 ORDER BY AppFuncLevelId,SequentialNum,DisplayName,UniqueName"; dt = DbHandler.IsMssqlConnected ? DbHandler.MSSQL.SelectDataTable(sql) : DbHandler.SQLite.SelectDataTable(sql); if ((dt?.Rows.Count ?? 0) > 0) { foreach (DataRow dr in dt.Rows) { Models.AppFunction o = Mapping(dr); if (o != null) { rList.Add(o); } } } return(rList); } finally { DbHandler.DisposeDataTable(ref dt); } }
public static Models.AppFuncLevel GetUnit(int id) { DataTable dt = null; try { string sql = "SELECT * FROM AppFuncLevels WHERE AppFuncLevelId=@AppFuncLevelId"; dt = DbHandler.IsMssqlConnected ? DbHandler.MSSQL.SelectDataTable(sql, new SqlParameter("@AppFuncLevelId", SqlDbType.Int) { Value = id }): DbHandler.SQLite.SelectDataTable(sql, new System.Data.SQLite.SQLiteParameter("@AppFuncLevelId", System.Data.DbType.Int32) { Value = id }); if ((dt?.Rows.Count ?? 0) > 0) { return(null); } return(Mapping(dt.Rows[0])); } finally { DbHandler.DisposeDataTable(ref dt); } }
public static List <Models.AppFunction> GetList(int AppFuncLevelId, int ParentId, bool?IsNavItem, bool isShort) { if (AppFuncLevelId > 3 || AppFuncLevelId < 0) { return(null); } DataTable dt = null; List <SqlParameter> listOfPara = null; List <System.Data.SQLite.SQLiteParameter> listOfPara2 = null; try { string sql; if (isShort) { sql = "SELECT AppFunctionId,UniqueName,DisplayName,ActionName,ControllerName,ParentId,AppFuncLevelId FROM AppFunctions WHERE AppFuncLevelId=@AppFuncLevelId AND ParentId=@ParentId" + (IsNavItem.HasValue ? " AND IsNavItem=@IsNavItem" : "") + " ORDER BY SequentialNum,DisplayName,UniqueName"; } else { sql = "SELECT * FROM AppFunctions WHERE AppFuncLevelId=@AppFuncLevelId AND ParentId=@ParentId" + (IsNavItem.HasValue ? " AND IsNavItem=@IsNavItem" : "") + " ORDER BY SequentialNum,DisplayName,UniqueName"; } if (DbHandler.IsMssqlConnected) { listOfPara = new List <SqlParameter>() { new SqlParameter("@AppFuncLevelId", SqlDbType.Int) { Value = AppFuncLevelId }, new SqlParameter("@ParentId", SqlDbType.Int) { Value = ParentId } }; if (IsNavItem.HasValue) { listOfPara.Add(new SqlParameter("@IsNavItem", SqlDbType.Bit) { Value = IsNavItem.GetValueOrDefault() }); } dt = DbHandler.MSSQL.SelectDataTable(sql, listOfPara.ToArray()); } else { listOfPara2 = new List <System.Data.SQLite.SQLiteParameter>() { new System.Data.SQLite.SQLiteParameter("@AppFuncLevelId", DbType.Int32) { Value = AppFuncLevelId }, new System.Data.SQLite.SQLiteParameter("@ParentId", DbType.Int32) { Value = ParentId } }; if (IsNavItem.HasValue) { listOfPara2.Add(new System.Data.SQLite.SQLiteParameter("@IsNavItem", DbType.Boolean) { Value = IsNavItem.GetValueOrDefault() }); } dt = DbHandler.SQLite.SelectDataTable(sql, listOfPara2.ToArray()); } if ((dt?.Rows.Count ?? 0) < 1) { return(null); } List <Models.AppFunction> rList = new List <Models.AppFunction>(); foreach (DataRow dr in dt.Rows) { Models.AppFunction o = Mapping(dr); if (o != null) { //o.ParentId = ParentId; //o.AppFuncLevelId = AppFuncLevelId; o.ChildList = GetList(AppFuncLevelId + 1, o.AppFunctionId, IsNavItem, isShort); rList.Add(o); } } return(rList); } finally { DbHandler.DisposeDataTable(ref dt); if (listOfPara != null) { listOfPara.Clear(); listOfPara = null; } if (listOfPara2 != null) { listOfPara2.Clear(); listOfPara2 = null; } } }