public static DataTable GetList(SqlCondition[] conditions)
{
String conditionSql = "";
StringBuilder conditionStringBuilder = new StringBuilder();
List<SqlParameter> paramList = new List<SqlParameter>();
SqlParameter[] paramItem;
// WHERE clause
if (conditions != null) {
foreach (SqlCondition condition in conditions) {
switch (condition.ColumnName.ToLower()) {
case "deptid":
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "userid":
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.VarChar, 6);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "flag":
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
default:
break;
}
}
if (conditionStringBuilder.Length > 0) {
conditionSql = conditionStringBuilder.ToString();
conditionSql = "WHERE " + conditionSql.Substring("AND ".Length);
}
}
string sql = "SELECT * FROM LeaveInfoView " + conditionSql + " ORDER BY ID";
return helper.ExecuteDataTable(sql, paramList.ToArray());
}
public static DataTable GetList(string UserIDs, string SortColumn = "UserId")
{
SqlCondition[] conditions = new SqlCondition[1];
conditions[0] = new SqlCondition("id", UserIDs.Split(','), SqlCondition.EnumConstraintType.In);
return UserDao.GetList(conditions.ToArray(), SortColumn);
}
/// <summary>
/// Get a list of users with specified parameters.
/// </summary>
/// <param name="conditions">Condition items will be connected by "AND"</param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="sortBy"></param>
/// <returns></returns>
/// <example>
/// SqlCondition[] conditions = new SqlCondition[2];
/// conditions[0] = new SqlCondition("Active", new String[] { "0", "1" }, SqlCondition.EnumConstraintType.In);
/// conditions[1] = new SqlCondition("PositionID", new String[] { "0", "1" }, SqlCondition.EnumConstraintType.Between);
/// DataTable userTable = UserDao.GetList(conditions, 1, 6, new String[] { "PositionID", "Active DESC" });
/// </example>
public static DataTable GetList(SqlCondition[] conditions, int pageIndex, int pageSize, string sortBy, out int pageCount)
{
String conditionSql = "";
StringBuilder conditionStringBuilder = new StringBuilder();
List<SqlParameter> paramList = new List<SqlParameter>();
SqlParameter[] paramItem;
String sort = "";
// WHERE clause
if (conditions != null) {
foreach (SqlCondition condition in conditions) {
switch (condition.ColumnName.ToLower()) {
case "id":
condition.ColumnName = "ID";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "code":
condition.ColumnName = "logonUser";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.VarChar, 20);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "roletype":
condition.ColumnName = "roleType";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "active":
condition.ColumnName = "flag";
object[] flagValue = {(bool)condition.ColumnValue[0] ? 1 : 0};
condition.ColumnValue = flagValue;
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "deptid":
condition.ColumnName = "depId";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
default:
break;
}
}
if (conditionStringBuilder.Length > 0) {
conditionSql = conditionStringBuilder.ToString();
conditionSql = "WHERE " + conditionSql.Substring("AND ".Length);
}
}
// ORDER BY clause
if (!string.IsNullOrWhiteSpace(sortBy)) {
string sortDirection = "ASC";
if (sortBy.ToLower().IndexOf(" desc") > 0) {
sortDirection = "DESC";
}
string sortColumnName = "";
sortColumnName = sortBy.ToLower().Replace(" asc", "").Replace(" desc", "");
switch (sortColumnName) {
case "id":
sortColumnName = "ID";
break;
case "code":
sortColumnName = "logonUser";
break;
case "password":
sortColumnName = "passwd";
break;
case "roletype":
sortColumnName = "roleType";
break;
case "active":
sortColumnName = "flag";
break;
case "deptid":
sortColumnName = "depId";
break;
default:
sortColumnName = "ID";
break;
}
sort = "ORDER BY " + sortColumnName + " " + sortDirection;
}
if (sort.Length == 0) {
sort = "ORDER BY ID";
}
//Make up final query statment
StringBuilder sql = new StringBuilder();
if (pageIndex > 0 && pageSize > 0) {
sql.AppendLine("SELECT * FROM");
sql.AppendLine(" (");
sql.AppendLine(" SELECT TOP " + (pageIndex * pageSize).ToString() + " ROW_NUMBER() OVER(" + sort + ") AS RowNo, * FROM LogonUserView");
sql.AppendLine(" " + conditionSql);
sql.AppendLine(" " + sort);
sql.AppendLine(" ) AS UsersTopRows");
sql.AppendLine("WHERE RowNo>" + ((pageIndex - 1) * pageSize).ToString());
}
else {
sql.AppendLine("SELECT * FROM LogonUserView");
sql.AppendLine(conditionSql);
sql.AppendLine(sort);
}
if (pageSize > 0) {
int rowCount = int.Parse(helper.ExecuteScalar("SELECT COUNT(*) FROM LogonUserView " + conditionSql, paramList.ToArray()).ToString());
pageCount = (rowCount - 1) / pageSize + 1;
}
else {
pageCount = 0;
}
return helper.ExecuteDataTable(sql.ToString(), paramList.ToArray());
}
public static DataTable GetList(SqlCondition[] conditions, string SortColumn = "userId")
{
String conditionSql = "";
StringBuilder conditionStringBuilder = new StringBuilder();
List<SqlParameter> paramList = new List<SqlParameter>();
SqlParameter[] paramItem;
// WHERE clause
if (conditions != null) {
foreach (SqlCondition condition in conditions) {
switch (condition.ColumnName.ToLower()) {
case "id":
condition.ColumnName = "userId";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.VarChar, 6);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "name":
condition.ColumnName = "userName";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.VarChar, 10);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "deptid":
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.Int);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
case "senderid":
condition.ColumnName = "senderId";
conditionSql = condition.GetConditionSqlInParameter(out paramItem, SqlDbType.VarChar, 6);
if (conditionSql.Length > 0) {
conditionStringBuilder.AppendLine("AND " + conditionSql);
paramList.AddRange(paramItem);
}
break;
default:
break;
}
}
if (conditionStringBuilder.Length > 0) {
conditionSql = conditionStringBuilder.ToString();
conditionSql = "WHERE " + conditionSql.Substring("AND ".Length);
}
}
string sql = "SELECT * FROM UserInfoView " + conditionSql;
if (!String.IsNullOrWhiteSpace(SortColumn)) {
sql += " ORDER BY " + SortColumn;
}
return helper.ExecuteDataTable(sql, paramList.ToArray());
}