public override string GenerateCreateTableFullDdl([NotNull] DatabaseConnection databaseConnection, string database, string schema, [NotNull] string tableName)
{
if (databaseConnection == null)
{
throw new ArgumentNullException("databaseConnection");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
var repo = new DB4Repository(databaseConnection.ConnectionString);
var generator = new Generator4(repo);
generator.GenerateTableCreate(tableName);
var infoProvider = databaseConnection.DatabaseServer.GetInfoProvider();
using (var connection = databaseConnection.CreateNewConnection())
{
connection.OpenIfRequired();
var indexes = infoProvider.GetIndexesForTable(connection, schema, tableName);
foreach (var index in indexes)
{
generator.GenerateIndexScript(tableName, index.Name);
}
}
return(generator.GeneratedScript);
}
public override string GenerateCreateIndexDdl(DatabaseConnection databaseConnection, string database, string indexSchema, string indexName, object indexId)
{
var repo = new DB4Repository(databaseConnection.ConnectionString);
string tableName;
using (var connection = databaseConnection.CreateNewConnection())
{
connection.OpenIfRequired();
using (var command = connection.CreateCommand())
{
command.BuildSqlCommand(
"SELECT TOP 1 TABLE_NAME from information_schema.indexes WHERE UPPER(INDEX_NAME) = @1", "@",
indexName.Trim().ToUpper());
using (var dr = command.ExecuteReader())
{
if (dr.Read())
{
tableName = dr.GetString(0).Trim().ToUpper();
}
else
{
throw new Exception("Index " + indexName + " does not exist in the database");
}
}
}
}
var generator = new Generator4(repo);
generator.GenerateIndexScript(tableName, indexName);
return(generator.GeneratedScript);
}
public void TestGraphSortComplex()
{
using (IRepository sourceRepository = new DB4Repository(sdfConnectionString2))
{
var generator = new Generator4(sourceRepository, @"C:\temp\testPZ.sqlce");
generator.ExcludeTables(new System.Collections.Generic.List <String>());
}
}
public void TestCeDgml()
{
using (IRepository sourceRepository = new DB4Repository(chinookConnectionString))
{
var generator = new Generator4(sourceRepository, @"C:\temp\testChinook40.dgml");
generator.GenerateSchemaGraph(chinookConnectionString);
}
}
public void TestCeDgml()
{
using (IRepository sourceRepository = new DB4Repository(chinookConnectionString))
{
var generator = new Generator4(sourceRepository, @"C:\temp\testChinook40.dgml");
generator.GenerateSchemaGraph(chinookConnectionString);
}
}
private void RunMigration(string localDbPath, string[] tablesToIgnoreOrAppend, string targetConnectionString, Scope scope, bool removeTempFiles)
{
using (var repository = new DB4Repository($"Data Source={localDbPath};Max Database Size=4000"))
{
var scriptRoot = Path.GetTempFileName();
var tempScript = scriptRoot + ".sqltb";
var generator = new Generator4(repository, tempScript);
if (scope == Scope.DataOnlyForSqlServerIgnoreIdentity)
{
//Ignore all tables except the ones in tablesToAppend
var tables = repository.GetAllTableNames();
var list = tables.Except(tablesToIgnoreOrAppend.ToList());
generator.ExcludeTables(list.ToList());
}
else
{
generator.ExcludeTables(tablesToIgnoreOrAppend.ToList());
}
generator.ScriptDatabaseToFile(scope);
using (var serverRepository = new ServerDBRepository4(targetConnectionString))
{
try
{
//Handles large exports also...
if (File.Exists(tempScript)) // Single file
{
serverRepository.ExecuteSqlFile(tempScript);
if (removeTempFiles)
{
TryDeleteFile(tempScript);
}
}
else // possibly multiple files - tmp2BB9.tmp_0.sqlce
{
for (var i = 0; i < 400; i++)
{
var testFile = string.Format("{0}_{1}{2}", scriptRoot, i.ToString("D4"), ".sqltb");
if (File.Exists(testFile))
{
serverRepository.ExecuteSqlFile(testFile);
if (removeTempFiles)
{
TryDeleteFile(testFile);
}
}
}
}
}
catch (Exception ex)
{
CreateBlockFile(localDbPath, ex);
throw;
}
}
}
}
public void TestDataGenWithoutWhere()
{
using (IRepository sourceRepository = new DB4Repository(@"Data Source=c:\tmp\sqlce\Chinook.sdf"))
{
var generator = new Generator4(sourceRepository);
generator.GenerateTableContent("Album", false);
var script = generator.GeneratedScript;
}
}
//https://sqlcetoolbox.codeplex.com/workitem/11165
public void TestSqlParse2()
{
string sql = "select\t\r\ncount(*) FROM Album\r\nGO";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
public void TestExportToSqlServer()
{
var path = @"C:\temp\testnw.sql";
using (var sourceRepository = new DB4Repository(northwindConn))
{
var generator = new Generator4(sourceRepository, path);
generator.ScriptDatabaseToFile(Scope.SchemaData);
}
}
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);
}
}
}
public void TestGetAllColumns()
{
var list = new List <Column>();
using (IRepository repo = new DB4Repository(northwindConn))
{
list = repo.GetAllColumns();
}
Assert.AreEqual(74, list.Count);
Assert.AreEqual("int", list[0].DataType);
}
public void TestExportToSqlServer2()
{
var path = @"C:\temp\testum.sql";
using (var sourceRepository = new DB4Repository(umbracoConn))
{
var generator = new Generator4(sourceRepository, path, false, false, false);
generator.ExcludeTables(new List <string>());
generator.ScriptDatabaseToFile(Scope.SchemaData);
}
}
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);
}
}
}
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 TestSqlParse3()
{
string sql = @"INSERT INTO Databases (Source, FileName, CeVersion) VALUES ('Data Source=C:\Data\SQLCE\Test\ams40.sdf', 'ams40.sdf', 1);
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
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);
}
}
}
public void TestServerMigration()
{
string path = @"C:\temp\testChinook40.sqlce";
using (IRepository sourceRepository = new DB4Repository(chinookConnectionString))
{
var generator = new Generator4(sourceRepository, path);
generator.GenerateAllAndSave(true, false, false, false);
}
Assert.IsTrue(System.IO.File.Exists(path));
using (IRepository serverRepository = new ServerDBRepository4(migrateConnectionString))
{
serverRepository.ExecuteSqlFile(path);
}
}
public void TestSqlParse4()
{
string sql = @"
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
GO";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
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 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 TestServerMigration()
{
string path = @"C:\temp\testChinook40.sqlce";
using (IRepository sourceRepository = new DB4Repository(chinookConnectionString))
{
var generator = new Generator4(sourceRepository, path);
generator.GenerateAllAndSave(true, false, false, false);
}
Assert.IsTrue(System.IO.File.Exists(path));
using (IRepository serverRepository = new ServerDBRepository4(migrateConnectionString))
{
serverRepository.ExecuteSqlFile(path);
}
}
//https://sqlcetoolbox.codeplex.com/workitem/11165
public void TestSqlParse1()
{
string sql = @"select
count(*)
from
Album
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
public override string GenerateCreateTableDdl([NotNull] DatabaseConnection databaseConnection, string database, string schema,
[NotNull] string tableName)
{
if (databaseConnection == null)
{
throw new ArgumentNullException("databaseConnection");
}
if (tableName == null)
{
throw new ArgumentNullException("tableName");
}
var repo = new DB4Repository(databaseConnection.ConnectionString);
var generator = new Generator4(repo);
generator.GenerateTableCreate(tableName);
return(generator.GeneratedScript);
}
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 TestSqlText()
{
string sql = @"-- Script Date: 13-03-2012 20:03 - Generated by ExportSqlCe version 3.5.2.7
-- Database information:
-- Locale Identifier: 1030
-- Encryption Mode:
-- Case Sensitive: False
-- Database: C:\data\sqlce\test\nw40.sdf
-- ServerVersion: 4.0.8854.1
-- DatabaseSize: 1499136
-- Created: 11-07-2010 10:46
-- User Table information:
-- Number of tables: 9
-- Categories: 8 row(s)
-- Customers: 91 row(s)
-- ELMAH: -1 row(s)
-- Employees: 15 row(s)
-- Order Details: 2820 row(s)
-- Orders: 1078 row(s)
-- Products: 77 row(s)
-- Shippers: 15 row(s)
-- Suppliers: 29 row(s)
SET IDENTITY_INSERT [Suppliers] ON;
GO
INSERT INTO [Suppliers] ([Supplier ID],[Company Name],[Contact Name],[Contact Title],[Address],[City],[Region],[Postal Code],[Country],[Phone],[Fax]) VALUES (1,N'Exotic Liquids',N'Charlotte Cooper',N'Purchasing Manager',N'49 Gilbert St.',N'London',null,N'EC1 4SD',N'UK',N'(71) 555-2222',null);
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
Assert.IsTrue(ds.Tables.Count > 0);
Assert.IsTrue(ds.Tables[0].Rows.Count > 0);
}
}
public void TestSqlParse4()
{
string sql = @"
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
GO";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
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 TestDataGenWithoutWhere()
{
using (IRepository sourceRepository = new DB4Repository(@"Data Source=c:\tmp\sqlce\Chinook.sdf"))
{
var generator = new Generator4(sourceRepository);
generator.GenerateTableContent("Album", false);
var script = generator.GeneratedScript;
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
var builder = new DbConnectionStringBuilder()
{
ConnectionString = GlobalSettings.DbDSN
};
if (builder.ContainsKey("datalayer"))
{
var dataLayer = builder["datalayer"].ToString();
if (string.Equals(dataLayer, "SQLCE4Umbraco.SqlCEHelper,SQLCE4Umbraco", StringComparison.OrdinalIgnoreCase))
{
builder.Remove("datalayer");
var sqlcePath = builder.ContainsKey("datasource") ? builder["datasource"].ToString() : builder["data source"].ToString();
// resolve the 'DataDirectory'
if (builder.ConnectionString.Contains("|DataDirectory|"))
{
var dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
if (!dataDirectory.EndsWith("\\"))
{
dataDirectory = string.Concat(dataDirectory, "\\");
}
sqlcePath = sqlcePath.Replace("|DataDirectory|", dataDirectory);
}
// check if file exists
if (!File.Exists(sqlcePath))
{
return; // FAIL
}
// set-up some vars
var datestamp = DateTime.UtcNow.ToString("_yyyyMMddHHmmss");
var fileInfo = new FileInfo(sqlcePath);
var originalFileName = fileInfo.Name.Replace(fileInfo.Extension, string.Empty);
var exportFileName = string.Concat(originalFileName, datestamp, ".sql");
var exportPath = Server.MapPath("~/App_Data/" + exportFileName);
var tempPath = sqlcePath.Replace(fileInfo.Extension, string.Concat(datestamp, fileInfo.Extension));
// make a copy of the db
File.Copy(sqlcePath, tempPath);
// replace the conn string
if (builder.ContainsKey("datasource"))
{
builder["datasource"] = tempPath;
}
else
{
builder["data source"] = tempPath;
}
// export db
using (var repository = new DB4Repository(builder.ConnectionString))
{
var generator = new Generator4(repository, exportPath);
var scope = this.rblScope.SelectedValue.Equals("data") ? Scope.SchemaData : Scope.Schema;
// save db script to file
generator.ScriptDatabaseToFile(scope);
}
// delete the temp db
File.Delete(tempPath);
// get the bytes from the export script
var bytes = File.ReadAllBytes(exportPath);
// check if the export script should be zipped
if (this.cbZip.Checked)
{
using (var zip = new ZipOutputStream(Response.OutputStream))
{
var entry = new ZipEntry(ZipEntry.CleanName(exportFileName));
zip.PutNextEntry(entry);
zip.Write(bytes, 0, bytes.Length);
}
// push the zip file to the browser
Response.AddHeader("content-disposition", string.Concat("attachment; filename=", exportFileName, ".zip"));
Response.ContentType = "application/zip";
}
else
{
// push the export script to the browser
Response.AddHeader("content-disposition", string.Concat("attachment; filename=", exportFileName));
Response.ContentType = "text/x-sql";
Response.BinaryWrite(bytes);
}
Response.Flush();
Response.End();
}
}
}
public void TestSqlParse3()
{
string sql = @"INSERT INTO Databases (Source, FileName, CeVersion) VALUES ('Data Source=C:\Data\SQLCE\Test\ams40.sdf', 'ams40.sdf', 1);
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
public void TestSqlText()
{
string sql = @"-- Script Date: 13-03-2012 20:03 - Generated by ExportSqlCe version 3.5.2.7
-- Database information:
-- Locale Identifier: 1030
-- Encryption Mode:
-- Case Sensitive: False
-- Database: C:\data\sqlce\test\nw40.sdf
-- ServerVersion: 4.0.8854.1
-- DatabaseSize: 1499136
-- Created: 11-07-2010 10:46
-- User Table information:
-- Number of tables: 9
-- Categories: 8 row(s)
-- Customers: 91 row(s)
-- ELMAH: -1 row(s)
-- Employees: 15 row(s)
-- Order Details: 2820 row(s)
-- Orders: 1078 row(s)
-- Products: 77 row(s)
-- Shippers: 15 row(s)
-- Suppliers: 29 row(s)
SET IDENTITY_INSERT [Suppliers] ON;
GO
INSERT INTO [Suppliers] ([Supplier ID],[Company Name],[Contact Name],[Contact Title],[Address],[City],[Region],[Postal Code],[Country],[Phone],[Fax]) VALUES (1,N'Exotic Liquids',N'Charlotte Cooper',N'Purchasing Manager',N'49 Gilbert St.',N'London',null,N'EC1 4SD',N'UK',N'(71) 555-2222',null);
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
Assert.IsTrue(ds.Tables.Count > 0);
Assert.IsTrue(ds.Tables[0].Rows.Count > 0);
}
}
//https://sqlcetoolbox.codeplex.com/workitem/11165
public void TestSqlParse1()
{
string sql = @"select
count(*)
from
Album
GO
";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
public void TestGraphSortComplex()
{
using (IRepository sourceRepository = new DB4Repository(sdfConnectionString2))
{
var generator = new Generator4(sourceRepository, @"C:\temp\testPZ.sqlce");
generator.ExcludeTables(new System.Collections.Generic.List<String>());
}
}
//https://sqlcetoolbox.codeplex.com/workitem/11165
public void TestSqlParse2()
{
string sql = "select\t\r\ncount(*) FROM Album\r\nGO";
using (IRepository repo = new DB4Repository(chinookConnectionString))
{
string showPlan = string.Empty;
var ds = repo.ExecuteSql(sql, out showPlan);
}
}
