//-------------------------------------------------------------------------------------- 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); }
/// ///////////////////////////////////////////////////////////////////////////// /// Modifié le 17 juillet 2003 par Stéphane : ajout du mot clé virtual public virtual DataTable[] FillSchema(DataSet dsDest, SchemaType schema) { string strReq = "select * from " + m_structure.NomTableInDb; IDataAdapter adapter = m_connexion.GetSimpleReadAdapter(strReq); foreach (DataTableMapping tm in TableMappings) { adapter.TableMappings.Add(tm); } adapter.MissingMappingAction = MissingMappingAction; adapter.MissingSchemaAction = MissingSchemaAction; DataTable[] tables = adapter.FillSchema(dsDest, schema); CUtilDataAdapter.DisposeAdapter(adapter); return(tables); }
//-------------------------------------------------------------------------------------- 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 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); }