public void ExerciseEngineWithTable()
{
using (IRepository sourceRepository = new DB4Repository(sdfConnectionString))
{
var generator = new Generator4(sourceRepository);
using (IRepository targetRepository = new ServerDBRepository4(serverConnectionString))
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, false);
}
}
}
private string CreateSqlDiffScript(string source, string target)
{
using (IRepository sourceRepository = new DB4Repository(source))
{
var diffGenerator = new Generator4(sourceRepository);
using (IRepository targetRepository = new DB4Repository(target))
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, diffGenerator, false);
return(diffGenerator.GeneratedScript);
}
}
}
private static void CreateSqlDiffScript(string source, string target, string outputPath)
{
using (IRepository sourceRepository = new DB4Repository(source))
{
var diffGenerator = new Generator4(sourceRepository);
using (IRepository targetRepository = new DB4Repository(target))
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, diffGenerator);
BuildDiffScript(diffGenerator.GeneratedScript, outputPath);
}
}
}
public void TestDiffNullRef()
{
string target = @"Data Source=C:\Data\SQLCE\Test\DiffNullRefDatabases\ArtistManager.sdf";
string source = @"Data Source=C:\Data\SQLCE\Test\DiffNullRefDatabases\ArtistManagerDesignDatabase.sdf";
using (IRepository sourceRepository = new DB4Repository(source))
{
var generator = new Generator4(sourceRepository);
using (IRepository targetRepository = new DB4Repository(target))
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, false);
}
}
}
public void TestDataDiff()
{
string source = @"Data Source=C:\projects\ChinookPart2\Chinook40Modified.sdf";
string target = @"Data Source=C:\projects\ChinookPart2\Chinook40.sdf";
string modPath = @"C:\projects\ChinookPart2\Chinook40Modified.sdf";
if (File.Exists(modPath))
{
File.Delete(modPath);
}
File.Copy(@"C:\projects\ChinookPart2\Chinook40.sdf", modPath);
using (IRepository sourceRepository = new DB4Repository(source))
{
sourceRepository.ExecuteSql("DELETE FROM InvoiceLine WHERE InvoiceLineId = 2;" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql("DELETE FROM InvoiceLine WHERE InvoiceLineId = 3;" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql("DELETE FROM InvoiceLine WHERE InvoiceLineId = 4;" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql("DELETE FROM InvoiceLine WHERE InvoiceLineId = 1000;" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql(@"INSERT INTO [InvoiceLine] ([InvoiceId],[TrackId],[UnitPrice],[Quantity])
VALUES (100, 500, 10.11, 1)" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql(@"INSERT INTO [InvoiceLine] ([InvoiceId],[TrackId],[UnitPrice],[Quantity])
VALUES (200, 500, 10.11, 1)" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql(@"INSERT INTO [InvoiceLine] ([InvoiceId],[TrackId],[UnitPrice],[Quantity])
VALUES (300, 500, 10.11, 1)" + System.Environment.NewLine + "GO");
sourceRepository.ExecuteSql("UPDATE InvoiceLine SET [UnitPrice]= 99.99 WHERE InvoiceLineId = 20;" + System.Environment.NewLine + "GO");
using (IRepository targetRepository = new DB4Repository(target))
{
var generator = new Generator4(targetRepository);
var script = SqlCeDiff.CreateDataDiffScript(sourceRepository, "InvoiceLine", targetRepository, "InvoiceLine", generator);
Assert.IsTrue(script.Contains("DELETE"));
}
}
}
public void GenerateDiffScript(object sender, ExecutedRoutedEventArgs e)
{
try
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
var databaseList = DataConnectionHelper.GetDataConnections(package, true, false);
foreach (var info in DataConnectionHelper.GetOwnDataConnections())
{
if (!databaseList.ContainsKey(info.Key))
{
databaseList.Add(info.Key, info.Value);
}
}
foreach (var info in databaseList)
{
var sourceType = string.Empty;
switch (info.Value.DatabaseType)
{
case DatabaseType.SQLCE35:
sourceType = "3.5";
break;
case DatabaseType.SQLCE40:
sourceType = "4.0";
break;
case DatabaseType.SQLServer:
sourceType = "Server";
break;
}
info.Value.Caption = string.Format("{0} ({1})", info.Value.Caption, sourceType);
}
var cd = new CompareDialog(databaseInfo.DatabaseInfo.Caption, databaseList);
var result = cd.ShowModal();
if (!result.HasValue || !result.Value || (cd.TargetDatabase.Key == null))
{
return;
}
var target = cd.TargetDatabase;
var swap = cd.SwapTarget;
var source = new KeyValuePair <string, DatabaseInfo>(databaseInfo.DatabaseInfo.ConnectionString, databaseInfo.DatabaseInfo);
if (swap)
{
source = target;
target = new KeyValuePair <string, DatabaseInfo>(databaseInfo.DatabaseInfo.ConnectionString, databaseInfo.DatabaseInfo);
}
var editorTarget = target;
if (editorTarget.Value.DatabaseType == DatabaseType.SQLServer)
{
editorTarget = source;
}
using (var sourceRepository = Helpers.RepositoryHelper.CreateRepository(source.Value))
{
var generator = DataConnectionHelper.CreateGenerator(sourceRepository, databaseInfo.DatabaseInfo.DatabaseType);
using (var targetRepository = Helpers.RepositoryHelper.CreateRepository(target.Value))
{
try
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, Properties.Settings.Default.DropTargetTables);
var sqlEditorWindow = package.CreateWindow <SqlEditorWindow>();
var editorControl = sqlEditorWindow.Content as SqlEditorControl;
if (editorControl != null)
{
editorControl.ExplorerControl = _parentWindow.Content as ExplorerControl;
Debug.Assert(editorControl != null);
editorControl.DatabaseInfo = editorTarget.Value;
var explain = @"-- This database diff script contains the following objects:
-- - Tables: Any that are not in the destination
-- - (tables that are only in the destination are NOT dropped, unless that option is set)
-- - Columns: Any added, deleted, changed columns for existing tables
-- - Indexes: Any added, deleted indexes for existing tables
-- - Foreign keys: Any added, deleted foreign keys for existing tables
-- ** Make sure to test against a production version of the destination database! ** " + Environment.NewLine + Environment.NewLine;
if (swap)
{
explain += "-- ** Please note that the soruce and target have been swapped! ** " + Environment.NewLine + Environment.NewLine;
}
editorControl.SqlText = explain + generator.GeneratedScript;
}
DataConnectionHelper.LogUsage("DatabaseScriptDiff");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
}
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35);
}
}
public void GenerateDataDiffScript(object sender, ExecutedRoutedEventArgs e)
{
try
{
var menuInfo = ValidateMenuInfo(sender);
var package = ParentWindow.Package as SqlCeToolboxPackage;
if (menuInfo == null)
{
return;
}
Dictionary <string, DatabaseInfo> databaseList = DataConnectionHelper.GetDataConnections(package, true, false);
foreach (KeyValuePair <string, DatabaseInfo> info in DataConnectionHelper.GetOwnDataConnections())
{
if (!databaseList.ContainsKey(info.Key))
{
databaseList.Add(info.Key, info.Value);
}
}
foreach (KeyValuePair <string, DatabaseInfo> info in databaseList)
{
string sourceType = string.Empty;
switch (info.Value.DatabaseType)
{
case DatabaseType.SQLCE35:
sourceType = "3.5";
break;
case DatabaseType.SQLCE40:
sourceType = "4.0";
break;
case DatabaseType.SQLServer:
sourceType = "Server";
break;
}
info.Value.Caption = string.Format("{0} ({1})", info.Value.Caption, sourceType);
}
var cd = new CompareDialog(menuInfo.DatabaseInfo.Caption, databaseList, menuInfo.Name);
var result = cd.ShowModal();
if (!result.HasValue || !result.Value || (cd.TargetDatabase.Key == null))
{
return;
}
var target = cd.TargetDatabase;
var source = new KeyValuePair <string, DatabaseInfo>(menuInfo.DatabaseInfo.ConnectionString, menuInfo.DatabaseInfo);
var editorTarget = target;
if (editorTarget.Value.DatabaseType == DatabaseType.SQLServer)
{
editorTarget = source;
}
using (var sourceRepository = DataConnectionHelper.CreateRepository(source.Value))
{
using (var targetRepository = DataConnectionHelper.CreateRepository(target.Value))
{
var generator = DataConnectionHelper.CreateGenerator(targetRepository, target.Value.DatabaseType);
try
{
var script = SqlCeDiff.CreateDataDiffScript(sourceRepository, menuInfo.Name, targetRepository, menuInfo.Name, generator);
if (package != null)
{
var sqlEditorWindow = package.CreateWindow <SqlEditorWindow>();
var editorControl = sqlEditorWindow.Content as SqlEditorControl;
if (editorControl != null)
{
editorControl.ExplorerControl = ParentWindow.Content as ExplorerControl;
editorControl.DatabaseInfo = editorTarget.Value;
editorControl.SqlText = script;
}
}
DataConnectionHelper.LogUsage("TableScriptDataDiff");
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, source.Value.DatabaseType, false);
}
}
}
}
catch (ArgumentException ae)
{
EnvDteHelper.ShowError(ae.Message);
}
catch (Exception ex)
{
DataConnectionHelper.SendError(ex, DatabaseType.SQLCE35, false);
}
}
static int Main(string[] args)
{
if (args.Length < 2 || args.Length > 6)
{
PrintUsageGuide();
return(2);
}
else
{
try
{
string connectionString = args[0];
string outputFileLocation = args[1];
bool includeData = true;
bool includeDataForServer = false;
bool includeSchema = true;
bool saveImageFiles = false;
bool sqlAzure = false;
bool sqlite = false;
bool toExcludeTables = true;
bool toIncludeTables = false;
System.Collections.Generic.List <string> exclusions = new System.Collections.Generic.List <string>();
System.Collections.Generic.List <string> inclusions = new System.Collections.Generic.List <string>();
System.Collections.Generic.List <string> whereClauses = new System.Collections.Generic.List <string>();
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
if (args[0].Equals("diff", StringComparison.OrdinalIgnoreCase))
{
#if V31
PrintUsageGuide();
return(2);
#else
if (args.Length == 4)
{
using (var source = Helper.CreateRepository(args[1]))
{
using (var target = Helper.CreateRepository(args[2]))
{
var generator = Helper.CreateGenerator(source);
SqlCeDiff.CreateDiffScript(source, target, generator, false);
System.IO.File.WriteAllText(args[3], generator.GeneratedScript);
return(0);
}
}
}
else
{
PrintUsageGuide();
return(2);
}
#endif
}
else if (args[0].Equals("dgml", StringComparison.OrdinalIgnoreCase))
{
#if V31
PrintUsageGuide();
return(2);
#endif
if (args.Length == 3)
{
using (var source = Helper.CreateRepository(args[1]))
{
var generator = Helper.CreateGenerator(source, args[2]);
generator.GenerateSchemaGraph(args[1]);
}
return(0);
}
else
{
PrintUsageGuide();
return(2);
}
}
else if (args[0].Equals("wpdc", StringComparison.OrdinalIgnoreCase))
{
#if V31
PrintUsageGuide();
return(2);
#endif
#if V40
PrintUsageGuide();
return(2);
#else
if (args.Length == 3)
{
using (var repo = Helper.CreateRepository(args[1]))
{
var dch = new DataContextHelper();
dch.GenerateWPDataContext(repo, args[1], args[2]);
}
return(0);
}
else
{
PrintUsageGuide();
return(2);
}
#endif
}
else
{
for (int i = 2; i < args.Length; i++)
{
if (args[i].Contains("dataonly"))
{
includeData = true;
includeSchema = false;
}
if (args[i].Contains("dataonlyserver"))
{
includeData = true;
includeDataForServer = true;
includeSchema = false;
}
if (args[i].Contains("schemaonly"))
{
includeData = false;
includeSchema = true;
}
if (args[i].Contains("saveimages"))
{
saveImageFiles = true;
}
if (args[i].Contains("sqlazure"))
{
sqlAzure = true;
}
if (args[i].Contains("sqlite"))
{
sqlite = true;
}
if (args[i].StartsWith("exclude:"))
{
ParseExclusions(exclusions, args[i], whereClauses);
toExcludeTables = true;
toIncludeTables = false;
}
if (args[i].StartsWith("include:"))
{
ParseInclusions(inclusions, args[i], whereClauses);
toIncludeTables = true;
toExcludeTables = false;
}
}
using (IRepository repository = Helper.CreateRepository(connectionString))
{
Console.WriteLine("Initializing....");
Helper.FinalFiles = outputFileLocation;
#if V40
var generator = new Generator4(repository, outputFileLocation, sqlAzure, false, sqlite);
#else
var generator = new Generator(repository, outputFileLocation, sqlAzure, false, sqlite);
#endif
if (toExcludeTables)
{
generator.ExcludeTables(exclusions);
}
else if (toIncludeTables)
{
generator.IncludeTables(inclusions, whereClauses);
}
Console.WriteLine("Generating the tables....");
if (sqlite)
{
generator.GenerateSqlitePrefix();
}
if (includeSchema)
{
#if V31
generator.GenerateTable(false);
#else
generator.GenerateTable(includeData);
#endif
}
if (sqlite)
{
if (includeData)
{
Console.WriteLine("Generating the data....");
generator.GenerateTableContent(false);
}
Console.WriteLine("Generating the indexes....");
generator.GenerateIndex();
generator.GenerateSqliteSuffix();
}
else
{
if (sqlAzure && includeSchema)
{
Console.WriteLine("Generating the primary keys (SQL Azure)....");
generator.GeneratePrimaryKeys();
}
if (includeData)
{
Console.WriteLine("Generating the data....");
generator.GenerateTableContent(saveImageFiles);
if (!includeSchema) // ie. DataOnly
{
Console.WriteLine("Generating IDENTITY reset statements....");
generator.GenerateIdentityResets(includeDataForServer);
}
}
if (!sqlAzure && includeSchema)
{
Console.WriteLine("Generating the primary keys....");
generator.GeneratePrimaryKeys();
}
if (includeSchema)
{
Console.WriteLine("Generating the indexes....");
generator.GenerateIndex();
Console.WriteLine("Generating the foreign keys....");
generator.GenerateForeignKeys();
}
}
Helper.WriteIntoFile(generator.GeneratedScript, outputFileLocation, generator.FileCounter, sqlite);
}
Console.WriteLine("Sent script to output file(s) : {0} in {1} ms", Helper.FinalFiles, (sw.ElapsedMilliseconds).ToString());
return(0);
}
}
catch (System.Data.SqlServerCe.SqlCeException e)
{
Console.WriteLine(Helper.ShowErrors(e));
return(1);
}
catch (System.Data.SqlClient.SqlException es)
{
Console.WriteLine(Helper.ShowErrors(es));
return(1);
}
catch (Exception ex)
{
Console.WriteLine("Error: " + ex);
return(1);
}
}
}
public void GenerateDiffScript(object sender, ExecutedRoutedEventArgs e)
{
var databaseInfo = ValidateMenuInfo(sender);
if (databaseInfo == null)
{
return;
}
try
{
SortedDictionary <string, string> databaseList = Helpers.DataConnectionHelper.GetDataConnections();
foreach (KeyValuePair <string, string> info in databaseList)
{
if (!databaseList.ContainsKey(info.Key))
{
databaseList.Add(info.Key, info.Value);
}
}
CompareDialog cd = new CompareDialog(databaseInfo.Caption, databaseList);
bool?result = cd.ShowDialog();
if (result.HasValue && result.Value == true && (cd.TargetDatabase.Key != null))
{
var target = cd.TargetDatabase.Value;
var source = databaseInfo.Connectionstring;
var editorTarget = target;
using (IRepository sourceRepository = RepoHelper.CreateRepository(source))
{
var generator = RepoHelper.CreateGenerator(sourceRepository);
using (IRepository targetRepository = RepoHelper.CreateRepository(target))
{
try
{
SqlCeDiff.CreateDiffScript(sourceRepository, targetRepository, generator, Properties.Settings.Default.DropTargetTables);
string explain = @"-- This database diff script contains the following objects:
-- - Tables: Any that are not in the destination
-- - (tables that are only in the destination are not dropped)
-- - Columns: Any added, deleted, changed columns for existing tables
-- - Indexes: Any added, deleted indexes for existing tables
-- - Foreign keys: Any added, deleted foreign keys for existing tables
-- ** Make sure to test against a production version of the destination database! ** " + Environment.NewLine + Environment.NewLine;
databaseInfo.Connectionstring = cd.TargetDatabase.Value;
databaseInfo.Caption = cd.TargetDatabase.Key;
OpenSqlEditorToolWindow(databaseInfo, explain + generator.GeneratedScript);
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(Helpers.DataConnectionHelper.ShowErrors(ex));
}
}