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