public Rezervace()
{
Id = 0;
Zbran = new Zbran();
Zakaznik = new Zakaznik();
Prostor = new Prostor();
}
public static Zbran getById(int id)
{
Zbran zbr = null;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "SELECT * FROM Zbran WHERE idZbr=@id;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
zbr = new Zbran(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDouble(3), reader.GetInt32(4));
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(zbr);
}
private static void PrepareCommand(SqlCommand command, Zbran Zbran)
{
command.Parameters.AddWithValue("@idZbr", Zbran.Id);
command.Parameters.AddWithValue("@Nazev", Zbran.Nazev);
command.Parameters.AddWithValue("@Typ_zbrane", Zbran.Typ);
command.Parameters.AddWithValue("@Raze", Zbran.Raze);
command.Parameters.AddWithValue("@Rok_vyroby", Zbran.Rok_vyroby);
}
public Strelba()
{
Id = 0;
Zbran = new Zbran();
Zamestnanec = new Zamestnanec();
Zakaznik = new Zakaznik();
Prostor = new Prostor();
}
public Rezervace(int ID, DateTime s, DateTime v, Zbran zbran, Zakaznik zakaznik, Prostor prostor)
{
Id = ID;
Start = s;
Vytvoreni = v;
Zbran = zbran;
Zakaznik = zakaznik;
Prostor = prostor;
}
public Strelba(int id, DateTime zacatek, DateTime konec, Zbran zbran, Zakaznik zakaznik, Prostor prostor, Zamestnanec zamestnanec)
{
Id = id;
Prostor = prostor;
Zakaznik = zakaznik;
Zamestnanec = zamestnanec;
Zbran = zbran;
Zacatek = zacatek;
Konec = konec;
}
public static int delete(Zbran Zbran)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "DELETE FROM Zbran Where idZbr = @idZbr";
string sql1 = "UPDATE Rezervace SET Zbran_idZbr=null WHERE Zbran_idZbr=@idZbr";
string sql2 = "UPDATE Strelba SET Zbran_idZbr=null WHERE Zbran_idZbr=@idZbr";
using (SqlCommand command = new SqlCommand(sql1, connection))
{
command.Parameters.AddWithValue("@idZbr", Zbran.Id);
ret = command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand(sql2, connection))
{
command.Parameters.AddWithValue("@idZbr", Zbran.Id);
ret = command.ExecuteNonQuery();
}
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@idZbr", Zbran.Id);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}
public static int update(Zbran Zbran)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "UPDATE Zbran SET Nazev=@Nazev, Typ_zbrane=@Typ_zbrane, Raze=@Raze, Rok_vyroby=@Rok_vyroby WHERE idZbr=@idZbr";
using (SqlCommand command = new SqlCommand(sql, connection))
{
PrepareCommand(command, Zbran);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}
public static int insert(Zbran Zbran)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "INSERT INTO Zbran (Nazev, Typ_Zbrane, Raze, Rok_Vyroby) VALUES (@Nazev, @Typ_zbrane, @Raze, @Rok_vyroby)";
using (SqlCommand command = new SqlCommand(sql, connection))
{
PrepareCommand(command, Zbran);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}
public static Collection <Strelba> SelectAll()
{
Collection <Strelba> Strelba = new Collection <Strelba>();
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "SELECT * FROM Strelba;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Strelba.Add(new Strelba(reader.GetInt32(0), reader.GetDateTime(1), reader.GetDateTime(2), reader.IsDBNull(3) ? new Zbran() : Zbran.getById(reader.GetInt32(3)),
reader.IsDBNull(4) ? new Zakaznik() : Zakaznik.getById(reader.GetInt32(4)), reader.IsDBNull(5) ? new Prostor() : Prostor.getById(reader.GetInt32(5)),
reader.IsDBNull(6) ? new Zamestnanec() : Zamestnanec.getById(reader.GetInt32(5))));
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(Strelba);
}
public static Strelba getById(int id)
{
Strelba str = null;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "SELECT * FROM Strelba WHERE idStr=@id;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
str = new Strelba(reader.GetInt32(0), reader.GetDateTime(1), reader.GetDateTime(2), reader.IsDBNull(3) ? new Zbran() : Zbran.getById(reader.GetInt32(3)),
reader.IsDBNull(4) ? new Zakaznik() : Zakaznik.getById(reader.GetInt32(4)), reader.IsDBNull(5) ? new Prostor() : Prostor.getById(reader.GetInt32(5)),
reader.IsDBNull(6) ? new Zamestnanec() : Zamestnanec.getById(reader.GetInt32(5)));
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(str);
}
public static Collection <Rezervace> SelectAll()
{
Collection <Rezervace> Rezervace = new Collection <Rezervace>();
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "SELECT * FROM Rezervace;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Rezervace.Add(new Rezervace(reader.GetInt32(0), reader.GetDateTime(1), reader.GetDateTime(2), Zbran.getById(reader.GetInt32(3)), Zakaznik.getById(reader.GetInt32(4)), Prostor.getById(reader.GetInt32(5))));
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(Rezervace);
}
public static Rezervace getById(int id)
{
Rezervace rez = null;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "SELECT * FROM Rezervace WHERE idRez=@id;";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", id);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
rez = new Rezervace(reader.GetInt32(0), reader.GetDateTime(1), reader.GetDateTime(2), Zbran.getById(reader.GetInt32(3)), Zakaznik.getById(reader.GetInt32(4)), Prostor.getById(reader.GetInt32(5)));
}
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(rez);
}
