Exemplo n.º 1
0
        private static void DropAllTables()
        {
            var keys = Repo.GetAllForeignKeys();

            foreach (var constraint in keys)
            {
                Repo.ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", constraint.ConstraintTableName,
                                                   constraint.ConstraintName));
            }

            var tbList = Repo.GetAllTableNames();

            tbList.ForEach(tb => Repo.ExecuteNonQuery("DROP TABLE " + tb));
            ClearCache();
        }
Exemplo n.º 2
0
        public List <DbDescription> GetDescriptions(DatabaseInfo databaseInfo)
        {
            var list = new List <DbDescription>();

            using (IRepository repo = DataConnectionHelper.CreateRepository(databaseInfo))
            {
                var tlist = repo.GetAllTableNames();
                if (tlist.Contains(TableName))
                {
                    var ds = repo.ExecuteSql(SelectScript);
                    if (ds.Tables.Count > 0)
                    {
                        foreach (DataRow row in ds.Tables[0].Rows)
                        {
                            var dbDesc = new DbDescription();
                            dbDesc.Object      = row[0] == DBNull.Value ? null : row[0].ToString();
                            dbDesc.Parent      = row[1] == DBNull.Value ? null : row[1].ToString();
                            dbDesc.Description = row[2] == DBNull.Value ? null : row[2].ToString();
                            list.Add(dbDesc);
                        }
                    }
                }
            }
            return(list);
        }
        private int GetVersionTableNumber(string databaseInfo, bool isDesktop)
        {
            if (isDesktop)
            {
                return(0);
            }

            int version = 0;

            using (IRepository repository = RepoHelper.CreateRepository(databaseInfo))
            {
                var list = repository.GetAllTableNames();
                if (list.Contains("__VERSION"))
                {
                    System.Data.DataSet ds = repository.ExecuteSql(@"
                                SELECT MAX([SchemaVersion]) FROM __VERSION;
                                GO");
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        version = int.Parse(ds.Tables[0].Rows[0][0].ToString());
                    }

                    repository.ExecuteSql(@"
                                DROP TABLE [__VERSION];
                                GO");
                }
            }

            return(version);
        }
        private static string CreateStore(DatabaseType storeDbType)
        {
            string fileName   = GetStoreName(storeDbType);
            string connString = string.Format("Data Source={0};", fileName);

            if (!File.Exists(fileName))
            {
                if (storeDbType == DatabaseType.SQLite)
                {
                    var helper = RepositoryHelper.CreateEngineHelper(storeDbType);
                    helper.CreateDatabase(connString);
                }
                else
                {
                    var sdf = Resources.SqlCe35AddinStore;
                    if (storeDbType == DatabaseType.SQLCE40)
                    {
                        sdf = Resources.SqlCe40AddinStore;
                    }
                    using (Stream stream = new MemoryStream(sdf))
                    {
                        // Create a FileStream object to write a stream to a file
                        using (FileStream fileStream = File.Create(fileName, (int)stream.Length))
                        {
                            // Fill the bytes[] array with the stream data
                            byte[] bytesInStream = new byte[stream.Length];
                            stream.Read(bytesInStream, 0, bytesInStream.Length);
                            // Use FileStream object to write to the specified file
                            fileStream.Write(bytesInStream, 0, bytesInStream.Length);
                        }
                    }
                }
            }

            var dbInfo = new DatabaseInfo {
                DatabaseType = storeDbType, ConnectionString = connString
            };

            using (IRepository repository = Helpers.RepositoryHelper.CreateRepository(dbInfo))
            {
                var tables = repository.GetAllTableNames();
                if (!tables.Contains("Databases"))
                {
                    var script =
                        "CREATE TABLE Databases (Id INT IDENTITY, Source nvarchar(2048) NOT NULL, FileName nvarchar(512) NOT NULL, CeVersion int NOT NULL)" +
                        separator;
                    if (storeDbType == DatabaseType.SQLite)
                    {
                        script =
                            "CREATE TABLE Databases (Id INTEGER PRIMARY KEY, Source nvarchar(2048) NOT NULL, FileName nvarchar(512) NOT NULL, CeVersion int NOT NULL)" +
                            separator;
                    }
                    repository.ExecuteSql(script);
                }
            }
            return(connString);
        }
Exemplo n.º 5
0
        private static void CreateExtPropsTable(IRepository repo)
        {
            var list = repo.GetAllTableNames();

            if (!list.Contains(TableName))
            {
                repo.ExecuteSql(CreateScript);
            }
        }
 private static void AddRowVersionColumns(DatabaseMenuCommandParameters databaseInfo)
 {
     using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring))
     {
         var list       = repository.GetAllTableNames();
         var allColumns = repository.GetAllColumns();
         foreach (var table in list)
         {
             if (!table.StartsWith("__"))
             {
                 var rowVersionCol = allColumns.Where(c => c.TableName == table && c.DataType == "rowversion").SingleOrDefault();
                 if (rowVersionCol == null)
                 {
                     repository.ExecuteSql(string.Format("ALTER TABLE {0} ADD COLUMN VersionColumn rowversion NOT NULL;{1}GO", table, Environment.NewLine));
                 }
             }
         }
     }
 }
