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