public List<string> GetAllBySystem(ConnexionSysteme sys)
{
var liste = new List<string>();
try
{
//construire le Connector
if (sys.ServerType.Equals("MSSQLSERVER"))
{
Connector = new MSSQLConnecctor(sys.CnxHelper);
}
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "SELECT name FROM sys.views";
cnx.Open();
var dr = cmd.ExecuteReader();
while (dr.Read())
{
liste.Add(dr[0].ToString());
}
cnx.Close();
}
catch (Exception)
{}
return liste;
}
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 List <ConnexionSysteme> GetAll()
{
var liste = new List <ConnexionSysteme>();
//try
//{
ConnexionSysteme obj;
var cnx = Connector.GetConnection();
var cmd = cnx.CreateCommand();
cmd.CommandText = "select * from Connexions_Systems";
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);
//}
//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);
}
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 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);
}
}
private void edit_btn_Click(object sender, EventArgs e)
{
if (typeserv_list.SelectedIndex != -1 && libelle_liste.SelectedIndex != -1)
{
var obj = new ConnexionSysteme();
obj.Code = Convert.ToInt32(libelle_liste.SelectedValue);
obj.Libelle = libelle_tb.Text;
obj.ServerType = typeserv_list.SelectedItem.ToString();
obj.CnxHelper.Database = db_tb.Text;
obj.CnxHelper.Server = server_tb.Text;
obj.CnxHelper.Username = login_tb.Text;
obj.CnxHelper.Password = pass_tb.Text;
//Update
if (servConnexionSystem.Modifier(obj) == 0)
{
MessageBox.Show("Modification Terminée");
Recharger();
}
else
{
MessageBox.Show("Une erreur est survenu lors de la modification!\n-Verifier Les champs");
}
}
}
private void add_btn_Click(object sender, EventArgs e)
{
if (typeserv_list.SelectedIndex != -1)
{
var obj = new ConnexionSysteme();
obj.Code = Convert.ToInt32(libelle_liste.SelectedValue);
obj.Libelle = libelle_tb.Text;
obj.ServerType = typeserv_list.SelectedItem.ToString();
obj.CnxHelper.Database = db_tb.Text;
obj.CnxHelper.Server = server_tb.Text;
obj.CnxHelper.Username = login_tb.Text;
obj.CnxHelper.Password = pass_tb.Text;
//Add
if (servConnexionSystem.Ajouter(obj) == 0)
{
MessageBox.Show("Ajout Terminé");
new_btn.PerformClick();
}
else
{
MessageBox.Show("Une erreur est survenu lors de l'ajout!\n-Verifier Les champs");
}
}
}
/// <summary> ///<para>1 List(sum_moy) output =>args[0]</para> ///<para>2 List(sum_moy) input =>args[1]</para> ///<para>3 string nomStoredProcedure =>args[2]</para> ///<para>4 string nom_table_temporaire =>args[3]</para> ///<para>5 string Nom_Evenemnt =>args[4]</para> ///<para>6 string viewName =>args[5]</para> /// </summary> /// <param name="connectToSystem">un connector pour se connecter à la BD du system</param> /// <param name="args">les argument needed par connector's methodes</param> /// <returns>string</returns> public abstract string CreateStoredProcedureQuery(ConnexionSysteme sysAmont, params object[] args);
