C# IRepository.GetAllColumns方法代码示例

        private void Init(IRepository repository, string outFile)
        {
            _outFile = outFile;
            _repository = repository;
            _sbScript = new StringBuilder(10485760);
            _tableNames = _repository.GetAllTableNames();
            _allColumns = _repository.GetAllColumns();
            _allForeignKeys = repository.GetAllForeignKeys();
            _allPrimaryKeys = repository.GetAllPrimaryKeys();
            if (!repository.IsServer())
                _allIndexes = repository.GetAllIndexes();

            string scriptEngineBuild = AssemblyFileVersion;

            if (_repository.IsServer())
            {
                // Check if datatypes are supported when exporting from server
                // Either they can be converted, are supported, or an exception is thrown (if not supported)
                // Currently only sql_variant is not supported
                foreach (Column col in _allColumns)
                {
                    col.CharacterMaxLength = Helper.CheckDateColumnLength(col.DataType, col);
                    col.DateFormat = Helper.CheckDateFormat(col.DataType);

                    // Check if the current column points to a unique identity column,
                    // as the two columns' datatypes must match
                    bool refToIdentity = false;
                    Dictionary<string, Constraint> columnForeignKeys = new Dictionary<string, Constraint>();

                    // Fix for multiple constraints with same columns
                    var _tableKeys = _allForeignKeys.Where(c => c.ConstraintTableName == col.TableName);
                    foreach (var constraint in _tableKeys)
                    { 
                        if (!columnForeignKeys.ContainsKey(constraint.Columns.ToString()))
                        {
                            columnForeignKeys.Add(constraint.Columns.ToString(), constraint);
                        }
                    }

                    if (columnForeignKeys.ContainsKey(string.Format("[{0}]", col.ColumnName)))
                    {
                        var refCol = _allColumns.Where(c => c.TableName == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueConstraintTableName
                            && string.Format("[{0}]", c.ColumnName) == columnForeignKeys[string.Format("[{0}]", col.ColumnName)].UniqueColumnName).FirstOrDefault();
                        if (refCol != null && refCol.AutoIncrementBy > 0)
                        {
                            refToIdentity = true;
                        }
                    }
                    col.ServerDataType = col.DataType;
                    // This modifies the datatype to be SQL Compact compatible
                    col.DataType = Helper.CheckDataType(col.DataType, col, refToIdentity, _preserveDateAndDateTime2);
                }
            }
            _sbScript.AppendFormat("-- Script Date: {0} {1}  - ErikEJ.SqlCeScripting version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), scriptEngineBuild);
            _sbScript.AppendLine();
            if (!string.IsNullOrEmpty(_outFile) && !_repository.IsServer())
            {
                GenerateDatabaseInfo();
            }
            //if (!string.IsNullOrEmpty(_outFile) && _sqlite)
            //{
            //    _sbScript.AppendLine("SELECT 1;");
            //    _sbScript.AppendLine("PRAGMA foreign_keys=OFF;");
            //    _sbScript.AppendLine("BEGIN TRANSACTION;");
            //}
        }

        public static string CreateDataDiffScript(IRepository sourceRepository, string sourceTable, IRepository targetRepository, string targetTable, IGenerator generator)
        {
            //more advanced would be a field-mapping to be able to transfer the data between different structures
            StringBuilder sb = new StringBuilder();

            sb.AppendFormat("-- Script Date: {0} {1}  - ErikEJ.SqlCeScripting version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), AssemblyFileVersion);
            sb.Append(Environment.NewLine);
            sb.AppendLine("-- Data Diff script:");
            List<Column> sourceColumns = (from c in sourceRepository.GetAllColumns()
                                                where c.TableName == sourceTable
                                                select c).ToList();
            List<Column> targetColumns = (from c in targetRepository.GetAllColumns()
                                                where c.TableName == targetTable
                                                select c).ToList();

            var sourcePkList = sourceRepository.GetAllPrimaryKeys().Where(pk => pk.TableName == sourceTable).Select(pk => pk.ColumnName).ToList();
            if (sourcePkList.Count < 1)
            {
                throw new ArgumentException("Source does not have a primary key, this is required");
            }

            var targetPkList = targetRepository.GetAllPrimaryKeys().Where(pk => pk.TableName == targetTable).Select(pk => pk.ColumnName).ToList();
            if (targetPkList.Count < 1)
            {
                throw new ArgumentException("Target does not have a primary key, this is required");
            }

            if (sourcePkList.Count != targetPkList.Count)
            {
                throw new ArgumentException("Source and Target primary key are not comparable, this is required");
            }

            if (sourceColumns.Count() != targetColumns.Count())
            {
                throw new ArgumentException("Source and target does not have same number of columns");
            }

            for (int i = 0; i < sourceColumns.Count(); i++)
            {
                if (sourceColumns[i].ShortType != targetColumns[i].ShortType)
                {
                    throw new ArgumentException(string.Format("The columm {0} does not have the expected type {1} in the target table", sourceColumns[i].ColumnName, sourceColumns[i].ShortType));
                }
            }

            string sourcePkSort = string.Empty;
            string targetPkSort = string.Empty;
            
            for(int i = 0; i < sourceColumns.Count(); i++)
            {
                if(sourcePkList.Contains(sourceColumns[i].ColumnName))
                {
                    string prefix = (sourcePkSort == string.Empty) ? "" : ", ";
                    sourcePkSort += prefix + sourceColumns[i].ColumnName;
                    targetPkSort += prefix + targetColumns[i].ColumnName;
                }
            }

            //two arrays in the same order, now just compare them
            DataRow[] targetRows = targetRepository.GetDataFromTable(targetTable, targetColumns).Select(null, targetPkSort);
            int targetRow = 0;
            foreach(DataRow sourceRow in sourceRepository.GetDataFromTable(sourceTable, sourceColumns).Select(null, sourcePkSort))
            {
                //compare
                int pkCompare = 0;

                string whereClause = string.Empty;
                if (targetRow < targetRows.Count())
                {
                    for (int i = 0; i < sourcePkList.Count; i++)
                    {
                        if (whereClause.Length > 0)
                            whereClause += " AND ";
                        whereClause += String.Format(" [{0}] = {1}", targetPkList[i], generator.SqlFormatValue(targetTable, sourcePkList[i], targetRows[targetRow][sourcePkList[i]].ToString()));
                    }
                    if (whereClause.Length > 0)
                        whereClause += ";";
                }
                while (targetRow < targetRows.Count()
                    && (pkCompare = CompareDataRows(sourceRow, sourcePkList, targetRows[targetRow], targetPkList)) > 0)
                {
                    sb.AppendLine(String.Format("DELETE FROM [{0}] WHERE {1}", targetTable, whereClause));
                    sb.AppendLine("GO");
                    targetRow++;
                    whereClause = string.Empty;
                    for (int i = 0; i < sourcePkList.Count; i++)
                    {
                        if (whereClause.Length > 0)
                            whereClause += " AND ";
                        whereClause += String.Format(" [{0}] = {1}", targetPkList[i], generator.SqlFormatValue(targetTable, sourcePkList[i], targetRows[targetRow][sourcePkList[i]].ToString()));
                    }
                    if (whereClause.Length > 0)
                        whereClause += ";";
                }
                if (targetRow >= targetRows.Count() || pkCompare < 0)
                {
                    sb.AppendLine(generator.GenerateInsertFromDataRow(targetTable, sourceRow));
                    targetRow++;
                }
                else if (CompareDataRows(sourceRow, null, targetRows[targetRow], null) != 0)
//.........这里部分代码省略.........

 /// <summary>
 /// Gets the column information (name,metadata,description) for a given table. This method is internal static so it can be reused in ColumnMenuCommandHandler
 /// </summary>
 internal static IList<TableColumnInfo> GetSiblingColumnInfo(IRepository repo, string parentTable)
 {
     List<TableColumnInfo> lst = new List<TableColumnInfo>();
     var desc_cols = ExplorerControl.DescriptionCache.Where(d => d.Parent == parentTable).ToList();
     var cols = repo.GetAllColumns().Where(c => c.TableName == parentTable);
     var pkList = repo.GetAllPrimaryKeys().Where(p => p.TableName == parentTable).Select(p => p.ColumnName);
     var fkList = repo.GetAllForeignKeys().Where(f => f.ConstraintTableName == parentTable).Select(f => f.ColumnName);
     string isNull = "not null", fk = "", pk = "", type = "";
     foreach (var item in cols)
     {
         if (pkList.Contains(item.ColumnName)) { pk = "PK, "; }
         if (fkList.Contains(item.ColumnName)) { fk = "FK, "; }
         if (item.IsNullable == YesNoOption.YES) { isNull = "null"; }
         type = item.ShortType;
         string desc = desc_cols.Where(d => d.Object == item.ColumnName).Select(s => s.Description).SingleOrDefault();
         lst.Add(new TableColumnInfo()
         {
             Name = item.ColumnName,
             Metadata = string.Format("{0}{1}{2} {3}", pk, fk, type, isNull),//space between type & isNUll always exists
             Description = desc
         });
         pk = "";
         fk = "";
         isNull = "not null";
     }
     return lst;
 }

        public static void CreateDiffScript(IRepository sourceRepository, IRepository targetRepository,IGenerator generator, bool includeTargetDrops)
        {
            List<string> sourceTables = sourceRepository.GetAllTableNames();
            List<string> targetTables = targetRepository.GetAllTableNames();

            // Script each table not in the target
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateTableCreate(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GeneratePrimaryKeys(tableName);
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                List<string> tableIndexes = sourceRepository.GetIndexesFromTable(tableName).Select(i => i.IndexName).Distinct().ToList();
                foreach (var index in tableIndexes)
                {
                    generator.GenerateIndexScript(tableName, index);
                }
            }
            foreach (string tableName in sourceTables.Except(targetTables))
            {
                generator.GenerateForeignKeys(tableName);
            }

            // Drop each table in the target but not the source
            if (includeTargetDrops)
            {
                foreach (string tableName in targetTables.Except(sourceTables))
                {
                    generator.GenerateTableDrop(tableName);
                }
            }

            //For each table both in target and source
            foreach (string tableName in sourceTables.Intersect(targetTables))
            {
                // Check columns for the table: Dropped, added or changed ?
                IEnumerable<Column> sourceColumns = from c in sourceRepository.GetAllColumns()
                                    where c.TableName == tableName
                                    select c;
                IEnumerable<Column> targetColumns = from c in targetRepository.GetAllColumns()
                                    where c.TableName == tableName
                                    select c;

                // Added columns
                foreach (var column in sourceColumns.Except(targetColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnAddScript(column);
                }
                // Same columns, check for changes
                foreach (var sourceColumn in sourceColumns.Intersect(targetColumns, new ColumnComparer()))
                {
                    bool altered = false;
                    // Check if they have any differences:
                    var targetColumn = (from c in targetColumns
                                        where c.TableName == sourceColumn.TableName && c.ColumnName == sourceColumn.ColumnName
                                        select c).Single();
                    if (sourceColumn.IsNullable != targetColumn.IsNullable)
                        altered = true;
                    if (sourceColumn.NumericPrecision != targetColumn.NumericPrecision)
                        altered = true;
                    if (sourceColumn.NumericScale != targetColumn.NumericScale)
                        altered = true;
                    if (sourceColumn.AutoIncrementBy != targetColumn.AutoIncrementBy)
                        altered = true;
                    if (sourceColumn.CharacterMaxLength != targetColumn.CharacterMaxLength)
                        altered = true;
                    if (sourceColumn.DataType != targetColumn.DataType)
                        altered = true;

                    if (altered)
                        generator.GenerateColumnAlterScript(sourceColumn);

                    // Changed defaults is special case
                    if (!targetColumn.ColumnHasDefault && sourceColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                    if (!sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault)
                    {
                        generator.GenerateColumnDropDefaultScript(sourceColumn);
                    }
                    // If both columns have defaults, but they are different
                    if ((sourceColumn.ColumnHasDefault && targetColumn.ColumnHasDefault) && (sourceColumn.ColumnDefault != targetColumn.ColumnDefault))
                    {
                        generator.GenerateColumnSetDefaultScript(sourceColumn);
                    }
                }

                //Check primary keys
                List<PrimaryKey> sourcePK = sourceRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();
                List<PrimaryKey> targetPK = targetRepository.GetAllPrimaryKeys().Where(p => p.TableName == tableName).ToList();

                // Add the PK
                if (targetPK.Count == 0 && sourcePK.Count > 0)
                {
                    generator.GeneratePrimaryKeys(tableName);
//.........这里部分代码省略.........