Exemplo n.º 7
0
 public List<DbDescription> GetDescriptions(IRepository repository)
 {
     var list = new List<DbDescription>();
     string res = string.Empty;
     var tlist = repository.GetAllTableNames();
     if (tlist.Contains(tableName))
     {
         var ds = repository.ExecuteSql(selectScript);
         if (ds.Tables.Count > 0)
         {
             foreach (DataRow row in ds.Tables[0].Rows)
             {
                 var dbDesc = new DbDescription();
                 dbDesc.Object = row[0] == DBNull.Value ? null : row[0].ToString();
                 dbDesc.Parent = row[1] == DBNull.Value ? null : row[1].ToString();
                 dbDesc.Description = row[2] == DBNull.Value ? null : row[2].ToString();
                 list.Add(dbDesc);
             }
         }
     }
     return list;
 }
Exemplo n.º 8
0
        public List <DbDescription> GetDescriptions(IRepository repository)
        {
            var    list  = new List <DbDescription>();
            string res   = string.Empty;
            var    tlist = repository.GetAllTableNames();

            if (tlist.Contains(tableName))
            {
                var ds = repository.ExecuteSql(selectScript);
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        var dbDesc = new DbDescription();
                        dbDesc.Object      = row[0] == DBNull.Value ? null : row[0].ToString();
                        dbDesc.Parent      = row[1] == DBNull.Value ? null : row[1].ToString();
                        dbDesc.Description = row[2] == DBNull.Value ? null : row[2].ToString();
                        list.Add(dbDesc);
                    }
                }
            }
            return(list);
        }
Exemplo n.º 9
0
        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;");
            //}
        }
Exemplo n.º 10
0
        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);
                }

                // Do we have the same columns, if not, drop and create.
                if (sourcePK.Count > 0 && targetPK.Count > 0)
                {
                    if (sourcePK.Count == targetPK.Count)
                    {
                        //Compare columns
                        for (int i = 0; i < sourcePK.Count; i++)
                        {
                            if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
                            {
                                generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
                                generator.GeneratePrimaryKeys(tableName);
                                break;
                            }
                        }
                    }
                    // Not same column count, just drop and create
                    else
                    {
                        generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
                        generator.GeneratePrimaryKeys(tableName);
                    }
                }

                // Check indexes
                List<Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
                List<Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);

                // Check added indexes (by name only)
                foreach (var index in sourceIXs)
                {
                    var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
                    if (targetIX.Count() == 0)
                    {
                        generator.GenerateIndexScript(index.TableName, index.IndexName);
                    }
                }

                // Check foreign keys
                List<Constraint> sourceFKs = sourceRepository.GetAllForeignKeys(tableName);
                List<Constraint> targetFKs = targetRepository.GetAllForeignKeys(tableName);

                // Check added foreign keys (by name only)
                foreach (var fk in sourceFKs)
                {
                    Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (targetFK == null)
                    {
                        generator.GenerateForeignKey(fk);
                    }
                }
                // Check deleted FKs (by name only)
                foreach (var fk in targetFKs)
                {
                    Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (sourceFK == null)
                    {
                        generator.GenerateForeignKeyDrop(fk);
                    }
                }

                // Check deleted indexes (by name only)
                foreach (var index in targetIXs)
                {
                    var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
                    if (sourceIX.Count() == 0)
                    {
                        generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
                    }
                }

                // Dropped columns
                foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnDropScript(column);
                }

            }
        }
