public List<UserDataGridItem> QueryAll()
{
DataTable dataTable = new DataTable();
List<UserDataGridItem> userList = new List<UserDataGridItem>();
SqlCommand query = new SqlCommand("SELECT * FROM [USER]");
dataTable = dataAccess.Query(query);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
UserDataGridItem userObject = new UserDataGridItem();
userObject.USER_ID = (int)dataTable.Rows[i][0];
userObject.USER_NAME = (string)dataTable.Rows[i][1];
userObject.USER_EMAIL = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
userObject.USER_ROLE = (string)dataTable.Rows[i][4];
userList.Add(userObject);
}
return userList;
}
public List<UserDataGridItem> QueryByItem(string item)
{
DataTable dataTable = new DataTable();
List<UserDataGridItem> userList = new List<UserDataGridItem>();
string query = "SELECT * FROM [USER] WHERE USER_ID = @USER_ID"
+" OR USER_NAME LIKE @USER_NAME OR USER_EMAIL LIKE @USER_EMAIL"
+ " OR USER_ROLE LIKE @USER_ROLE";
SqlCommand queryCommand = new SqlCommand(query);
SqlParameter userIDParam = new SqlParameter("@USER_ID", SqlDbType.Int);
int intValOfitem = 0;
int.TryParse(item, out intValOfitem);
userIDParam.Value = intValOfitem;
SqlParameter userNameParam = new SqlParameter("@USER_NAME", SqlDbType.VarChar, 50);
userNameParam.Value = "%" + item + "%";
SqlParameter userEmailParam = new SqlParameter("@USER_EMAIL", SqlDbType.VarChar, 50);
userEmailParam.Value = "%" + item + "%";
SqlParameter userRoleParam = new SqlParameter("@USER_ROLE", SqlDbType.VarChar, 50);
userRoleParam.Value = "%" + item + "%";
queryCommand.Parameters.Add(userIDParam);
queryCommand.Parameters.Add(userNameParam);
queryCommand.Parameters.Add(userEmailParam);
queryCommand.Parameters.Add(userRoleParam);
dataTable = dataAccess.Query(queryCommand);
for (int i = 0; i < dataTable.Rows.Count; i++)
{
UserDataGridItem userObj = new UserDataGridItem();
userObj.USER_ID = (int)dataTable.Rows[i][0];
userObj.USER_NAME = (string)dataTable.Rows[i][1];
userObj.USER_EMAIL = (dataTable.Rows[i][2] == DBNull.Value) ? string.Empty : dataTable.Rows[i][2].ToString();
userObj.USER_ROLE = (string)dataTable.Rows[i][4];
userList.Add(userObj);
}
return userList;
}
