public void modifiko(Klienti klienti) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("update Klienti set Emri = '" + klienti.emri + "', Adresa = '" + klienti.adresa + "', Perdoruesi = '" + klienti.perdoruesi + "', Password = '******', Agjensi = '" + klienti.agjensi + "', Aktiv = '" + klienti.aktiv + "', Shenime = '" + klienti.shenime + "',EmerSubjekti = '" + klienti.emerSubjekti + "',PersonKontakti = '" + klienti.personKontakti + "',Email = '" + klienti.email + "',LlogariBankare = '" + klienti.llogBankare + "',Id_magazina = " + klienti.idMagazina + ",Id_dispecheri = " + klienti.idDispecheri + " where Id_klienti = @Id", conn); cmd.Parameters.Add("@Id", System.Data.SqlDbType.BigInt); cmd.Parameters["@Id"].Value = klienti.id; conn.Open(); cmd.ExecuteNonQuery(); conn.Close(); } }
public List <Klienti> GetKlient() { List <Klienti> kl = new List <Klienti>(); using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("select * from Klienti", conn); conn.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Klienti k = new Klienti(); k.id = Convert.ToInt64(reader["Id_klienti"].ToString()); k.emri = reader["Emri"].ToString(); kl.Add(k); } conn.Close(); return(kl); } }
public void shto(Klienti klienti) { using (SqlConnection conn = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("insert into Klienti (Emri,Adresa,Perdoruesi,Password,Agjensi,Aktiv,Shenime,EmerSubjekti,PersonKontakti,Email,LlogariBankare,Id_magazina,Id_dispecheri,Detyrimi) values (@emri,@adresa,@perd,@pass,@agjensi,@aktiv,@shenime,@emersubjekti,@personkontakti,@email,@llogaribankare,@idmagazina,@iddispecheri,@detyrimi)", conn); cmd.Parameters.Add("@perd", System.Data.SqlDbType.VarChar); cmd.Parameters["@perd"].Value = klienti.perdoruesi; cmd.Parameters.Add("@emri", System.Data.SqlDbType.VarChar); cmd.Parameters["@emri"].Value = klienti.emri; cmd.Parameters.Add("@adresa", System.Data.SqlDbType.VarChar); cmd.Parameters["@adresa"].Value = klienti.adresa; cmd.Parameters.Add("@pass", System.Data.SqlDbType.VarChar); cmd.Parameters["@pass"].Value = klienti.password; cmd.Parameters.Add("@agjensi", System.Data.SqlDbType.Bit); cmd.Parameters["@agjensi"].Value = klienti.agjensi; cmd.Parameters.Add("@aktiv", System.Data.SqlDbType.Bit); cmd.Parameters["@aktiv"].Value = true; cmd.Parameters.Add("@shenime", System.Data.SqlDbType.VarChar); cmd.Parameters["@shenime"].Value = (object)klienti.shenime ?? DBNull.Value; cmd.Parameters.Add("@emersubjekti", System.Data.SqlDbType.VarChar); cmd.Parameters["@emersubjekti"].Value = (object)klienti.emerSubjekti ?? DBNull.Value; cmd.Parameters.Add("@personkontakti", System.Data.SqlDbType.VarChar); cmd.Parameters["@personkontakti"].Value = (object)klienti.personKontakti ?? DBNull.Value; cmd.Parameters.Add("@email", System.Data.SqlDbType.VarChar); cmd.Parameters["@email"].Value = (object)klienti.email ?? DBNull.Value; cmd.Parameters.Add("@llogaribankare", System.Data.SqlDbType.VarChar); cmd.Parameters["@llogaribankare"].Value = (object)klienti.llogBankare ?? DBNull.Value; cmd.Parameters.Add("@idmagazina", System.Data.SqlDbType.BigInt); cmd.Parameters["@idmagazina"].Value = (object)klienti.idMagazina ?? DBNull.Value; cmd.Parameters.Add("@iddispecheri", System.Data.SqlDbType.BigInt); cmd.Parameters["@iddispecheri"].Value = (object)klienti.idDispecheri ?? DBNull.Value; cmd.Parameters.Add("@detyrimi", System.Data.SqlDbType.Decimal); cmd.Parameters["@detyrimi"].Value = 0; conn.Open(); cmd.ExecuteNonQuery(); if (klienti.agjensi == false) { //gjej id e klientit te futur SqlCommand cmdKlientiFundit = new SqlCommand("SELECT MAX (Id_klienti) AS maxklienti FROM Klienti", conn); Int64 maxKlientId = Convert.ToInt64(cmdKlientiFundit.ExecuteScalar()); //insert te tabella e skontos me perqindje skonto 0 SqlCommand cmdSelectZona = new SqlCommand("SELECT * FROM Zonat", conn); SqlCommand cmdSelectLloji = new SqlCommand("SELECT * FROM Llojet", conn); SqlDataReader readerZona = cmdSelectZona.ExecuteReader(); while (readerZona.Read()) { SqlDataReader readerLloji = cmdSelectLloji.ExecuteReader(); while (readerLloji.Read()) { SqlCommand cmdInsertSkonto = new SqlCommand("insert into Skonto (Klientiid,Llojiid,Zonaid,Perqindje_skonto) values (" + maxKlientId + "," + Convert.ToInt64(readerLloji["Id_lloji"]) + "," + Convert.ToInt64(readerZona["Id_Zona"]) + "," + 0 + ")", conn); cmdInsertSkonto.ExecuteNonQuery(); } readerLloji.Close(); } } conn.Close(); } }