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 int delete(Strelba Strelba)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "DELETE FROM Strelba Where idStr = @idStr";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@idStr", Strelba.Id);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}
private static void PrepareCommand(SqlCommand command, Strelba Strelba)
{
command.Parameters.AddWithValue("@idStr", Strelba.Id);
command.Parameters.AddWithValue("@Zacatek", Strelba.Zacatek.ToString("yyyy-MM-dd HH:mm"));
command.Parameters.AddWithValue("@Konec", Strelba.Konec.ToString("yyyy-MM-dd HH:mm"));
command.Parameters.AddWithValue("@Zakaznik_idZak", Strelba.Zakaznik.Id);
command.Parameters.AddWithValue("@Prostor_idSpr", Strelba.Prostor.Id);
command.Parameters.AddWithValue("@Zbran_idZbr", Strelba.Zbran.Id);
command.Parameters.AddWithValue("@Zamestnanec_idZam", Strelba.Zamestnanec.Id);
}
public static Collection <StrelbaDTO> GetDTOs()
{
Collection <Strelba> strelby = Strelba.SelectAll();
Collection <StrelbaDTO> DT = new Collection <StrelbaDTO>();
foreach (var str in strelby)
{
DT.Add(new StrelbaDTO(str));
}
return(DT);
}
public StrelbaDTO(Strelba strelba)
{
Id = strelba.Id;
Zacatek = strelba.Zacatek.ToString();
Konec = strelba.Konec.ToString();
if (strelba.Prostor.Id != 0)
{
Prostor = strelba.Prostor.Id.ToString() + " " + strelba.Prostor.Vzdalenost.ToString() + "m";
}
else
{
Prostor = "Daná položka se již nevyskytuje v systému.";
}
if (strelba.Zakaznik.Id != 0)
{
Zakaznik = strelba.Zakaznik.Email;
}
else
{
Zakaznik = "Daná položka se již nevyskytuje v systému.";
}
if (strelba.Zamestnanec.Id != 0)
{
Zamestnanec = strelba.Zamestnanec.Email;
}
else
{
Zamestnanec = "Daná položka se již nevyskytuje v systému.";
}
if (strelba.Zbran.Id != 0)
{
Zbran = strelba.Zbran.Nazev;
}
else
{
Zbran = "Daná položka se již nevyskytuje v systému.";
}
}
public static int update(Strelba Strelba)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "UPDATE Strelba SET Zacatek=@Zacatek, Konec=@Konec, Zakaznik_idZak=@Zakaznik_idZak, Prostor_idSpr=@Prostor_idSpr, Zbran_idZbr=@Zbran_idZbr, Zamestnanec_idZam=@Zamestnanec_idZam WHERE idRez=@idRez";
using (SqlCommand command = new SqlCommand(sql, connection))
{
PrepareCommand(command, Strelba);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}
public static int insert(Strelba Strelba)
{
int ret = 0;
try
{
using (SqlConnection connection = new SqlConnection(conStr))
{
connection.Open();
string sql = "INSERT INTO Strelba (Zacatek, Konec, Zakaznik_idZak, Prostor_idSpr, Zbran_idZbr, Zamestnanec_idZam) VALUES (@Zacatek, @Konec, @Zakaznik_idZak, @Prostor_idSpr, @Zbran_idZbr, @Zamestnanec_idZam)";
using (SqlCommand command = new SqlCommand(sql, connection))
{
PrepareCommand(command, Strelba);
ret = command.ExecuteNonQuery();
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
return(ret);
}