public static void delete_Db(Rezervare rez, string db = null)
{
if (db == null)
{
// Verifica daca exista fisierul cu configuratii
if (ConfigurationManager.AppSettings.Count != 0)
{
db = ConfigurationManager.AppSettings["dbLocation"];
}
else
{
db = Program.DB_LOCATION;
}
}
OleDbConnection connection = new OleDbConnection(connString + db);
try {
connection.Open();
OleDbCommand delCom = new OleDbCommand("DELETE FROM Rezervari WHERE Id = ?", connection);
delCom.Parameters.Add("Id", OleDbType.Integer).Value = rez.Id;
delCom.ExecuteNonQuery();
}
catch (Exception ex) {
MessageBox.Show(ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
connection.Close();
}
}
public static void citireRezervariDb(ref List <Rezervare> rezervari, string db = null)
{
if (db == null)
{
// Verifica daca exista fisierul cu configuratii
if (ConfigurationManager.AppSettings.Count != 0)
{
db = ConfigurationManager.AppSettings["dbLocation"];
}
else
{
db = Program.DB_LOCATION;
}
}
if (rezervari != null)
{
rezervari.Clear();
}
else
{
rezervari = new List <Rezervare>();
}
OleDbConnection connection = new OleDbConnection(connString + db);
try {
connection.Open();
OleDbCommand com = new OleDbCommand("SELECT * FROM REZERVARI");
com.Connection = connection;
OleDbDataReader reader = com.ExecuteReader();
while (reader.Read())
{
Rezervare rz = new Rezervare(
Convert.ToInt32(reader["Id"]),
Convert.ToInt16(reader["Nr_camera"]),
new Persoana(
reader["Nume_persoana"].ToString(),
Convert.ToInt64(reader["Cnp_persoana"])
),
Convert.ToDateTime(reader["Data_rezervare"]),
Convert.ToDateTime(reader["Data_restituire"]),
Convert.ToUInt32(reader["Pret_total"])
);
rezervari.RemoveAll(r => r.Id == rz.Id);
rezervari.Add(rz);
}
} catch (Exception ex) {
MessageBox.Show(ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
} finally {
connection.Close();
}
}
// Rezervari
public static void citireRezervari(ref List <Rezervare> rezervari, string file)
{
if (rezervari == null)
{
rezervari = new List <Rezervare>();
}
try {
StreamReader sr = new StreamReader(new FileStream(file, FileMode.Open, FileAccess.Read));
string fisier = sr.ReadToEnd();
foreach (string linie in fisier.Split(Environment.NewLine.ToArray()))
{
string[] rez = linie.Split(',');
if (rez.Length != 7)
{
continue;
}
try {
Rezervare rz = new Rezervare(
Convert.ToInt32(rez[0]),
Convert.ToInt16(rez[1]),
new Persoana(rez[2], Convert.ToInt64(rez[3])),
Convert.ToDateTime(rez[4]),
Convert.ToDateTime(rez[5]),
Convert.ToUInt32(rez[6])
);
rezervari.RemoveAll(r => r.Id == rz.Id);
rezervari.Add(rz);
} catch (FormatException ex) {
MessageBox.Show("Eroare de format\n\n" + ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
sr.Close();
}catch (FileNotFoundException) { }
catch (Exception ex) {
MessageBox.Show(ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public static void addOrUpdate_Db(Rezervare rez, string db = null)
{
if (db == null)
{
// Verifica daca exista fisierul cu configuratii
if (ConfigurationManager.AppSettings.Count != 0)
{
db = ConfigurationManager.AppSettings["dbLocation"];
}
else
{
db = Program.DB_LOCATION;
}
}
OleDbConnection connection = new OleDbConnection(connString + db);
try {
connection.Open();
OleDbCommand selCom = new OleDbCommand("SELECT COUNT(*) FROM Rezervari WHERE Id = ?");
selCom.Parameters.Add("Id", OleDbType.Integer).Value = rez.Id;
selCom.Connection = connection;
int rowCount = (int)selCom.ExecuteScalar();
OleDbCommand command;
if (rowCount == 0)
{
command = new OleDbCommand("INSERT INTO Rezervari VALUES(@id, @nr_cam, @nume_p, @cnp_p, @data_rez, @data_rest, @pret_t)", connection);
command.Parameters.Add("@id", OleDbType.Integer).Value = rez.Id;
command.Parameters.Add("@nr_cam", OleDbType.Integer).Value = rez.NrCamera;
command.Parameters.Add("@nume_p", OleDbType.VarChar).Value = rez.Pers.Nume;
command.Parameters.Add("@cnp_p", OleDbType.VarChar).Value = rez.Pers.CNP.ToString();
command.Parameters.Add("@data_rez", OleDbType.Date).Value = rez.DataInchiriere;
command.Parameters.Add("@data_rest", OleDbType.Date).Value = rez.DataRestituire;
command.Parameters.Add("@pret_t", OleDbType.Integer).Value = rez.Pret;
}
else
{
command = new OleDbCommand("UPDATE Rezervari SET " +
"Nr_camera = @nr_cam, Nume_persoana = @nume_p, Cnp_persoana = @cnp_p," +
"Data_rezervare = @data_rez, Data_restituire = @data_rest, Pret_total = @pret_t " +
"WHERE Id = @id", connection);
command.Parameters.Add("@nr_cam", OleDbType.Integer).Value = rez.NrCamera;
command.Parameters.Add("@nume_p", OleDbType.VarChar).Value = rez.Pers.Nume;
command.Parameters.Add("@cnp_p", OleDbType.VarChar).Value = rez.Pers.CNP.ToString();
command.Parameters.Add("@data_rez", OleDbType.Date).Value = rez.DataInchiriere;
command.Parameters.Add("@data_rest", OleDbType.Date).Value = rez.DataRestituire;
command.Parameters.Add("@pret_t", OleDbType.Integer).Value = rez.Pret;
command.Parameters.Add("@id", OleDbType.Integer).Value = rez.Id;
}
command.ExecuteNonQuery();
}
catch (Exception ex) {
MessageBox.Show(ex.Message, "Eroare", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally {
connection.Close();
}
}