// retourne le répertoire pour les fichiers de config pour la base ou pour un client donné
private string GetDirectoryFile(string NomClient = "")
{
environnementProjet.sPathBase = AppDomain.CurrentDomain.BaseDirectory;
environnementProjet.sPathBase += "configFiles\\";
environnementProjet.sPathBaseClient = environnementProjet.sPathBase; // par defaut
NomClient = NomClient.Trim();
NomClient = NomClient.Replace(' ', '_');
if (!string.IsNullOrEmpty(NomClient))
{
environnementProjet.sPathBaseClient = environnementProjet.sPathBase + NomClient;
if (!Directory.Exists(environnementProjet.sPathBaseClient))
{
try {
Directory.CreateDirectory(environnementProjet.sPathBaseClient);
SingleLogFileAsXml.Instance().AjouteLog("SingleSessionConfig", "@GetDirectoryFile : Creation nouveau répertoire : " + environnementProjet.sPathBaseClient);
}
catch (Exception ex) {
SingleLogFileAsXml.Instance().AjouteLog("SingleSessionConfig", "@GetDirectoryFile : PB : " + ex.Message);
}
}
else
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@GetDirectoryFile : Environnement Global Existe : " + environnementProjet.sPathBaseClient);
}
environnementProjet.sPathBaseClient += "\\";
return(environnementProjet.sPathBaseClient);
}
return(environnementProjet.sPathBase);
}
public string GetConnexionString()
{
string sResult = "";
try {
// Recherche des connexion pour BDD :
var appSetting = ConfigurationManager.AppSettings;
foreach (var app in appSetting)
{
var appItem = app;
}
var config = ConfigurationManager.ConnectionStrings;
foreach (var s in config)
{
string sconfig = s.ToString();
var liste = sconfig.Split(';').ToList();
string SourceProvider = "";
string SourceCatalogue = "";
foreach (var param in liste)
{
if (param.Contains("provider connection string=\"data source="))
{
SourceProvider = param.Replace("provider connection string=\"data source=", "");
}
if (param.Contains("initial catalog="))
{
SourceCatalogue = param.Replace("initial catalog=", "");
}
}
if (!string.IsNullOrEmpty(SourceProvider) && !string.IsNullOrEmpty(SourceCatalogue))
{
StatutItemBDD statutItemBDD = new StatutItemBDD()
{
SourceProvider = SourceProvider, SourceCatalogue = SourceCatalogue
};
publishStatutAllBDD.listStatutItemBDD.Add(statutItemBDD);
sResult += "SourceProvider : " + SourceProvider + " \nSourceCatalogue : " + SourceCatalogue;
}
else
{
sResult += "No SourceProvider and NoSourceCatalogue";
}
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("SingleStatutBDD", "GetConnexionString ex :" + ex.Message);
sResult += "Exception : " + ex.Message;
}
return(sResult);
}
public void QUERY_GetStructure_CONSTRAINT_COLUMN_USAGE(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
sDebug += " TABLE_SCHEMA : " + TABLE_SCHEMA;
string TABLE_NAME = dr["TABLE_NAME"].ToString();
sDebug += " TABLE_NAME : " + TABLE_NAME;
string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
sDebug += " COLUMN_NAME : " + COLUMN_NAME;
string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
sDebug += " CONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
sDebug += " CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
sDebug += " CONSTRAINT_NAME : " + CONSTRAINT_NAME;
var table = infoDataBase.listInfosTable.Where(c => c.NomTable == TABLE_NAME).FirstOrDefault();
if (table != null)
{
var column = table.listInfosColumn.Where(c => c.NomColonne == COLUMN_NAME).FirstOrDefault();
if (column != null)
{
column.ContrainteCatalog = CONSTRAINT_CATALOG;
column.ContrainteSchema = CONSTRAINT_SCHEMA;
column.ContrainteNom = CONSTRAINT_NAME;
}
}
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public void QUERY_GetStructure_sys_objects(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM sys.sysobjects ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read()) // signification voir : https://msdn.microsoft.com/fr-fr/library/ms177596(v=sql.120).aspx
{
string name = dr["name"].ToString(); // Nom de l'objet
sDebug += "\nname : " + name;
string id = dr["id"].ToString().Trim(); // Numero identification de l'objet
sDebug += " id : " + id;
string xtype = dr["xtype"].ToString(); // Type d'objet exple : S = TableSystème, P = Procédure stockée, U= Table Utilisateur, IF = Fonction de table Inline,
sDebug += " xtype : " + xtype; // D : Valeur par défaut ou contrainte DEFAULT, F = Contrainte Foreign Key, PK = Contrainte PrimaryKey, UK = Contrainte UNIQUE
string uid = dr["uid"].ToString();
sDebug += " uid : " + uid; // uid ID de schèma du propriétaire de l'objet... voir
// On saute ici plusieurs colonnes Non prises en charge...
string parent_obj = dr["parent_obj"].ToString(); // Numero d'id de l'objet parent, par exemple l'Id de table s'il s'agit d'un declencheur ou d'une contrainte.
sDebug += " parent_obj : " + parent_obj;
string crdate = dr["crdate"].ToString(); // Date de creation de l'objet
sDebug += " crdate : " + crdate;
string ftcatid = dr["ftcatid"].ToString(); // Identificateur du catalogue de texte intégral pour tables users enregistrées
sDebug += " ftcatid : " + ftcatid;
string type = dr["type"].ToString(); // Type d'objet
sDebug += " type : " + type;
InfoSysObject infoSysObject = new InfoSysObject()
{
ObjectName = name, Id_Object = int.Parse(id), xTypeObject = xtype, ParentIdObject = int.Parse(parent_obj), sDateTimeCreationObject = crdate
};
infoDataBase.listInfoSysObject.Add(infoSysObject);
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
// RECIP /INIT des fichiers de config
#region Fichier de config d'entrée environnement et client a conserver.
private XmlEnvironnementSession GetXmlEnvironnementSession(string sPath)
{
XmlEnvironnementSession xmlEnvironnementSession = null;
if (File.Exists(sPath))
{
xmlEnvironnementSession = FastSerialisation.Instance().GetSaveStructInCurrentDirectory <XmlEnvironnementSession>(sPath);
return(xmlEnvironnementSession);
}
else
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@GetXmlEnvironnementSession : Path n'EXISTE PAS : "******"", listEnvironnementExecutionDefault = new List <EnvironnementExecution>()
};
EnvironnementExecution environnementExecution = new EnvironnementExecution()
{
IsDefault = true, NomEnvironnement = "DEV1", listConnexionDB = new List <ConnexionDB>()
};
ConnexionDB connexionDB = new ConnexionDB()
{
NomConnectionBDD = "BDDProcess", ToConnect = true, ModeConnectionString = true, NomServeur = "FAUVEL-PORTABLE\\SQLEXPRESS", NomDB = "XXPackingProcessV3", NomModel = "BdModel.ModelDBxxProcessV3", ExtraConnectionStringOdbc = ""
};
environnementExecution.listConnexionDB.Add(connexionDB);
environnementExecution.connexionServeurPrint = new ConnexionServeurPrint()
{
ToConnect = true, sServeurWCF = "127.0.0.1", pathConfigSerciceMoteur = "D:\\ServiceMoteurImpression\fichierini.config", IsMoteurOnThisHost = true, VersionGestionnaire = "V1", ListNomImprimanteToConnect = new List <string>()
};
environnementExecution.configWebInterface = new ConfigWebInterface()
{
ListeMenuVisibles = new List <string>(), ListeShortButton = new List <ConfigShortButton>()
};
environnementExecution.configWebInterface.ListeMenuVisibles.Add("showProcess");
environnementExecution.configWebInterface.ListeMenuVisibles.Add("showPrinterData");
environnementExecution.configWebInterface.ListeShortButton.Add(new ConfigShortButton()
{
BtnName = "TPrint", BtnClick = "Printer"
});
xmlEnvironnementSession.listEnvironnementExecutionDefault.Add(environnementExecution);
string statutMes = FastSerialisation.Instance().SaveStructInCurrentDirectory <XmlEnvironnementSession>(xmlEnvironnementSession, sPath);
SingleLogFileAsXml.Instance().AjouteLog("SingleSessionConfig", "@GetXmlEnvironnementSession : Config n'existe pas : creation : " + sPath + " : " + statutMes);
return(xmlEnvironnementSession);
}
public void QUERY_GetStructure_TABLES(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
sDebug += " TABLE_SCHEMA : " + TABLE_SCHEMA;
string TABLE_NAME = dr["TABLE_NAME"].ToString();
sDebug += " TABLE_NAME : " + TABLE_NAME;
string TABLE_TYPE = dr["TABLE_TYPE"].ToString();
sDebug += " TABLE_TYPE : " + TABLE_TYPE;
InfosTable infosTable = new InfosTable()
{
NomTable = TABLE_NAME, TableType = TABLE_TYPE
};
var sysTable = infoDataBase.listInfoSysObject.Where(c => c.ObjectName == TABLE_NAME).FirstOrDefault();
if (sysTable != null)
{
infosTable.infoSysObject = sysTable;
}
else
{
infoDataBase.CptNotDefined++;
}
infoDataBase.listInfosTable.Add(infosTable);
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public int QUERY_Simple(string sQuery)
{
int Reponse = -1;
try {
var Cmd = GetNewCmdSQL(sQuery);
var dr = Cmd.ExecuteReader();
while (dr.Read())
{
var r = dr[0].ToString(); // seulement le premier élément interesse
Reponse = int.Parse(r);
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xArticleAS400", "@QUERY_Simple : Exception : " + ex.Message);
}
return(Reponse);
}
public void QUERY_GetStructure_REFERENTIAL_CONSTRAINTS(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
sDebug += "\nCONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
sDebug += " CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
sDebug += " CONSTRAINT_NAME : " + CONSTRAINT_NAME;
string UNIQUE_CONSTRAINT_CATALOG = dr["UNIQUE_CONSTRAINT_CATALOG"].ToString();
sDebug += " UNIQUE_CONSTRAINT_CATALOG : " + UNIQUE_CONSTRAINT_CATALOG;
string UNIQUE_CONSTRAINT_SCHEMA = dr["UNIQUE_CONSTRAINT_SCHEMA"].ToString();
sDebug += " UNIQUE_CONSTRAINT_SCHEMA : " + UNIQUE_CONSTRAINT_SCHEMA;
string UNIQUE_CONSTRAINT_NAME = dr["UNIQUE_CONSTRAINT_NAME"].ToString();
sDebug += " UNIQUE_CONSTRAINT_NAME : " + UNIQUE_CONSTRAINT_NAME;
string MATCH_OPTION = dr["MATCH_OPTION"].ToString();
sDebug += " MATCH_OPTION : " + MATCH_OPTION;
string UPDATE_RULE = dr["UPDATE_RULE"].ToString();
sDebug += " UPDATE_RULE : " + UPDATE_RULE;
string DELETE_RULE = dr["DELETE_RULE"].ToString();
sDebug += " DELETE_RULE : " + DELETE_RULE;
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public int QUERY_Lignes(string sQuery)
{
int Reponse = -1;
try {
var Cmd = GetNewCmdSQL(sQuery);
var dr = Cmd.ExecuteReader();
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
dr[i].ToString();
}
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xArticleAS400", "@QUERY_Lignes : " + ex.Message);
}
return(Reponse);
}
public int QUERY_Count(string sTable)
{
string query = "SELECT Count(*) FROM " + sTable;
int count = 0;
try {
OdbcCommand Cmd = null;
Cmd = GetNewCmdSQL(query);
var dr = Cmd.ExecuteReader();
while (dr.Read())
{
count = (int)dr[0];
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xArticleAS400", "@xArticleAS400#07 QUERY_CountListeArticlesFamille : Exception : " + ex.Message);
}
return(count);
}
public void QUERY_GetStructure_KEY_COLUMN_USAGE(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
sDebug += "\nCONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
sDebug += " CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
sDebug += " CONSTRAINT_NAME : " + CONSTRAINT_NAME;
string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
sDebug += " TABLE_CATALOG : " + TABLE_CATALOG;
string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
sDebug += " TABLE_SCHEMA : " + TABLE_SCHEMA;
string TABLE_NAME = dr["TABLE_NAME"].ToString();
sDebug += " TABLE_NAME : " + TABLE_NAME;
string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
sDebug += " COLUMN_NAME : " + COLUMN_NAME;
string ORDINAL_POSITION = dr["ORDINAL_POSITION"].ToString();
sDebug += " ORDINAL_POSITION : " + ORDINAL_POSITION;
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public bool OpenConnectionDB()
{
if (IsKeepOpen) // si KeepOpen on n'ouvre pas
{
return(true);
}
if (ConnectOdbc != null)
{
try {
if (ConnectOdbc.State != ConnectionState.Open)
{
ConnectOdbc.Open();
}
return(true);
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("SingleAnalyseTableSQL", "@OpenConnectionDB Exception :" + ex.Message);
return(false);
}
}
return(false);
}
public void QUERY_GetStructure_sys_indexes(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM sys.indexes ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string object_id = dr["object_id"].ToString();
sDebug += "\nobject_id : " + object_id;
string name = dr["name"].ToString();
sDebug += " name : " + name;
string index_id = dr["index_id"].ToString();
sDebug += " index_id : " + index_id;
string type = dr["type"].ToString();
sDebug += " type : " + type;
string type_desc = dr["type_desc"].ToString();
sDebug += " type_desc : " + type_desc;
string referenced_column_id = dr["is_primary_key"].ToString();
sDebug += " is_primary_key : " + referenced_column_id;
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public void QUERY_GetStructure_COLUMS(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
sDebug += " TABLE_SCHEMA : " + TABLE_SCHEMA;
string TABLE_NAME = dr["TABLE_NAME"].ToString();
sDebug += " TABLE_NAME : " + TABLE_NAME;
string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
sDebug += " COLUMN_NAME : " + COLUMN_NAME;
string ORDINAL_POSITION = dr["ORDINAL_POSITION"].ToString();
sDebug += " ORDINAL_POSITION : " + ORDINAL_POSITION;
string IS_NULLABLE = dr["IS_NULLABLE"].ToString();
sDebug += " IS_NULLABLE : " + IS_NULLABLE;
string DATA_TYPE = dr["DATA_TYPE"].ToString();
sDebug += " DATA_TYPE : " + DATA_TYPE;
string CHARACTER_MAXIMUM_LENGTH = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();
sDebug += " CHARACTER_MAXIMUM_LENGTH : " + CHARACTER_MAXIMUM_LENGTH;
var table = infoDataBase.listInfosTable.Where(c => c.NomTable == TABLE_NAME).FirstOrDefault();
if (table != null)
{
InfosColumn infosColumn = new InfosColumn()
{
NomColonne = COLUMN_NAME, sPositionOrdinale = ORDINAL_POSITION, isNullable = IS_NULLABLE, sDataType = DATA_TYPE, caracterMaxLenght = CHARACTER_MAXIMUM_LENGTH
};
var syscolumn = table.infoSysObject.listInfosSysColums.Where(c => c.NameColumns == COLUMN_NAME).FirstOrDefault();
if (syscolumn != null)
{
infosColumn.infosSysColums = syscolumn;
}
else
{
infoDataBase.CptNotDefined++;
}
table.listInfosColumn.Add(infosColumn);
}
else
{
infoDataBase.CptNotDefined++;
}
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public string FirstInit()
{
ProjectClientName = "";
string sPathBase = GetDirectoryFile(); // get répertoire racine pour les configs
try {
// exploration des répertoires et fichiers pour infos
var listDirectoryClient = Directory.GetDirectories(sPathBase).ToList();
string sTemp = "";
string sTempFile = "";
foreach (var d in listDirectoryClient)
{
sTemp = d.Replace(sPathBase, "");
ClientRepertoire clientRepertoire = new ClientRepertoire()
{
NomRepertoire = sTemp, listFichiers = new List <string>()
};
var listFiles = Directory.GetFiles(d).ToList();
foreach (var f in listFiles)
{
sTempFile = f.Replace(d + "\\", "");
clientRepertoire.listFichiers.Add(sTempFile);
}
environnementGlobal.listClientRepertoire.Add(clientRepertoire);
}
// tentative de récupérations des 2 principaux fichiers de config au demarrage ConfigEnvironnementSession et ConfigEnvironnementClient s'il y a lieu
string sPathMainConfig = sPathBase + "ConfigEnvironnementSession.xml";
if (File.Exists(sPathMainConfig))
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@FirstInit : Config Environnement Client EXISTE : " + sPathMainConfig);
}
else
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@FirstInit : Config Environnement Client N'EXISTE PAS : "******"SingleSessionConfig", "@FirstInit : configEnvironnementSession OK / AutoStart : " + configEnvironnementSession.AutoStartSurClient + "/ client Name : " + configEnvironnementSession.AutoStartClientName);
}
else
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@FirstInit : configEnvironnementSession = NULL !!");
}
if (configEnvironnementSession != null && configEnvironnementSession.AutoStartSurClient && !string.IsNullOrEmpty(configEnvironnementSession.AutoStartClientName))
{
ProjectClientName = configEnvironnementSession.AutoStartClientName;
DummyModifMainEnvironnementConfig(configEnvironnementSession, sPathMainConfig); // pour mise a jour eventuelle
sPathBase = GetDirectoryFile(configEnvironnementSession.AutoStartClientName);
environnementProjet.ClientName = configEnvironnementSession.AutoStartClientName;
EnvironnementExecutionActif = configEnvironnementSession.listEnvironnementExecutionDefault.Where(c => c.IsDefault).FirstOrDefault(); // set de l'environnement d'exécution actif
// Recup / Init des fichiers dans rep client..
// a ajuster en fonction utilité des fichiers
string sPathClientConfig = sPathBase + "ConfigEnvironnementClient.xml";
configEnvironnementClient = GetXmlConfigEnvironnementClient(sPathClientConfig);
if (configEnvironnementClient != null && configEnvironnementClient.listTypeFileConfigNeeded != null)
{
DummyModifClientEnvironnementConfig(configEnvironnementClient, sPathClientConfig); // pour mise a jour eventuelle
environnementProjet.IsClientSelectedOK = true;
environnementProjet.listTypeFileConfigNeeded = new List <TypeFileConfigNeeded>();
environnementProjet.listTypeFileConfigNeeded.Add(new TypeFileConfigNeeded()
{
LibelleFile = "Config", TypeFileConfig = "XmlConfigEnvironnementClient", NameFileConfig = "ConfigEnvironnementClient.xml"
});
environnementProjet.listTypeFileConfigNeeded.AddRange(configEnvironnementClient.listTypeFileConfigNeeded);
foreach (var conf in configEnvironnementClient.listTypeFileConfigNeeded)
{
LoadConfigFile(conf);
}
var environnemntExecutionClient = configEnvironnementClient.listEnvironnementExecution.Where(c => c.IsDefault).FirstOrDefault(); // set de l'environnement d'exécution actif
if (environnemntExecutionClient != null)
{
EnvironnementExecutionActif = environnemntExecutionClient;
}
}
}
else
{
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@FirstInit : Il n'y a pas de Config Environnement : " + sPathMainConfig);
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("SingleSessionConfig", "@FirstInit : Exception : " + ex.Message);
}
return(ProjectClientName);
}
public void QUERY_GetStructure_sys_columns(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM sys.columns ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string object_id = dr["object_id"].ToString();
sDebug += "\nobject_id : " + object_id;
string name = dr["name"].ToString();
sDebug += " name : " + name;
string column_id = dr["column_id"].ToString();
sDebug += " column_id : " + column_id;
string system_type_id = dr["system_type_id"].ToString();
sDebug += " system_type_id : " + system_type_id;
string user_type_id = dr["user_type_id"].ToString();
sDebug += " user_type_id : " + user_type_id;
string max_length = dr["max_length"].ToString();
sDebug += " max_length : " + user_type_id;
string precision = dr["precision"].ToString();
sDebug += " precision : " + precision;
string scale = dr["scale"].ToString();
sDebug += " scale : " + scale;
string collation_name = dr["collation_name"].ToString();
sDebug += " collation_name : " + collation_name;
string is_nullable = dr["is_nullable"].ToString();
sDebug += " is_nullable : " + is_nullable;
string is_ansi_padded = dr["is_ansi_padded"].ToString();
sDebug += " is_ansi_padded : " + is_ansi_padded;
string is_rowguidcol = dr["is_rowguidcol"].ToString();
sDebug += " is_rowguidcol : " + is_rowguidcol;
string is_identity = dr["is_identity"].ToString();
sDebug += " is_identity : " + is_identity;
string is_computed = dr["is_computed"].ToString();
sDebug += " is_computed : " + is_computed;
string is_filestream = dr["is_filestream"].ToString();
sDebug += " is_filestream : " + is_filestream;
InfosSysColums infosSysColums = new InfosSysColums()
{
OwnerId = int.Parse(object_id), NameColumns = name, column_Id = int.Parse(column_id), sysTypeId = system_type_id, useTypeId = user_type_id, MaxLenght = int.Parse(max_length), IsNullable = is_nullable, IsIdentity = is_identity, IsComputed = is_computed, IsRowGuidCol = is_rowguidcol
};
var obj = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosSysColums.OwnerId).FirstOrDefault();
if (obj != null)
{
obj.listInfosSysColums.Add(infosSysColums);
}
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public void QUERY_GetStructure_sys_foreign_key_columns(InfoDataBase infoDataBase)
{
string queryGetNumCmd = "SELECT * FROM sys.foreign_key_columns ";
Stopwatch sw = new Stopwatch();
sw.Start();
string sDebug = "";
try {
OdbcCommand CmdGetNumCmd = null;
CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);
string sdebug1 = "";
var dr = CmdGetNumCmd.ExecuteReader();
int cpt = 0;
while (dr.Read())
{
string constraint_object_id = dr["constraint_object_id"].ToString();
sDebug += "\nconstraint_object_id : " + constraint_object_id;
string constraint_column_id = dr["constraint_column_id"].ToString();
sDebug += " constraint_column_id : " + constraint_column_id;
string parent_object_id = dr["parent_object_id"].ToString();
sDebug += " parent_object_id : " + parent_object_id;
string parent_column_id = dr["parent_column_id"].ToString();
sDebug += " parent_column_id : " + parent_column_id;
string referenced_object_id = dr["referenced_object_id"].ToString();
sDebug += " referenced_object_id : " + referenced_object_id;
string referenced_column_id = dr["referenced_column_id"].ToString();
sDebug += " referenced_column_id : " + referenced_column_id;
InfosContraintes infosContraintes = new InfosContraintes()
{
constraint_object_id = int.Parse(constraint_object_id),
constraint_column_id = int.Parse(constraint_column_id),
parent_object_id = int.Parse(parent_object_id),
parent_column_id = int.Parse(parent_column_id),
referenced_object_id = int.Parse(referenced_object_id),
referenced_column_id = int.Parse(referenced_column_id)
};
var obj1 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.constraint_object_id).FirstOrDefault();
if (obj1 != null)
{
infosContraintes.name_constraint_object_id = obj1.ObjectName;
}
var obj2 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.parent_object_id).FirstOrDefault();
if (obj2 != null)
{
infosContraintes.name_parent_object_id = obj2.ObjectName;
}
var obj3 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.referenced_object_id).FirstOrDefault();
if (obj3 != null)
{
infosContraintes.name_referenced_object_id = obj3.ObjectName;
}
sdebug1 += "\n" + infosContraintes.name_constraint_object_id;
sdebug1 += " [" + infosContraintes.constraint_column_id + "] ";
sdebug1 += " = ";
sdebug1 += string.IsNullOrEmpty(infosContraintes.name_parent_object_id) ? "?? " + infosContraintes.parent_object_id.ToString() + " ??" : infosContraintes.name_parent_object_id;
sdebug1 += " [" + infosContraintes.parent_column_id + "] ";
sdebug1 += " <-> ";
sdebug1 += string.IsNullOrEmpty(infosContraintes.name_referenced_object_id) ? "?? " + infosContraintes.referenced_object_id.ToString() + " ??" : infosContraintes.name_referenced_object_id;
sdebug1 += " [" + infosContraintes.referenced_column_id + "] ";
infoDataBase.listInfosContraintes.Add(infosContraintes);
cpt++;
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure Exception :" + ex.Message);
}
sw.Stop();
}
public InfoDataBase GetStructure(bool modeForce)
{
if (infoDataBase == null || modeForce)
{
infoDataBase = new InfoDataBase();
if (OpenConnectionDB())
{
// Récupération des donnes sys..
QUERY_GetStructure_sys_objects(infoDataBase); // récupération de tous les obejcts de la database
QUERY_GetStructure_sys_foreign_key_columns(infoDataBase); // Récupération des contraintes foreign key
QUERY_GetStructure_sys_columns(infoDataBase); // Récup des infos sys pour les colonnes
QUERY_GetStructure_sys_indexes(infoDataBase);
QUERY_GetStructure_TABLES(infoDataBase);
QUERY_GetStructure_COLUMS(infoDataBase);
QUERY_GetStructure_CONSTRAINT_COLUMN_USAGE(infoDataBase);
QUERY_GetStructure_KEY_COLUMN_USAGE(infoDataBase);
QUERY_GetStructure_REFERENTIAL_CONSTRAINTS(infoDataBase);
foreach (var table in infoDataBase.listInfosTable)
{
int count = QUERY_Count(table.NomTable);
table.NbreCount = count;
table.DestinationDifferent = table.NbreCount != table.NbreCountOrigine; // pour le moment les différences sont sur le count..
// Pour les reférence Foreign Key :
TableRelationTable tableRelationTableFK = new TableRelationTable()
{
NomTable = table.NomTable, TypeContrainte = eTypeContrainte.ForeignKey, NbreCountRec = count, NbreRelation = 0, listRelationToTable = new List <RelationToTable>()
};
var lstTableForeignKey = infoDataBase.listInfosContraintes.Where(c => c.name_parent_object_id == table.NomTable).ToList();
if (lstTableForeignKey.Count != 0)
{
tableRelationTableFK.NbreRelation = lstTableForeignKey.Count;
foreach (var t in lstTableForeignKey) // On ne descend ici qu'au premier niveau de relation
{
TableRelationTable tableRelationTable = new TableRelationTable()
{
NomTable = t.name_referenced_object_id, listRelationToTable = new List <RelationToTable>()
};
RelationToTable relationToTable = new RelationToTable()
{
NomContrainte = t.name_constraint_object_id, TableRelation = tableRelationTable, IdColumnFrom = t.parent_column_id, IdColumnTo = t.referenced_column_id
};
tableRelationTableFK.listRelationToTable.Add(relationToTable);
}
}
table.NbreForeignKey = lstTableForeignKey.Count;
table.TableRelationTableForeignKey = tableRelationTableFK;
infoDataBase.listTableRelationTable.Add(tableRelationTableFK);
// Pour les reférence Ref Foreign Key :
TableRelationTable tableRelationTableRefFK = new TableRelationTable()
{
NomTable = table.NomTable, TypeContrainte = eTypeContrainte.RefForeignKey, NbreCountRec = count, NbreRelation = 0, listRelationToTable = new List <RelationToTable>()
};
var lstTableRefForeignKey = infoDataBase.listInfosContraintes.Where(c => c.name_referenced_object_id == table.NomTable).ToList();
if (lstTableRefForeignKey.Count != 0)
{
tableRelationTableRefFK.NbreRelation = lstTableRefForeignKey.Count;
foreach (var t in lstTableRefForeignKey)
{
TableRelationTable tableRelationTable = new TableRelationTable()
{
NomTable = t.name_parent_object_id, listRelationToTable = new List <RelationToTable>()
};
RelationToTable relationToTable = new RelationToTable()
{
NomContrainte = t.name_constraint_object_id, TableRelation = tableRelationTable, IdColumnFrom = t.referenced_column_id, IdColumnTo = t.parent_column_id
};
tableRelationTableRefFK.listRelationToTable.Add(relationToTable);
}
}
table.NbreRefForeignKey = lstTableRefForeignKey.Count;
table.TableRelationTableRefForeignKey = tableRelationTableRefFK;
infoDataBase.listTableRelationTable.Add(tableRelationTableRefFK);
// POur l'identity :
foreach (var c in table.listInfosColumn)
{
int Position = int.Parse(c.sPositionOrdinale);
bool Identity = c.infosSysColums.IsIdentity == "True";
if (Position == 1 && Identity)
{
table.HasColumnIdentity = true;
}
}
}
CloseConnectionDB();
}
else
{
GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@GetStructure #01 GetLignes DB not open !");
}
// Premier filtrage général , on ne retient que les tables USer (BASE TABLE), on exclu les vues et table système:
infoDataBase.listInfosTable = infoDataBase.listInfosTable.Where(c => c.TableType == "BASE TABLE" && c.NomTable != "sysdiagrams").OrderBy(c => c.NomTable).ToList();
}
return(infoDataBase);
}
public string ReadExcelFile(string FileName)
{
bool ConfigExist = true;
xmlConfigInputExcelFile = SingleSessionConfig.Instance().GetXmlConfigInputExcelFile();
if (xmlConfigInputExcelFile == null || xmlConfigInputExcelFile.ColInfos == null)
{
xmlConfigInputExcelFile = new XmlConfigInputExcelFile()
{
ColInfos = new List <ColInfo>(), NbCol = 0, FamilleName = "", ConfigOKForUpdateArticle = false
};
ConfigExist = false;
}
//this.xmlConfigInputExcelFile = xmlConfigInputExcelFile;
int NumOnglet = 1;
lastStatutFichier = new StatutFichier()
{
ListExcellLine = new List <ExcelLine>(), ListeColonnes = new List <ExcelColonne>(), ChampsColonnes = new List <string>()
};
try {
FileInfo template = new FileInfo(FileName);
using (ExcelPackage xlPackage = new ExcelPackage(template)) {
ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[NumOnglet];
// Calcul du nombre de ligne consécutives
int RowBase = 1;
string sValeur = "";
do
{
sValeur = worksheet.Cell(RowBase, 1).Value;
RowBase++;
}while (!string.IsNullOrEmpty(sValeur));
// calcul du nombre de colonnes
int ColBase = 1;
do
{
sValeur = worksheet.Cell(1, ColBase).Value;
ColBase++;
}while (!string.IsNullOrEmpty(sValeur));
RowBase--;
ColBase--;
lastStatutFichier.NbRow = RowBase;
lastStatutFichier.NbCol = ColBase;
for (int row = 1; row < RowBase; row++)
{
var line = new ExcelLine()
{
LineRow = row, listValues = new List <string>(), listValuesExtended = new List <ValueExtended>()
};
for (int col = 1; col < ColBase; col++)
{
string varCellule = worksheet.Cell(row, col).Value;
if (string.IsNullOrEmpty(varCellule))
{
//varCellule = "___";
}
if (row == 1)
{
ExcelColonne excelColonne = new ExcelColonne()
{
Nom = varCellule, NumColonne = col
};
lastStatutFichier.ListeColonnes.Add(excelColonne);
lastStatutFichier.ChampsColonnes.Add(varCellule); // old..
}
else
{
line.listValues.Add(varCellule);
ValueExtended valueExtended = new ValueExtended()
{
PosCol = col, Value = varCellule
};
line.listValuesExtended.Add(valueExtended);
}
}
lastStatutFichier.ListExcellLine.Add(line);
}
if (ConfigExist) // Test si compatibilité du fichier excel avec Config Fichier
{
lastStatutFichier.Message = "Config excel available : ";
lastStatutFichier.ConfigOKForUpdateArticle = xmlConfigInputExcelFile.ConfigOKForUpdateArticle;
if (xmlConfigInputExcelFile.NbCol != lastStatutFichier.NbCol)
{
lastStatutFichier.Erreur = true;
lastStatutFichier.Message += "Col Number not match with config";
}
else
{
int NbreDifference = 0;
for (int i = 0; i < lastStatutFichier.NbCol; i++)
{
if (xmlConfigInputExcelFile.ColInfos[i].ColName != lastStatutFichier.ListeColonnes[i].Nom)
{
NbreDifference++;
}
}
if (NbreDifference > 0)
{
lastStatutFichier.Erreur = true;
lastStatutFichier.Message += "Col Name not match with config";
}
}
if (!lastStatutFichier.Erreur)
{
// SI tout est OK..
lastStatutFichier.Message += "Erreur sur xmlConfigInputExcelFile ( a regènèrer)";
}
}
else
{
lastStatutFichier.Message = "Config excel NOT available : Creation xmlConfigInputExcelFile ";
xmlConfigInputExcelFile.NbCol = lastStatutFichier.NbCol;
xmlConfigInputExcelFile.ColInfos = new List <ColInfo>();
foreach (var v in lastStatutFichier.ListeColonnes)
{
xmlConfigInputExcelFile.ColInfos.Add(new ColInfo()
{
ColPosition = v.NumColonne, ColName = v.Nom, ToArticleCodeCritere = "", ToArticleNomCritere = "", ToArticleTypeCritere = 0, IsCritereFamille = false, IsMandatory = true
});
}
}
}
}
catch (Exception ex) {
GlobalLog.Instance().AjouteLog("SingleExcel", "ReadExcelFile ex :" + ex.Message);
}
return(lastStatutFichier.Message);
}