C# IRepository.GetAllPrimaryKeys方法代码示例

        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 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.GetColumnsFromTable()
                                    where c.TableName == tableName
                                    select c;
                IEnumerable<Column> targetColumns = from c in targetRepository.GetColumnsFromTable()
                                    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);
//.........这里部分代码省略.........

        private static void AddIndexes(IRepository repository, List<string> dcLines, string n, bool cs)
        {

            for (int y = 0; y < dcLines.Count; y++)
            {
                string attr = "<Global.System.Data.Linq.Mapping.TableAttribute(";
                if (cs)
                    attr = "[global::System.Data.Linq.Mapping.TableAttribute(";
                if (dcLines[y].StartsWith(n + attr))
                {
                    string tableName = string.Empty;
                    // if the Name attribute is used, that is the table name, otherwise use class name
                    string[] names = dcLines[y].Split('"');
                    if (names.Count() > 1)
                        tableName = names[1];
                    string[] words = dcLines[y + 1].Split(' ');
                    if (words.Count() > 3)
                    {
                        if (string.IsNullOrEmpty(tableName))
                            tableName = words[3];
                        List<Index> indexList = repository.GetIndexesFromTable(tableName);
                        List<PrimaryKey> pkList = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == tableName).ToList();
                        //If there are indexes, add them
                        if (indexList.Count > 0)
                        {
                            IEnumerable<string> uniqueIndexNameList = indexList.Select(ind => ind.IndexName).Distinct();
                            foreach (string uniqueIndexName in uniqueIndexNameList)
                            {
                                string colList = string.Empty;
                                IOrderedEnumerable<Index> indexesByName = from ind in indexList
                                                                          where ind.IndexName == uniqueIndexName
                                                                          orderby ind.OrdinalPosition
                                                                          select ind;

                                // Check if a Unique index overlaps an existing primary key
                                // If that is the case, do not add the duplicate index
                                // as this will cause LINQ to SQL to crash 
                                // when doing updates with rowversion columns
                                var ixList = indexesByName.ToList();
                                if (ixList.Count > 0 && ixList[0].Unique)
                                {
                                    int i = 0;
                                    foreach (var pk in pkList)
                                    {
                                        if (ixList.Count > i)
                                        {
                                            if (pk.ColumnName != ixList[i].ColumnName)
                                            {
                                                break;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                        i++;
                                    }
                                    if (i > 0)
                                        continue;
                                }


                                bool unique = false;
                                var idx = indexesByName.First();
                                if (idx.Unique)
                                {
                                    unique = true;
                                }

                                foreach (Index col in indexesByName)
                                {
                                    colList += string.Format("{0} {1}, ", ToUpperFirst(col.ColumnName.Replace(" ", string.Empty)), col.SortOrder.ToString());
                                }
                                colList = colList.Remove(colList.Length - 2, 2);
                                string indexAttr = "<Index(Name:=\"{0}\", Columns:=\"{1}\", IsUnique:={2})> _";
                                if (cs)
                                    indexAttr = "[Index(Name=\"{0}\", Columns=\"{1}\", IsUnique={2})]";
                                string falseString = "False";
                                if (cs)
                                    falseString = "false";
                                string trueString = "True";
                                if (cs)
                                    trueString = "true";

                                dcLines[y - 1] += Environment.NewLine + n + string.Format(indexAttr, idx.IndexName, colList, unique ? trueString : falseString);
                            }
                        }
                    }
                }
            }
        }

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

        private void Init(IRepository repository, string outFile)
        {
            _outFile = outFile;
            _repository = repository;
            _sbScript = new StringBuilder(10485760);
            _tableNames = _repository.GetAllTableNames();
            _allColumns = _repository.GetColumnsFromTable();
            _allForeignKeys = repository.GetAllForeignKeys();
            _allPrimaryKeys = repository.GetAllPrimaryKeys();

            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).Single();
                        if (refCol != null && refCol.AutoIncrementBy > 0)
                        {
                            refToIdentity = true;
                        }
                    }

                    // This modifies the datatype to be SQL Compact compatible
                    col.DataType = Helper.CheckDataType(col.DataType, col, refToIdentity);
                }
                _sbScript.AppendFormat("-- Script Date: {0} {1}  - Generated by Export2SqlCe version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Version);
            }
            else
            {
                _sbScript.AppendFormat("-- Script Date: {0} {1}  - Generated by ExportSqlCe version {2}", DateTime.Now.ToShortDateString(), DateTime.Now.ToShortTimeString(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Version);
            }
            _sbScript.AppendLine();
            if (!string.IsNullOrEmpty(_outFile) && !_repository.IsServer())
            {
                _sbScript.Append("-- Database information:");
                _sbScript.AppendLine();

                foreach (var kv in _repository.GetDatabaseInfo())
                {
                    _sbScript.Append("-- ");
                    _sbScript.Append(kv.Key);
                    _sbScript.Append(": ");
                    _sbScript.Append(kv.Value);
                    _sbScript.AppendLine();
                }
                _sbScript.AppendLine();

                // Populate all tablenames
                _sbScript.Append("-- User Table information:");
                _sbScript.AppendLine();
                _sbScript.Append("-- ");
                _sbScript.Append("Number of tables: ");
                _sbScript.Append(_tableNames.Count);
                _sbScript.AppendLine();

                foreach (string tableName in _tableNames)
                {
                    Int64 rowCount = _repository.GetRowCount(tableName);
                    _sbScript.Append("-- ");
                    _sbScript.Append(tableName);
                    _sbScript.Append(": ");
                    _sbScript.Append(rowCount);
                    _sbScript.Append(" row(s)");
                    _sbScript.AppendLine();
                }
                _sbScript.AppendLine();
            }
        }

        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)
//.........这里部分代码省略.........