public bool checkLogin(string login, string password) { Database.Database_DAO.Database db = new Database.Database_DAO.Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT_LOGIN); command.Parameters.AddWithValue("@login", login); SqlDataReader reader = db.Select(command); Collection <Database.Client> Clients = Database.Database_DAO.ClientTable.Read(reader); Database.Client Client = null; if (Clients.Count == 1) { Client = Clients[0]; } else { return(false); } reader.Close(); db.Close(); if (Client.password == password) { return(true); } else { return(false); } }
public static void UpdateAvailable(string plate) { Database db = new Database(); db.Connect(); db.BeginTransaction(); try { SqlCommand command = db.CreateCommand(SQL_SPEC_ORDER); command.Parameters.AddWithValue("@truck_plate", plate); //date is fixed for testing command.Parameters.AddWithValue("@current_date", "2016-05-03"); SqlDataReader reader = db.Select(command); Collection <Orders> orders = OrdersTable.Read(reader); reader.Close(); Console.WriteLine("Changed orders: (order_id, start_date)"); foreach (Orders o in orders) { Console.WriteLine(o.order_id + " " + o.start_date); SqlCommand command2 = db.CreateCommand(SQL_UPDATE_ORDER_TRUCK); command2.Parameters.AddWithValue("@order_id", o.order_id); db.ExecuteNonQuery(command2); } SqlCommand command3 = db.CreateCommand(SQL_UPDATE_TRUCK_AVAILABLE); command3.Parameters.AddWithValue("@plate", plate); db.ExecuteNonQuery(command3); Console.WriteLine("Executed"); db.EndTransaction(); }catch (Exception e) { Console.WriteLine("Sth wrong"); db.Rollback(); } db.Close(); }
/// <summary> /// Update the record. /// </summary> /// <summary> /// Select the records. /// </summary> public static Collection <Place> Select() { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT); SqlDataReader reader = db.Select(command); Collection <Place> places = Read(reader); reader.Close(); db.Close(); Console.WriteLine("List of all the places"); foreach (Place p in places) { Console.WriteLine(p.place_id + " " + p.client_id + " " + p.city + " " + p.street + " " + p.numer + " " + p.ZIP); } return(places); }
public static void Delete(int client_id) { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_future_orders); command.Parameters.AddWithValue("@client_id", client_id); command.Parameters.AddWithValue("@current_date", Convert.ToDateTime("2016-04-01")); SqlDataReader reader = db.Select(command); Collection <Orders> orders = Read(reader); reader.Close(); foreach (Orders o in orders) { Console.WriteLine("order_id: " + o.order_id); } int selected_order_id = id(); SqlCommand command2 = db.CreateCommand("delete_order"); command2.CommandType = CommandType.StoredProcedure; SqlParameter selected_order = new SqlParameter(); selected_order.ParameterName = "@order_id"; selected_order.DbType = DbType.Int32; selected_order.Value = selected_order_id; selected_order.Direction = ParameterDirection.Input; command2.Parameters.Add(selected_order); int ret = db.ExecuteNonQuery(command2); Console.WriteLine("Deleted the " + selected_order_id + " order"); db.Close(); }
public static Collection <Orders> Select() { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT); SqlDataReader reader = db.Select(command); Collection <Orders> orders = Read(reader); reader.Close(); db.Close(); Console.WriteLine("List of orders:"); foreach (Orders t in orders) { Console.WriteLine(t.order_id + " " + t.order_status); } return(orders); }
public static Collection <Truck> Select() { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT); SqlDataReader reader = db.Select(command); Collection <Truck> trucks = Read(reader); reader.Close(); db.Close(); Console.WriteLine("List of trucks:"); foreach (Truck t in trucks) { Console.WriteLine(t.plate + " " + t.driver_id + " " + t.brand + " " + t.model + " " + t.width + " " + t.height + " " + t.available); } return(trucks); }
/// <summary> /// Select the records. /// </summary> public static Collection <Driver> Select() { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT); SqlDataReader reader = db.Select(command); Collection <Driver> drivers = Read(reader); reader.Close(); db.Close(); Console.WriteLine("All drivers: "); foreach (Driver c in drivers) { Console.WriteLine(c.driver_id + " " + c.fname + " " + c.lname); } return(drivers); }
public static Collection <Client> Select() { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_SELECT); SqlDataReader reader = db.Select(command); Collection <Client> clients = Read(reader); reader.Close(); db.Close(); Console.WriteLine("All clients:"); foreach (Client c in clients) { Console.WriteLine(c.client_id + " " + c.fname + " " + c.lname + " " + c.email); } return(clients); }
public static void Specific_places(int id) { Database db = new Database(); db.Connect(); SqlCommand command = db.CreateCommand(SQL_THE_ORDERS); command.Parameters.AddWithValue("@driver_id", id); command.Parameters.AddWithValue("@current_date", Convert.ToDateTime("2016-05-20")); //Current date is fixed because of the testing SqlDataReader reader = db.Select(command); Collection <Orders> orders = OrdersTable.Read(reader); reader.Close(); Console.WriteLine("List of drivers places:"); foreach (Orders o in orders) { SqlCommand command2 = db.CreateCommand("place_details"); command2.CommandType = CommandType.StoredProcedure; SqlParameter route = new SqlParameter(); route.ParameterName = "@route_id"; route.DbType = DbType.Int32; route.Value = o.route_id; route.Direction = ParameterDirection.Input; command2.Parameters.Add(route); SqlParameter p1city = new SqlParameter(); p1city.ParameterName = "@p1_city"; //p1city.SqlDbType = SqlDbType.VarChar; p1city.DbType = DbType.String; p1city.Size = 50; p1city.Direction = ParameterDirection.Output; command2.Parameters.Add(p1city); SqlParameter p1street = new SqlParameter(); p1street.ParameterName = "@p1_street"; p1street.DbType = DbType.String; p1street.Size = 50; p1street.Direction = ParameterDirection.Output; command2.Parameters.Add(p1street); SqlParameter p1numer = new SqlParameter(); p1numer.ParameterName = "@p1_numer"; p1numer.DbType = DbType.String; p1numer.Size = 50; p1numer.Direction = ParameterDirection.Output; command2.Parameters.Add(p1numer); SqlParameter p2city = new SqlParameter(); p2city.ParameterName = "@p2_city"; p2city.DbType = DbType.String; p2city.Size = 50; p2city.Direction = ParameterDirection.Output; command2.Parameters.Add(p2city); SqlParameter p2street = new SqlParameter(); p2street.ParameterName = "@p2_street"; p2street.DbType = DbType.String; p2street.Size = 50; p2street.Direction = ParameterDirection.Output; command2.Parameters.Add(p2street); SqlParameter p2numer = new SqlParameter(); p2numer.ParameterName = "@p2_numer"; p2numer.DbType = DbType.String; p2numer.Size = 50; p2numer.Direction = ParameterDirection.Output; command2.Parameters.Add(p2numer); db.ExecuteNonQuery(command2); string result1 = command2.Parameters["@p1_city"].Value.ToString(); string result2 = command2.Parameters["@p1_street"].Value.ToString(); string result3 = command2.Parameters["@p1_numer"].Value.ToString(); string result4 = command2.Parameters["@p2_city"].Value.ToString(); string result5 = command2.Parameters["@p2_street"].Value.ToString(); string result6 = command2.Parameters["@p2_numer"].Value.ToString(); Console.WriteLine(result1 + " " + result2 + " " + result3 + " - " + result4 + " " + result5 + " " + result6); } db.Close(); }