//--------------------------------------------------------------------------------------
private static CResultAErreur SupprimeDependances(string strNomTable, string strNomChamp, IDatabaseConnexion connexion)
{
CResultAErreur result = CResultAErreur.True;
string strRequeteGetForeignKey =
@" SELECT obj2.name
FROM sysobjects AS obj1
INNER JOIN syscolumns AS sc ON sc.id=obj1.id
INNER JOIN sysconstraints AS scon ON (scon.id=obj1.id AND sc.colid=scon.colid)
INNER JOIN sysobjects AS obj2 ON scon.constid=obj2.id
AND obj1.name='" + strNomTable + "' " +
"AND sc.name='" + strNomChamp + "'";
IDataAdapter adapter = connexion.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
connexion.FillAdapter(adapter, ds);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTable + " DROP CONSTRAINT " + (string)row[0];
result = connexion.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constrain @1 of table @2|133", (string)row[0], strNomTable));
return(result);
}
}
string strRequeteIndexes =
@"SELECT si.name
FROM sysobjects tbl INNER JOIN
syscolumns sc ON sc.id = tbl.id INNER JOIN
sysindexkeys sik ON sik.id = tbl.id AND sc.colid = sik.colid INNER JOIN
sysindexes si ON si.id = tbl.id AND si.indid = sik.indid
WHERE (tbl.name = '" + strNomTable + "') AND (sc.name = '" + strNomChamp + "') and " +
"si.name like 'IX%'";
adapter = connexion.GetSimpleReadAdapter(strRequeteIndexes);
ds = new DataSet();
connexion.FillAdapter(adapter, ds);
dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionIndex = " DROP INDEX " + strNomTable + "." + (string)row[0];
result = connexion.RunStatement(strRequeteSuppressionIndex);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting index @1 of the table @2|130", (string)row[0], strNomTable));
return(result);
}
}
return(result);
}
//--------------------------------------------------------------------------------------
private static CResultAErreur SupprimeForeignKeys(string strNomTable, string strNomChamp, IDatabaseConnexion connexion)
{
CResultAErreur result = CResultAErreur.True;
string strRequeteGetForeignKey =
@" SELECT obj2.name
FROM sysobjects AS obj1
INNER JOIN syscolumns AS sc ON sc.id=obj1.id
INNER JOIN sysforeignkeys AS fk ON (fk.fkeyid=obj1.id AND colid=fk.fkey)
INNER JOIN sysobjects AS obj2 ON fk.constid=obj2.id
WHERE obj1.xtype='U'
AND obj1.name='" + strNomTable + "' " +
"AND sc.name='" + strNomChamp + "'";
IDataAdapter adapter = connexion.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
connexion.FillAdapter(adapter, ds);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTable + " DROP CONSTRAINT " + (string)row[0];
result = connexion.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constrain @1 of table @2|133", (string)row[0], strNomTable));
return(result);
}
}
return(result);
}
//------------------------------------------------------------------------------------------------
public static CResultAErreur DeleteTable(string strNomTable, IDatabaseConnexion connexion)
{
string strRequeteSuppressionTable = "DROP TABLE " + strNomTable;
CResultAErreur result = connexion.RunStatement(strRequeteSuppressionTable);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting of table @1|129", strNomTable));
}
return(result);
}
//--------------------------------------------------------------------------------------
public static CResultAErreur SupprimeChamp(string strTable, string strChamp, IDatabaseConnexion connexion)
{
string strReq = "ALTER TABLE " + strTable + " DROP COLUMN " + strChamp;
CResultAErreur result = connexion.RunStatement(strReq);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting column @1 of table @2|132", strChamp, strTable));
}
return(result);
}
// /////////////////////////////////////////////////////////////////////////////
public virtual CResultAErreur DoAdd(DataRow row)
{
string strRequete = "insert into " + m_structure.NomTableInDb + "(";
string strValeurs = "";
foreach (CInfoChampTable info in m_structure.Champs)
{
if (!info.IsAutoId && !IsExclu(info.NomChamp))
{
strRequete += info.NomChamp + ",";
strValeurs += GetStringForRequete(row[info.NomChamp]) + ",";
}
}
if (strValeurs.Length > 0)
{
//Supprime la virgule en trop
strRequete = strRequete.Substring(0, strRequete.Length - 1);
strValeurs = strValeurs.Substring(0, strValeurs.Length - 1);
}
strRequete += ") values(" + strValeurs + ")";
return(m_connexion.RunStatement(strRequete));
}
//------------------------------------------------------------------------------------------------
private static CResultAErreur SupprimeIndex(string strNomTable, IDatabaseConnexion connexion, params string[] strChamps)
{
CResultAErreur result = CResultAErreur.True;
if (!ExistIndex(strNomTable, connexion, strChamps))
{
return(result);
}
string strNomIndex = GetNomIndex(strNomTable, strChamps);
string strRequeteIndex = "DROP INDEX " + strNomTable + "." + strNomIndex;
result = connexion.RunStatement(strRequeteIndex);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting index @1 of the table @2|130", strNomIndex, strNomTable));
return(result);
}
return(result);
}
//------------------------------------------------------------------------------------------------
private static CResultAErreur CreateOrUpdateIndex(string strNomTable, IDatabaseConnexion connexion, params string[] strChamps)
{
CResultAErreur result = CResultAErreur.True;
if (ExistIndex(strNomTable, connexion, strChamps))
{
return(result);
}
string strNomIndex = GetNomIndex(strNomTable, strChamps);
string strRequeteIndex = GetRequeteIndex(strNomTable, strChamps);
result = connexion.RunStatement(strRequeteIndex);
if (!result)
{
result.EmpileErreur(I.T("Error while creating index in the table @1|128", strNomTable));
return(result);
}
return(result);
}
//--------------------------------------------------------------------------------------------------------------------
public override CResultAErreur ExecuterOperation(IDatabaseConnexion connection, IIndicateurProgression indicateur)
{
CResultAErreur result = CResultAErreur.True;
IDataBaseCreator createur = connection.GetDataBaseCreator();
string strNomTableInContexte = CContexteDonnee.GetNomTableForType(m_type);
string strNomTableInDb = CContexteDonnee.GetNomTableInDbForNomTable(strNomTableInContexte);
createur.CreationOuUpdateTableFromType(m_type);
string strChampIdObjet = m_strChampIdObjet;
if (m_strChampIdObjet.StartsWith("#SQL#"))
{
strChampIdObjet = m_strChampIdObjet.Substring("#SQL#".Length);
}
CObjetServeur.ClearCacheSchemas();
if (createur.ChampExists(strNomTableInDb, strChampIdObjet) &&
createur.ChampExists(strNomTableInDb, m_strChampDbKey))
{
using (CContexteDonnee ctx = new CContexteDonnee(connection.IdSession, true, false))
{
C2iRequeteAvancee requete = new C2iRequeteAvancee();
requete.TableInterrogee = strNomTableInDb;
CStructureTable structure = CStructureTable.GetStructure(m_type);
/*
* requete.ListeChamps.Add ( new C2iChampDeRequete (
* "ID",
* new CSourceDeChampDeRequete(structure.ChampsId[0].NomChamp),
* typeof(int),
* OperationsAgregation.None,
* true ));*/
requete.ListeChamps.Add(new C2iChampDeRequete(
"IDOBJET",
new CSourceDeChampDeRequete(m_strChampIdObjet),
typeof(int),
OperationsAgregation.None,
true));
if (m_typeObjetFixe == null)
{
requete.ListeChamps.Add(new C2iChampDeRequete(
"TYPEOBJET",
new CSourceDeChampDeRequete(m_strChampTypeObjet),
typeof(string),
OperationsAgregation.None,
true));
}
result = requete.ExecuteRequete(connection.IdSession);
if (!result)
{
return(result);
}
DataTable table = result.Data as DataTable;
Dictionary <int, int?> dicIdToIdObjet = new Dictionary <int, int?>();
string strFieldIdObjetInTable = m_strChampIdObjet.Replace("#SQL#", "");
foreach (DataRow row in table.Rows)
{
object val = row["IDOBJET"];
int? nValId = val == DBNull.Value?null:(int?)val;
if (nValId != null && nValId >= 0)
{
CDbKey key = null;
Type tp = m_typeObjetFixe;
if (tp == null)//Type non fixe
{
string strType = (string)row["TYPEOBJET"];
tp = CActivatorSurChaine.GetType(strType);
}
if (tp != null)
{
CObjetDonneeAIdNumerique objPointe = (CObjetDonneeAIdNumerique)Activator.CreateInstance(tp, ctx);
if (objPointe.ReadIfExists(nValId.Value))
{
key = objPointe.DbKey;
}
}
if (key != null)
{
string strRequete = "Update " + strNomTableInDb + " set " +
m_strChampDbKey + "=" + connection.GetStringForRequete(key.StringValue) + " where " +
strFieldIdObjetInTable + "=" + nValId.Value;
if (m_typeObjetFixe == null)
{
strRequete += " and " + m_strChampTypeObjet + "='" +
row["TYPEOBJET"].ToString() + "'";
}
connection.RunStatement(strRequete);
}
}
}
}
//Supprime le champ
createur.DeleteChamp(strNomTableInDb, strChampIdObjet);
}
return(result);
}
//--------------------------------------------------------------------------------------------------------------------
public override CResultAErreur ExecuterOperation(IDatabaseConnexion connection, IIndicateurProgression indicateur)
{
CResultAErreur result = CResultAErreur.True;
if (!typeof(IObjetDonneeAIdNumerique).IsAssignableFrom(m_type) || !CObjetDonnee.TypeManageIdUniversel(m_type))
{
result.EmpileErreur(m_type.ToString() + " can not have Universal Id");
}
string strNomTable = CContexteDonnee.GetNomTableForType(m_type);
CStructureTable structure = CStructureTable.GetStructure(m_type);
string strChampId = structure.ChampsId[0].NomChamp;
C2iRequeteAvancee requete = new C2iRequeteAvancee();
requete.FiltreAAppliquer = new CFiltreData(CObjetDonnee.c_champIdUniversel + "=@1", "");
requete.TableInterrogee = CContexteDonnee.GetNomTableForType(m_type);
requete.ListeChamps.Add(new C2iChampDeRequete(strChampId, new CSourceDeChampDeRequete(strChampId),
typeof(int),
OperationsAgregation.None, true));
Console.WriteLine("Update Universal id on " + strNomTable);
result = requete.ExecuteRequete(connection.IdSession);
if (!result)
{
return(result);
}
string strNomTableInDb = CContexteDonnee.GetNomTableInDbForNomTable(strNomTable);
DataTable table = result.Data as DataTable;
int nNb = 0;
bool bWasInTrans = connection.IsInTrans();
if (bWasInTrans)
{
connection.CommitTrans();
}
DateTime dt = DateTime.Now;
if (table.Rows.Count > 0)
{
if (table.Rows.Count > 10000 && connection is COracleDatabaseConnexion)
{
string strQuery = "Update " + strNomTableInDb + " set " + CObjetDonnee.c_champIdUniversel +
"=CONCAT('" + strNomTableInDb + "'," + strChampId + ")";
result = connection.RunStatement(strQuery);
}
else
{
foreach (DataRow row in table.Rows)
{
string strQuery = "Update " + strNomTableInDb + " set " +
CObjetDonnee.c_champIdUniversel + "='" + CUniqueIdentifier.GetNew() + "' where " +
strChampId + "=" + row[0].ToString();
result = connection.ExecuteScalar(strQuery);
if (!result)
{
break;
}
nNb++;
if (nNb % 100 == 0)
{
TimeSpan sp = DateTime.Now - dt;
double fVal = (double)sp.TotalSeconds / (double)nNb * (double)(table.Rows.Count - nNb);
Console.WriteLine(strNomTableInDb + " " + nNb + "/" + table.Rows.Count + " reste " + fVal.ToString("0s"));
}
}
}
}
if (bWasInTrans)
{
connection.BeginTrans();
}
return(result);
/*
*
* //Trouve tous les éléments du type qui n'ont pas d'id universel
* using (CContexteDonnee ctx = new CContexteDonnee(connection.IdSession, true, false))
* {
* CListeObjetsDonnees lst = new CListeObjetsDonnees(ctx, m_type, false);
* lst.Filtre = new CFiltreData(CObjetDonnee.c_champIdUniversel + "=@1", "");
* lst.Filtre.IgnorerVersionDeContexte = true;
* lst.AppliquerFiltreAffichage = false;
*
* foreach (IObjetDonnee objet in lst.ToArrayList())
* {
* objet.Row[CObjetDonnee.c_champIdUniversel] = CUniqueIdentifier.GetNew();
* }
* string strNomTable = CContexteDonnee.GetNomTableForType(m_type);
* CObjetServeur objetServeur = CContexteDonnee.GetTableLoader(strNomTable, null, connection.IdSession) as CObjetServeur;
* int nCount = ctx.Tables[strNomTable].Rows.Count;
* DataTable tableSource = ctx.Tables[strNomTable];
*
* List<string> lstChampsExclus = new List<string>();
* HashSet<string> lstIDs = new HashSet<string>();
* CStructureTable structure = CStructureTable.GetStructure(m_type);
* foreach (CInfoChampTable info in structure.ChampsId)
* lstIDs.Add(info.NomChamp);
*
* foreach (CInfoChampTable champ in structure.Champs)
* if (champ.NomChamp != CObjetDonnee.c_champIdUniversel && !lstIDs.Contains(champ.NomChamp))
* lstChampsExclus.Add(champ.NomChamp);
*
* IDataAdapter adapter = objetServeur.GetDataAdapter(DataRowState.Modified, lstChampsExclus.ToArray());
*
* for (int nRow = 0; nRow < nCount; nRow += 5000)
* {
*
* using (DataSet dsCopie = new DataSet())
* {
* dsCopie.EnforceConstraints = false;
* DataTable tableCopie = ctx.Tables[strNomTable].Clone();
* tableCopie.BeginLoadData();
* dsCopie.Tables.Add(tableCopie);
* int nMax = Math.Min(nRow + 5000, nCount);
* DateTime dt = DateTime.Now;
* for (int n = nRow; n < nMax; n++)
* {
* tableCopie.ImportRow(tableSource.Rows[n]);
* }
* TimeSpan sp = DateTime.Now - dt;
* Console.WriteLine("Write 1" + strNomTable + " " + nRow + "/" + nCount+" "+sp.TotalSeconds.ToString());
* adapter.Update(dsCopie);
* sp = DateTime.Now - dt;
* Console.WriteLine("Write 2" + strNomTable + " " + nRow + "/" + nCount + " " + sp.TotalSeconds.ToString());
* }
* }
* }
* return result;*/
}
//------------------------------------------------------------------------------------------------
public static CResultAErreur UpdateTable(CStructureTable structure, IDatabaseConnexion connexion, ArrayList strChampsAutorisesANull)
{
ArrayList champsCrees = new ArrayList();
CResultAErreur result = CResultAErreur.True;
IDataAdapter adapter = connexion.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
DataTable dt = ds.Tables["TABLE"];
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region M.A.J des champs de la table
foreach (CInfoChampTable info in structure.Champs)
{
bool bExists = true;
bool bModifiedType = false;
bool bModifiedNotNull = false;
bool bModifiedLength = false;
foreach (DataColumn colonne in dt.Columns)
{
if (info.NomChamp == colonne.ColumnName)
{
if (info.IsLongString)
{
if (colonne.MaxLength < 10000)
{
bModifiedLength = true;
}
}
else
if (colonne.MaxLength != info.Longueur && info.TypeDonnee == typeof(string))
{
bModifiedLength = true;
}
if (colonne.DataType != info.TypeDonnee)
{
if (info.TypeDonnee != typeof(CDonneeBinaireInRow) && colonne.DataType != typeof(byte[]))
{
bModifiedType = true;
}
}
if (colonne.AllowDBNull != info.NullAuthorized)
{
bModifiedNotNull = true;
}
bExists = true;
break;
}
bExists = false;
}
if (!bExists)
{
string strRequeteUpdate = "ALTER TABLE " + structure.NomTableInDb + " ADD " + info.NomChamp + " ";
string strType = (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(info.TypeDonnee);
if (info.TypeDonnee == typeof(string))
{
if (!info.IsLongString)
{
strType += "(" + info.Longueur.ToString() + ") ";
}
else
{
strType = "NText";
}
}
strRequeteUpdate += strType;
if (!info.NullAuthorized && !strChampsAutorisesANull.Contains(info.NomChamp))
{
strRequeteUpdate += " NOT NULL DEFAULT ''";
}
result = connexion.RunStatement(strRequeteUpdate);
if (!result)
{
result.EmpileErreur(I.T("Error while fields updating of table @1|131", structure.NomTableInDb));
return(result);
}
champsCrees.Add(info.NomChamp);
}
else if (bModifiedType || bModifiedNotNull || bModifiedLength)
{
if (info.IsIndex)
{
SupprimeIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
string strRequeteUpdate = "ALTER TABLE " + structure.NomTableInDb + " ALTER COLUMN " + info.NomChamp + " ";
string strType = (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(info.TypeDonnee);
if (info.TypeDonnee == typeof(string))
{
if (!info.IsLongString)
{
strType += "(" + info.Longueur.ToString() + ") ";
}
else
{
strType = "NText";
}
}
strRequeteUpdate += strType;
if (!info.NullAuthorized && !strChampsAutorisesANull.Contains(info.NomChamp))
{
strRequeteUpdate += " NOT NULL ";
}
result = connexion.RunStatement(strRequeteUpdate);
if (!result)
{
result.EmpileErreur(I.T("Error while fields updating of table @1|131", structure.NomTableInDb));
return(result);
}
}
if (info.IsIndex)
{
result = CreateOrUpdateIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
else
{
result = SupprimeIndex(structure.NomTableInDb, connexion, info.NomChamp);
}
}
// Suppression d'une colonne de la table
foreach (DataColumn colonne in dt.Columns)
{
bool bExists = false;
foreach (CInfoChampTable info in structure.Champs)
{
if (info.NomChamp == colonne.ColumnName)
{
bExists = true;
break;
}
}
if (!bExists)
{
CResultAErreur tempResult = CAccessTableCreatorOld.SupprimeDependances(structure.NomTableInDb, colonne.ColumnName, connexion);
if (!tempResult)
{
result.Erreur.EmpileErreurs(tempResult.Erreur);
result.SetFalse();
return(result);
}
tempResult = CAccessTableCreatorOld.SupprimeForeignKeys(structure.NomTableInDb, colonne.ColumnName, connexion);
if (!tempResult)
{
result.Erreur.EmpileErreurs(tempResult.Erreur);
result.SetFalse();
return(result);
}
string strReq = "ALTER TABLE " + structure.NomTableInDb + " DROP COLUMN " + colonne.ColumnName;
result = connexion.RunStatement(strReq);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting column @1 of table @2|132", colonne.ColumnName, structure.NomTableInDb));
return(result);
}
}
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region M.A.J des contraintes de la table
foreach (CInfoRelation rel in structure.RelationsParentes)
{
foreach (string strChampCree in champsCrees)
{
foreach (string strChamp in rel.ChampsFille)
{
if (strChampCree == strChamp)
{
string strRequeteRelation = GetRequeteRelation(rel);
result = connexion.RunStatement(strRequeteRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while creating foreign key of @1 (@2)|127", structure.NomTableInDb, strRequeteRelation));
return(result);
}
break;
}
}
}
if (result)
{
string strNomTableFilleInDb = CContexteDonnee.GetNomTableInDbForNomTable(rel.TableFille);
if (rel.Indexed)
{
result = CreateOrUpdateIndex(strNomTableFilleInDb, connexion, rel.ChampsFille);
}
else
{
result = SupprimeIndex(strNomTableFilleInDb, connexion, rel.ChampsFille);
}
}
if (!result)
{
return(result);
}
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
return(result);
}
//------------------------------------------------------------------------------------------------
public static CResultAErreur CreateTable(CStructureTable structure, IDatabaseConnexion connexion)
{
CResultAErreur result = CResultAErreur.True;
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region Creation de la table
string strRequeteCreation = "";
strRequeteCreation += "CREATE TABLE " + structure.NomTableInDb + " (";
if (structure.HasChampIdAuto)
{
strRequeteCreation += structure.ChampsId[0].NomChamp + " ";
strRequeteCreation += (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(structure.ChampsId[0].TypeDonnee) + " ";
strRequeteCreation += "NOT NULL IDENTITY (1, 1)";
strRequeteCreation += ",";
}
ArrayList lstIndex = new ArrayList();
foreach (CInfoChampTable info in structure.Champs)
{
if (!info.IsId || !structure.HasChampIdAuto)
{
strRequeteCreation += info.NomChamp + " ";
string strType = (new CSQLServeurTypeMappeur()).GetStringDBTypeFromType(info.TypeDonnee);
if (info.TypeDonnee == typeof(string))
{
if (info.IsLongString)
{
strType = "NText";
}
else
{
strType += "(" +
((info.Longueur > 0) ? info.Longueur.ToString() : "1024") +
")";
}
}
strRequeteCreation += strType;
strRequeteCreation += " ";
if (!info.NullAuthorized)
{
strRequeteCreation += "NOT NULL";
}
strRequeteCreation += ",";
if (info.IsIndex)
{
lstIndex.Add(info.NomChamp);
}
}
}
strRequeteCreation = strRequeteCreation.Substring(0, strRequeteCreation.Length - 1);
strRequeteCreation += ")";
result = connexion.RunStatement(strRequeteCreation);
if (!result)
{
result.EmpileErreur(I.T("Error while creating table @1|125", structure.NomTableInDb));
return(result);
}
#endregion
#region création des indexs
foreach (string strChamp in lstIndex)
{
string strRequeteIndex = GetRequeteIndex(structure.NomTableInDb, strChamp);
result = connexion.RunStatement(strRequeteIndex);
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region Creation de la contrainte de clé primaire
string strRequetePrimaryKey = "";
strRequetePrimaryKey += "ALTER TABLE " + structure.NomTableInDb + " ";
strRequetePrimaryKey += "ADD CONSTRAINT PK_" + structure.NomTableInDb + " ";
strRequetePrimaryKey += "PRIMARY KEY (";
foreach (CInfoChampTable info in structure.ChampsId)
{
strRequetePrimaryKey += info.NomChamp + ",";
}
strRequetePrimaryKey = strRequetePrimaryKey.Substring(0, strRequetePrimaryKey.Length - 1);
strRequetePrimaryKey += ")";
result = connexion.RunStatement(strRequetePrimaryKey);
if (!result)
{
result.EmpileErreur(I.T("Error while creating primary key of table @1|126", structure.NomTableInDb));
return(result);
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
#region Creation des contrainte de clés étrangères
foreach (CInfoRelation rel in structure.RelationsParentes)
{
string strRequeteRelation = GetRequeteRelation(rel);
result = connexion.RunStatement(strRequeteRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while creating foreign key of @1 (@2)|127", structure.NomTableInDb, strRequeteRelation));
return(result);
}
if (result && rel.Indexed)
{
string strNomTableFilleInDb = CContexteDonnee.GetNomTableInDbForNomTable(rel.TableFille);
string strRequeteIndex = GetRequeteIndex(strNomTableFilleInDb, rel.ChampsFille);
result = connexion.RunStatement(strRequeteIndex);
if (!result)
{
result.EmpileErreur(I.T("Error while creating index in table @1|128", structure.NomTableInDb));
return(result);
}
}
}
#endregion
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
return(result);
}
public static void MonitorAlarmes()
{
int nCptWatchDogAlSyst = 0;
Thread.Sleep(50000);
CSessionClient session = CSessionClient.CreateInstance();
session.OpenSession(new CAuthentificationSessionServer());
while (true)
{
//Thread.Sleep(200); // inhibition pour faciliter debug des autres objets
//continue;
nCptWatchDogAlSyst++;
using (CContexteDonnee ctx = new CContexteDonnee(session.IdSession, true, true))
{
CListeObjetsDonnees lst = new CListeObjetsDonnees(ctx, typeof(CSpvMessalrm));
if (lst.Count > 0)
{
List <CEvenementAlarm> lstAlarm = new List <CEvenementAlarm>();
StringBuilder bl = new StringBuilder();
foreach (CSpvMessalrm message in lst)
{
try
{
CEvenementAlarm eventAlarm = message.GetNewEvenementAlarm();
if (eventAlarm != null)
{
lstAlarm.Add(eventAlarm);
}
}
catch (Exception e)
{
C2iEventLog.WriteErreur(e.Message);
}
bl.Append(message.Id);
bl.Append(',');
}
bl.Remove(bl.Length - 1, 1);
if (lstAlarm.Count > 0)
{
CDonneeNotificationAlarmes notif = new CDonneeNotificationAlarmes(session.IdSession, lstAlarm);
CEnvoyeurNotification.EnvoieNotifications(new IDonneeNotification[] { notif });
}
SendMailsDelegate sndMail = new SendMailsDelegate(SendMails);
sndMail.BeginInvoke(lstAlarm, ctx, null, null);
IDatabaseConnexion connexion = CSc2iDataServer.GetInstance().GetDatabaseConnexion(session.IdSession, CSpvServeur.c_spvConnection);
connexion.RunStatement("Delete from " + CSpvMessalrm.c_nomTableInDb + " where " +
CSpvMessalrm.c_champMESSALRM_ID + " in (" + bl.ToString() + ")");
/* TimeSpan sp = DateTime.Now - dt;
* double fTime = sp.TotalMilliseconds;*/
}//if (lst.Count > 0)
if (nCptWatchDogAlSyst >= 200) // vérification toutes les 20 secondes
{
nCptWatchDogAlSyst = 0;
IDatabaseConnexion cnx = CSc2iDataServer.GetInstance().GetDatabaseConnexion(session.IdSession, CSpvServeur.c_spvConnection);
cnx.RunStatement("begin WatchDog; end;");
}
}
if (C2iStopApp.AppStopper.WaitOne(100, false))
{
break;
}
}
}
public static CResultAErreur Init(
string strEventJournalName,
string strEventJournalTexte,
IIndicateurProgression indicateurProgress)
{
CResultAErreur result = CResultAErreur.True;
try
{
int nValeurIndicateur = 0;
C2iEventLog.Init(strEventJournalName, strEventJournalTexte, NiveauBavardage.VraiPiplette);
CConteneurIndicateurProgression indicateur = CConteneurIndicateurProgression.GetConteneur(indicateurProgress);
if (indicateur != null)
{
indicateur.PushSegment(0, 13);
}
CTraducteur.ReadFichier("");
#region Configuration du remoting
indicateur.SetValue(nValeurIndicateur++);
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
C2iSponsor.EnableSecurite();
#endregion
#region Configuration de l'allocateur d'objets
Dictionary <string, MarshalByRefObject> dicLocalSingleton = new Dictionary <string, MarshalByRefObject>();
dicLocalSingleton["sc2i.multitiers.client.IGestionnaireSessions"] = new CGestionnaireSessionSagexProSolo();
C2iFactory.InitEnLocal(new C2iObjetServeurFactory(), dicLocalSingleton);
indicateur.SetValue(nValeurIndicateur++);
CSessionClient session = CSessionClient.CreateInstance();
result = session.OpenSession(new CAuthentificationSessionServer(), "SagexProSolo", ETypeApplicationCliente.Service);
if (!result)
{
result.EmpileErreur(I.T("Opening session error|30010"));
return(result);
}
CSc2iDataServer.AddDefinitionConnexion(
new CDefinitionConnexionDataSource(
c_droitsDataSource,
typeof(CGestionnaireDroitsUtilisateurs),
""));
CSc2iDataServer.SetIdConnexionForClasse(typeof(CDroitUtilisateurServeur), c_droitsDataSource);
#endregion
#region Configuration de la base de données
indicateur.SetValue(nValeurIndicateur++);
Type typeConnexion = typeof(CAccess97DatabaseConnexion);
//Récuperation du type de connection
CSc2iDataServer.Init(
new CDefinitionConnexionDataSource(
c_mainDataSource,
typeConnexion,
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"C:\\Documents and Settings\\GENERIC\\Mes documents\\BASEVIDE2000.MDB\"",
""));
#endregion
#region Ajout des références DLL
indicateur.SetValue(nValeurIndicateur++);
AppDomain.CurrentDomain.Load("sc2i.data.client");
AppDomain.CurrentDomain.Load("sc2i.data.serveur");
AppDomain.CurrentDomain.Load("sc2i.data.dynamic");
AppDomain.CurrentDomain.Load("sc2i.data.dynamic.loader");
AppDomain.CurrentDomain.Load("sc2i.process");
AppDomain.CurrentDomain.Load("sc2i.process.serveur");
AppDomain.CurrentDomain.Load("sc2i.expression");
AppDomain.CurrentDomain.Load("sc2i.Formulaire");
AppDomain.CurrentDomain.Load("futurocom.vectordb.data");
AppDomain.CurrentDomain.Load("futurocom.vectordb.data.server");
AppDomain.CurrentDomain.Load("futurocomapp.data");
AppDomain.CurrentDomain.Load("futurocomapp.data.server");
AppDomain.CurrentDomain.Load("sagexpro.data");
AppDomain.CurrentDomain.Load("sagexpro.data.serveur");
foreach (Assembly ass in AppDomain.CurrentDomain.GetAssemblies())
{
CContexteDonnee.AddAssembly(ass);
}
#endregion
#region Teste la connexion
indicateur.SetValue(nValeurIndicateur++);
IDatabaseConnexion cnx = CSc2iDataServer.GetInstance().GetDatabaseConnexion(session.IdSession, c_mainDataSource);
//Attend la connexion pendant au max 5 minutes pour que ça démarre
DateTime dtStartAttente = DateTime.Now;
TimeSpan delaiAttente = DateTime.Now - dtStartAttente;
result = cnx.IsConnexionValide();
while (!result && delaiAttente.TotalSeconds < 5 * 60)
{
C2iEventLog.WriteErreur(result.MessageErreur);
delaiAttente = DateTime.Now - dtStartAttente;
C2iEventLog.WriteErreur(I.T("Connection not availiable(@1)|30014", delaiAttente.TotalSeconds.ToString() + " s)") +
Environment.NewLine);
string messageErreur = I.T("The connection with the database could not have been established. Verify the connection string and check if the database has been started|30015");
C2iEventLog.WriteErreur(messageErreur);
result.EmpileErreur(messageErreur);
return(result);
}
if (typeof(CSqlDatabaseConnexion).IsAssignableFrom(typeConnexion))
{
cnx.RunStatement("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}
#endregion
//Initialisation des classes autoexecutables
CAutoexecuteurClasses.RunAutoexecs();
//Initialisation du serveur de documents GED
//Initialise les restrictions standards
#region Vérifie que les champs des tables font bien moins de 25 cars
indicateur.SetValue(nValeurIndicateur++);
DateTime dt = DateTime.Now;
foreach (Type tp in CContexteDonnee.GetAllTypes())
{
CStructureTable structure = CStructureTable.GetStructure(tp);
if (structure.NomTable.Length > 25)
{
result.EmpileErreur("Table " + structure.NomTable + " (" + tp.ToString() + ")=" + structure.NomTable.Length + "cars");
}
if (structure.NomTable.ToUpper() != structure.NomTable)
{
result.EmpileErreur(I.T("Table name @1 must be uppercase|30018", structure.NomTable));
}
foreach (CInfoChampTable champ in structure.Champs)
{
if (champ.NomChamp.Length > 25)
{
result.EmpileErreur("Table " + structure.NomTable + " (" + tp.ToString() + ")\t champ " + champ.NomChamp + "=" + champ.NomChamp.Length + "cars");
}
if (champ.NomChamp.ToUpper() != champ.NomChamp)
{
result.EmpileErreur(I.T("The name of the field '@1' of the field '@2' must be uppercase|30019", champ.NomChamp, structure.NomTable));
}
}
}
TimeSpan sp = DateTime.Now - dt;
Console.WriteLine(I.T("Table name verification |30020") + sp.TotalMilliseconds);
if (!result)
{
return(result);
}
#endregion
#region Mise à jour de la structure de la base
indicateur.SetValue(nValeurIndicateur++);
CUpdaterDataBase updaterDataBase = CUpdaterDataBase.GetInstance(cnx, new CSagexproStructureBase());
//S'assure que la gestion des éléments est initialisé dans les licences
//CLicenceCheckElementNb.GetInstance();
result = updaterDataBase.UpdateStructureBase(indicateur);
if (!result)
{
return(result);
}
#endregion
//Restrictions sur applications
//Initialisation du serveur de documents GED
//Initialisation de la base d'utilisateurs AD
//CAdBase.Init(CfuturocomappServeurRegistre.RacineAd,"","");
//Initialise les fournisseurs de services
//CSessionClientSurServeur.RegisterFournisseur( new CFournisseurFiltresForSynchronisation() );
CGestionnaireEvenements.Init();
if (!result)
{
return(result);
}
//CGestionnaireObjetsAttachesASession.OnAttacheObjet += new LinkObjectEventHandler(CGestionnaireObjetsAttachesASession_OnAttacheObjet);
}
catch (Exception e)
{
result.EmpileErreur(new CErreurException(e));
}
return(result);
}
public CResultAErreur Execute(int nIdSession, System.Collections.Hashtable valeursParametres)
{
CResultAErreur result = CResultAErreur.True;
using (CContexteDonnee contexte = new CContexteDonnee(nIdSession, true, false))
{
CSessionClient session = CSessionClient.GetSessionForIdSession(nIdSession);
session.BeginTrans();
try
{
CHandlerEvenementServeur.SuspendGestionnaire(true);
IDatabaseConnexion connexion = CSc2iDataServer.GetInstance().GetDatabaseConnexion(nIdSession, typeof(CVersionDonneesObjetServeur));
//Supprime les versions
string strRequete = "delete from " + CVersionDonneesObjetOperation.c_nomTable;
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
strRequete = "delete from " + CVersionDonneesObjet.c_nomTable;
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
foreach (CInfoRelation info in CContexteDonnee.GetListeRelationsTable(CVersionDonnees.c_nomTable))
{
if (info.TableParente == CVersionDonnees.c_nomTable)
{
strRequete = "update " + info.TableFille + " set " +
info.ChampsFille[0] + "=null where " +
info.ChampsFille[0] + " is not null";
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
}
}
strRequete = "delete from " + CVersionDonnees.c_nomTable;
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
contexte.SetVersionDeTravail(-1, false);
//Charge toutes les tables
List <Type> typesAChamps = new List <Type>();
foreach (Type tp in CContexteDonnee.GetAllTypes())
{
contexte.GetTableSafe(CContexteDonnee.GetNomTableForType(tp));
if (typeof(IObjetDonneeAChamps).IsAssignableFrom(tp))
{
typesAChamps.Add(tp);
}
}
/*Problème sur les champs custom : parfois (c'est rare, mais ça arrive
* les valeurs de champs ne sont pas supprimées alors que l'entité est bien
* supprimée. On ne sait pas pourquoi, mais les lignes suivantes
* règlent le problème*/
foreach (Type tp in typesAChamps)
{
string strNomTable = CContexteDonnee.GetNomTableForType(tp);
IObjetDonneeAChamps elt = (IObjetDonneeAChamps)Activator.CreateInstance(tp, new object[] { contexte });
string strTableValeurs = elt.GetNomTableRelationToChamps();
strRequete = "Update " + strTableValeurs + " set " +
CSc2iDataConst.c_champIsDeleted + "=1 where " +
elt.GetChampId() + " in (select " + elt.GetChampId() + " from " +
strNomTable + " where " + CSc2iDataConst.c_champIsDeleted + "=1)";
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
}
ArrayList lstTables = CContexteDonnee.GetTableOrderDelete(contexte);
DataTable tableChampsCustomEnDernier = null;
foreach (DataTable table in lstTables)
{
/* J'ai un problème avec la table Champs Custom
* La requête suivante ne passe pas même directement dans SQL Server
* DELETE FROM CUSTOM_FIELD WHERE (SC2I_VERSION IS NOT NULL) OR (SC2I_DELETED = 1)
* Si je ne traite pas cette table, la purge se passe bien
* */
if (table.TableName == CChampCustom.c_nomTable)
{
tableChampsCustomEnDernier = table;
continue;
}
if (table.Columns.Contains(CSc2iDataConst.c_champIdVersion))
{
strRequete = "delete from " + table.TableName + " where " +
CSc2iDataConst.c_champIdVersion + " is not null or " +
CSc2iDataConst.c_champIsDeleted + "=1";
result = connexion.RunStatement(strRequete);
if (!result)
{
return(result);
}
}
}
//if (tableChampsCustomEnDernier != null)
//{
// if (tableChampsCustomEnDernier.Columns.Contains(CSc2iDataConst.c_champIdVersion))
// {
// strRequete = "delete from " + tableChampsCustomEnDernier.TableName + " where " +
// CSc2iDataConst.c_champIdVersion + " is not null or " +
// CSc2iDataConst.c_champIsDeleted + "=1";
// result = connexion.RunStatement(strRequete);
// if (!result)
// return result;
// }
//}
}
catch (Exception e)
{
result.EmpileErreur(new CErreurException(e));
}
finally
{
CHandlerEvenementServeur.SuspendGestionnaire(false);
if (!result)
{
session.RollbackTrans();
}
else
{
result = session.CommitTrans();
}
}
}
return(result);
}
