예제 #1
0
        public void save(Commande commande)
        {
            using (SqlConnection conn = UtilDB.getInstance().getConnection())
            {
                SqlDataAdapter da    = new SqlDataAdapter();
                SqlTransaction trans = null;
                int            idCommande;
                try
                {
                    conn.Open();
                    trans = conn.BeginTransaction();

                    da.InsertCommand = new SqlCommand("INSERT INTO COMMANDE (DATE, APPLICATIONUSERID) output INSERTED.ID " +
                                                      "VALUES (@dateCommande, @id) ", conn, trans);
                    da.InsertCommand.Parameters.AddWithValue("@dateCommande", commande.Date);
                    da.InsertCommand.Parameters.AddWithValue("@id", commande.ApplicationUserID);
                    idCommande = (int)da.InsertCommand.ExecuteScalar();

                    foreach (DetailCommande dc in commande.DetailCommande)
                    {
                        da.InsertCommand = new SqlCommand("INSERT INTO DETAILCOMMANDE (QUANTITE, DONE, PRIXUNITAIRE, COMMANDEID, PRODUITID) " +
                                                          "VALUES (@Quantite, @done, @PrixUnit, @CommandeId, @ProduitId) ", conn, trans);
                        da.InsertCommand.Parameters.AddWithValue("@Quantite", dc.Quantite);
                        da.InsertCommand.Parameters.AddWithValue("@PrixUnit", dc.PrixUnitaire);
                        da.InsertCommand.Parameters.AddWithValue("@done", dc.Done);
                        da.InsertCommand.Parameters.AddWithValue("@CommandeId", idCommande);
                        da.InsertCommand.Parameters.AddWithValue("@ProduitId", dc.ProduitId);
                        da.InsertCommand.ExecuteNonQuery();

                        da.UpdateCommand = new SqlCommand("UPDATE PRODUIT SET QUANTITESTOCK = QUANTITESTOCK - @Quantite", conn, trans);
                        da.UpdateCommand.Parameters.AddWithValue("@Quantite", dc.Quantite);
                        da.UpdateCommand.ExecuteNonQuery();
                    }
                    trans.Commit();
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    throw e;
                }
                finally
                {
                    if (da != null)
                    {
                        da.Dispose();
                    }
                    if (trans != null)
                    {
                        trans.Dispose();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }
 public List <DetailCommande> findAllByCommande(int id, bool lazy)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         String        request    = "SELECT * FROM DETAILCOMMANDE WHERE COMMANDEID = @id";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         sqlCommand.Parameters.AddWithValue("@id", id);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             List <DetailCommande> liste = new List <DetailCommande>();
             if (!lazy)
             {
                 while (reader.Read())
                 {
                     DetailCommande p = UtilDB.getInstance().createDetailCommande(reader);
                     p.Commande = new CommandeDAO().findById(id, true);
                     p.Produit  = new ProduitDAO().findById(p.Produit.Id, lazy);
                     liste.Add(p);
                 }
             }
             else
             {
                 while (reader.Read())
                 {
                     liste.Add(UtilDB.getInstance().createDetailCommande(reader));
                 }
             }
             return(liste);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #3
0
 public List <Produit> findAll(bool lazy)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         string        request    = "SELECT * FROM PRODUIT";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             List <Produit> liste = new List <Produit>();
             if (!lazy)
             {
                 while (reader.Read())
                 {
                     Produit p = UtilDB.getInstance().createProduit(reader);
                     p.Categorie = this.findCategorieById(p.Categorie.Id);
                     liste.Add(p);
                 }
             }
             else
             {
                 while (reader.Read())
                 {
                     liste.Add(UtilDB.getInstance().createProduit(reader));
                 }
             }
             return(liste);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #4
0
 public Commande findByIdAndUser(int id, string userid, bool lazy)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         String        request    = "SELECT * FROM COMMANDE WHERE ID = @id AND APPLICATIONUSERID = @userid";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         sqlCommand.Parameters.AddWithValue("@id", id);
         sqlCommand.Parameters.AddWithValue("@userid", userid);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             Commande item = null;
             if (reader.Read())
             {
                 item = new Commande();
                 if (lazy)
                 {
                     item = UtilDB.getInstance().createCommande(reader);
                 }
                 else
                 {
                     item = UtilDB.getInstance().createCommande(reader);
                     item.DetailCommande = dcDao.findAllByCommande(item, lazy);
                 }
             }
             return(item);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #5
0
 public List <Commande> findAllByUser(string id, bool lazy)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         String        request    = "SELECT * FROM COMMANDE WHERE APPLICATIONUSERID = @id";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         sqlCommand.Parameters.AddWithValue("@id", id);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             List <Commande> listeCommande = new List <Commande>();
             if (lazy)
             {
                 while (reader.Read())
                 {
                     listeCommande.Add(UtilDB.getInstance().createCommande(reader));
                 }
             }
             else
             {
                 while (reader.Read())
                 {
                     Commande c = UtilDB.getInstance().createCommande(reader);
                     c.DetailCommande = dcDao.findAllByCommande(c.Id, lazy);
                     listeCommande.Add(c);
                 }
             }
             return(listeCommande);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             reader.Close();
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #6
0
 public Produit findById(int id, bool lazy)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         string        request    = "SELECT * FROM PRODUIT WHERE ID = @id";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         sqlCommand.Parameters.AddWithValue("@id", id);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             Produit item = new Produit();
             if (reader.Read())
             {
                 item = UtilDB.getInstance().createProduit(reader);
                 if (!lazy)
                 {
                     item.Categorie = new CategorieDAO().findCategorieById(item.Categorie.Id);
                 }
             }
             return(item);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #7
0
 public Categorie findCategorieById(int id)
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         String        request    = "SELECT * FROM CATEGORIE WHERE ID = @id";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         sqlCommand.Parameters.AddWithValue("@id", id);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             Categorie item = new Categorie();
             if (reader.Read())
             {
                 item = UtilDB.getInstance().createCategorie(reader);
             }
             return(item);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #8
0
 public List <Categorie> findAll()
 {
     using (SqlConnection conn = UtilDB.getInstance().getConnection())
     {
         SqlDataReader reader     = null;
         String        request    = "SELECT * FROM CATEGORIE";
         SqlCommand    sqlCommand = new SqlCommand(request, conn);
         try
         {
             conn.Open();
             reader = sqlCommand.ExecuteReader();
             List <Categorie> liste = new List <Categorie>();
             while (reader.Read())
             {
                 Categorie c = UtilDB.getInstance().createCategorie(reader);
                 liste.Add(c);
             }
             return(liste);
         }
         catch (Exception e)
         {
             throw e;
         }
         finally
         {
             if (reader != null)
             {
                 reader.Close();
             }
             if (sqlCommand != null)
             {
                 sqlCommand.Dispose();
             }
             if (conn != null)
             {
                 conn.Close();
             }
         }
     }
 }
예제 #9
0
        public void findLike(int?categorie, string query, BaseModelPagination <Produit> pagination, bool lazy)
        {
            using (SqlConnection conn = UtilDB.getInstance().getConnection())
            {
                SqlDataReader reader  = null;
                string        request = "SELECT * FROM PRODUIT WHERE NOM LIKE @query";
                string        count   = "SELECT count(*) FROM PRODUIT WHERE NOM LIKE @query";
                if (categorie > 0)
                {
                    request += " AND CATEGORIEID = @categorie";
                    count   += " AND CATEGORIEID = @categorie";
                }
                request += " ORDER BY NOM OFFSET @offset ROWS FETCH NEXT @maxResult ROWS ONLY ";
                SqlCommand sqlCommand      = new SqlCommand(request, conn);
                SqlCommand countSqlCommand = new SqlCommand(count, conn);
                sqlCommand.Parameters.AddWithValue("@query", "%" + query + "%");
                sqlCommand.Parameters.AddWithValue("@maxResult", pagination.maxResult);
                sqlCommand.Parameters.AddWithValue("@offset", pagination.offset());
                countSqlCommand.Parameters.AddWithValue("@query", "%" + query + "%");
                if (categorie > 0)
                {
                    sqlCommand.Parameters.AddWithValue("@categorie", categorie);
                    countSqlCommand.Parameters.AddWithValue("@categorie", categorie);
                }
                try
                {
                    conn.Open();
                    pagination.totalResult = (int)countSqlCommand.ExecuteScalar();
                    List <Produit> liste = new List <Produit>();
                    reader = sqlCommand.ExecuteReader();

                    if (!lazy)
                    {
                        while (reader.Read())
                        {
                            Produit p = UtilDB.getInstance().createProduit(reader);
                            p.Categorie = this.findCategorieById(p.Categorie.Id);
                            liste.Add(p);
                        }
                    }
                    else
                    {
                        while (reader.Read())
                        {
                            liste.Add(UtilDB.getInstance().createProduit(reader));
                        }
                    }

                    pagination.liste = liste;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (sqlCommand != null)
                    {
                        sqlCommand.Dispose();
                    }
                    if (countSqlCommand != null)
                    {
                        countSqlCommand.Dispose();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
        }