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