public int Ajouter(Condition obj)
{
//try
//{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"insert into Conditions values(
@ref,@fils,@lbl,@pere,@typ,@sel,@niv,@req,@val,@tmp,@ecr,@cle,@frm,@tab)";
DatasUtility.AddParameterWithValue(cmd, "@ref", obj.Ref_Util);
DatasUtility.AddParameterWithValue(cmd, "@fils", obj.Code_Fils);
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@pere", obj.Code_Pere);
DatasUtility.AddParameterWithValue(cmd, "@typ", obj.Type);
DatasUtility.AddParameterWithValue(cmd, "@sel", obj.Selection == null ? (object)DBNull.Value : obj.Selection);
DatasUtility.AddParameterWithValue(cmd, "@niv", obj.Niveau);
DatasUtility.AddParameterWithValue(cmd, "@req", obj.Requete == null ? (object)DBNull.Value : obj.Requete);
DatasUtility.AddParameterWithValue(cmd, "@val", obj.Valeur == null ? (object)DBNull.Value : obj.Valeur);
DatasUtility.AddParameterWithValue(cmd, "@tmp", obj.TableTmp == null ? (object)DBNull.Value : obj.TableTmp);
DatasUtility.AddParameterWithValue(cmd, "@ecr", obj.NumEcriture);
DatasUtility.AddParameterWithValue(cmd, "@cle", obj.Cle == null ? (object)DBNull.Value : obj.Cle);
DatasUtility.AddParameterWithValue(cmd, "@frm", obj.Form == null ? (object)DBNull.Value : obj.Form);
DatasUtility.AddParameterWithValue(cmd, "@tab", obj.CodeTable == 0 ? (object)DBNull.Value : obj.CodeTable);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
//}
//catch (Exception)
//{ return -1; }
}
public int Modifier(Condition obj)
{
//try
//{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"update Conditions set
Libelle=@lbl,Code_pere=@pere,Type=@typ,Selection=@sel,Niveau=@niv,Req=@req,
Valeur=@val,TableTmp=@tmp,Numecrit=@ecr,Cle=@cle,Form=@frm,Code_Table=@tab
where Ref_util=@ref and Code_fils=@fils";
DatasUtility.AddParameterWithValue(cmd, "@ref", obj.Ref_Util);
DatasUtility.AddParameterWithValue(cmd, "@fils", obj.Code_Fils);
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@pere", obj.Code_Pere);
DatasUtility.AddParameterWithValue(cmd, "@typ", obj.Type);
DatasUtility.AddParameterWithValue(cmd, "@sel", obj.Selection == null ? (object)DBNull.Value : obj.Selection);
DatasUtility.AddParameterWithValue(cmd, "@niv", obj.Niveau);
DatasUtility.AddParameterWithValue(cmd, "@req", obj.Requete == null ? (object)DBNull.Value : obj.Requete);
DatasUtility.AddParameterWithValue(cmd, "@val", obj.Valeur == null ? (object)DBNull.Value : obj.Valeur);
DatasUtility.AddParameterWithValue(cmd, "@tmp", obj.TableTmp == null ? (object)DBNull.Value : obj.TableTmp);
DatasUtility.AddParameterWithValue(cmd, "@ecr", obj.NumEcriture);
DatasUtility.AddParameterWithValue(cmd, "@cle", obj.Cle == null ? (object)DBNull.Value : obj.Cle);
DatasUtility.AddParameterWithValue(cmd, "@frm", obj.Form == null ? (object)DBNull.Value : obj.Form);
DatasUtility.AddParameterWithValue(cmd, "@tab", obj.CodeTable == 0 ? (object)DBNull.Value : obj.CodeTable);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
//}
//catch (Exception)
//{ return -1; }
}
//I'm here
public int Modifier(Ecriture obj, Evenement evenement)
{
//try
//{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
var sql = string.Format("update Ecritures_{0} set condition=@condition", evenement.Libelle);
StringBuilder sb = new StringBuilder();
foreach (var item in obj.Colonnes)
{
sb.Append(",");
sb.Append(item.Champs.Champs);
sb.Append(string.Format("=@{0}", item.Champs.Champs));
}
sb.Append(" where cleecrit=@cleecrit and numecrit=@numecrit");
sql += sb.ToString();
cmd.CommandText = sql;
//remplissage des parametres
DatasUtility.AddParameterWithValue(cmd, "@cleecrit", obj.Cle);
DatasUtility.AddParameterWithValue(cmd, "@numecrit", obj.Numero);
DatasUtility.AddParameterWithValue(cmd, "@condition", obj.Condition);
foreach (var item in obj.Colonnes)
{
DatasUtility.AddParameterWithValue(cmd, "@" + item.Champs.Champs, item.Requette);
}
cnx.Open();
System.Windows.Forms.MessageBox.Show(cmd.CommandText);
cnx.Close();
return(0);
//}
//catch (Exception)
//{ return 1; }
}
public int Modifier(ConnexionSysteme obj)
{
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"update Connexions_Systems set Libelle=@lbl,
TypeServeur=@ts,Serveur=@sr,DB_Nom=@db,Username=@user,Password=@pass
Where Code=@cd";
DatasUtility.AddParameterWithValue(cmd, "@cd", obj.Code);
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@ts", obj.ServerType);
DatasUtility.AddParameterWithValue(cmd, "@sr", obj.CnxHelper.Server);
DatasUtility.AddParameterWithValue(cmd, "@db", obj.CnxHelper.Database);
DatasUtility.AddParameterWithValue(cmd, "@user", obj.CnxHelper.Username);
DatasUtility.AddParameterWithValue(cmd, "@pass", obj.CnxHelper.Password);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
catch (Exception)
{}
return(1);
}
public List <ConnexionSysteme> GetByServerType(string serverType)
{
var liste = new List <ConnexionSysteme>();
ConnexionSysteme obj;
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "select * from Connexions_Systems Where TypeServeur=@ts";
DatasUtility.AddParameterWithValue(cmd, "@ts", serverType);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new ConnexionSysteme();
obj.Code = dr.GetInt32(0);
obj.Libelle = dr[1].ToString();
obj.ServerType = dr[2].ToString();
obj.CnxHelper.Server = dr[3].ToString();
obj.CnxHelper.Database = dr[4].ToString();
obj.CnxHelper.Username = dr[5].ToString();
obj.CnxHelper.Password = dr[6].ToString();
liste.Add(obj);
}
cnx.Close();
return(liste);
}
public List <Evenement> GetBySystem(int sys)
{
//try
//{
servConSys.Connector = Connector;
var liste = new List <Evenement>();
var allConSys = servConSys.GetAll();
Evenement obj;
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "select * from Tables_Sorties where system=@sys";
DatasUtility.AddParameterWithValue(cmd, "@sys", sys);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new Evenement();
obj.Code = dr.GetInt32(0);
obj.Libelle = dr[1].ToString();
obj.System = allConSys.Single(o => o.Code == dr.GetInt32(2));
liste.Add(obj);
}
return(liste);
//}
//catch (Exception)
//{ return null; }
}
public ConnexionSysteme GetById(int id)
{
//try
//{
ConnexionSysteme obj = null;
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "select * from Connexions_Systems Where Code=@cd";
DatasUtility.AddParameterWithValue(cmd, "@cd", id);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new ConnexionSysteme();
obj.Code = dr.GetInt32(0);
obj.Libelle = dr[1].ToString();
obj.ServerType = dr[2].ToString();
obj.CnxHelper.Server = dr[3].ToString();
obj.CnxHelper.Database = dr[4].ToString();
obj.CnxHelper.Username = dr[5].ToString();
obj.CnxHelper.Password = dr[6].ToString();
}
return(obj);
//}
//catch (Exception)
//{}
//return null;
}
public Evenement GetByCode(int code)
{
//try
//{
servConSys.Connector = Connector;
Evenement obj = null;
var allSystems = servConSys.GetAll();
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "select * from Tables_Sorties where code=@cd";
DatasUtility.AddParameterWithValue(cmd, "@cd", code);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
obj = new Evenement();
obj.Code = dr.GetInt32(0);
obj.Libelle = dr[1].ToString();
obj.System = allSystems.Single(o => o.Code == dr.GetInt32(0));
}
return(obj);
//}
//catch (Exception)
//{ return null; }
}
public int Supprimer(int code)
{
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"delete from Tables_Sorties where code=@cd)";
DatasUtility.AddParameterWithValue(cmd, "@cd", code);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
catch (Exception)
{ return(1); }
}
public int Authenticate(string username, string password)
{
try
{
DbCommand cmd = Connector.GetConnection().CreateCommand();
cmd.CommandText = "select id from [Authentication] where username=@user AND mdp=@mdp";
DatasUtility.AddParameterWithValue(cmd, "@user", username);
DatasUtility.AddParameterWithValue(cmd, "@mdp", password);
Connector.GetConnection().Open();
int res = (int)cmd.ExecuteScalar();
Connector.GetConnection().Close();
return(0);
}
catch (Exception)
{ return(1); }
}
public int Ajouter(Evenement obj)
{
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"insert into Tables_Sorties values(@lbl,@sys)";
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@sys", obj.System.Code);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
catch (Exception)
{ return(1); }
}
public int Supprimer(int id)
{
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "delete from Connexions_Systems where Code=@cd";
DatasUtility.AddParameterWithValue(cmd, "@cd", id);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
catch (Exception)
{}
return(1);
}
public bool IsExist(Ecriture ecr, Evenement evenement)
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
var sql = string.Format("select 1 from Ecritures_{0} where cleecrit=@cle", evenement.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@cle", ecr.Cle);
cnx.Open();
try
{
int rslt = (int)cmd.ExecuteScalar();
cnx.Close(); return(true);
}
catch (Exception)
{
cnx.Close(); return(false);
}
}
public int Modifier(Evenement obj)
{
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"update Tables_Sorties set Libelle=@lbl, System=@sys where code=@cd)";
DatasUtility.AddParameterWithValue(cmd, "@cd", obj.Code);
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@sys", obj.System.Code);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
catch (Exception)
{ return(1); }
}
public int Ajouter(ConnexionSysteme obj)
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"insert into Connexions_Systems values(
@lbl,@ts,@sr,@db,@user,@pass)";
DatasUtility.AddParameterWithValue(cmd, "@lbl", obj.Libelle);
DatasUtility.AddParameterWithValue(cmd, "@ts", obj.ServerType);
DatasUtility.AddParameterWithValue(cmd, "@sr", obj.CnxHelper.Server);
DatasUtility.AddParameterWithValue(cmd, "@db", obj.CnxHelper.Database);
DatasUtility.AddParameterWithValue(cmd, "@user", obj.CnxHelper.Username);
DatasUtility.AddParameterWithValue(cmd, "@pass", obj.CnxHelper.Password);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
}
public int Supprimer(int ref_util)
{
//try
//{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"delete from Conditions
where Ref_util=@ref";
DatasUtility.AddParameterWithValue(cmd, "@ref", ref_util);
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
//}
//catch (Exception)
//{ return 1; }
}
public List<string> GetViewFields(string view)
{
var liste = new List<string>();
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name=@view";
DatasUtility.AddParameterWithValue(cmd, "@view", view);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
liste.Add(dr[0].ToString());
}
cnx.Close();
}
catch (Exception)
{}
return liste;
}
public int Ajouter(Ecriture obj, Evenement evenement)
{
//try
//{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
var sql = string.Format("insert into Ecritures_{0}(cleecrit,numecrit,condition", evenement.Libelle);
StringBuilder sb = new StringBuilder();
foreach (var item in obj.Colonnes)
{
sb.Append(",");
sb.Append(item.Champs.Champs);
}
sb.Append(") values(@cleecrit,@numecrit,@condition");
foreach (var item in obj.Colonnes)
{
sb.Append(",");
sb.Append(string.Format("@{0}", item.Champs.Champs));
}
sb.Append(")");
sql += sb.ToString();
cmd.CommandText = sql;
//remplissage des parametres
DatasUtility.AddParameterWithValue(cmd, "@cleecrit", obj.Cle);
DatasUtility.AddParameterWithValue(cmd, "@numecrit", obj.Numero);
DatasUtility.AddParameterWithValue(cmd, "@condition", obj.Condition == null ? (object)DBNull.Value : obj.Condition);
foreach (var item in obj.Colonnes)
{
DatasUtility.AddParameterWithValue(cmd, string.Format("@{0}", item.Champs.Champs), item.Requette);
}
cnx.Open();
cmd.ExecuteNonQuery();
cnx.Close();
return(0);
//}
//catch (Exception)
//{ return 1; }
}
public List <Condition> GetByType(string type)
{
var liste = new List <Condition>();
try
{
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = @"select * from Conditions where Type=@ty";
DatasUtility.AddParameterWithValue(cmd, "@ty", type);
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
var obj = new Condition();
obj.Ref_Util = (!dr.IsDBNull(0)) ? dr.GetInt32(0) : 0;
obj.Code_Fils = (!dr.IsDBNull(1)) ? dr.GetInt32(1) : 0;
obj.Libelle = dr[2].ToString();
obj.Code_Pere = (!dr.IsDBNull(3)) ? dr.GetInt32(3) : 0;
obj.Type = dr[4].ToString();
obj.Selection = dr[5].ToString();
obj.Niveau = (!dr.IsDBNull(6)) ? dr.GetInt32(6) : 0;
obj.Requete = dr[7].ToString();
obj.Valeur = dr[8].ToString();
obj.TableTmp = dr[9].ToString();
obj.NumEcriture = (!dr.IsDBNull(10)) ? dr.GetInt32(10) : 0;
obj.Cle = dr[11].ToString();
obj.Form = dr[12].ToString();
obj.CodeTable = (!dr.IsDBNull(13)) ? dr.GetInt32(13) : 0;
liste.Add(obj);
}
cnx.Close();
return(liste);
}
catch (Exception)
{ return(null); }
}