//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 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 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 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; }
}
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 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 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 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);
}
//Methodes
private void join_btn_Click(object sender, EventArgs e)
{
var bw = new BackgroundWorker();
server_tb.Enabled = servers_list.Enabled = join_btn.Enabled = false;
//vider la liste
ServerDatabases.Clear();
//Affecter le helper pour contenir les information de connection
Helper.Server = server_tb.Text;
Helper.Username = login_tb.Text;
Helper.Password = password_tb.Text;
Connector = DatasUtility.GetConenctorByProvider(servers_list.SelectedItem.ToString());
Connector.cnxHelper = Helper;
bw.DoWork += (send, ev) =>
{
ev.Result = Connector.GetServerDatabases();
};
bw.RunWorkerCompleted += (send, ev) =>
{
ServerDatabases = (List <string>)ev.Result;
getDbProgress.Visible = false;
server_tb.Enabled = servers_list.Enabled = join_btn.Enabled = true;
//Affecter l'evenement du fin de traitement
if (FinTraitements != null)
{
FinTraitements(this, new EventArgs());
}
};
getDbProgress.Visible = true;
bw.RunWorkerAsync();
}
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 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 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 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 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 override string CreateStoredProcedureQuery(ConnexionSysteme sysAmont, params object[] args)
{
try
{
var output = (List <Parametre>)args[0]; var input = (List <Parametre>)args[1];
string nomSp = args[2].ToString(); string tmp = args[3].ToString();
string evenement = args[4].ToString(); string viewName = args[5].ToString();
//Connecteur vers le systéme amont
DbConnector connectToSystem = DatasUtility.GetConenctorByProvider(sysAmont.ServerType);
connectToSystem.cnxHelper = sysAmont.CnxHelper;
StringBuilder sb = new StringBuilder();
//l'entete de la procedure
sb.Append(connectToSystem.CreateSPHeader(input, nomSp));
sb.Append("\nBEGIN");
sb.Append("\n" + connectToSystem.MakeCreateTableQuery(output, tmp));//Creation de la table temporaire
sb.Append("\n");
//Check L'existance du trigger
sb.Append("\nDeclare @checkTrSQL nvarchar(max) = '");
sb.Append(DatasUtility.GetFileCheckTrValue(tmp, evenement));
sb.Append("';");
sb.Append("\nExecute(@checkTrSQL);");
sb.Append("\n");
//Creation du Trigger de trasmission: [Tmp] Vers La table[Mvt_tbLSortie] dans BD(INTERMEDIAIRE)
sb.Append("\nDeclare @TrSQL nvarchar(max) = '");
sb.Append(connectToSystem.CreateTriggerQuery(tmp, evenement));
sb.Append("';");
sb.Append("\nExecute(@TrSQL);");
sb.Append("\n");
//remplir la table temporaire avec le SELECT
sb.Append("\n" + connectToSystem.CreateInsertIntoTmpQuery(output, tmp));
sb.Append("\n" + connectToSystem.CreateSelectQuery(output, input, viewName));
sb.Append("\nEND");//les SP de tous providers termine par END
return(sb.ToString());
}
catch (Exception ex)
{
return("Error In Create Procedure:\n" + ex.Message);
}
}
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; }
}
private void valider_btn_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(sqlStoredProcedure))
{
var systemAmont = (ConnexionSysteme)systemes_liste.SelectedItem;
var amontConnector = DatasUtility.GetConenctorByProvider(systemAmont.ServerType);
amontConnector.cnxHelper = systemAmont.CnxHelper;
MessageBox.Show("AmontConnector: " + amontConnector.GetChaineConnection());
if (amontConnector.ExecuteNonQuery(sqlStoredProcedure) == 0)
{
MessageBox.Show("La Procedure à été bien créer sur le systeme");
}
else
{
MessageBox.Show("Il y avait une erreur dans la creation de la procedure stockée");
}
}
else
{
MessageBox.Show("Veuillez generer d'abord la procedure stockée");
}
}
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); }
}
public override string CreateTriggerQuery(string tableTmp, string evenenement)
{
var triggerValue = DatasUtility.GetFileTriggerValue(tableTmp, evenenement);
return(triggerValue);
}
