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);
}
}
public void ScriptAsAlter(object sender, ExecutedRoutedEventArgs e)
{
try
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo != null)
{
using (IRepository repository = RepoHelper.CreateRepository(menuInfo.Connectionstring))
{
var generator = RepoHelper.CreateGenerator(repository, null);
List <Column> columns = repository.GetAllColumns();
var col = columns.Where(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name).SingleOrDefault();
if (col == null)
{
MessageBox.Show("Could not find the column in the table, has it been dropped?");
}
else
{
generator.GenerateColumnAlterScript(col);
}
_handler.OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
}
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
public void AddIndex(object sender, ExecutedRoutedEventArgs e)
{
var menuInfo = ValidateMenuInfo(sender);
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = Helpers.DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
IndexDialog idxDlg = new IndexDialog(menuInfo.Name);
idxDlg.Columns = repository.GetAllColumns().Where(c => c.TableName == menuInfo.Name).ToList();
if (idxDlg.ShowModal() == true)
{
//var generator = Helpers.DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
Index idx = idxDlg.NewIndex;
StringBuilder _sbScript = new StringBuilder();
_sbScript.Append("CREATE ");
if (idx.Unique)
{
_sbScript.Append("UNIQUE ");
}
_sbScript.AppendFormat("INDEX [{0}] ON [{1}] (", idx.IndexName, idx.TableName);
_sbScript.AppendFormat("[{0}] {1}", idx.ColumnName, idx.SortOrder.ToString());
_sbScript.AppendLine(");");
//foreach (Index col in indexes)
//{
// _sbScript.AppendFormat("[{0}] {1},", col.ColumnName, col.SortOrder.ToString());
//}
//// Remove the last comma
//_sbScript.Remove(_sbScript.Length - 1, 1);
//_sbScript.AppendLine(");");
_sbScript.Append("GO" + Environment.NewLine);
OpenSqlEditorToolWindow(menuInfo, _sbScript.ToString());
Helpers.DataConnectionHelper.LogUsage("TableIndexAdd");
}
}
}
catch (Exception ex)
{
Helpers.DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType, false);
}
}
public void ModifyColumn(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
List <Column> columns = repository.GetAllColumns();
var col = columns.SingleOrDefault(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name);
if (col == null)
{
EnvDteHelper.ShowError("Could not find the column in the table, has it been dropped?");
return;
}
TableBuilderDialog tbd = new TableBuilderDialog(menuInfo.Description, menuInfo.DatabaseInfo.DatabaseType);
tbd.TableColumns = new List <Column> {
col
};
tbd.Mode = 2;
if (tbd.ShowModal() == true && tbd.TableColumns.Count == 1)
{
generator.GenerateColumnAlterScript(tbd.TableColumns[0]);
var script = generator.GeneratedScript;
OpenSqlEditorToolWindow(menuInfo, script);
DataConnectionHelper.LogUsage("TableBuildColumnEdit");
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, menuInfo.DatabaseInfo.DatabaseType);
}
}
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));
}
}
}
}
}
public void ScriptAsCreate(object sender, ExecutedRoutedEventArgs e)
{
var menuItem = sender as MenuItem;
if (menuItem == null)
{
return;
}
var menuInfo = menuItem.CommandParameter as MenuCommandParameters;
if (menuInfo == null)
{
return;
}
try
{
using (IRepository repository = DataConnectionHelper.CreateRepository(menuInfo.DatabaseInfo))
{
var generator = DataConnectionHelper.CreateGenerator(repository, menuInfo.DatabaseInfo.DatabaseType);
List <Column> columns = repository.GetAllColumns();
var col = columns.SingleOrDefault(c => c.TableName == menuInfo.Description && c.ColumnName == menuInfo.Name);
if (col == null)
{
EnvDteHelper.ShowError("Could not find the column in the table, has it been dropped?");
return;
}
else
{
generator.GenerateColumnAddScript(col);
}
OpenSqlEditorToolWindow(menuInfo, generator.GeneratedScript);
DataConnectionHelper.LogUsage("ColumnScriptAsCreate");
}
}
catch (Exception ex)
{
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 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);
}
}
/// <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 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());
}
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 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();
}
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);
}
}
}
