public List <Kniha> NactiKnihyVypujcene(Ctenar ctenar)
{
List <Kniha> knihy = new List <Kniha>();
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"select k.* from Vypujcky v join Knihy k on v.IdKnihy=k.IdKnihy join Ctenari c on v.IdCtenari=c.IdCtenari where v.DatumVraceni is null and c.IdCtenari={ctenar.Id}";
sqlConnection.Open();
using (SqlDataReader dataReader = sqlCommand.ExecuteReader())
{
while (dataReader.Read())
{
knihy.Add(new Kniha(Convert.ToInt32(dataReader["IdKnihy"]),
dataReader["Nazev"].ToString(),
dataReader["Autor"].ToString(),
Convert.ToInt32(dataReader["PocetStran"]),
dataReader["Zanr"].ToString()));
}
}
sqlConnection.Close();
}
}
return(knihy);
}
public FrmVypujcky(Ctenar ctenar)
{
InitializeComponent();
Ctenar = ctenar;
sqlRepository = new SqlRepository("Data Source=titanic.vassboskovice.cz;Initial Catalog=Hla_knihovna_2021;User ID=sa;Password=Agent007;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False");
nevypujceneKnihy = new List <Kniha>();
}
public void SmazCtenare(Ctenar ctenar)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"delete from Ctenari where IdCtenari={ctenar.Id}";
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
public void StornoVypujcky(Ctenar ctenar, Kniha kniha)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"delete from Vypujcky where IdCtenari={ctenar.Id} and IdKnihy={kniha.Id} and DatumVraceni is null";
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
public void VratKnihu(Ctenar ctenar, Kniha kniha)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"update Vypujcky set DatumVraceni=getdate() where IdCtenari={ctenar.Id} and IdKnihy={kniha.Id} and DatumVraceni is null";
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
public void VypujcKnihu(Ctenar ctenar, Kniha kniha)
{
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"insert into Vypujcky(IdCtenari,IdKnihy,DatumVypujceni) values({ctenar.Id},{kniha.Id},getdate())";
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
public void UlozCtenare(Ctenar ctenar)
{
if (ctenar.Id == 0)
{
// není v databázi -> budeme ho vytvářet
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = "insert into Ctenari(Jmeno,Prijmeni,DatumNarozeni) values(@Jmeno,@Prijmeni,@DatumNarozeni)";
sqlCommand.Parameters.AddWithValue("Jmeno", ctenar.Jmeno);
sqlCommand.Parameters.AddWithValue("Prijmeni", ctenar.Prijmeni);
sqlCommand.Parameters.AddWithValue("DatumNarozeni", ctenar.DatumNarozeni);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
else
{
//pouze update
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
{
using (SqlCommand sqlCommand = new SqlCommand("", sqlConnection))
{
sqlCommand.CommandText = $"update Ctenari set Jmeno=@Jmeno ,Prijmeni=@Prijmeni,DatumNarozeni=@DatumNarozeni where IdCtenari={ctenar.Id}";
sqlCommand.Parameters.AddWithValue("Jmeno", ctenar.Jmeno);
sqlCommand.Parameters.AddWithValue("Prijmeni", ctenar.Prijmeni);
sqlCommand.Parameters.AddWithValue("DatumNarozeni", ctenar.DatumNarozeni);
sqlConnection.Open();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
}
}
}
public FrmCtenariUpravy(Ctenar ctenar)
{
InitializeComponent();
Ctenar = ctenar;
}