internal bool IsChampAutoIncremente(string strNomTable, string strNomChamp)
{
IDataAdapter adapter;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strColAuto = "SELECT " +
COracleDatabaseConnexion.c_nomChampSysChampAutoTriggerName + ","
+ COracleDatabaseConnexion.c_nomChampSysChampAutoSeqName + " FROM " + COracleDatabaseConnexion.c_nomTableSysChampAuto
+ " WHERE "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + " = '"
+ strNomTable + "' AND "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + " = '"
+ strNomChamp + "'";
adapter = Connection.GetSimpleReadAdapter(strColAuto);
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
dt = ds.Tables[0];
if (dt.Rows.Count != 0)
{
return(true);
}
else
{
return(false);
}
}
/////////////////////////////////////////////////////////////////////////////////////
public override int CountRecords(string strNomTableIndb, CFiltreData filtre)
{
filtre.SortOrder = "";
string strNomTableInContexte = GetNomTableInContexteFromNomTableInDb(strNomTableIndb);
Type tp = CContexteDonnee.GetTypeForTable(strNomTableInContexte);
string strRequete = "";
if (tp != null)
{
CStructureTable structure = CStructureTable.GetStructure(tp);
if (structure.ChampsId.Length == 1)
{
strRequete = "select count ( distinct " + strNomTableIndb + "." + structure.ChampsId[0].NomChamp + ") COMBIEN from " + strNomTableIndb;
}
}
if (strRequete == "")
{
strRequete = "select count (*) COMBIEN from " + strNomTableIndb;
}
IDataAdapter adapter = GetSimpleReadAdapter(strRequete, filtre);
DataSet ds = new DataSet();
FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
return(Convert.ToInt32(ds.Tables["Table"].Rows[0]["COMBIEN"]));
}
public override CResultAErreur DeleteChamp_ClesEtrangeres(string strNomTableInDb, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
//Suppression des clefs etrangeres
string strRequeteGetForeignKey =
"SELECT DISTINCT fk.[name] " +
"FROM sys.foreign_key_columns as fks " +
"INNER JOIN sysobjects as tb on tb.id = fks.parent_object_id " +
"INNER JOIN sysobjects as fk on fk.id = fks.constraint_object_id " +
"LEFT JOIN syscolumns as col on (col.id = tb.id AND col.colid = fks.parent_column_id) " +
"WHERE tb.[name] = '" + strNomTableInDb + "' " +
"AND col.[name] = '" + strNomChamp + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTableInDb + " DROP CONSTRAINT " + row[0].ToString();
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constraint @1 of table @2|133", row[0].ToString(), strNomTableInDb));
return(result);
}
}
return(result);
}
internal bool SystemeAutoIncrementeExist(string strNomTable, string strNomChamp)
{
if (!TableExists(COracleDatabaseConnexion.c_nomTableSysChampAuto))
{
return(false);
}
IDataAdapter adapter;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strColAuto = "SELECT TG_NAME, SQ_NAME FROM " + COracleDatabaseConnexion.c_nomTableSysChampAuto +
" WHERE " +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + " = '" +
strNomTable + "' AND " +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + " = '" +
strNomChamp + "' from " +
COracleDatabaseConnexion.c_nomTableSysChampAuto;
adapter = Connection.GetSimpleReadAdapter(strColAuto);
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
dt = ds.Tables[0];
if (dt.Rows.Count != 0)
{
return(true);
}
else
{
return(false);
}
}
public CResultAErreur DeleteTable_ClesPrimaires(string strNomTableInDb)
{
CResultAErreur result = CResultAErreur.True;
string strRequeteGetForeignKey =
"SELECT pk.name FROM sys.all_objects as pk " +
"INNER JOIN sys.all_objects as tb " +
"ON tb.object_id = pk.parent_object_id " +
"WHERE pk.type = 'PK' " +
"AND tb.name = '" + strNomTableInDb + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTableInDb + " DROP CONSTRAINT " + row[0].ToString();
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting primary key @1 of table @2|30009", row[0].ToString(), strNomTableInDb));
return(result);
}
}
return(result);
}
public CResultAErreur DeleteTable_ClesEtrangeres(string strNomTableInDb)
{
CResultAErreur result = CResultAErreur.True;
string strRequeteGetForeignKey =
"SELECT tb1.name, relNom.name FROM sys.foreign_key_columns as rel " +
"INNER JOIN sys.all_objects as tb1 " +
"on tb1.object_id = rel.parent_object_id " +
"INNER JOIN sys.all_objects as tb2 " +
"on tb2.object_id = rel.referenced_object_id " +
"INNER JOIN sys.all_objects as relNom " +
"on relNom.object_id = rel.constraint_object_id " +
"WHERE tb2.name = '" + strNomTableInDb + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + row[0].ToString() + " DROP CONSTRAINT " + row[1].ToString();
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting foreign key @1 attached at table @2|30010", row[1].ToString(), strNomTableInDb));
return(result);
}
}
return(result);
}
public override CResultAErreur DeleteChamp_ClesEtrangeres(string strNomTable, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
//Récupère le nom des contraintes clefs Etrangeres de la colonne de la table
string strRequeteGetForeignKey = " SELECT DISTINCT Con.CONSTRAINT_NAME " +
"FROM SYS.USER_CONS_COLUMNS Col, SYS.USER_CONSTRAINTS Con " +
"WHERE Col.CONSTRAINT_NAME = Con.CONSTRAINT_NAME " +
"AND (Col.TABLE_NAME ='" + strNomTable + "') " +
"AND (Col.COLUMN_NAME='" + strNomChamp + "') " +
"AND (Con.CONSTRAINT_TYPE='R') ";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTable + " DROP CONSTRAINT " + row[0].ToString();
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constraint @1 of table @2|133", (string)row[0], strNomTable));
return(result);
}
}
return(result);
}
//Table
protected override DataTable GetDataTableForUpdateTable(CStructureTable structure)
{
IDataAdapter adapter = Connection.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables["TABLE"];
return(dt);
}
protected override string GetNomIndex(string strNomTable, params string[] strFields)
{
/*
* string strRequete = "SELECT IDX.INDEX_NAME ";
* strRequete += "FROM SYS.USER_IND_COLUMNS Col, SYS.USER_INDEXES Idx ";
* strRequete += "WHERE Col.INDEX_NAME = Idx.INDEX_NAME AND (Col.TABLE_NAME ='" + strNomTable + "') ";
* foreach (string strField in strFields)
* strRequete += "AND(Col.COLUMN_NAME='" + strField + "') ";*/
string strRequete = "SELECT IDX.INDEX_NAME FROM ";
string strSep = "";
string [] strCols = new string [strFields.Length];
for (int i = 0; i < strFields.Length; i++)
{
strCols[i] = "Col" + i.ToString();
strRequete += strSep + "SYS.USER_IND_COLUMNS " + strCols[i];
strSep = ", ";
}
strRequete += ", SYS.USER_INDEXES Idx WHERE ";
strSep = "";
for (int j = 0; j < strFields.Length; j++)
{
strRequete += strSep + strCols[j] + ".COLUMN_NAME='" + strFields[j] + "' AND " + strCols[j] + ".INDEX_NAME = Idx.INDEX_NAME AND (" + strCols[j] + ".TABLE_NAME ='" + strNomTable + "') ";
strSep = " AND ";
}
DataSet dsTmp = new DataSet();
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequete);
Connection.FillAdapter(adapter, dsTmp);
CUtilDataAdapter.DisposeAdapter(adapter);
if (dsTmp.Tables.Count > 0 && dsTmp.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dsTmp.Tables[0].Rows)
{
//Vérifie que l'index a le bon nombre de colonnes. En effet, un index peut porter
//une colonne attendue, mais aussi sur d'autres
CResultAErreur result = m_connection.ExecuteScalar(@"select count(*) from
SYS.USER_IND_COLUMNS where INDEX_NAME='" + row[0].ToString() + "'");
if (result)
{
int nVal = Convert.ToInt32(result.Data);
if (nVal == strFields.Count())
{
return(row[0].ToString());
}
}
}
}
return("");
}
//--------------------------------------------------------------------------------------
public override bool IndexExists(string strNomTable, params string[] strChamps)
{
//Vérifie l'existance de l'index
string strNomIndex = GetNomIndex(strNomTable, strChamps);
string strRequete = "select name from dbo.sysindexes where name='" + strNomIndex + "'";
DataSet dsTmp = new DataSet();
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequete);
Connection.FillAdapter(adapter, dsTmp);
CUtilDataAdapter.DisposeAdapter(adapter);
return(dsTmp.Tables[0].Rows.Count != 0);
}
internal void ChargementSYSCHAMPAUTO()
{
string strTableChampsAuto = "";
foreach (string strNomTable in TablesNames)
{
if (strNomTable == c_nomTableSysChampAuto)
{
strTableChampsAuto = strNomTable;
break;
}
}
if (strTableChampsAuto != "")
{
m_dtSYS_CHAMP_AUTO = new DataTable();
string strRequete = "SELECT * FROM " + c_nomTableSysChampAuto;
C2iOracleDataAdapter adapter = (C2iOracleDataAdapter)GetSimpleReadAdapter(strRequete);
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.TableName = "strTableChampsAuto";
ds.Tables.Add(dt);
this.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
m_dtSYS_CHAMP_AUTO = ds.Tables[0];
}
if (m_dtSYS_CHAMP_AUTO == null)
{
//Création de la tbale
m_dtSYS_CHAMP_AUTO = new DataTable(c_nomTableSysChampAuto);
m_dtSYS_CHAMP_AUTO.Columns.Add(c_nomChampSysChampAutoNomTable, typeof(string));
m_dtSYS_CHAMP_AUTO.Columns.Add(c_nomChampSysChampAutoNomChamp, typeof(string));
m_dtSYS_CHAMP_AUTO.Columns.Add(c_nomChampSysChampAutoSeqName, typeof(string));
m_dtSYS_CHAMP_AUTO.Columns.Add(c_nomChampSysChampAutoTriggerName, typeof(string));
}
foreach (CInfoChampAuto info in m_listeChampsAutoHorsGestionSys)
{
DataRow row = m_dtSYS_CHAMP_AUTO.NewRow();
row[c_nomChampSysChampAutoNomChamp] = info.NomChamp;
row[c_nomChampSysChampAutoNomTable] = info.NomTable;
row[c_nomChampSysChampAutoTriggerName] = info.NomTrigger;
row[c_nomChampSysChampAutoSeqName] = info.NomSequence;
m_dtSYS_CHAMP_AUTO.Rows.Add(row);
}
}
//Champ
public override CResultAErreur DeleteChamp_Dependances(string strNomTableInDb, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
//Suppression des clefs etrangeres
result = DeleteChamp_ClesEtrangeres(strNomTableInDb, strNomChamp);
//Regarde les autres contraintes
string strRequeteGetForeignKey =
"SELECT obj2.name " +
"FROM dbo.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='" + strNomTableInDb + "' " +
"AND sc.name='" + strNomChamp + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTableInDb + " DROP CONSTRAINT " + (string)row[0];
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constrain @1 of table @2|133", (string)row[0], strNomTableInDb));
return(result);
}
}
//Suppression des indexs
result = DeleteIndex(strNomTableInDb, strNomChamp);
return(result);
}
//--------------------------------------------------------------------------------------
public override bool TriggerExists(string NomTrigger)
{
string strTriExist = "SELECT * FROM SYS.USER_TRIGGERS ";
strTriExist += "WHERE SEQUENCE_NAME ='" + NomTrigger + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strTriExist);
DataSet dsTmp = new DataSet();
Connection.FillAdapter(adapter, dsTmp);
CUtilDataAdapter.DisposeAdapter(adapter);
if (dsTmp.Tables[0].Rows.Count > 0)
{
return(true);
}
else
{
return(false);
}
}
/////////////////////////////////////////////////////////////////////////////////////
public override int CountRecords(string strNomTableInDb, CFiltreData filtre)
{
filtre.SortOrder = "";
string strNomTableInContexte = GetNomTableInContexteFromNomTableInDb(strNomTableInDb);
Type tp = CContexteDonnee.GetTypeForTable(strNomTableInContexte);
string strRequete = "";
if (tp != null)
{
CStructureTable structure = CStructureTable.GetStructure(tp);
if (structure.ChampsId.Length == 1)
{
strRequete = "select count ( distinct " + strNomTableInDb + "." + structure.ChampsId[0].NomChamp + ") as COMBIEN from " + strNomTableInDb;
}
}
if (strRequete == "")
{
strRequete = "select count (*) as COMBIEN from " + strNomTableInDb;
}
IDataAdapter adapter = GetSimpleReadAdapter(strRequete, filtre);
DataSet ds = new DataSet();
try
{
/*SqlCommand cmd = ((SqlDataAdapter)adapter).SelectCommand;
* cmd.Connection = (SqlConnection)GetConnexion(true);
* cmd.Transaction = (SqlTransaction)Transaction;
* object val = ((SqlDataAdapter)adapter).SelectCommand.ExecuteScalar();*/
this.FillAdapter(adapter, ds);
}
catch (Exception e)
{
string strTmp = e.ToString();
return(0);
}
finally
{
CUtilDataAdapter.DisposeAdapter(adapter);
}
return((int)ds.Tables["Table"].Rows[0]["COMBIEN"]);
}
//------------------------------------------------------------------------------------------------
public List <string> GetNomColonnes(string strNomTable)
{
List <string> cols = new List <string>();
IDataAdapter adapter = m_connection.GetTableAdapter(strNomTable);
adapter.TableMappings.Add("Table", strNomTable);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable tbl = ds.Tables[strNomTable];
foreach (DataColumn col in tbl.Columns)
{
cols.Add(col.ColumnName);
}
return(cols);
}
//------------------------------------------------------------------------------------------------
public List <string> GetNomColonnes(string strNomTable)
{
List <string> cols = new List <string>();
string strCols = "SELECT CNAME FROM SYS.COL WHERE (TNAME = '" + strNomTable + "')";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strCols);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
cols.Add(dr[0].ToString());
}
return(cols);
}
//Table
protected override DataTable GetDataTableForUpdateTable(CStructureTable structure)
{
DataTable schema = GetOleDbSchemaColonnes();
IDataAdapter adapter = Connection.GetSimpleReadAdapter("SELECT * FROM " + structure.NomTableInDb);
DataSet ds = new DataSet();
adapter.FillSchema(ds, SchemaType.Mapped);
DataTable dt = ds.Tables["TABLE"];
DataView view = new DataView(schema);
foreach (DataColumn col in dt.Columns)
{
view.RowFilter = "TABLE_NAME='" + structure.NomTableInDb + "' and " +
"COLUMN_NAME='" + col.ColumnName + "'";
if (view.Count > 0)
{
col.AllowDBNull = (bool)view[0]["IS_NULLABLE"];
}
}
CUtilDataAdapter.DisposeAdapter(adapter);
return(dt);
}
/// /////////////////////////////////////////////////////
public override void DesactiverContraintes(bool bDesactiver)
{
foreach (string strTable in TablesNames)
{
string strRequeteGetConstraints = " SELECT Contrainte.CONSTRAINT_NAME ";
strRequeteGetConstraints += "FROM SYS.USER_CONS_COLUMNS Colonne, SYS.USER_CONSTRAINTS Contrainte ";
strRequeteGetConstraints += "WHERE Colonne.CONSTRAINT_NAME = Contrainte.CONSTRAINT_NAME ";
strRequeteGetConstraints += "AND (Colonne.TABLE_NAME ='" + strTable + "') ";
IDataAdapter adapter = GetSimpleReadAdapter(strRequeteGetConstraints);
DataSet ds = new DataSet();
this.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable table = ds.Tables[0];
foreach (DataRow row in table.Rows)
{
string strRequete = "ALTER TABLE " + strTable + " " +
(bDesactiver ? "DISABLE " : "Enable") + " CONSTRAINT " +
row[0];
RunStatement(strRequete);
}
}
}
//------------------------------------------------------------------------------------------------
public List <string> GetNomColonnes(string strNomTable)
{
List <string> cols = new List <string>();
string strCols = "SELECT col.name FROM sys.all_columns as col " +
"INNER JOIN sys.all_objects as tb " +
"ON tb.object_id = col.object_id " +
"WHERE tb.type = 'U' and tb.name = '" + strNomTable + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strCols);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
cols.Add(dr[0].ToString());
}
return(cols);
}
public override CResultAErreur GetRelationsExistantes(string strNomTable, ref List <CInfoRelation> relationsTable)
{
CResultAErreur result = CResultAErreur.True;
string strNomTableInContexte = m_connection.GetNomTableInContexteFromNomTableInDb(strNomTable);
#region 1 - Selection des clefs etrangeres de la table
string strRequeteGetForeignKey =
"SELECT constraint_name AS NomRelation, " +
"r_constraint_name AS NomRelationParente " +
"FROM USER_constraints " +
"WHERE constraint_type='R' " +
"AND table_name = '" + strNomTable + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
if (ds.Tables.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the foreign keys of table '@1' to update them|213", strNomTable));
return(result);
}
DataTable dt = ds.Tables[0];
#endregion
#region 2 - Recupération des relations des clé etrangeres
foreach (DataRow row in dt.Rows)
{
string[] strChampsParents;
string[] strChampsFils;
string strTableParente;
string strNomRelation = row[0].ToString();
string strNomContrainteParente = row[1].ToString();
#region Recuperation des champs fils
string strRequeteChampsFils =
"SELECT COLUMN_NAME " +
"FROM SYS.USER_CONS_COLUMNS " +
"WHERE CONSTRAINT_NAME = '" + strNomRelation + "'";
IDataAdapter adapterChampsFils = Connection.GetSimpleReadAdapter(strRequeteChampsFils);
DataSet dsChampsFils = new DataSet();
Connection.FillAdapter(adapterChampsFils, dsChampsFils);
CUtilDataAdapter.DisposeAdapter(adapterChampsFils);
if (dsChampsFils.Tables.Count != 1 ||
dsChampsFils.Tables[0].Rows.Count == 0 ||
dsChampsFils.Tables[0].Columns.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the fields of table '@1' concerned with the relation '@2'|214", strNomTable, strNomRelation));
return(result);
}
ArrayList champsFils = new ArrayList();
foreach (DataRow rowFils in dsChampsFils.Tables[0].Rows)
{
champsFils.Add(rowFils[0].ToString());
}
strChampsFils = (string[])champsFils.ToArray(typeof(string));
#endregion
#region Recuperation du nom et des colonnes de la table parente
string strRequeteChampsParents =
"SELECT COLUMN_NAME, TABLE_NAME " +
"FROM SYS.USER_CONS_COLUMNS " +
"WHERE CONSTRAINT_NAME = '" + strNomContrainteParente + "'";
IDataAdapter adapterChampsPeres = Connection.GetSimpleReadAdapter(strRequeteChampsParents);
DataSet dsChampsPeres = new DataSet();
Connection.FillAdapter(adapterChampsPeres, dsChampsPeres);
CUtilDataAdapter.DisposeAdapter(adapterChampsPeres);
if (dsChampsFils.Tables.Count != 1 ||
dsChampsFils.Tables[0].Rows.Count == 0 ||
dsChampsFils.Tables[0].Columns.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the fields of the parent table of the relation '@1' with the table '@2'|215", strNomRelation, strNomTable));
return(result);
}
ArrayList champsPeres = new ArrayList();
foreach (DataRow rowPere in dsChampsPeres.Tables[0].Rows)
{
champsPeres.Add(rowPere[0].ToString());
}
strTableParente = dsChampsPeres.Tables[0].Rows[0][1].ToString();
string strTableParenteInContexte = m_connection.GetNomTableInContexteFromNomTableInDb(strTableParente);
strChampsParents = (string[])champsPeres.ToArray(typeof(string));
#endregion
relationsTable.Add(new CInfoRelationAClefDefinissable(strNomRelation, strTableParenteInContexte, strNomTableInContexte, strChampsParents, strChampsFils));
}
#endregion
return(result);
}
internal CResultAErreur CreerSystemeAutoIncremente(string strNomTableInDb, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
bool bAvecTrigger = true;
try
{
Type tp = CContexteDonnee.GetTypeForTable(((COracleDatabaseConnexion)Connection).GetNomTableInContexteFromNomTableInDb(strNomTableInDb));
IEnumerable <object> attrs = from a in tp.GetCustomAttributes(typeof(NoTriggerAutoIncrementAttribute), true)
where ((NoTriggerAutoIncrementAttribute)a).Champ == strNomChamp
select a;
if (attrs.Count() > 0)
{
bAvecTrigger = false;
}
}
catch
{
}
//Creation Sequence
string strNomSeq = GetNewNom(EntiteTable.Sequence, strNomTableInDb);
if (!SequenceExists(strNomSeq))
{
result = Connection.RunStatement("CREATE SEQUENCE " + strNomSeq + " INCREMENT BY 1 START WITH 1");
}
//Creation Trigger
string strNomTrigger = " ";
if (result && bAvecTrigger)
{
strNomTrigger = GetNewNom(EntiteTable.Trigger, strNomTableInDb);
if (!SequenceExists(strNomTrigger))
{
string strTrigg = "CREATE TRIGGER " + strNomTrigger;
strTrigg += " BEFORE INSERT ON " + strNomTableInDb;
strTrigg += " FOR EACH ROW ";
strTrigg += " BEGIN SELECT " + strNomSeq + ".NEXTVAL INTO ";
strTrigg += ":NEW." + strNomChamp + " FROM dual; END;";
result = Connection.RunStatement(strTrigg);
}
}
if (result)
{
string strRefSysChampAutoExist = "SELECT * FROM " +
COracleDatabaseConnexion.c_nomTableSysChampAuto + " WHERE "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + " = '"
+ strNomTableInDb + "' AND "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + " = '"
+ strNomChamp + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRefSysChampAutoExist);
DataSet dsTmp = new DataSet();
Connection.FillAdapter(adapter, dsTmp);
CUtilDataAdapter.DisposeAdapter(adapter);
string strUpdateRefSysChampAuto;
if (dsTmp.Tables[0].Rows.Count == 0)
{
//Referencement
strUpdateRefSysChampAuto = "INSERT INTO " + COracleDatabaseConnexion.c_nomTableSysChampAuto +
"(" +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + "," +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + "," +
COracleDatabaseConnexion.c_nomChampSysChampAutoSeqName + "," +
COracleDatabaseConnexion.c_nomChampSysChampAutoTriggerName + ") " +
"VALUES('" + strNomTableInDb + "', '"
+ strNomChamp + "', '"
+ strNomSeq + "', '"
+ strNomTrigger + "')";
}
else
{
strUpdateRefSysChampAuto =
"UPDATE " + COracleDatabaseConnexion.c_nomTableSysChampAuto + " set " +
COracleDatabaseConnexion.c_nomChampSysChampAutoSeqName + " = '" + strNomSeq + "', " +
COracleDatabaseConnexion.c_nomChampSysChampAutoTriggerName + " = '" + strNomTrigger + "' where " +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + "='" + strNomTableInDb + "' and " +
COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + "='" + strNomChamp + "'";
result = Connection.RunStatement(strUpdateRefSysChampAuto);
}
result = Connection.RunStatement(strUpdateRefSysChampAuto);
if (!result)
{
result.EmpileErreur(I.T("Impossible to reference the field '@1' automation of @2 table|196", strNomChamp, strNomTableInDb));
}
}
if (!result)
{
result.EmpileErreur(I.T("Error while creating sequence of @1 table|195", strNomTableInDb));
}
return(result);
}
public override CResultAErreur GetRelationsExistantes(string strNomTable, ref List <CInfoRelation> relationsTable)
{
CResultAErreur result = CResultAErreur.True;
string strNomTableInContexte = m_connection.GetNomTableInContexteFromNomTableInDb(strNomTable);
if (strNomTableInContexte == null)
{
strNomTableInContexte = strNomTable;
}
#region 1 - Selection des clefs etrangeres de la table
string strRequeteGetForeignKey =
"SELECT DISTINCT obj2.id as IDCles, obj2.[name] as Nom " +
"FROM dbo.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 + "'";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetForeignKey);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
if (ds.Tables.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the foreign keys of table '@1' to update them|213", strNomTable));
return(result);
}
DataTable dt = ds.Tables[0];
#endregion
#region 2 - Recupération des relations des clé etrangeres
foreach (DataRow row in dt.Rows)
{
string[] strChampsParents;
string[] strChampsFils;
string strTableParente;
string strIdContrainte = row[0].ToString();
string strNomRelation = row[1].ToString();
#region Recuperation des champs fils
string strRequeteChampsFils =
"SELECT DISTINCT syscolumns.[name] FROM sysforeignkeys " +
"INNER JOIN syscolumns ON fkeyid = id " +
"WHERE constid = '" + strIdContrainte + "' AND fkey = colid";
IDataAdapter adapterChampsFils = Connection.GetSimpleReadAdapter(strRequeteChampsFils);
DataSet dsChampsFils = new DataSet();
Connection.FillAdapter(adapterChampsFils, dsChampsFils);
CUtilDataAdapter.DisposeAdapter(adapterChampsFils);
if (dsChampsFils.Tables.Count != 1 ||
dsChampsFils.Tables[0].Rows.Count == 0 ||
dsChampsFils.Tables[0].Columns.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the fields of table '@1' concerned with the relation '@2'|214", strNomTable, strNomRelation));
return(result);
}
ArrayList champsFils = new ArrayList();
foreach (DataRow rowFils in dsChampsFils.Tables[0].Rows)
{
champsFils.Add(rowFils[0].ToString());
}
strChampsFils = (string[])champsFils.ToArray(typeof(string));
#endregion
#region Recuperation du nom de la table parente
string strRequeteNomTableParente =
"SELECT DISTINCT sysobjects.[name] FROM sysforeignkeys " +
"LEFT JOIN sysobjects ON rkeyid = id " +
"WHERE constid = '" + strIdContrainte + "'";
IDataAdapter adapterNomTableParente = Connection.GetSimpleReadAdapter(strRequeteNomTableParente);
DataSet dsNomTableParente = new DataSet();
Connection.FillAdapter(adapterNomTableParente, dsNomTableParente);
CUtilDataAdapter.DisposeAdapter(adapterNomTableParente);
if (dsNomTableParente.Tables.Count != 1 ||
dsNomTableParente.Tables[0].Rows.Count != 1 ||
dsNomTableParente.Tables[0].Columns.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the table name associated with the table '@1' by the relation '@2'|217", strNomTable, strNomRelation));
return(result);
}
strTableParente = dsNomTableParente.Tables[0].Rows[0][0].ToString();
#endregion
#region Recuperation des champs parents
string strRequeteChampsParents =
"SELECT DISTINCT syscolumns.[name] FROM sysforeignkeys " +
"INNER JOIN syscolumns ON rkeyid = id " +
"WHERE constid = '" + strIdContrainte + "' AND rkey = colid";
IDataAdapter adapterChampsPeres = Connection.GetSimpleReadAdapter(strRequeteChampsParents);
DataSet dsChampsPeres = new DataSet();
Connection.FillAdapter(adapterChampsPeres, dsChampsPeres);
CUtilDataAdapter.DisposeAdapter(adapterChampsPeres);
if (dsChampsPeres.Tables.Count != 1 ||
dsChampsPeres.Tables[0].Rows.Count == 0 ||
dsChampsPeres.Tables[0].Columns.Count != 1)
{
result.EmpileErreur(I.T("Impossible to recover the fields of the parent table '@1' of the relation '@2' with the table '@3'|216", strTableParente, strNomRelation, strNomTable));
return(result);
}
ArrayList champsPeres = new ArrayList();
foreach (DataRow rowPere in dsChampsPeres.Tables[0].Rows)
{
champsPeres.Add(rowPere[0].ToString());
}
strChampsParents = (string[])champsPeres.ToArray(typeof(string));
#endregion
string strTableParenteInContexte = m_connection.GetNomTableInContexteFromNomTableInDb(strTableParente);
if (strTableParenteInContexte == null)
{
strTableParenteInContexte = strTableParente;
}
relationsTable.Add(new CInfoRelationAClefDefinissable(strNomRelation, strTableParenteInContexte, strNomTableInContexte, strChampsParents, strChampsFils));
}
#endregion
return(result);
}
internal CResultAErreur SupprimerSystemeAutoIncremente(string strNomTableInDb, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
if (TableExists(COracleDatabaseConnexion.c_nomTableSysChampAuto))
{
return(result);
}
IDataAdapter adapter;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string strColAuto = "SELECT " +
COracleDatabaseConnexion.c_nomChampSysChampAutoTriggerName + "," +
COracleDatabaseConnexion.c_nomChampSysChampAutoSeqName + " from " +
COracleDatabaseConnexion.c_nomTableSysChampAuto +
" WHERE TABLE_NAME = '" + strNomTableInDb + "' AND " +
"CHAMP_NAME = '" + strNomChamp + "'";
adapter = Connection.GetSimpleReadAdapter(strColAuto);
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
dt = ds.Tables[0];
if (dt.Rows.Count != 0)
{
//Sequence
string strNomSQ = dt.Rows[0]["SQ_NAME"].ToString();
result = Connection.RunStatement("DROP SEQUENCE " + strNomSQ);
if (!result)
{
result.EmpileErreur("Error wile deleting sequence @1 of table @2|191", strNomSQ, strNomTableInDb);
return(result);
}
//Trigger
string strNomTG = dt.Rows[0]["TG_NAME"].ToString();
result = Connection.RunStatement("DROP TRIGGER " + strNomTG);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting numbering trigger of field @1 in table @2|193", strNomChamp, strNomTableInDb));
return(result);
}
string strSuppRefSysAuto = "DELETE FROM " +
COracleDatabaseConnexion.c_nomTableSysChampAuto + " WHERE "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomTable + " = '"
+ strNomTableInDb + "' AND "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoNomChamp + " = '"
+ strNomChamp + "' AND "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoSeqName + " = '"
+ strNomSQ + "' AND "
+ COracleDatabaseConnexion.c_nomChampSysChampAutoTriggerName + " = '"
+ strNomTG + "' ";
result = Connection.RunStatement(strSuppRefSysAuto);
if (!result)
{
result.EmpileErreur(I.T("Error while auto incremental field reference deleting for the field '@1' of table @2 in SYS_CHAMP_AUTO|194", strNomChamp, strNomTableInDb));
}
}
return(result);
}
public override CResultAErreur DeleteChamp_Dependances(string strNomTableInDb, string strNomChamp)
{
CResultAErreur result = CResultAErreur.True;
#region Trigger et de la Séquence de numérotation si colonne auto-incrémenté
result = SupprimerSystemeAutoIncremente(strNomTableInDb, strNomChamp);
if (!result)
{
return(result);
}
#endregion
#region Contraintes (y compri clef primaire)
//Récupère le nom des contraintes de la colonne de la table
string strRequeteGetConstraints = "SELECT DISTINCT Contrainte.CONSTRAINT_NAME " +
"FROM SYS.USER_CONS_COLUMNS Colonne, SYS.USER_CONSTRAINTS Contrainte " +
"WHERE Colonne.CONSTRAINT_NAME = Contrainte.CONSTRAINT_NAME " +
"AND (Colonne.TABLE_NAME ='" + strNomTableInDb + "') " +
"AND (Colonne.COLUMN_NAME='" + strNomChamp + "') ";
IDataAdapter adapter = Connection.GetSimpleReadAdapter(strRequeteGetConstraints);
DataSet ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionRelation = "ALTER TABLE " + strNomTableInDb + " DROP CONSTRAINT " + (string)row[0];
result = Connection.RunStatement(strRequeteSuppressionRelation);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting constrain @1 of table @2|133", (string)row[0], strNomTableInDb));
return(result);
}
}
#endregion
#region Index (tout les index lié à la colonne)
string strRequeteIndexes = " SELECT Idx.INDEX_NAME " +
"FROM SYS.USER_IND_COLUMNS Colonne, SYS.USER_INDEXES Idx " +
"WHERE Colonne.INDEX_NAME = Idx.INDEX_NAME " +
"AND (Colonne.TABLE_NAME ='" + strNomTableInDb + "') " +
"AND (Colonne.COLUMN_NAME='" + strNomChamp + "') ";
adapter = Connection.GetSimpleReadAdapter(strRequeteIndexes);
ds = new DataSet();
Connection.FillAdapter(adapter, ds);
CUtilDataAdapter.DisposeAdapter(adapter);
dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string strRequeteSuppressionIndex = " DROP INDEX " + row[0].ToString();
result = Connection.RunStatement(strRequeteSuppressionIndex);
if (!result)
{
result.EmpileErreur(I.T("Error while deleting index @1 of the table @2|130", row[0].ToString(), strNomTableInDb));
return(result);
}
}
#endregion
return(result);
}
protected string GetNewNom(EntiteTable Entite, string NomTable)
{
string NomEntite = "";
#region 1 - On récupère l'ID de notre nouvelle entité
string strRequete = "SELECT ";
string strNomSequence = "SQ";
DataSet dsTmp = new DataSet();
IDataAdapter adapter;
switch (Entite)
{
case EntiteTable.Trigger: strNomSequence += "TG_"; break;
case EntiteTable.ClefEtrangere: strNomSequence += "FK_"; break;
case EntiteTable.Index: strNomSequence += "IX_"; break;
case EntiteTable.Sequence: strNomSequence += "SQ_"; break;
default: break;
}
strNomSequence += NomTable;
if (!SequenceExists(strNomSequence))
{
CreerSequencesNumerotationTable(NomTable);
}
strRequete += strNomSequence + ".NEXTVAL FROM SYS.\"DUAL\" ";
adapter = Connection.GetSimpleReadAdapter(strRequete);
Connection.FillAdapter(adapter, dsTmp);
CUtilDataAdapter.DisposeAdapter(adapter);
string nID = dsTmp.Tables[0].Rows[0][0].ToString();
#endregion
#region 2 - On Compose le nom de notre entité avec cet ID
switch (Entite)
{
case EntiteTable.Trigger: NomEntite = "T"; break;
case EntiteTable.ClefEtrangere: NomEntite = "F"; break;
case EntiteTable.Index: NomEntite = "I"; break;
case EntiteTable.Sequence: NomEntite = "S"; break;
}
NomEntite += CGenerateurStringUnique.GetCodeFor(long.Parse(nID));
if ((NomEntite.Length + NomTable.Length) > 30)
{
int nDiff = Math.Abs(30 - (NomEntite.Length + NomTable.Length));
string strSubNomTable = NomTable.Substring(0, (NomTable.Length - nDiff));
NomEntite = strSubNomTable + NomEntite;
}
else
{
NomEntite = NomTable + NomEntite;
}
#endregion
return(NomEntite);
}
