Example #1
0
        private Model.Table GetNewTable(Microsoft.SqlServer.Management.Smo.Table smoTable)
        {
            Model.Table table = new Model.Table(smoTable.Name, Script.GetSingluar(smoTable.Name), false);

            #region Columns
            int ordinalPosition = 0;
            List<Microsoft.SqlServer.Management.Smo.Column> smoColumns = new List<Microsoft.SqlServer.Management.Smo.Column>();

            foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoTable.Columns)
            {
                smoColumns.Add(smoColumn);
            }
            foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
            {
                if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.DataType.Name.ToLower() + "'") >= 0)
                {
                    continue;
                }

                // Some columns do not have default values
                string defaultValue = null;
                try
                {
                    defaultValue = smoColumn.Default;
                }
                catch { }

                Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false, smoColumn.Name, table, ordinalPosition, smoColumn.Nullable, smoColumn.DataType.Name,
                    smoColumn.DataType.MaximumLength, smoColumn.InPrimaryKey, smoColumn.Identity, defaultValue, smoColumn.Computed);
                table.AddColumn(column);
                ordinalPosition++;
            }
            #endregion

            #region Indexes
            //foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes)
            //{
            //    string indexType;
            //    string indexKeyType = smoIndex.IndexKeyType.ToString();
            //    if (indexKeyType == "DriPrimaryKey")
            //    {
            //        continue;
            //    }
            //    else if (indexKeyType == "DriUniqueKey")
            //    {
            //        continue;
            //    }
            //    else if (indexKeyType == "None")
            //    {
            //        continue;
            //        //indexType = DatabaseConstant.IndexType.None;
            //    }
            //    else
            //    {
            //        throw new Exception("IndexType " + indexKeyType + " Not Defined");
            //    }

            //    // Create Alias
            //    string indexAlias = indexType + "_";
            //    for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++)
            //    {
            //        Microsoft.SqlServer.Management.Smo.IndexedColumn smoIndexedColumn = smoIndex.IndexedColumns[i];

            //        indexAlias += smoIndexedColumn.Name;
            //        if (i < smoIndex.IndexedColumns.Count - 1)
            //        {
            //            indexAlias += "And";
            //        }
            //    }

            //    Index index = new Index(smoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table);

            //    // Fill Columns
            //    foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns)
            //    {
            //        Column indexColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
            //        index.AddColumn(indexColumn);
            //    }

            //    table.AddIndex(index);
            //}

            // Indexes -- that should be keys
            foreach (Microsoft.SqlServer.Management.Smo.Index smoIndex in smoTable.Indexes)
            {
                string keyType;
                string indexKeyType = smoIndex.IndexKeyType.ToString();
                if (indexKeyType == "DriPrimaryKey")
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (indexKeyType == "DriUniqueKey")
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (indexKeyType == "None")
                {
                    keyType = DatabaseConstant.KeyType.None;
                    continue;
                }
                else
                {
                    throw new Exception("KeyType " + indexKeyType + " Not Defined");
                }

                // Create Alias
                string keyAlias = keyType + "_";
                for (int i = 0; i <= smoIndex.IndexedColumns.Count - 1; i++)
                {
                    Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoIndex.IndexedColumns[i].Name];

                    keyAlias += smoColumn.Name;
                    if (i < smoIndex.IndexedColumns.Count - 1)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(smoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                foreach (Microsoft.SqlServer.Management.Smo.IndexedColumn smoColumn in smoIndex.IndexedColumns)
                {
                    Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
                    key.AddColumn(keyColumn);
                }

                table.AddKey(key);
            }
            #endregion

            #region Keys
            foreach (Microsoft.SqlServer.Management.Smo.ForeignKey smoForeignKey in smoTable.ForeignKeys)
            {
                string keyType = DatabaseConstant.KeyType.Foreign;

                // Create Alias
                string keyAlias = keyType + "_";
                for (int i = 0; i <= smoForeignKey.Columns.Count - 1; i++)
                {
                    Microsoft.SqlServer.Management.Smo.Column smoColumn = smoTable.Columns[smoForeignKey.Columns[i].Name];

                    keyAlias += smoColumn.Name;
                    if (i < smoForeignKey.Columns.Count - 1)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(smoForeignKey.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns)
                {
                    Column keyColumn = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), false);
                    key.AddColumn(keyColumn);
                }

                // Fill References
                key.ReferencedTable = new Model.Table(smoForeignKey.ReferencedTable, Script.GetSingluar(smoForeignKey.ReferencedTable), false);
                key.ReferencedKey = new Key(smoForeignKey.ReferencedKey, Script.GetSingluar(smoForeignKey.ReferencedKey), false);

                // Fill Referenced Columns
                foreach (Microsoft.SqlServer.Management.Smo.ForeignKeyColumn smoColumn in smoForeignKey.Columns)
                {
                    Column referencedKeyColumn = new Column(smoColumn.ReferencedColumn, Script.GetSingluar(smoColumn.ReferencedColumn), false);
                    key.AddReferencedColumn(referencedKeyColumn);
                }

                table.AddKey(key);
            }
            #endregion

            return table;
        }
