Exemplo n.º 1
0
    void Start()
    {
        TextAsset dataBase = Resources.Load <TextAsset>("database");

        string[] data = dataBase.text.Split(new char[] { '\n' });

        for (int i = 1; i < data.Length - 1; i++)
        {
            string[] row = data[i].Split(new char[] { ';' });

            InfoDataBase b = new InfoDataBase();

            int.TryParse(row[0], out b.id);
            b.word    = row[1];
            b.answer1 = row[2];
            b.answer2 = row[3];
            b.answer3 = row[4];
            b.answer4 = row[5];


            info.Add(b);
        }

        foreach (InfoDataBase b in info)
        {
            Debug.Log(b.word);
        }
    }
Exemplo n.º 2
0
        public override object GetDBStructure()
        {
            InfoDataBase dataResult = new InfoDataBase();

            string structSQL = @"select so.name as tableName, sc.name as columnName, st.name as type, sc.max_length as size, sc.is_nullable
                                 from sys.objects so
                                 inner join sys.columns sc on sc.object_id = so.object_id
                                 inner join sys.types st on st.system_type_id = sc.system_type_id
                                 where so.type = 'U' 
                                 order by so.name";

            using (SqlConnection conn = new SqlConnection(this.ConnectionString))
            {
                conn.Open();

                try
                {
                    SqlCommand comm = new SqlCommand(structSQL, conn);
                    using (SqlDataReader dsResult = comm.ExecuteReader())
                    {
                        string table = "";
                        while (dsResult.Read())
                        {
                            string currentTable = dsResult["tableName"].ToString();
                            string currentColumn = dsResult["columnName"].ToString();
                            string currentType = dsResult["type"].ToString();
                            int currentSize = Convert.ToInt32(dsResult["size"]);
                            bool currentNullable = Convert.ToBoolean(dsResult["is_nullable"]);

                            if (table != currentTable)
                            {
                                dataResult.Tabelas.Add(new InfoDataBase.Tabela() { Nome = currentTable, Colunas = new List<InfoDataBase.Coluna>() });
                            }

                            dataResult.Tabelas[dataResult.Tabelas.Count - 1].Colunas
                                .Add(new InfoDataBase.Coluna()
                                {
                                    Nome = currentColumn,
                                    Type = currentType,
                                    Size = currentSize,
                                    Nullable = currentNullable
                                });

                            table = currentTable;
                        }
                    }
                }
                catch (Exception ex)
                {
                    _strResult += ex.Message;
                }
            }

            return new
            {
                msg = _strResult,
                data = dataResult
            };
        }
Exemplo n.º 3
0
        protected override bool equals(InfoDataBase other)
        {
            var o = other as SimpleInfo;

            if (o == null)
            {
                return(false);
            }

            return(o.XssSanitized == XssSanitized);
        }
