Esempio n. 1
0
        /// <summary>
        /// Reads specified table foreign keys.
        /// </summary>
        private void ApplyTablesForeignKeys(List <DbTable> tables)
        {
            if (_dbConnection.State != ConnectionState.Open)
            {
                _dbConnection.Open();
            }

            // Used to get columns Sql DataType
            using (DataTable foreignKeysTable = _dbConnection.GetSchema("ForeignKeys"))
            {
                // nothing found!
                if (foreignKeysTable.Rows.Count == 0)
                {
                    return;
                }

                // find description if there is any
                foreach (var table in tables)
                {
                    // only FOREIGN KEY
                    foreignKeysTable.DefaultView.RowFilter =
                        string.Format(" CONSTRAINT_TYPE='FOREIGN KEY' AND TABLE_NAME='{0}' ", table.TableName);

                    // Fetch the rows
                    foreach (DataRowView keysData in foreignKeysTable.DefaultView)
                    {
                        // foreign key found!
                        DataRow keyRow = keysData.Row;

                        var foreignKeyTableName = keyRow["FKEY_TO_TABLE"].ToString();
                        var primaryKeyTableName = table.TableName;

                        var foreignKeyTable = FindTable(tables, foreignKeyTableName);
                        var primaryKeyTable = table;

                        if (primaryKeyTable != null)
                        {
                            // foreign key
                            var foreignKey = new DbForeignKey()
                            {
                                ForeignKeyName    = keyRow["CONSTRAINT_NAME"].ToString(),
                                LocalColumnName   = keyRow["FKEY_FROM_COLUMN"].ToString(),
                                ForeignColumnName = keyRow["FKEY_TO_COLUMN"].ToString(),
                                ForeignTableName  = keyRow["FKEY_TO_TABLE"].ToString(),
                                Multiplicity      = DbForeignKey.ForeignKeyMultiplicity.OneToMany
                            };

                            // add foreign key
                            table.ForeignKeys.Add(foreignKey);

                            // apply local column
                            DbColumn localColumn = table.FindColumnDb(foreignKey.LocalColumnName);
                            foreignKey.LocalColumn = localColumn;

                            //apply foreign table
                            DbTable foreignTable = foreignKeyTable;

                            // referenced key
                            if (!localColumn.PrimaryKey)
                            {
                                localColumn.IsReferenceKey      = true;
                                localColumn.IsReferenceKeyTable = foreignTable;
                            }

                            if (foreignTable != null)
                            {
                                foreignKey.ForeignTable = foreignTable;

                                // apply foreign column
                                DbColumn foreignColumn = foreignTable.FindColumnDb(foreignKey.ForeignColumnName);
                                foreignKey.ForeignColumn = foreignColumn;
                            }
                            else
                            {
                                foreignKey.ForeignTable  = null;
                                foreignKey.ForeignColumn = null;
                            }
                        }

                        // adding the relation to the foreign table!
                        if (foreignKeyTable != null)
                        {
                            // foreign key
                            var oneMultiplicityKey_Foreign = new DbForeignKey()
                            {
                                ForeignKeyName    = keyRow["CONSTRAINT_NAME"].ToString(),
                                LocalColumnName   = keyRow["FKEY_TO_COLUMN"].ToString(),
                                ForeignColumnName = keyRow["FKEY_FROM_COLUMN"].ToString(),
                                ForeignTableName  = primaryKeyTableName,
                                Multiplicity      = DbForeignKey.ForeignKeyMultiplicity.ManyToOne
                            };

                            // check if it is already there
                            if (foreignKeyTable.ForeignKeys.Exists(x => x.ForeignKeyName == oneMultiplicityKey_Foreign.ForeignKeyName))
                            {
                                continue;
                            }

                            // to the list
                            foreignKeyTable.ForeignKeys.Add(oneMultiplicityKey_Foreign);

                            // apply local column
                            DbColumn localColumn = foreignKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.LocalColumnName);
                            oneMultiplicityKey_Foreign.LocalColumn = localColumn;
                            if (!localColumn.PrimaryKey)
                            {
                                localColumn.IsReferenceKey      = true;
                                localColumn.IsReferenceKeyTable = primaryKeyTable;
                            }

                            if (primaryKeyTable != null)
                            {
                                // foreign table of that!
                                oneMultiplicityKey_Foreign.ForeignTable = primaryKeyTable;

                                // apply foreign column
                                DbColumn foreignColumn = primaryKeyTable.FindColumnDb(oneMultiplicityKey_Foreign.ForeignColumnName);
                                oneMultiplicityKey_Foreign.ForeignColumn = foreignColumn;
                            }
                            else
                            {
                                oneMultiplicityKey_Foreign.ForeignTable  = null;
                                oneMultiplicityKey_Foreign.ForeignColumn = null;
                            }
                        }
                    }
                }
            }
        }