Example #2
0
        private Model.Table GetNewTable(string tableName)
        {
            ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(tableName, "Table");
            //_columns = null; // Reset the columns
            //_indexes = null;
            //_dtReferencedColumns = null;
            //return new Model.Table();
            Model.Table table = new Model.Table(tableName, false);

            #region Columns
            DataRow[] columnRows = Columns.Select(string.Format("TABLE_NAME = '{0}'", tableName));

            foreach (DataRow row in columnRows)
            {
                bool isReadOnly = false;

                if (!row.IsNull("IsIdentity") && (int)row["IsIdentity"] == 1)
                {
                    isReadOnly = true;
                }
                else if (!row.IsNull("IsComputed") && (int)row["IsComputed"] == 1)
                {
                    isReadOnly = true;
                }
                else if (Slyce.Common.Utility.StringsAreEqual((string)row["DATA_TYPE"], "timestamp", false))
                {
                    isReadOnly = true;
                }
                // Check whether we have added this column before. Columns are repeated if they are both a PRIMARY_KEY and a FOREIGN_KEY
                Column column = new Column(
                    (string)row["COLUMN_NAME"],
                    false,
                    table,
                    (int)row["ORDINAL_POSITION"],
                    Slyce.Common.Utility.StringsAreEqual((string)row["IS_NULLABLE"], "YES", false),
                    (string)row["DATA_TYPE"],
                    row.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : Convert.ToInt32(row["CHARACTER_MAXIMUM_LENGTH"]),
                    (int)row["InPrimaryKey"] == 1,
                    row.IsNull("IsIdentity") ? false : Convert.ToInt32(row["IsIdentity"]) == 1,
                    row.IsNull("COLUMN_DEFAULT") ? "" : (string)row["COLUMN_DEFAULT"],
                    isReadOnly,
                    row.IsNull("IsComputed") ? false : Convert.ToInt32(row["IsComputed"]) == 1,
                    row.IsNull("NUMERIC_PRECISION") ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]),
                    row.IsNull("NUMERIC_SCALE") ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"]));

                table.AddColumn(column);
                //ordinalPosition++;
            }
            #endregion

            #region Indexes

            DataRow[] indexRows = Indexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));
            foreach (DataRow indexRow in indexRows)
            {
                string indexType;
                string indexKeyType = indexRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    continue;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    continue;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    continue;
                    //indexType = DatabaseConstant.IndexType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    indexType = DatabaseConstant.IndexType.Check;
                }
                else if (indexKeyType == "NONE")    //TODO check is NONE
                {
                    indexType = DatabaseConstant.IndexType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("IndexType " + indexKeyType + " Not Defined");
                }
                DataRow[] indexColumnRows;// = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));

                if (indexKeyType == "NONE")
                {
                    indexColumnRows = Columns.Select(string.Format("TABLE_NAME = '{0}' AND COLUMN_NAME  = '{1}'", tableName, indexRow["ColumnName"]));
                }
                else
                {
                    indexColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME  = '{1}'", tableName, indexRow["CONSTRAINT_NAME"]));
                }
                Index index = new Index(indexRow["CONSTRAINT_NAME"].ToString(), false, indexType, table, (bool)indexRow["IS_UNIQUE"], (bool)indexRow["IS_CLUSTERED"]);

                // Fill Columns
                foreach (DataRow indexColumnRow in indexColumnRows)
                {
                    Column indexColumn = new Column(indexColumnRow["COLUMN_NAME"].ToString(), false);
                    index.AddColumn(indexColumn);
                }
                index.ResetDefaults();
                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            foreach (DataRow keyRow in indexRows)
            {
                string keyType;
                string indexKeyType = keyRow["CONSTRAINT_TYPE"].ToString();
                if (indexKeyType == "PRIMARY KEY")
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (indexKeyType == "FOREIGN KEY")
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (indexKeyType == "UNIQUE")
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (indexKeyType == "CHECK")
                {
                    continue;
                }
                else if (indexKeyType == "NONE")
                {
                    continue;
                    //keyType = DatabaseConstant.KeyType.None;
                }
                else
                {
                    //continue;
                    throw new Exception("KeyType " + indexKeyType + " Not Defined");
                }
                Key key = new Key(keyRow["CONSTRAINT_NAME"].ToString(), false, keyType, table, false);
                DataRow[] keyColumnRows = IndexColumns.Select(string.Format("TABLE_NAME = '{0}' AND CONSTRAINT_NAME = '{1}'", tableName, keyRow["CONSTRAINT_NAME"]));

                // Fill Columns
                foreach (DataRow keyColumnRow in keyColumnRows)
                {
                    Column keyColumn = new Column(keyColumnRow["COLUMN_NAME"].ToString(), false);
                    keyColumn.DataType = (string)keyColumnRow["DATA_TYPE"];
                    key.AddColumn(keyColumn);
                }

                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    DataRow[] keyReferencedColumnRows = IndexReferencedColumns.Select(string.Format("ForeignKey = '{0}'", keyRow["CONSTRAINT_NAME"]));
                    DataRow firstKeyReferencedColumnRow = keyReferencedColumnRows[0];
                    // Fill References
                    key.ReferencedTable = new Model.Table(firstKeyReferencedColumnRow["ReferencedTable"].ToString(), false);

                    //if (dmoKey.ReferencedKey != null)
                    //{
                    key.ReferencedKey = new Key(firstKeyReferencedColumnRow["ReferencedKey"].ToString(), false, true);
                    //}

                    // Fill Referenced Columns
                    foreach (DataRow keyReferencedColumnRow in keyReferencedColumnRows)
                    {
                        Column keyReferencedColumn = new Column(keyReferencedColumnRow["ReferencedColumn"].ToString(), false);
                        key.AddReferencedColumn(keyReferencedColumn);
                    }
                }
                key.ResetDefaults();
                table.AddKey(key);
            }

            #endregion

            //#region Indexes -- that should be keys
            //string prevConstraintName = "";
            //Key key = null;
            //DataRow[] indexRows = DtIndexes.Select(string.Format("TABLE_NAME = '{0}'", tableName));

            //for (int rowCounter = 0; rowCounter < indexRows.Length; rowCounter++)
            //{
            //    DataRow row = indexRows[rowCounter];
            //    for (int colCounter = 0; colCounter < DtIndexes.Columns.Count; colCounter++)
            //    {
            //        string colName = DtIndexes.Columns[colCounter].ColumnName;
            //    }
            //    string keyType;
            //    string indexKeyType = row.IsNull("Constraint_Type") ? "NONE" : (string)row["Constraint_Type"];
            //    string constraintName = row.IsNull("Constraint_Name") ? "" : (string)row["Constraint_Name"];
            //    string columnName = (string)row["COLUMN_NAME"];

            //    if (indexKeyType == "NONE")
            //    {
            //        keyType = DatabaseConstant.KeyType.None;
            //    }
            //    else if (indexKeyType == "PRIMARY KEY")
            //    {
            //        keyType = DatabaseConstant.KeyType.Primary;
            //    }
            //    else if (indexKeyType == "UNIQUE")
            //    {
            //        keyType = DatabaseConstant.KeyType.Unique;
            //    }
            //    else if (indexKeyType == "CHECK") // TODO: was 'None' for SMO
            //    {
            //        keyType = DatabaseConstant.KeyType.None;
            //    }
            //    else if (indexKeyType == "FOREIGN KEY") // TODO: was 'None' for SMO
            //    {
            //        keyType = DatabaseConstant.KeyType.Foreign;
            //    }
            //    else
            //    {
            //        throw new Exception("KeyType " + indexKeyType + " Not Defined");
            //    }
            //    // Create Alias
            //    if (string.Format("{0}{1}", constraintName, keyType) != prevConstraintName)
            //    {
            //        if (key != null)
            //        {
            //            // Reset the alias, because it is based on the Columns collection which has just finished being modified.
            //            key.ResetDefaults();
            //        }
            //        // Create a new Key
            //        key = new Key(constraintName, false, keyType, table, false);
            //        table.AddKey(key);
            //        prevConstraintName = string.Format("{0}{1}", constraintName, keyType);
            //        Column keyColumn = new Column(columnName, false);
            //        key.AddColumn(keyColumn);
            //    }
            //    else
            //    {
            //        // We are processing another column of the same Index as the previous index
            //        Column keyColumn = new Column(columnName, false);
            //        key.AddColumn(keyColumn);
            //    }
            //    if (keyType == DatabaseConstant.KeyType.Foreign)
            //    {
            //        DataRow[] referencedColumnRows = DtReferencedColumns.Select(string.Format("FOREIGN_KEY = '{0}'", key.Name));

            //        foreach (DataRow refColRow in referencedColumnRows)
            //        {
            //            // Fill References
            //            if (key.ReferencedTable == null)
            //            {
            //                string referencedTableName = (string)refColRow["Referenced_Table_Name"];
            //                string referencedKeyName = (string)refColRow["Referenced_Key"];

            //                key.ReferencedTable = new Model.Table(referencedTableName, false);
            //                key.ReferencedKey = new Key(referencedKeyName, false, true);
            //            }
            //            string referencedColumnName = (string)refColRow["Referenced_Column_Name"];

            //            // Fill Referenced Columns
            //            Column referencedKeyColumn = new Column(referencedColumnName, false);
            //            key.AddReferencedColumn(referencedKeyColumn);
            //        }
            //    }
            //    key.ResetDefaults();
            //}
            //#endregion

            return table;
        }
