public void shto(Lloji lloji)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
//insert te tabella e llojeve
SqlCommand cmd = new SqlCommand("insert into Llojet (Emertimi) values (@emri)", conn);
cmd.Parameters.Add("@emri", System.Data.SqlDbType.VarChar);
cmd.Parameters["@emri"].Value = lloji.emri;
conn.Open();
cmd.ExecuteNonQuery();
//gjej id e llojit te futur
SqlCommand cmdLlojiFundit = new SqlCommand("SELECT MAX (Id_lloji) AS maxlloji FROM Llojet", conn);
Int64 maxLlojiId = Convert.ToInt64(cmdLlojiFundit.ExecuteScalar());
//insert te tabella e skontos me perqindje skonto 0 per te gjithe klientet
SqlCommand cmdSelectKlienti = new SqlCommand("SELECT * FROM Klienti", conn);
SqlCommand cmdSelectZona = new SqlCommand("SELECT * FROM Zonat", conn);
SqlDataReader readerKlienti = cmdSelectKlienti.ExecuteReader();
while (readerKlienti.Read())
{
SqlDataReader readerZona = cmdSelectZona.ExecuteReader();
while (readerZona.Read())
{
SqlCommand cmdInsertSkonto = new SqlCommand("insert into Skonto (Klientiid,Llojiid,Zonaid,Perqindje_skonto) values (" + Convert.ToInt64(readerKlienti["Id_klienti"]) + "," + maxLlojiId + "," + Convert.ToInt64(readerZona["Id_Zona"]) + "," + 0 + ")", conn);
cmdInsertSkonto.ExecuteNonQuery();
}
readerZona.Close();
}
//insert te tabela e cmimeve
SqlDataReader readerZonaCmimet = cmdSelectZona.ExecuteReader();
while (readerZonaCmimet.Read())
{
SqlCommand cmdInsertCmimet = new SqlCommand("insert into Cmimet (Id_lloji,Id_zona,Valuta,Cmimi) values (" + maxLlojiId + "," + Convert.ToInt64(readerZonaCmimet["Id_Zona"]) + ",'" + "LEK" + "'," + 0 + ")", conn);
cmdInsertCmimet.ExecuteNonQuery();
}
conn.Close();
}
}
public void modifiko(Lloji lloji)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
//modifikon zonen te tabela e zonave
SqlCommand cmd = new SqlCommand("update Llojet set Emertimi = '" + lloji.emri + "' where Id_lloji = @Id", conn);
cmd.Parameters.Add("@Id", System.Data.SqlDbType.BigInt);
cmd.Parameters["@Id"].Value = lloji.id;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
public List <Lloji> GetLlojet()
{
List <Lloji> zo = new List <Lloji>();
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand("select * from Llojet", conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Lloji l = new Lloji();
l.id = Convert.ToInt64(reader["Id_lloji"].ToString());
l.emri = reader["Emertimi"].ToString();
zo.Add(l);
}
conn.Close();
return(zo);
}
}