Exemplo n.º 4
0
        public void QUERY_GetStructure_CONSTRAINT_COLUMN_USAGE(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
                    sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
                    string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
                    sDebug += "  TABLE_SCHEMA : " + TABLE_SCHEMA;
                    string TABLE_NAME = dr["TABLE_NAME"].ToString();
                    sDebug += "  TABLE_NAME : " + TABLE_NAME;
                    string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
                    sDebug += "  COLUMN_NAME : " + COLUMN_NAME;
                    string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
                    sDebug += "  CONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
                    string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
                    sDebug += "  CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
                    string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
                    sDebug += "  CONSTRAINT_NAME : " + CONSTRAINT_NAME;

                    var table = infoDataBase.listInfosTable.Where(c => c.NomTable == TABLE_NAME).FirstOrDefault();
                    if (table != null)
                    {
                        var column = table.listInfosColumn.Where(c => c.NomColonne == COLUMN_NAME).FirstOrDefault();
                        if (column != null)
                        {
                            column.ContrainteCatalog = CONSTRAINT_CATALOG;
                            column.ContrainteSchema  = CONSTRAINT_SCHEMA;
                            column.ContrainteNom     = CONSTRAINT_NAME;
                        }
                    }
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 5
0
        public void QUERY_GetStructure_sys_objects(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM sys.sysobjects  ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())                             // signification voir : https://msdn.microsoft.com/fr-fr/library/ms177596(v=sql.120).aspx
                {
                    string name = dr["name"].ToString();      // Nom de l'objet
                    sDebug += "\nname : " + name;
                    string id = dr["id"].ToString().Trim();   // Numero identification de l'objet
                    sDebug += "  id : " + id;
                    string xtype = dr["xtype"].ToString();    // Type d'objet exple : S = TableSystème, P = Procédure stockée, U= Table Utilisateur, IF = Fonction de table Inline,
                    sDebug += "  xtype : " + xtype;           //  D : Valeur par défaut ou contrainte DEFAULT, F = Contrainte Foreign Key, PK = Contrainte PrimaryKey, UK = Contrainte UNIQUE
                    string uid = dr["uid"].ToString();
                    sDebug += "  uid : " + uid;               // uid  ID de schèma du propriétaire de l'objet... voir

                    // On saute ici plusieurs colonnes Non prises en charge...
                    string parent_obj = dr["parent_obj"].ToString(); // Numero d'id de l'objet parent, par exemple l'Id de table s'il s'agit d'un declencheur ou d'une contrainte.
                    sDebug += "  parent_obj : " + parent_obj;
                    string crdate = dr["crdate"].ToString();         // Date de creation de l'objet
                    sDebug += "  crdate : " + crdate;
                    string ftcatid = dr["ftcatid"].ToString();       // Identificateur du catalogue de texte intégral pour tables users enregistrées
                    sDebug += "  ftcatid : " + ftcatid;
                    string type = dr["type"].ToString();             // Type d'objet
                    sDebug += "  type : " + type;

                    InfoSysObject infoSysObject = new InfoSysObject()
                    {
                        ObjectName = name, Id_Object = int.Parse(id), xTypeObject = xtype, ParentIdObject = int.Parse(parent_obj), sDateTimeCreationObject = crdate
                    };
                    infoDataBase.listInfoSysObject.Add(infoSysObject);
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 6
0
        public void QUERY_GetStructure_TABLES(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.TABLES ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
                    sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
                    string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
                    sDebug += "  TABLE_SCHEMA : " + TABLE_SCHEMA;
                    string TABLE_NAME = dr["TABLE_NAME"].ToString();
                    sDebug += "  TABLE_NAME : " + TABLE_NAME;
                    string TABLE_TYPE = dr["TABLE_TYPE"].ToString();
                    sDebug += "  TABLE_TYPE : " + TABLE_TYPE;

                    InfosTable infosTable = new InfosTable()
                    {
                        NomTable = TABLE_NAME, TableType = TABLE_TYPE
                    };
                    var sysTable = infoDataBase.listInfoSysObject.Where(c => c.ObjectName == TABLE_NAME).FirstOrDefault();
                    if (sysTable != null)
                    {
                        infosTable.infoSysObject = sysTable;
                    }
                    else
                    {
                        infoDataBase.CptNotDefined++;
                    }
                    infoDataBase.listInfosTable.Add(infosTable);
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 7
0
        public void QUERY_GetStructure_REFERENTIAL_CONSTRAINTS(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS  ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
                    sDebug += "\nCONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
                    string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
                    sDebug += "  CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
                    string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
                    sDebug += "  CONSTRAINT_NAME : " + CONSTRAINT_NAME;
                    string UNIQUE_CONSTRAINT_CATALOG = dr["UNIQUE_CONSTRAINT_CATALOG"].ToString();
                    sDebug += "  UNIQUE_CONSTRAINT_CATALOG : " + UNIQUE_CONSTRAINT_CATALOG;
                    string UNIQUE_CONSTRAINT_SCHEMA = dr["UNIQUE_CONSTRAINT_SCHEMA"].ToString();
                    sDebug += "  UNIQUE_CONSTRAINT_SCHEMA : " + UNIQUE_CONSTRAINT_SCHEMA;
                    string UNIQUE_CONSTRAINT_NAME = dr["UNIQUE_CONSTRAINT_NAME"].ToString();
                    sDebug += "  UNIQUE_CONSTRAINT_NAME : " + UNIQUE_CONSTRAINT_NAME;
                    string MATCH_OPTION = dr["MATCH_OPTION"].ToString();
                    sDebug += "  MATCH_OPTION : " + MATCH_OPTION;
                    string UPDATE_RULE = dr["UPDATE_RULE"].ToString();
                    sDebug += "  UPDATE_RULE : " + UPDATE_RULE;
                    string DELETE_RULE = dr["DELETE_RULE"].ToString();
                    sDebug += "  DELETE_RULE : " + DELETE_RULE;

                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 8
0
        public void QUERY_GetStructure_KEY_COLUMN_USAGE(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string CONSTRAINT_CATALOG = dr["CONSTRAINT_CATALOG"].ToString();
                    sDebug += "\nCONSTRAINT_CATALOG : " + CONSTRAINT_CATALOG;
                    string CONSTRAINT_SCHEMA = dr["CONSTRAINT_SCHEMA"].ToString();
                    sDebug += "  CONSTRAINT_SCHEMA : " + CONSTRAINT_SCHEMA;
                    string CONSTRAINT_NAME = dr["CONSTRAINT_NAME"].ToString();
                    sDebug += "  CONSTRAINT_NAME : " + CONSTRAINT_NAME;
                    string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
                    sDebug += "  TABLE_CATALOG : " + TABLE_CATALOG;
                    string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
                    sDebug += "  TABLE_SCHEMA : " + TABLE_SCHEMA;
                    string TABLE_NAME = dr["TABLE_NAME"].ToString();
                    sDebug += "  TABLE_NAME : " + TABLE_NAME;
                    string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
                    sDebug += "  COLUMN_NAME : " + COLUMN_NAME;
                    string ORDINAL_POSITION = dr["ORDINAL_POSITION"].ToString();
                    sDebug += "  ORDINAL_POSITION : " + ORDINAL_POSITION;

                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 9
0
        public void QUERY_GetStructure_sys_indexes(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM sys.indexes  ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string object_id = dr["object_id"].ToString();
                    sDebug += "\nobject_id : " + object_id;
                    string name = dr["name"].ToString();
                    sDebug += "  name : " + name;
                    string index_id = dr["index_id"].ToString();
                    sDebug += "  index_id : " + index_id;
                    string type = dr["type"].ToString();
                    sDebug += "  type : " + type;
                    string type_desc = dr["type_desc"].ToString();
                    sDebug += "  type_desc : " + type_desc;
                    string referenced_column_id = dr["is_primary_key"].ToString();
                    sDebug += "  is_primary_key : " + referenced_column_id;
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 10
0
        public void QUERY_GetStructure_COLUMS(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string TABLE_CATALOG = dr["TABLE_CATALOG"].ToString();
                    sDebug += "\nTABLE_CATALOG : " + TABLE_CATALOG;
                    string TABLE_SCHEMA = dr["TABLE_SCHEMA"].ToString();
                    sDebug += "  TABLE_SCHEMA : " + TABLE_SCHEMA;
                    string TABLE_NAME = dr["TABLE_NAME"].ToString();
                    sDebug += "  TABLE_NAME : " + TABLE_NAME;
                    string COLUMN_NAME = dr["COLUMN_NAME"].ToString();
                    sDebug += "  COLUMN_NAME : " + COLUMN_NAME;
                    string ORDINAL_POSITION = dr["ORDINAL_POSITION"].ToString();
                    sDebug += "  ORDINAL_POSITION : " + ORDINAL_POSITION;
                    string IS_NULLABLE = dr["IS_NULLABLE"].ToString();
                    sDebug += "  IS_NULLABLE : " + IS_NULLABLE;
                    string DATA_TYPE = dr["DATA_TYPE"].ToString();
                    sDebug += "  DATA_TYPE : " + DATA_TYPE;
                    string CHARACTER_MAXIMUM_LENGTH = dr["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    sDebug += "  CHARACTER_MAXIMUM_LENGTH : " + CHARACTER_MAXIMUM_LENGTH;

                    var table = infoDataBase.listInfosTable.Where(c => c.NomTable == TABLE_NAME).FirstOrDefault();
                    if (table != null)
                    {
                        InfosColumn infosColumn = new InfosColumn()
                        {
                            NomColonne = COLUMN_NAME, sPositionOrdinale = ORDINAL_POSITION, isNullable = IS_NULLABLE, sDataType = DATA_TYPE, caracterMaxLenght = CHARACTER_MAXIMUM_LENGTH
                        };
                        var syscolumn = table.infoSysObject.listInfosSysColums.Where(c => c.NameColumns == COLUMN_NAME).FirstOrDefault();
                        if (syscolumn != null)
                        {
                            infosColumn.infosSysColums = syscolumn;
                        }
                        else
                        {
                            infoDataBase.CptNotDefined++;
                        }
                        table.listInfosColumn.Add(infosColumn);
                    }
                    else
                    {
                        infoDataBase.CptNotDefined++;
                    }
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 11
0
        public void QUERY_GetStructure_sys_columns(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM sys.columns  ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                var dr  = CmdGetNumCmd.ExecuteReader();
                int cpt = 0;
                while (dr.Read())
                {
                    string object_id = dr["object_id"].ToString();
                    sDebug += "\nobject_id : " + object_id;
                    string name = dr["name"].ToString();
                    sDebug += "  name : " + name;
                    string column_id = dr["column_id"].ToString();
                    sDebug += "  column_id : " + column_id;
                    string system_type_id = dr["system_type_id"].ToString();
                    sDebug += "  system_type_id : " + system_type_id;
                    string user_type_id = dr["user_type_id"].ToString();
                    sDebug += "  user_type_id : " + user_type_id;
                    string max_length = dr["max_length"].ToString();
                    sDebug += "  max_length : " + user_type_id;
                    string precision = dr["precision"].ToString();
                    sDebug += "  precision : " + precision;
                    string scale = dr["scale"].ToString();
                    sDebug += "  scale : " + scale;

                    string collation_name = dr["collation_name"].ToString();
                    sDebug += "  collation_name : " + collation_name;
                    string is_nullable = dr["is_nullable"].ToString();
                    sDebug += "  is_nullable : " + is_nullable;
                    string is_ansi_padded = dr["is_ansi_padded"].ToString();
                    sDebug += "  is_ansi_padded : " + is_ansi_padded;
                    string is_rowguidcol = dr["is_rowguidcol"].ToString();
                    sDebug += "  is_rowguidcol : " + is_rowguidcol;
                    string is_identity = dr["is_identity"].ToString();
                    sDebug += "  is_identity : " + is_identity;
                    string is_computed = dr["is_computed"].ToString();
                    sDebug += "  is_computed : " + is_computed;
                    string is_filestream = dr["is_filestream"].ToString();
                    sDebug += "  is_filestream : " + is_filestream;

                    InfosSysColums infosSysColums = new InfosSysColums()
                    {
                        OwnerId = int.Parse(object_id), NameColumns = name, column_Id = int.Parse(column_id), sysTypeId = system_type_id, useTypeId = user_type_id, MaxLenght = int.Parse(max_length), IsNullable = is_nullable, IsIdentity = is_identity, IsComputed = is_computed, IsRowGuidCol = is_rowguidcol
                    };
                    var obj = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosSysColums.OwnerId).FirstOrDefault();
                    if (obj != null)
                    {
                        obj.listInfosSysColums.Add(infosSysColums);
                    }

                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 12
0
        public void QUERY_GetStructure_sys_foreign_key_columns(InfoDataBase infoDataBase)
        {
            string    queryGetNumCmd = "SELECT * FROM sys.foreign_key_columns  ";
            Stopwatch sw             = new Stopwatch();

            sw.Start();
            string sDebug = "";

            try {
                OdbcCommand CmdGetNumCmd = null;
                CmdGetNumCmd = GetNewCmdSQL(queryGetNumCmd);

                string sdebug1 = "";
                var    dr      = CmdGetNumCmd.ExecuteReader();
                int    cpt     = 0;
                while (dr.Read())
                {
                    string constraint_object_id = dr["constraint_object_id"].ToString();
                    sDebug += "\nconstraint_object_id : " + constraint_object_id;
                    string constraint_column_id = dr["constraint_column_id"].ToString();
                    sDebug += "  constraint_column_id : " + constraint_column_id;
                    string parent_object_id = dr["parent_object_id"].ToString();
                    sDebug += "  parent_object_id : " + parent_object_id;
                    string parent_column_id = dr["parent_column_id"].ToString();
                    sDebug += "  parent_column_id : " + parent_column_id;
                    string referenced_object_id = dr["referenced_object_id"].ToString();
                    sDebug += "  referenced_object_id : " + referenced_object_id;
                    string referenced_column_id = dr["referenced_column_id"].ToString();
                    sDebug += "  referenced_column_id : " + referenced_column_id;

                    InfosContraintes infosContraintes = new InfosContraintes()
                    {
                        constraint_object_id = int.Parse(constraint_object_id),
                        constraint_column_id = int.Parse(constraint_column_id),
                        parent_object_id     = int.Parse(parent_object_id),
                        parent_column_id     = int.Parse(parent_column_id),
                        referenced_object_id = int.Parse(referenced_object_id),
                        referenced_column_id = int.Parse(referenced_column_id)
                    };

                    var obj1 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.constraint_object_id).FirstOrDefault();
                    if (obj1 != null)
                    {
                        infosContraintes.name_constraint_object_id = obj1.ObjectName;
                    }
                    var obj2 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.parent_object_id).FirstOrDefault();
                    if (obj2 != null)
                    {
                        infosContraintes.name_parent_object_id = obj2.ObjectName;
                    }
                    var obj3 = infoDataBase.listInfoSysObject.Where(c => c.Id_Object == infosContraintes.referenced_object_id).FirstOrDefault();
                    if (obj3 != null)
                    {
                        infosContraintes.name_referenced_object_id = obj3.ObjectName;
                    }

                    sdebug1 += "\n" + infosContraintes.name_constraint_object_id;
                    sdebug1 += " [" + infosContraintes.constraint_column_id + "] ";
                    sdebug1 += " = ";
                    sdebug1 += string.IsNullOrEmpty(infosContraintes.name_parent_object_id) ? "?? " + infosContraintes.parent_object_id.ToString() + " ??" : infosContraintes.name_parent_object_id;
                    sdebug1 += " [" + infosContraintes.parent_column_id + "] ";
                    sdebug1 += " <-> ";
                    sdebug1 += string.IsNullOrEmpty(infosContraintes.name_referenced_object_id) ? "?? " + infosContraintes.referenced_object_id.ToString() + " ??" : infosContraintes.name_referenced_object_id;
                    sdebug1 += " [" + infosContraintes.referenced_column_id + "] ";

                    infoDataBase.listInfosContraintes.Add(infosContraintes);
                    cpt++;
                }
            }
            catch (Exception ex) {
                GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@QUERY_GetStructure  Exception :" + ex.Message);
            }
            sw.Stop();
        }
Exemplo n.º 13
0
        public InfoDataBase GetStructure(bool modeForce)
        {
            if (infoDataBase == null || modeForce)
            {
                infoDataBase = new InfoDataBase();
                if (OpenConnectionDB())
                {
                    // Récupération des donnes sys..
                    QUERY_GetStructure_sys_objects(infoDataBase);             // récupération de tous les obejcts de la database
                    QUERY_GetStructure_sys_foreign_key_columns(infoDataBase); // Récupération des contraintes foreign key
                    QUERY_GetStructure_sys_columns(infoDataBase);             // Récup des infos sys pour les colonnes
                    QUERY_GetStructure_sys_indexes(infoDataBase);

                    QUERY_GetStructure_TABLES(infoDataBase);
                    QUERY_GetStructure_COLUMS(infoDataBase);
                    QUERY_GetStructure_CONSTRAINT_COLUMN_USAGE(infoDataBase);
                    QUERY_GetStructure_KEY_COLUMN_USAGE(infoDataBase);
                    QUERY_GetStructure_REFERENTIAL_CONSTRAINTS(infoDataBase);

                    foreach (var table in infoDataBase.listInfosTable)
                    {
                        int count = QUERY_Count(table.NomTable);
                        table.NbreCount            = count;
                        table.DestinationDifferent = table.NbreCount != table.NbreCountOrigine;  // pour le moment les différences sont sur le count..

                        // Pour les reférence Foreign Key :
                        TableRelationTable tableRelationTableFK = new TableRelationTable()
                        {
                            NomTable = table.NomTable, TypeContrainte = eTypeContrainte.ForeignKey, NbreCountRec = count, NbreRelation = 0, listRelationToTable = new List <RelationToTable>()
                        };
                        var lstTableForeignKey = infoDataBase.listInfosContraintes.Where(c => c.name_parent_object_id == table.NomTable).ToList();
                        if (lstTableForeignKey.Count != 0)
                        {
                            tableRelationTableFK.NbreRelation = lstTableForeignKey.Count;
                            foreach (var t in lstTableForeignKey)    // On ne descend ici qu'au premier niveau de relation
                            {
                                TableRelationTable tableRelationTable = new TableRelationTable()
                                {
                                    NomTable = t.name_referenced_object_id, listRelationToTable = new List <RelationToTable>()
                                };
                                RelationToTable relationToTable = new RelationToTable()
                                {
                                    NomContrainte = t.name_constraint_object_id, TableRelation = tableRelationTable, IdColumnFrom = t.parent_column_id, IdColumnTo = t.referenced_column_id
                                };
                                tableRelationTableFK.listRelationToTable.Add(relationToTable);
                            }
                        }
                        table.NbreForeignKey = lstTableForeignKey.Count;
                        table.TableRelationTableForeignKey = tableRelationTableFK;
                        infoDataBase.listTableRelationTable.Add(tableRelationTableFK);

                        // Pour les reférence Ref Foreign Key :
                        TableRelationTable tableRelationTableRefFK = new TableRelationTable()
                        {
                            NomTable = table.NomTable, TypeContrainte = eTypeContrainte.RefForeignKey, NbreCountRec = count, NbreRelation = 0, listRelationToTable = new List <RelationToTable>()
                        };
                        var lstTableRefForeignKey = infoDataBase.listInfosContraintes.Where(c => c.name_referenced_object_id == table.NomTable).ToList();
                        if (lstTableRefForeignKey.Count != 0)
                        {
                            tableRelationTableRefFK.NbreRelation = lstTableRefForeignKey.Count;
                            foreach (var t in lstTableRefForeignKey)
                            {
                                TableRelationTable tableRelationTable = new TableRelationTable()
                                {
                                    NomTable = t.name_parent_object_id, listRelationToTable = new List <RelationToTable>()
                                };
                                RelationToTable relationToTable = new RelationToTable()
                                {
                                    NomContrainte = t.name_constraint_object_id, TableRelation = tableRelationTable, IdColumnFrom = t.referenced_column_id, IdColumnTo = t.parent_column_id
                                };
                                tableRelationTableRefFK.listRelationToTable.Add(relationToTable);
                            }
                        }
                        table.NbreRefForeignKey = lstTableRefForeignKey.Count;
                        table.TableRelationTableRefForeignKey = tableRelationTableRefFK;
                        infoDataBase.listTableRelationTable.Add(tableRelationTableRefFK);


                        // POur l'identity :
                        foreach (var c in table.listInfosColumn)
                        {
                            int  Position = int.Parse(c.sPositionOrdinale);
                            bool Identity = c.infosSysColums.IsIdentity == "True";
                            if (Position == 1 && Identity)
                            {
                                table.HasColumnIdentity = true;
                            }
                        }
                    }

                    CloseConnectionDB();
                }
                else
                {
                    GlobalLog.Instance().AjouteLog("xAffaireLigneArticleAS400", "@GetStructure #01 GetLignes DB not open !");
                }

                // Premier filtrage général , on ne retient que les tables USer (BASE TABLE), on exclu les vues et table système:
                infoDataBase.listInfosTable = infoDataBase.listInfosTable.Where(c => c.TableType == "BASE TABLE" && c.NomTable != "sysdiagrams").OrderBy(c => c.NomTable).ToList();
            }
            return(infoDataBase);
        }
Exemplo n.º 14
0
 /// <inheritdoc />
 protected override bool equals(InfoDataBase other)
 {
     return(getHashCode() == (other as Flags).getHashCode());
 }