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