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;
}
}
}
