public void AddUser(string name, string login, string password, string address, int age)
{
MySqlConnection connect = DBWorker.getMySqlConnection();
connect.Open();
string query = "INSERT INTO users (name, login, password, address, age) VALUES (@name, @login, @password," +
"@address, @age)";
MySqlCommand command = new MySqlCommand(query, connect);
command.Parameters.Add(new MySqlParameter("@name", name));
command.Parameters.Add(new MySqlParameter("@login", login));
command.Parameters.Add(new MySqlParameter("@password", password));
command.Parameters.Add(new MySqlParameter("@address", address));
command.Parameters.Add(new MySqlParameter("@age", age));
command.ExecuteNonQuery();
connect.Close();
}
public List <Order> GetAllOrders()
{
MySqlConnection connect = DBWorker.getMySqlConnection();
connect.Open();
string query = "SELECT * FROM orders";
MySqlCommand command = new MySqlCommand(query, connect);
List <Order> orderCol = new List <Order>(); // collection for my users
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int idO = reader.GetOrdinal("id");
int user_idO = reader.GetOrdinal("user_id");
int nameO = reader.GetOrdinal("name");
int modelO = reader.GetOrdinal("model");
long id = Convert.ToInt64(reader.GetValue(idO));
long user_id = Convert.ToInt64(reader.GetValue(user_idO));
string name = reader.GetString(nameO);
int model = Convert.ToInt32(reader.GetValue(modelO));
Order tempOrder = new Order(); // temp user
tempOrder.id = id;
tempOrder.user_id = user_id;
tempOrder.name = name;
tempOrder.model = model;
orderCol.Add(tempOrder);
}
}
}
connect.Close();
return(orderCol);
}
public List <UserWithOrder> GetUserWithOrders(int id)
{
MySqlConnection connect = DBWorker.getMySqlConnection();
connect.Open();
string query = "SELECT users.name, orders.name, orders.model FROM users JOIN orders ON users.id = orders.users_id" +
"WHERE users.id = " + id;
MySqlCommand command = new MySqlCommand(query, connect);
List <UserWithOrder> usWithOr = new List <UserWithOrder>();
using (DbDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
int nameO = reader.GetOrdinal("name");
int nameOrderO = reader.GetOrdinal("name");
int modelOrderO = reader.GetOrdinal("model");
string name = reader.GetString(nameO);
string nameOrder = reader.GetString(nameOrderO);
int model = Convert.ToInt32(reader.GetValue(modelOrderO));
UserWithOrder userAndOrders = new UserWithOrder(); // temp user
userAndOrders.name = name;
userAndOrders.nameOrder = nameOrder;
userAndOrders.model = model;
usWithOr.Add(userAndOrders);
}
}
}
connect.Close();
return(usWithOr);
}