//------------------------------------------------------------------------------------------------ public static CResultAErreur SuppressionTable(string strNomTable, IDatabaseConnexion connexion) { CResultAErreur result = CResultAErreur.True; if (CAccessTableCreatorOld.TableExists(strNomTable, connexion)) { result = CAccessTableCreatorOld.DeleteTable(strNomTable, connexion); } return(result); }
//------------------------------------------------------------------------------------------------ public static CResultAErreur CreationOuUpdateTableFromType(Type tp, IDatabaseConnexion connexion, ArrayList strChampsAutorisesANull) { CResultAErreur result = CResultAErreur.True; CStructureTable structure = CStructureTable.GetStructure(tp); if (!result) { return(result); } //S'assure que toutes les tables parentes existent foreach (CInfoRelation relation in structure.RelationsParentes) { if (relation.TableParente != structure.NomTable) { string strNomTableParenteInDb = CContexteDonnee.GetNomTableInDbForNomTable(relation.TableParente); if (!CAccessTableCreatorOld.TableExists(strNomTableParenteInDb, connexion)) { result = CreationOuUpdateTableFromType(CContexteDonnee.GetTypeForTable(relation.TableParente), connexion); if (!result) { return(result); } } } } if (CAccessTableCreatorOld.TableExists(structure.NomTableInDb, connexion)) { result = CAccessTableCreatorOld.UpdateTable(structure, connexion, strChampsAutorisesANull); } else { result = CAccessTableCreatorOld.CreateTable(structure, connexion); } return(result); }
//------------------------------------------------------------------------------------------------ public static CResultAErreur CreationOuUpdateTableFromType(Type tp, IDatabaseConnexion connexion) { return(CAccessTableCreatorOld.CreationOuUpdateTableFromType(tp, connexion, new ArrayList())); }
//------------------------------------------------------------------------------------------------ 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 UpdateTable(CStructureTable structure, IDatabaseConnexion connexion) { return(CAccessTableCreatorOld.UpdateTable(structure, connexion, new ArrayList())); }