Example #3
0
        private Model.Table GetNewTable(SQLDMO.Table dmoTable)
        {
            Model.Table table = new Model.Table(dmoTable.Name, Script.GetSingluar(dmoTable.Name), false);

            // Columns
            int ordinalPosition = 0;
            List<SQLDMO.Column> dmoColumns = new List<SQLDMO.Column>();
            foreach (SQLDMO.Column dmoColumn in dmoTable.Columns)
            {
                dmoColumns.Add(dmoColumn);
            }

            foreach (SQLDMO.Column dmoColumn in dmoColumns)
            {
                if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
                {
                    continue;
                }

                Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false, dmoColumn.Name, table, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
                    dmoColumn.InPrimaryKey, dmoColumn.Identity, dmoColumn.Default, dmoColumn.IsComputed);
                table.AddColumn(column);
                ordinalPosition++;
            }

            // Index
            foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
            {
                string indexType;
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
                {
                    continue;
                }
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
                {
                    continue;
                }
                else
                {
                    continue;
                    //throw new Exception("IndexType " + dmoIndex.Type + " Not Defined");
                }

                // Create Alias
                string indexAlias = indexType + "_";
                SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();

                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);

                    indexAlias += dmoColumn.Name;
                    if (i < indexes.Count)
                    {
                        indexAlias += "And";
                    }
                }

                Index index = new Index(dmoIndex.Name, Script.GetSingluar(indexAlias), false, indexType, table);

                // Fill Columns
                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = (SQLDMO._Column)indexes.Item(i);

                    Column indexColumn = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), false);
                    index.AddColumn(indexColumn);
                }

                table.AddIndex(index);
            }

            // Indexes -- that should be keys
            foreach (SQLDMO.Index dmoIndex in dmoTable.Indexes)
            {
                string keyType;
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIPrimaryKey)
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                if (dmoIndex.Type == SQLDMO.SQLDMO_INDEX_TYPE.SQLDMOIndex_DRIUniqueKey)
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else
                {
                    continue;
                    //throw new Exception("KeyType " + dmoIndex.Type + " Not Defined");
                }

                // Create Alias
                string keyAlias = keyType + "_";
                SQLDMO.SQLObjectList indexes = dmoIndex.ListIndexedColumns();

                for (int i = 1; i <= indexes.Count; i++)
                {
                    SQLDMO._Column dmoColumn = dmoTable.Columns.Item(indexes.Item(i));

                    keyAlias += dmoColumn.Name;
                    if (i < indexes.Count)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(dmoIndex.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                for (int i = 1; i <= indexes.Count; i++)
                {
                    Column keyColumn = new Column(indexes.Item(i).ToString(), Script.GetSingluar(indexes.Item(i).ToString()), false);
                    key.AddColumn(keyColumn);
                }

                table.AddKey(key);
            }

            // Keys
            foreach (SQLDMO.Key dmoKey in dmoTable.Keys)
            {
                string keyType;
                if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary)
                {
                    keyType = DatabaseConstant.KeyType.Primary;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Foreign)
                {
                    keyType = DatabaseConstant.KeyType.Foreign;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unique)
                {
                    keyType = DatabaseConstant.KeyType.Unique;
                }
                else if (dmoKey.Type == SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Unknown)
                {
                    continue;
                }
                else
                {
                    throw new Exception("KeyType " + dmoKey.Type.ToString() + " Not Defined");
                }

                // Create Alias
                string keyAlias = keyType + "_";
                for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                {
                    SQLDMO._Column dmoColumn = dmoTable.Columns.Item(dmoKey.KeyColumns.Item(i));

                    keyAlias += dmoColumn.Name;
                    if (i < dmoKey.KeyColumns.Count)
                    {
                        keyAlias += "And";
                    }
                }

                Key key = new Key(dmoKey.Name, Script.GetSingluar(keyAlias), false, keyType, table);

                // Fill Columns
                for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                {
                    Column keyColumn = new Column(dmoKey.KeyColumns.Item(i), Script.GetSingluar(dmoKey.KeyColumns.Item(i)), false);
                    key.AddColumn(keyColumn);
                }

                if (keyType == DatabaseConstant.KeyType.Foreign)
                {
                    // Fill References
                    key.ReferencedTable = new Model.Table(dmoKey.ReferencedTable, Script.GetSingluar(dmoKey.ReferencedTable), false);

                    if (dmoKey.ReferencedKey != null)
                    {
                        key.ReferencedKey = new Key(dmoKey.ReferencedKey, Script.GetSingluar(dmoKey.ReferencedKey), false);
                    }

                    // Fill Referenced Columns
                    for (int i = 1; i <= dmoKey.KeyColumns.Count; i++)
                    {
                        Column referencedKeyColumn = new Column(dmoKey.ReferencedColumns.Item(i), Script.GetSingluar(dmoKey.ReferencedColumns.Item(i)), false);
                        key.AddReferencedColumn(referencedKeyColumn);
                    }
                }

                table.AddKey(key);
            }

            return table;
        }