/// <summary>
/// Recupère la liste de tous les Modèles
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public List <Modele> GetAllModele()
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetAllModele";
cde.CommandType = CommandType.StoredProcedure;
List <Modele> modeles = new List <Modele>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
int idmodele = reader.GetInt32(0);
string libellemodele = reader.GetString(1);
modeles.Add(new Modele(idmodele, libellemodele));
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionSecteur("[DAO] GetAllMOdele :\n" + se.Message, se);
}
return(modeles);
} //End using command
} //End using connection
}
static void Main(string[] args)
{
var dbServer = "localhost";
var dbUsername = "******";
var dbName = "projectalpha";
var dbPassword = "";
DAOConnection daoConnection = new DAOConnection(dbServer, dbUsername, dbPassword, dbName);
Console.WriteLine("Try connect to database");
while (!daoConnection.TestConnection())
{
Console.WriteLine("DB connection failed");
}
Console.WriteLine("DB connection was successful");
string url = "http://localhost:8000/DEMOService";
WebService.WebService webService = new WebService.WebService(url);
webService.StartWebService();
Console.WriteLine("WebService Running");
Console.WriteLine("----------------------------------");
Console.WriteLine("URL: " + webService.URL);
Console.WriteLine("DB Connection ");
Console.ReadKey();
webService.StopWebService();
Console.WriteLine("WebService was stopped");
}
public User getUser(string username, string password)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "SELECT * from dbo.auth where username = '******' and password = '******'";
User user = new User();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
user.Id = reader.GetInt32(0);
user.Username = reader.GetString(1);
user.Password = reader.GetString(2);
}
reader.Close();
}
}
catch (DbException se)
{
throw new Exception("[DAO] GetUser :\n" + se.Message, se);
}
return(user);
} //End using command
} //End using connection
}
/// <summary>
/// Execution de la procedure stocké
/// Recupere la liste de tous les equipements par centre
/// </summary>
/// <param name="num"></param>
/// <returns></returns>
public List <Equipement> GetEquipementByCentre(int num)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetEquipementsByIdCentre";
cde.CommandType = CommandType.StoredProcedure;
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@num";
odbP1.Value = num;
cde.Parameters.Add(odbP1);
List <Equipement> lstEqui = new List <Equipement>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
string numSerie = reader.GetString(0);
string nomModele = reader.GetString(1);
int numModele = reader.GetInt32(2);
decimal tarif = reader.GetDecimal(3);
int codetarif = reader.GetInt32(4);
string typeE = reader.GetString(5);
int idtype = reader.GetInt32(6);
DateTime?dt = reader.GetDateTime(7);
TypeEquipement te = new TypeEquipement(0, typeE);
Tarif Letarif = new Tarif(Convert.ToDouble(tarif), dt);
Modele m = new Modele(numModele, nomModele, Letarif, te);
Equipement e = new Equipement(m, numSerie);
lstEqui.Add(e);
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionClient("[DAO] GetClientByName :\n" + se.Message, se);
}
return(lstEqui);
} //End using command
} //End using connection
}
/// <summary>
/// Execution de la procedure stocké
/// Recupere la liste de tous les centre par centre
/// </summary>
/// <param name="n"></param>
/// <returns></returns>
public List <Centre> GetCentresByIdClient(int num)
{
// return c.GetCatalogue;
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetCentresByIdClient";
cde.CommandType = CommandType.StoredProcedure;
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@num";
odbP1.Value = num;
cde.Parameters.Add(odbP1);
List <Centre> lstClient = new List <Centre>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string nom = reader.GetString(1);
int clt = reader.GetInt32(2);
int sec = reader.GetInt32(3);
string adresse = reader.GetString(5);
string tel = reader.GetString(4);
string libsec = reader.GetString(6);
Secteur s = new Secteur {
CodeSecteur = sec, LibelleSecteur = libsec
};
Centre c = new Centre(id, nom, tel, adresse, null, s);
lstClient.Add(c);
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionClient("[DAO] GetClientByName :\n" + se.Message, se);
}
return(lstClient);
} //End using command
} //End using connection
}
/// <summary>
/// Ajoute dans le table Equipements
/// </summary>
/// <param name="equipement"></param>
/// <returns></returns>
public int AddEquipement(Equipement equipement)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "[dbo].[AddEquipement]";
cde.CommandType = CommandType.StoredProcedure;
// Nom
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@numserie";
odbP1.Value = equipement.NumSerie;
cde.Parameters.Add(odbP1);
// MODELE
DbParameter odbP2 = cde.CreateParameter();
odbP2.DbType = System.Data.DbType.Int32;
odbP2.Direction = System.Data.ParameterDirection.Input;
odbP2.ParameterName = "@idmodele";
odbP2.Value = equipement.Modele.CodeModele;
cde.Parameters.Add(odbP2);
// CENTRE
DbParameter odbP3 = cde.CreateParameter();
odbP3.DbType = System.Data.DbType.Int32;
odbP3.Direction = System.Data.ParameterDirection.Input;
odbP3.ParameterName = "@numcentre";
odbP3.Value = equipement.Centre.NumCentre;
cde.Parameters.Add(odbP3);
try
{
int n = cde.ExecuteNonQuery();
if (n != 1)
{
throw new DAOExceptionClient("L'opération n'a pas été réalisée");
}
// récup du parametre de sortie
return(n);
}
catch (DbException de)
{
throw new DAOExceptionClient("[DAO] AddEquipement \n" + de.Message, de);
}
} // fin using command
} // fin using connection
}
/// <summary>
/// Execution de la procedure stocké
/// Recupere la liste de tous les clients en fonction de lettres
/// </summary>
/// <param name="n"></param>
/// <returns></returns>
public List <Client> GetClientsByName(string n)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetClientsByName";
cde.CommandType = CommandType.StoredProcedure;
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@name";
odbP1.Value = n;
cde.Parameters.Add(odbP1);
List <Client> lstClient = new List <Client>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string nom = reader.GetString(1);
string adresse = reader.GetString(2);
string tel = reader.GetString(3);
Client clt = new Client(id, nom, adresse, tel);
lstClient.Add(clt);
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionClient("[DAO] GetClientByName :\n" + se.Message, se);
}
return(lstClient);
} //End using command
} //End using connection
}
/// <summary>
/// Efface de la table Equipement tous les equipements par centre
/// </summary>
/// <param name="centre"></param>
/// <returns></returns>
public int DelEquipement(int centre)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "[dbo].[DelEquipement]";
cde.CommandType = CommandType.StoredProcedure;
// CENTRE
DbParameter odbP3 = cde.CreateParameter();
odbP3.DbType = System.Data.DbType.Int32;
odbP3.Direction = System.Data.ParameterDirection.Input;
odbP3.ParameterName = "@idCentre";
odbP3.Value = centre;
cde.Parameters.Add(odbP3);
try
{
int n = cde.ExecuteNonQuery();
if (n != 1)
{
throw new DAOExceptionClient("L'opération n'a pas été réalisée");
}
// récup du parametre de sortie
return(n);
}
catch (DbException de)
{
throw new DAOExceptionClient("[DAO] DELEquipement \n" + de.Message, de);
}
} // fin using command
} // fin using connection
}
/// <summary>
/// RecRecupère la liste de tous les Secteurs géographiques
/// </summary>
/// <returns></returns>
public List <Secteur> GetAllSecteur()
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetAllSecteur";
cde.CommandType = CommandType.StoredProcedure;
List <Secteur> secteurs = new List <Secteur>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
int numSecteur = reader.GetInt32(0);
string nomSecteur = reader.GetString(1);
secteurs.Add(new Secteur(numSecteur, nomSecteur));
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionSecteur("[DAO] GetAllSecteur :\n" + se.Message, se);
}
return(secteurs);
} //End using command
} //End using connection
}
/// <summary>
/// Recupère la liste de tous les Type d'equiepements
/// </summary>
/// <returns></returns>
public List <TypeEquipement> GetAllTypeEquipement()
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "GetAllTypeEquipement";
cde.CommandType = CommandType.StoredProcedure;
List <TypeEquipement> equipements = new List <TypeEquipement>();
try
{
using (DbDataReader reader = cde.ExecuteReader())
{
while (reader.Read())
{
int numType = reader.GetInt32(0);
string nomType = reader.GetString(1);
equipements.Add(new TypeEquipement(numType, nomType));
}
reader.Close();
}
}
catch (DbException se)
{
throw new DAOExceptionTypeEquipement("[DAO] GetAllTypeEquipement :\n" + se.Message, se);
}
return(equipements);
} //End using command
} //End using connection
}
/// <summary>
/// Mise à jour de la table Client
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
public int UpdateClient(Client c)
{
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "[dbo].[UpdateClient]";
cde.CommandType = CommandType.StoredProcedure;
// Nom
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@nom";
odbP1.Value = c.NomClient;
cde.Parameters.Add(odbP1);
// Adresse
DbParameter odbP2 = cde.CreateParameter();
odbP2.DbType = System.Data.DbType.String;
odbP2.Direction = System.Data.ParameterDirection.Input;
odbP2.ParameterName = "@adresse";
odbP2.Value = c.AdresseClient;
cde.Parameters.Add(odbP2);
// telephone
DbParameter odbP3 = cde.CreateParameter();
odbP3.DbType = System.Data.DbType.String;
odbP3.Direction = System.Data.ParameterDirection.Input;
odbP3.ParameterName = "@tel";
odbP3.Value = c.TelephoneClient;
cde.Parameters.Add(odbP3);
// idClient
DbParameter odbP4 = cde.CreateParameter();
odbP4.DbType = System.Data.DbType.Int32;
odbP4.Direction = System.Data.ParameterDirection.Input;
odbP4.Value = c.NumClient;
odbP4.ParameterName = "@idClient";
cde.Parameters.Add(odbP4);
try
{
int n = cde.ExecuteNonQuery();
if (n != 1)
{
throw new DAOExceptionClient("L'opération n'a pas été réalisée");
}
// récup du parametre de sortie
return((int)odbP4.Value);
}
catch (DbException de)
{
throw new DAOExceptionClient("[DAO] AddClient \n" + de.Message, de);
}
} // fin using command
} // fin using connection
}
/// <summary>
/// Ajoute un centre dans la table Centre de la base de donnée maintinfo
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
public int AddCentre(Centre c)
{
// c.AddProduit(p);
using (DbConnection db = DAOConnection.GetConnexion())
{
using (DbCommand cde = db.CreateCommand())
{
cde.CommandText = "[dbo].[AddCentre]";
cde.CommandType = CommandType.StoredProcedure;
// Nom
DbParameter odbP1 = cde.CreateParameter();
odbP1.DbType = System.Data.DbType.String;
odbP1.Direction = System.Data.ParameterDirection.Input;
odbP1.ParameterName = "@nomCentre";
odbP1.Value = c.NomCentre;
cde.Parameters.Add(odbP1);
// Id secteur
DbParameter odbP2 = cde.CreateParameter();
odbP2.DbType = System.Data.DbType.Int32;
odbP2.Direction = System.Data.ParameterDirection.Input;
odbP2.ParameterName = "@idSecteur";
odbP2.Value = c.Secteur.CodeSecteur;
cde.Parameters.Add(odbP2);
// telephone
DbParameter odbP3 = cde.CreateParameter();
odbP3.DbType = System.Data.DbType.String;
odbP3.Direction = System.Data.ParameterDirection.Input;
odbP3.ParameterName = "@telCentre";
odbP3.Value = c.TelCentre;
cde.Parameters.Add(odbP3);
// idCentre
DbParameter odbP4 = cde.CreateParameter();
odbP4.DbType = System.Data.DbType.Int32;
odbP4.Direction = System.Data.ParameterDirection.Output;
odbP4.ParameterName = "@idCentre";
cde.Parameters.Add(odbP4);
// Adresse
DbParameter odbP5 = cde.CreateParameter();
odbP5.DbType = System.Data.DbType.String;
odbP5.Direction = System.Data.ParameterDirection.Input;
odbP5.ParameterName = "@AdresseCentre";
odbP5.Value = c.AdresseCentre;
cde.Parameters.Add(odbP5);
// CLIENT
DbParameter odbP6 = cde.CreateParameter();
odbP6.DbType = System.Data.DbType.Int32;
odbP6.Direction = System.Data.ParameterDirection.Input;
odbP6.ParameterName = "@numClient";
odbP6.Value = c.Client.NumClient;
cde.Parameters.Add(odbP6);
try
{
int n = cde.ExecuteNonQuery();
if (n != 1)
{
throw new DAOExceptionClient("L'opération n'a pas été réalisée");
}
// récup du parametre de sortie
return((int)odbP4.Value);
}
catch (DbException de)
{
throw new DAOExceptionClient("[DAO] AddCentre \n" + de.Message, de);
}
} // fin using command
} // fin using connection
}
