public void AddForeignKey(object sender, ExecutedRoutedEventArgs e) { var menuInfo = ValidateMenuInfo(sender); if (menuInfo == null) { return; } try { using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo)) { ForeignKeyDialog fkDlg = new ForeignKeyDialog(menuInfo.Name); fkDlg.AllColumns = repository.GetAllColumns().ToList(); fkDlg.AllPrimaryKeys = repository.GetAllPrimaryKeys(); if (fkDlg.ShowModal() == true) { var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType); generator.GenerateForeignKey(fkDlg.NewKey); OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript); Helpers.DataConnectionHelper.LogUsage("TableKeyAdd"); } } } catch (Exception ex) { Helpers.DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false); } }
/// <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) { var lst = new List <TableColumnInfo>(); var descCols = 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).ToList(); var fkList = repo.GetAllForeignKeys().Where(f => f.ConstraintTableName == parentTable).Select(f => f.ColumnName).ToList(); string isNull = "not null", fk = "", pk = ""; 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"; } var type = item.ShortType; var desc = descCols.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.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 void SpawnDataEditorWindow(object sender, ExecutedRoutedEventArgs e) { try { var menuItem = sender as MenuItem; if (menuItem == null) { return; } var menuInfo = menuItem.CommandParameter as MenuCommandParameters; if (menuInfo == null) { return; } WindowsFormsHost wh = new WindowsFormsHost(); ResultsetGrid rg = new ResultsetGrid(); List <int> readOnlyColumns = new List <int>(); using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring)) { var tpks = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == menuInfo.Name).ToList(); if (tpks.Count == 0) { rg.ReadOnly = true; } List <Column> cols = repository.GetAllColumns(); cols = cols.Where(c => c.TableName == menuInfo.Name).ToList(); int x = 0; foreach (Column col in cols) { if (col.AutoIncrementBy > 0 || col.RowGuidCol) { readOnlyColumns.Add(x); } x++; } } var sqlText = string.Format(Environment.NewLine + "SELECT TOP({0}) * FROM [{1}]", Properties.Settings.Default.MaxRowsToEdit, menuInfo.Name); rg.TableName = sqlText; rg.ConnectionString = menuInfo.Connectionstring; rg.Tag = wh; rg.ReadOnlyColumns = readOnlyColumns; wh.Child = rg; string tabTitle = System.IO.Path.GetFileNameWithoutExtension(menuInfo.Caption) + "-" + menuInfo.Name + "-Edit"; if (rg.ReadOnly) { tabTitle = System.IO.Path.GetFileNameWithoutExtension(menuInfo.Caption) + "-" + menuInfo.Name + "-ReadOnly"; } bool alreadyThere = false; int i = -1; foreach (var item in _parent.FabTab.Items) { i++; if (item is FabTabItem) { FabTabItem ftItem = (FabTabItem)item; if (ftItem.Header.ToString() == tabTitle) { alreadyThere = true; } } } if (alreadyThere) { _parent.FabTab.SelectedIndex = i; _parent.FabTab.Focus(); } else { FabTabItem tab = new FabTabItem(); tab.Content = wh; tab.Header = tabTitle; _parent.FabTab.Items.Add(tab); _parent.FabTab.SelectedIndex = _parent.FabTab.Items.Count - 1; rg.Focus(); } } catch (Exception ex) { MessageBox.Show(DataConnectionHelper.ShowErrors(ex)); } }
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); } } }
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); } } } } } }
public void EditTableData(object sender, ExecutedRoutedEventArgs e) { string sqlText = null; var menuInfo = ValidateMenuInfo(sender); if (menuInfo == null) { return; } bool dbProviderPresent = menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLCE35 && Helpers.DataConnectionHelper.IsV35DbProviderInstalled(); if (menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLCE40 && Helpers.DataConnectionHelper.IsV40DbProviderInstalled()) { dbProviderPresent = true; } if (menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite) { dbProviderPresent = true; } if (!dbProviderPresent) { EnvDTEHelper.ShowError("The required DbProvider registration is not present, please re-install/repair the SQL Server Compact runtime"); return; } try { bool readOnly = false; List <int> readOnlyColumns = new List <int>(); using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo)) { List <PrimaryKey> pks = repository.GetAllPrimaryKeys(); var tpks = repository.GetAllPrimaryKeys().Where(pk => pk.TableName == menuInfo.Name).ToList(); if (tpks.Count == 0) { readOnly = true; } List <Column> cols = repository.GetAllColumns(); cols = cols.Where(c => c.TableName == menuInfo.Name).ToList(); int x = 0; foreach (Column col in cols) { if (col.AutoIncrementBy > 0 || col.RowGuidCol) { readOnlyColumns.Add(x); } x++; } var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType); generator.GenerateTableSelect(menuInfo.Name); sqlText = generator.GeneratedScript.Replace(";" + Environment.NewLine + "GO", ""); sqlText = sqlText.Replace(";" + Environment.NewLine, ""); if (menuInfo.DatabaseInfo.DatabaseType == DatabaseType.SQLite) { sqlText = sqlText + string.Format(" LIMIT {0}", Properties.Settings.Default.MaxRowsToEdit); } else { sqlText = sqlText.Replace(Environment.NewLine + "SELECT ", string.Format(Environment.NewLine + "SELECT TOP({0}) ", Properties.Settings.Default.MaxRowsToEdit)); } } var pkg = ParentWindow.Package as SqlCeToolboxPackage; Debug.Assert(pkg != null, "Package property of the Explorere Tool Window should never be null, have you tried to create it manually and not through FindToolWindow()?"); string dbName = System.IO.Path.GetFileNameWithoutExtension(menuInfo.DatabaseInfo.Caption); var window = pkg.CreateWindow <DataGridViewWindow>(Math.Abs(menuInfo.Name.GetHashCode() - dbName.GetHashCode())); window.Caption = menuInfo.Name + " (" + dbName + ")"; pkg.ShowWindow(window); var control = window.Content as DataEditControl; control.DatabaseInfo = menuInfo.DatabaseInfo; control.TableName = menuInfo.Name; control.ReadOnly = readOnly; control.ReadOnlyColumns = readOnlyColumns; control.SqlText = sqlText; control.ShowGrid(); DataConnectionHelper.LogUsage("TableEdit"); } catch (Exception ex) { DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false); } }
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) { sb.AppendLine(String.Format("UPDATE [{0}] SET {1} WHERE {2}", targetTable, generator.GenerateUpdateFromDataRow(targetTable, sourceRow), whereClause)); sb.AppendLine("GO"); } targetRow++; } return(sb.ToString()); }
/// <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); } // 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); } } }
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); } } } } } }
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)); } }
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) { sb.AppendLine(String.Format("UPDATE [{0}] SET {1} WHERE {2}", targetTable, generator.GenerateUpdateFromDataRow(targetTable, sourceRow), whereClause)); sb.AppendLine("GO"); } targetRow++; } return sb.ToString(); }