private static void Create(CommonDbContext context)
{
context.Database.Create();
context.ObjContext().ExecuteStoreCommand("CREATE TABLE VersionInfo (Version bigint not null)");
context.ObjContext().ExecuteStoreCommand("CREATE NONCLUSTERED INDEX IX_Tickets_LastPaymentDate ON Tickets(LastPaymentDate)");
GetMigrateVersions(context);
LocalSettings.CurrentDbVersion = LocalSettings.DbVersion;
}
private static void Create(CommonDbContext context)
{
try
{
int versionNumber = 0;
if (LocalSettings.RecreateDatabase)
{
if (context.Database.Exists())
{
context.ObjContext().DeleteDatabase();
}
versionNumber = 1;
}
//Note - there is a bug with setting the default command timeout in the connection string
//https://ttntuyen.wordpress.com/2017/05/30/entity-framework-the-wait-operation-timed-out/
((IObjectContextAdapter)context).ObjectContext.CommandTimeout = 60 * 15;
try
{
context.Database.CreateIfNotExists();
}
catch (Exception ex)
{
try
{
// If we are connecting to an Azure database it tends to timeout before the tables are created
if (context.Database.Exists())
{
//Wait ten seconds to give Azure time to complete
Thread.Sleep(10000);
var dbScript = context.ObjContext().CreateDatabaseScript();
var result = context.ObjContext().ExecuteStoreCommand(dbScript);
}
}
catch (Exception ex2)
{
throw;
}
}
ExecuteCommandIfNotExists(context, "CREATE TABLE VersionInfo (Version bigint not null)", "VersionInfo");
ExecuteCommandIfNotExistsAndExists(context, "CREATE NONCLUSTERED INDEX IX_Tickets_LastPaymentDate ON Tickets(LastPaymentDate)", "IX_Tickets_LastPaymentDate", "Tickets");
ExecuteCommandIfNotExistsAndExists(context, "CREATE UNIQUE INDEX IX_EntityStateValue_EntityId ON EntityStateValues (EntityId)", "IX_EntityStateValue_EntityId", "EntityStateValues");
context.ObjContext().SaveChanges();
SetMigrateVersions(context);
LocalSettings.CurrentDbVersion = LocalSettings.DbVersion;
}
catch (Exception ex)
{
context.ObjContext().DeleteDatabase();
}
}
private static void ApplyV16Fix(CommonDbContext context)
{
try
{
context.ObjContext().ExecuteStoreCommand("Alter Table PaymentTypeMaps drop column DisplayAtPaymentScreen");
}
catch { }
try
{
context.ObjContext().ExecuteStoreCommand("Alter Table PaymentTypeMaps drop column DisplayUnderTicket");
}
catch { }
}
private static void Create(CommonDbContext context)
{
context.Database.Create();
context.ObjContext().ExecuteStoreCommand("CREATE TABLE VersionInfo (Version bigint not null)");
context.ObjContext().ExecuteStoreCommand("CREATE NONCLUSTERED INDEX IX_Tickets_LastPaymentDate ON Tickets(LastPaymentDate)");
if (!context.Database.Connection.ConnectionString.ToLower().Contains(".sdf"))
{
context.ObjContext().ExecuteStoreCommand("CREATE NONCLUSTERED INDEX IDX_TicketItems_All ON TicketItems (TicketId) INCLUDE (Id,MenuItemId,MenuItemName,PortionName,Price,CurrencyCode,Quantity,PortionCount,Locked,Voided,ReasonId,Gifted,OrderNumber,CreatingUserId,CreatedDateTime,ModifiedUserId,ModifiedDateTime,PriceTag,Tag,DepartmentId,VatRate,VatAmount,VatTemplateId,VatIncluded)");
context.ObjContext().ExecuteStoreCommand("CREATE NONCLUSTERED INDEX IDX_TicketItemProperties_All ON TicketItemProperties (TicketItemId) INCLUDE (Id,Name,PropertyPrice_CurrencyCode,PropertyPrice_Amount,PropertyGroupId,Quantity,MenuItemId,PortionName,CalculateWithParentPrice,VatAmount)");
context.ObjContext().ExecuteStoreCommand("CREATE NONCLUSTERED INDEX IDX_Payments_All ON Payments (Ticket_Id) INCLUDE (Id,Amount,Date,PaymentType,UserId,DepartmentId)");
}
GetMigrateVersions(context);
LocalSettings.CurrentDbVersion = LocalSettings.DbVersion;
}
private static void Migrate(CommonDbContext context)
{
if (!File.Exists(LocalSettings.UserPath + "\\migrate.txt"))
{
return;
}
var preVersion = context.ObjContext().ExecuteStoreQuery <long>("select top(1) Version from VersionInfo order by version desc").FirstOrDefault();
var db = context.Database.Connection.ConnectionString.Contains(".sdf") ? "sqlserverce" : "sqlserver";
if (preVersion < 18 && db == "sqlserverce")
{
ApplyV16Fix(context);
}
IAnnouncer announcer = new TextWriterAnnouncer(Console.Out);
IRunnerContext migrationContext =
new RunnerContext(announcer)
{
ApplicationContext = context,
Connection = context.Database.Connection.ConnectionString,
Database = db,
Target = LocalSettings.AppPath + "\\Magentix.Persistance.DbMigration.dll"
};
new TaskExecutor(migrationContext).Execute();
File.Delete(LocalSettings.UserPath + "\\migrate.txt");
}
private static void GetMigrateVersions(CommonDbContext context)
{
for (var i = 0; i < LocalSettings.DbVersion; i++)
{
context.ObjContext().ExecuteStoreCommand("Insert into VersionInfo (Version) Values (" + (i + 1) + ")");
}
}
public static void ExecuteCommandIfNotExists(CommonDbContext context, string commandText, string objectName, string schemaName = "")
{
if (DoesObjectExistInDatabase(context, objectName, schemaName) == false)
{
context.ObjContext().ExecuteStoreCommand(commandText);
}
}
public static bool DoesObjectExistInDatabase(CommonDbContext context, string objectName, string objectSchemaName = "")
{
string queryUnformatted = @"select count(*) From
(
select sysobjects.[name], sysobjects.id, OBJECT_SCHEMA_NAME(id) as schema_name from sysobjects
where lower(sysobjects.[name]) = lower('{0}')
) as innerQuery
where lower('{1}') = '' OR lower('{1}') = schema_name";
var query = string.Format(queryUnformatted, objectName, objectSchemaName);
var queryResult = context.ObjContext().ExecuteStoreQuery <int>(query).FirstOrDefault();
return(queryResult > 0);
}
private static void SetMigrateVersions(CommonDbContext context)
{
for (var i = 0; i < LocalSettings.DbVersion; i++)
{
var versionNumber = (i + 1).ToString();
var queryText = "if Not Exists(select 'x' From VersionInfo where Version = '" + versionNumber + "' ) begin Insert into VersionInfo (Version) Values (" + versionNumber + ") end";
try
{
context.ObjContext().ExecuteStoreCommand(queryText);
}
catch (Exception ex)
{
throw;
}
}
}
public static long GetVersionNumber(CommonDbContext context)
{
var version = context.ObjContext().ExecuteStoreQuery <long>("select top(1) Version from VersionInfo order by version desc").FirstOrDefault();
return(version);
}