public static Fine SelectByReservation(int reservationId)
{
string sql = $"SELECT * FROM fine WHERE fk_reservation = {reservationId}";
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
mySqlConnection.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(mySqlCommand);
DataTable dt = new DataTable();
mda.Fill(dt);
mySqlConnection.Close();
mda.Dispose();
if (dt.Rows.Count == 0)
{
return(null);
}
var row = dt.Rows[0];
Fine fine = new Fine
{
id = Convert.ToInt32(row["id"]),
date = Convert.ToDateTime(row["Date"]),
sum = Convert.ToDouble(row["Sum"]),
state = (FineState)Convert.ToInt32(row["State"]),
fk_reservation = Convert.ToInt32(row["fk_reservation"])
};
return(fine);
}
public List <Fine> GetFines()
{
string sql = $"SELECT * FROM fine";
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
mySqlConnection.Open();
MySqlDataAdapter mda = new MySqlDataAdapter(mySqlCommand);
DataTable dt = new DataTable();
mda.Fill(dt);
mySqlConnection.Close();
mda.Dispose();
List <Fine> list = new List <Fine>();
foreach (DataRow row in dt.Rows)
{
Fine fine = new Fine
{
id = Convert.ToInt32(row["id"]),
date = Convert.ToDateTime(row["Date"]),
sum = Convert.ToDouble(row["Sum"]),
state = (FineState)Convert.ToInt32(row["State"]),
fk_reservation = Convert.ToInt32(row["fk_reservation"])
};
list.Add(fine);
}
return(list);
}
public static void Create(Fine fine)
{
string sql = $"INSERT INTO `fine` (`Date`, `Sum`, `State`, `fk_reservation`) VALUES ('{fine.date.ToString("yyyy-MM-dd HH:mm:ss.fff")}', '{fine.sum}', '{(int)fine.state + 1}', '{fine.fk_reservation}')";
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
}
public static bool UpdateFineSum(Fine fine, double sum)
{
string sql = $"UPDATE fine SET fine.Sum = '{sum}' WHERE fine.id = '{fine.id}'";
string conn = ConfigurationManager.ConnectionStrings["MysqlConnection"].ConnectionString;
MySqlConnection mySqlConnection = new MySqlConnection(conn);
MySqlCommand mySqlCommand = new MySqlCommand(sql, mySqlConnection);
mySqlConnection.Open();
mySqlCommand.ExecuteNonQuery();
mySqlConnection.Close();
return(true);
}