//-------------------------------------------------------------------------------------- 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); }