public List<User> GetUserList()
{
List<User> uList = new List<User>();
GetDBConnectionReference.GetDBConnectionClient gdbcc = new GetDBConnectionReference.GetDBConnectionClient();
string conString = gdbcc.GetDBConnectionString();
MySqlConnection db = new MySqlConnection(conString);
db.Open();
MySqlCommand query = new MySqlCommand();
query.Connection = db;
query.CommandText = "SELECT user_id, username, name, surname, email, phone, city, title FROM user u, role r WHERE u.role_id = r.role_id";
query.Prepare();
MySqlDataReader queryResults = query.ExecuteReader();
while (queryResults.Read())
{
User user = new User();
user.user_id = queryResults.GetInt32(0);
user.username = queryResults.GetString(1);
user.name = queryResults.GetString(2);
user.surname = queryResults.GetString(3);
user.email = queryResults.GetString(4);
user.phone = queryResults.GetString(5);
user.city = queryResults.GetString(6);
user.title = queryResults.GetString(7);
uList.Add(user);
}
queryResults.Close();
queryResults = null;
db.Close();
return uList;
}
public User GetUserInfo(int user_id)
{
User user = new User();
GetDBConnectionReference.GetDBConnectionClient gdbcc = new GetDBConnectionReference.GetDBConnectionClient();
string conString = gdbcc.GetDBConnectionString();
MySqlConnection db = new MySqlConnection(conString);
db.Open();
MySqlCommand query = new MySqlCommand();
query.Connection = db;
query.CommandText = "SELECT user_id, username, name, surname, email, phone, city, r.title FROM user u, role r WHERE u.role_id = r.role_id AND USER_ID = @user_id";
query.Prepare();
query.Parameters.AddWithValue("@user_id", user_id);
MySqlDataReader queryResults = query.ExecuteReader();
if (queryResults.Read())
{
user.user_id = queryResults.GetInt32(0);
user.username = queryResults.GetString(1);
user.name = queryResults.GetString(2);
user.surname = queryResults.GetString(3);
user.email = queryResults.GetString(4);
user.phone = queryResults.GetString(5);
user.city = queryResults.GetString(6);
user.title = queryResults.GetString(7);
}
queryResults.Close();
queryResults = null;
db.Close();
return user;
}
