public ActionResult InterogareTrei() { List <Interogare1> clienti = new List <Interogare1>(); SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True"); SqlCommand cmd = new SqlCommand(@" select top 1 COUNT(c.IdClient), cl.Nume, cl.Prenume, cl.IdClient from Comenzi c inner join Client cl on cl.IdClient = c.IdClient group by cl.IdClient, cl.Prenume, cl.Nume order by COUNT(c.IdClient) desc", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Interogare1 x = new Interogare1(); Client Y = new Client(); x.count = reader.GetInt32(0); Y.Nume = reader.GetString(1); Y.Prenume = reader.GetString(2); Y.IdClient = reader.GetInt32(3); x.client = Y; clienti.Add(x); } reader.Close(); return(View(clienti)); }
public ActionResult InterogareUnu(List <Interogare1> x) { List <Interogare1> clienti = new List <Interogare1>(); SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True"); SqlCommand cmd = new SqlCommand(@" select c1.Nume, c1.Prenume, cl1.Pret from Client c1 inner join Comenzi cl1 on c1.IdClient = cl1.IdClient where " + x[0].count + @" > (select count(*) from Client c2 inner join Comenzi cl2 on cl2.IdClient = c2.IdClient where cl1.Pret < cl2.Pret) ", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Interogare1 interogare = new Interogare1(); Client y = new Client(); Comenzi z = new Comenzi(); y.Nume = reader.GetString(0); y.Prenume = reader.GetString(1); z.Pret = reader.GetDecimal(2); interogare.client = y; interogare.comanda = z; clienti.Add(interogare); } reader.Close(); return(View(clienti)); }
//clientii cu pretul comenzii > media tuturor comenzilor public ActionResult InterogareDoi() { List <Interogare1> clienti = new List <Interogare1>(); SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True"); SqlCommand cmd = new SqlCommand(@" select cl.Nume, cl.Prenume, c.IdComenzi, c.TimpStart, c.TimpStop, c.Pret from Comenzi c inner join Client cl on cl.IdClient = c.IdClient where c.Pret > (select avg(Pret) from Comenzi )", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Interogare1 x = new Interogare1(); Client y = new Client(); Comenzi z = new Comenzi(); y.Nume = reader.GetString(0); y.Prenume = reader.GetString(1); z.IdComenzi = reader.GetInt32(2); z.TimpStart = reader.GetDateTime(3); z.TimpStop = reader.GetDateTime(4); z.Pret = reader.GetDecimal(5); x.client = y; x.comanda = z; clienti.Add(x); } reader.Close(); return(View(clienti)); }
//top x clienti cu comenzile cele mai scumpe public ActionResult InterogareUnu() { List <Interogare1> y = new List <Interogare1>(); Interogare1 z = new Interogare1(); y.Add(z); return(View(y)); }
public ActionResult InterogareUnu() { List <Interogare1> clienti = new List <Interogare1>(); SqlConnection connection = new SqlConnection("data source = DESKTOP-IBNRKP3\\SQLEXPRESS; initial catalog = BD_Project; integrated security = True"); SqlCommand cmd = new SqlCommand(@" select co.IdComenzi, c.Nume, c.Prenume, co.Pret from Client c inner join Comenzi co on co.IdClient = c.IdClient inner join Comenzi_Biciclete cb on cb.IdComenzi = co.IdComenzi where co.Pret > 90 group by c.Nume, c.Prenume, co.Pret, co.IdComenzi", connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Interogare1 x = new Interogare1(); Comenzi y = new Comenzi(); Client z = new Client(); y.IdComenzi = reader.GetInt32(0); z.Nume = reader.GetString(1); z.Prenume = reader.GetString(2); y.Pret = reader.GetDecimal(3); x.client = z; x.comanda = y; clienti.Add(x); } reader.Close(); return(View(clienti)); }