Exemplo n.º 11
0
        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);
                }

                // Do we have the same columns, if not, drop and create.
                if (sourcePK.Count > 0 && targetPK.Count > 0)
                {
                    if (sourcePK.Count == targetPK.Count)
                    {
                        //Compare columns
                        for (int i = 0; i < sourcePK.Count; i++)
                        {
                            if (sourcePK[i].ColumnName != targetPK[i].ColumnName)
                            {
                                generator.GeneratePrimaryKeyDrop(sourcePK[i], tableName);
                                generator.GeneratePrimaryKeys(tableName);
                                break;
                            }
                        }
                    }
                    // Not same column count, just drop and create
                    else
                    {
                        generator.GeneratePrimaryKeyDrop(sourcePK[0], tableName);
                        generator.GeneratePrimaryKeys(tableName);
                    }
                }

                // Check indexes
                List <Index> sourceIXs = sourceRepository.GetIndexesFromTable(tableName);
                List <Index> targetIXs = targetRepository.GetIndexesFromTable(tableName);

                // Check added indexes (by name only)
                foreach (var index in sourceIXs)
                {
                    var targetIX = targetIXs.Where(s => s.IndexName == index.IndexName);
                    if (targetIX.Count() == 0)
                    {
                        generator.GenerateIndexScript(index.TableName, index.IndexName);
                    }
                }

                // Check foreign keys
                List <Constraint> sourceFKs = sourceRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();
                List <Constraint> targetFKs = targetRepository.GetAllForeignKeys().Where(fk => fk.ConstraintTableName == tableName).ToList();

                // Check added foreign keys (by name only)
                foreach (var fk in sourceFKs)
                {
                    Constraint targetFK = targetFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (targetFK == null)
                    {
                        generator.GenerateForeignKey(fk);
                    }
                }
                // Check deleted FKs (by name only)
                foreach (var fk in targetFKs)
                {
                    Constraint sourceFK = sourceFKs.Where(s => s.ConstraintName == fk.ConstraintName).SingleOrDefault();
                    if (sourceFK == null)
                    {
                        generator.GenerateForeignKeyDrop(fk);
                    }
                }

                // Check deleted indexes (by name only)
                foreach (var index in targetIXs)
                {
                    var sourceIX = sourceIXs.Where(s => s.IndexName == index.IndexName);
                    if (sourceIX.Count() == 0)
                    {
                        generator.GenerateIndexOnlyDrop(index.TableName, index.IndexName);
                    }
                }

                // Dropped columns
                foreach (var column in targetColumns.Except(sourceColumns, new ColumnComparer()))
                {
                    generator.GenerateColumnDropScript(column);
                }
            }
        }
        public void GenerateDataContext(object sender, ExecutedRoutedEventArgs e)
        {
            var databaseInfo = ValidateMenuInfo(sender);

            if (databaseInfo == null)
            {
                return;
            }

            bool isDesktop = false;

            if ((bool)((MenuItem)sender).Tag == true)
            {
                isDesktop = true;
            }

            SqlCeHelper helper = new SqlCeHelper();

            if (!helper.IsV35DbProviderInstalled())
            {
                MessageBox.Show("This feature requires the SQL Server Compact 3.5 SP2 runtime & DbProvider to be installed");
                return;
            }

            string sqlMetalPath = (string)Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v7.0A\WinSDK-NetFx40Tools", "InstallationFolder", null);

            if (string.IsNullOrEmpty(sqlMetalPath))
            {
                sqlMetalPath = (string)Registry.GetValue(@"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SDKs\Windows\v8.0A", "InstallationFolder", string.Empty) + "bin\\NETFX 4.0 Tools\\";
                if (string.IsNullOrEmpty(sqlMetalPath))
                {
                    MessageBox.Show("Could not find SQLMetal location in registry");
                    return;
                }
            }
            sqlMetalPath = Path.Combine(sqlMetalPath, "sqlmetal.exe");
            if (!File.Exists(sqlMetalPath))
            {
                MessageBox.Show("Could not find SqlMetal in the expected location: " + sqlMetalPath);
                return;
            }
            string sdfFileName = string.Empty;

            string fileName = string.Empty;

            SaveFileDialog fd = new SaveFileDialog();

            fd.Title           = "Save Data Context as";
            fd.Filter          = "C# code (*.cs)|*.cs|VB code|*.vb";
            fd.OverwritePrompt = true;
            fd.ValidateNames   = true;
            bool?fdresult = fd.ShowDialog();

            if (fdresult.HasValue && fdresult.Value == true)
            {
                fileName = fd.FileName;
            }
            if (string.IsNullOrEmpty(fileName))
            {
                return;
            }

            try
            {
                using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring))
                {
                    var    tables        = repository.GetAllTableNames();
                    var    pks           = repository.GetAllPrimaryKeys();
                    string checkedTables = string.Empty;
                    foreach (string tableName in tables)
                    {
                        var pk = pks.Where(k => k.TableName == tableName).FirstOrDefault();
                        if (pk.TableName == null)
                        {
                            checkedTables += tableName + Environment.NewLine;
                        }
                    }
                    if (!string.IsNullOrEmpty(checkedTables))
                    {
                        string message = string.Format("The tables below do not have Primary Keys defined,{0}and will not be generated properly:{1}{2}", Environment.NewLine, Environment.NewLine, checkedTables);
                        MessageBox.Show(message);
                    }
                    List <KeyValuePair <string, string> > dbInfo = repository.GetDatabaseInfo();
                    foreach (var kvp in dbInfo)
                    {
                        if (kvp.Key == "Database")
                        {
                            sdfFileName = kvp.Value;
                            break;
                        }
                    }
                    sdfFileName = Path.GetFileName(sdfFileName);
                }

                string model = Path.GetFileNameWithoutExtension(databaseInfo.Caption).Replace(" ", string.Empty).Replace("#", string.Empty).Replace(".", string.Empty).Replace("-", string.Empty);
                model = model + "Context";
                DataContextDialog dcDialog = new DataContextDialog();
                dcDialog.ModelName    = model;
                dcDialog.IsDesktop    = isDesktop;
                dcDialog.NameSpace    = string.Empty;
                dcDialog.CodeLanguage = "C#";
                bool?result = dcDialog.ShowDialog();
                if (result.HasValue && result.Value == true && (!string.IsNullOrWhiteSpace(dcDialog.ModelName)))
                {
                    if (dcDialog.AddRowversionColumns)
                    {
                        AddRowVersionColumns(databaseInfo);
                    }

                    string sdfPath = databaseInfo.Connectionstring;

#if V35
#else
                    //If version 4.0, create a 3.5 schema sdf, and use that as connection string
                    if (isDesktop)
                    {
                        var tempFile = Path.GetTempFileName();
                        using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring))
                        {
                            var generator = RepoHelper.CreateGenerator(repository, tempFile);
                            generator.ScriptDatabaseToFile(Scope.Schema);
                        }
                        sdfPath = Path.Combine(Path.GetTempPath(), sdfFileName);
                        if (File.Exists(sdfPath))
                        {
                            File.Delete(sdfPath);
                        }
                        sdfPath = "Data Source=" + sdfPath;

                        helper.CreateDatabase(sdfPath);
                        using (IRepository repository = new DBRepository(sdfPath))
                        {
                            string script = File.ReadAllText(tempFile);
                            repository.ExecuteSql(script);
                        }
                    }
