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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}