public ActionResult GetDetailsJson(int noOffre) { SqlConnection cnn = null; string connetionString = Properties.Settings.Default.dbConnectionString; SqlCommand command = null; OffreBean offre = null; string sql = "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, l.CodeISBN_10, " + "l.CodeISBN_13, l.Nom, l.Image, o.Remarques, u.Email, u.PhoneNumber, l.Auteur, o.Prix, l.AnneeEdition " + "FROM Offre o " + "JOIN Livre l On o.IdArticle = l.Id " + "JOIN Etat e ON o.Etat = e.CodeEtat " + "JOIN AspNetUsers u On u.Id = o.userId " + "Where o.IdTypeArticle = 1 AND o.Id = " + noOffre; cnn = new SqlConnection(connetionString); try { cnn.Open(); command = new SqlCommand(sql, cnn); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { offre = new OffreBean { NoOffre = (int)dataReader.GetValue(0), EtatLivre = dataReader.GetValue(1).ToString(), CoursObligatoires = dataReader.GetValue(2).ToString(), CoursRecommandes = dataReader.GetValue(3).ToString(), CodeIsbn_10 = dataReader.GetValue(4).ToString(), CodeIsbn_13 = dataReader.GetValue(5).ToString(), NomLivre = dataReader.GetValue(6).ToString(), ImageLivre = dataReader.GetValue(7).ToString(), Remarques = dataReader.GetValue(8).ToString(), Email = dataReader.GetValue(9).ToString(), Phone = dataReader.GetValue(10).ToString(), Auteur = dataReader.GetValue(11).ToString(), Prix = Convert.ToDouble(dataReader.GetValue(12).ToString()), AnneeEdition = dataReader.GetValue(13).ToString() }; } dataReader.Close(); command.Dispose(); cnn.Close(); } catch (Exception ex) { offre = new OffreBean(); offre.Message = "Une erreur est survenue"; } return Json(offre, JsonRequestBehavior.AllowGet); }
public List<OffreBean> ObtenirListeOffresUtil() { List<OffreBean> offres = null; SqlConnection cnn = null; var connetionString = Properties.Settings.Default.dbConnectionString; var sql = "SELECT o.Id, l.Nom " + "FROM Offre o " + "JOIN Livre l On o.IdLivre = l.Id " + "Where o.userId = '" + User.Identity.GetUserId() + "' And o.IndActif='1' order by o.Id desc"; cnn = new SqlConnection(connetionString); try { cnn.Open(); var command = new SqlCommand(sql, cnn); offres = new List<OffreBean>(); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { var offre = new OffreBean { NoOffre = (int)dataReader.GetValue(0), NomLivre = dataReader.GetValue(1).ToString(), estNouv = false }; offres.Add(offre); } dataReader.Close(); command.Dispose(); cnn.Close(); } catch (Exception ex) { } return offres; }
public List<OffreBean> ObtenirListeLivres(int start = 1, int last = 8, string reqRech = "", string tri = "l.Nom", string ordre = "ASC") { List<OffreBean> offres = null; SqlConnection cnn = null; string connetionString = Properties.Settings.Default.dbConnectionString; if (string.IsNullOrEmpty(tri)) { tri = "l.Nom"; ordre = "ASC"; } var sql = "SELECT a.* FROM("; sql += "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, l.CodeISBN_10, " + "l.CodeISBN_13, l.Nom, l.Image, o.Remarques, l.SousTitre, l.Auteur,o.Prix, o.IndActif, " + "ROW_NUMBER() OVER (ORDER BY " + tri + " " + ordre + " ) AS ROWNUMBERS " + "FROM Offre o " + "JOIN Livre l On o.IdLivre = l.Id " + "JOIN Etat e ON o.Etat = e.CodeEtat " + "Where o.IndActif='1' " + reqRech; sql += ") a WHERE a.ROWNUMBERS BETWEEN " + start + " AND " + last; cnn = new SqlConnection(connetionString); try { cnn.Open(); var command = new SqlCommand(sql, cnn); offres = new List<OffreBean>(); SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { var offre = new OffreBean { NoOffre = (int) dataReader.GetValue(0), EtatLivre = dataReader.GetValue(1).ToString(), CoursObligatoires = dataReader.GetValue(2).ToString(), CoursRecommandes = dataReader.GetValue(3).ToString(), CodeIsbn_10 = dataReader.GetValue(4).ToString(), CodeIsbn_13 = dataReader.GetValue(5).ToString(), NomLivre = dataReader.GetValue(6).ToString(), ImageLivre = dataReader.GetValue(7).ToString() != "" ? dataReader.GetValue(7).ToString() : "https://encrypted-tbn3.gstatic.com/images?q=tbn:ANd9GcR7lSOcD29L4UQ8vuBd3rj1CREOKYOxCQE1Qrf8rAJmC500pR_9dA", Remarques = dataReader.GetValue(8).ToString(), SousTitre = dataReader.GetValue(9).ToString(), Auteur = dataReader.GetValue(10).ToString(), Prix = Convert.ToDouble(dataReader.GetValue(11).ToString()) }; offres.Add(offre); } dataReader.Close(); command.Dispose(); cnn.Close(); } catch (Exception ex) { } if (offres != null && offres.Count > 0) { offres.First().OrdreItems.First(x => x.Value.Equals(ordre)).Selected = true; offres.First().TriItems.First(x => x.Value.Equals(tri)).Selected = true; } return offres; }
public ActionResult GetDetailsJsonNotes(int noOffre) { SqlConnection cnn = null; string connetionString = Properties.Settings.Default.dbConnectionString; SqlCommand command = null; OffreBean offre = null; string sql = "SELECT o.Id, e.DesctEtat, o.CoursOblig, o.CoursRecom, " + "n.Nom, n.SousTitre,n.MoisRedaction,n.AnneeRedaction,n.MoisRevision,n.AnneeRevision, o.Remarques, u.Email, u.PhoneNumber, o.Prix " + "FROM Offre o " + "JOIN NotesDeCours n On o.IdArticle = n.IdNotesDeCours " + "JOIN Etat e ON o.Etat = e.CodeEtat " + "JOIN AspNetUsers u On u.Id = o.userId " + "Where o.IdTypeArticle=2 AND o.Id = " + noOffre; cnn = new SqlConnection(connetionString); try { cnn.Open(); command = new SqlCommand(sql, cnn); var dataReader = command.ExecuteReader(); while (dataReader.Read()) { offre = new OffreBean { NoOffre = (int)dataReader.GetValue(0), EtatLivre = dataReader.GetValue(1).ToString(), CoursObligatoires = dataReader.GetValue(2).ToString(), CoursRecommandes = dataReader.GetValue(3).ToString(), NomNotesCours = dataReader.GetValue(4).ToString(), SousTitre = dataReader.GetValue(5).ToString(), MoisRedaction = dataReader.GetValue(6).ToString(), AnneeRedaction = int.Parse(dataReader.GetValue(7).ToString()), MoisRevision = dataReader.GetValue(8).ToString(), AnneeRevision = int.Parse(dataReader.GetValue(9).ToString()), Remarques = dataReader.GetValue(10).ToString(), Email = dataReader.GetValue(11).ToString(), Phone = dataReader.GetValue(12).ToString(), Prix = Convert.ToDouble(dataReader.GetValue(13).ToString()), }; } dataReader.Close(); command.Dispose(); cnn.Close(); } catch (Exception ex) { offre = new OffreBean(); offre.Message = "Une erreur est survenue"; } return Json(offre, JsonRequestBehavior.AllowGet); }