#endif
                    int versionNumber = GetVersionTableNumber(databaseInfo.Connectionstring, isDesktop);

                    model = dcDialog.ModelName;
                    string dcPath     = fileName;
                    string parameters = " /provider:SQLCompact /code:\"" + dcPath + "\"";
                    parameters += " /conn:\"" + sdfPath + "\"";
                    parameters += " /context:" + model;
                    if (dcDialog.Pluralize)
                    {
                        parameters += " /pluralize";
                    }
                    if (!string.IsNullOrWhiteSpace(dcDialog.NameSpace))
                    {
                        parameters += " /namespace:" + dcDialog.NameSpace;
                    }
                    var dcH = new ErikEJ.SqlCeScripting.DataContextHelper();

                    string sqlmetalResult = dcH.RunSqlMetal(sqlMetalPath, parameters);

                    if (!File.Exists(dcPath))
                    {
                        MessageBox.Show("Error during SQL Metal run: " + sqlmetalResult);
                        return;
                    }

                    if (!isDesktop)
                    {
                        using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring))
                        {
                            if (dcDialog.CodeLanguage == "VB")
                            {
                                DataContextHelper.FixDataContextVB(dcPath, model, dcDialog.NameSpace, sdfFileName, repository);
                            }
                            else
                            {
                                DataContextHelper.FixDataContextCS(dcPath, model, dcDialog.NameSpace, sdfFileName, repository);
                            }
                        }
                    }

                    // Creates __Version table and adds one row if desired
                    if (!isDesktop && dcDialog.AddVersionTable)
                    {
                        using (IRepository repository = RepoHelper.CreateRepository(databaseInfo.Connectionstring))
                        {
                            var list = repository.GetAllTableNames();
                            if (!list.Contains("__VERSION"))
                            {
                                repository.ExecuteSql(string.Format(@"
                                CREATE TABLE [__VERSION] (
                                  [SchemaVersion] int NOT NULL
                                , [DateUpdated] datetime NOT NULL DEFAULT (GETDATE())
                                );
                                GO
                                CREATE INDEX [IX_SchemaVersion] ON [__VERSION] ([SchemaVersion] DESC);
                                GO
                                INSERT INTO [__VERSION] ([SchemaVersion]) VALUES ({0});
                                GO", versionNumber));
                            }
                        }
                    }
                    MessageBox.Show("DataContext class successfully created");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
            }
        }
Exemplo n.º 13
0
 private static void CreateExtPropsTable(IRepository repo)
 {
     var list = repo.GetAllTableNames();
     if (!list.Contains(tableName))
         repo.ExecuteSql(createScript);
 }
Exemplo n.º 14
0
        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();
            }
        }