private async Task executeTestAsync() { using (FileLog l = new FileLog("MdbContextTest", new FileLogSettings() { DateFolderMask = "yyyy-MM-dd" })) { MdbContextOptions.GetOptions().CommandTimeout = 120; using (var ctx = new MdbContext(_cn)) { l.Debug("Test 1 Start "); int i = 0; string sql = @"Create Table TestTable( ID uniqueidentifier not null primary key, Name varchar(128) not null, DateOper datetime not null, Notes varchar(256) null, Handle int not null)" ; i = await ctx.ExecuteAsync(sql); l.Debug($"Test 1 Finish rows result {i}"); l.Debug("Test 2 Start "); sql = @"Insert Into TestTable (ID, Name, DateOper, Handle) Values(@ID, @Name, @DateOper, @Handle)" ; i = 0; for (; i < 1000; i++) { await ctx.ExecuteAsync(sql, new MdbParameter("@ID", Guid.NewGuid()), new MdbParameter("@Name", "Тестовая строка № " + i.ToString()), new MdbParameter("@DateOper", vbo.Date().AddDays(i - 1000)), new MdbParameter("@Handle", i) { NullIfEmpty = false });; } l.Debug($"Test 2 Finish rows result {i}"); l.Debug("Test 3 Start "); i = 0; using (var dr = await ctx.GetReaderAsync("Select * From TestTable Order By Handle")) { for (; dr.Read(); i++) { l.Debug(dr.GetRowJSON()); } } l.Debug($"Test 3 Finish rows result {i}"); l.Debug("Test 4 Start "); sql = "Drop Table TestTable"; i = await ctx.ExecuteAsync(sql); l.Debug($"Test 4 Finish rows result {i}"); } } }
private static async Task <string> GetDefaultDbSchemaAsync(MdbContext mdb) { if (_defaultDbSchema.IsEmpty()) { _defaultDbSchema = await mdb.ExecuteAsync <string>(SQLite.GetDefaultSchema); } return(_defaultDbSchema); }
private static async Task CreateDbSchemaAsync(MdbContext mdb, ILogger log) { try { await mdb.BeginTransactionAsync(); var scripts = SQLite.CreateSchemaObjects_12.Split(new string[] { "--go", "--GO" }, StringSplitOptions.RemoveEmptyEntries); foreach (string statement in scripts) { await mdb.ExecuteAsync(statement); } log.LogDebug($"Schema SysCat was created in database {mdb.DbName}"); log.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema"); string owner = await GetDefaultDbSchemaAsync(mdb); long id = await mdb.ExecuteAsync <long>(SQLite.AddSysAreas, new MdbParameter("@SchemaName", owner), new MdbParameter("@SchemaOwner", owner), new MdbParameter("@SchemaVersion", schema_version), new MdbParameter("@SchemaConfig", "") { NullIfEmpty = true }, new MdbParameter("@IsDefault", 1), new MdbParameter("@UpdateTime", DateTime.Now), new MdbParameter("@DateBegin", DateTime.Now.Date), new MdbParameter("@DateEnd", DateTime.MinValue) { NullIfEmpty = true }); id = await mdb.ExecuteAsync <long>(SQLite.AddSysAreas, new MdbParameter("@SchemaName", "SysCat"), new MdbParameter("@SchemaOwner", owner), new MdbParameter("@SchemaVersion", schema_version), new MdbParameter("@SchemaConfig", "") { NullIfEmpty = true }, new MdbParameter("@IsDefault", 1), new MdbParameter("@UpdateTime", DateTime.Now), new MdbParameter("@DateBegin", DateTime.Now.Date), new MdbParameter("@DateEnd", DateTime.MinValue) { NullIfEmpty = true }); await mdb.CommitAsync(); } catch (Exception e) { mdb.RollBack(); log.LogError($"CreateSchema error: {e.Message}"); throw; } }
/// <summary> /// required teting /// </summary> /// <param name="dbSchema"></param> /// <param name="objectName"></param> /// <returns></returns> private async Task <JMXSchema> SyncSchemaAsync(JMXObjectName objectName) { MdbContext mdb = Factory.GetMdbContext(); ILogger log = this.Logger; var schema = await _schemaRepo.GetSchemaAsync(objectName); //schema was syncronized //change this logic if (schema.SyncState == 1) { return(schema); } foreach (var o in GetDependences(schema)) { await SyncSchemaAsync(o); } var schemaFromDb = await GetObjectSchemaAsync(schema.DbObjectName.ToString()); bool createNew = (schemaFromDb == null); string[] sqlList; if (createNew) { sqlList = CreateNewStatements(schema); } else { // Compare with previos version of schema // error schema not found if db objects exists, but // synced version schema don't exists //var prevSchema = await GetSchemaInternalAsync(mdb, dbSchema, objectName, 1); //!!! not tested var prevSchema = schemaFromDb; sqlList = await CompareSchemasAsync(schema, prevSchema); } await mdb.BeginTransactionAsync(); try { foreach (var sql in sqlList) { log.LogDebug(sql); await mdb.ExecuteAsync(sql); } schema = await _schemaRepo.SetSchemaStateAsync(schema.ObjectName, 1); await mdb.CommitAsync(); } catch { mdb.RollBack(); throw; } return(schema); }
static async Task dropTestTableAsync(MdbContext ctx, FileLogger l) { l.Debug("Drop Test Table Start"); int i = 0; string sql = @"Drop Table TestTable"; i = await ctx.ExecuteAsync(sql); l.Debug($"Drop Test Table Finish rows result {i}"); }
public async Task <DataPackage> InvokeAsync(ActionInfo ai, DataPackage dp) { DateTime date = (DateTime)dp["@Date"]; string branchID = (string)dp["@BranchID"]; string dealType = (string)dp["@DealType"]; int confirmed = (int)dp["@Confirmed"]; string batch = (string)dp["@Batch"]; var ctx = ai.GetContext(); var cs = ctx.Services .GetRequiredService <IConfiguration>() .GetSection($"connectionStrings:{ctx.ConnectionName}").Get <ConnectInfo>(); string[] filials = DealValuesCalculate.GetParamListData(ai, "@BranchID", branchID, "0"); string[] dealTypes = DealValuesCalculate.GetParamListData(ai, "@DealType", dealType, "Все"); var pipe = ctx.Services .GetRequiredService <PipeQueue>(); StringBuilder sb = new StringBuilder(); foreach (var filial in filials) { foreach (var dtype in dealTypes) { using (MdbContext mdb = new MdbContext(cs)) { try { await mdb.ExecuteAsync($@" exec workdb..mib_msfo_Reserve_OperGen @Date = '{date.ToString("yyyyMMdd")}' ,@BranchID = {filial} ,@DealType = '{dtype}' ,@Confirmed = {confirmed} ,@Batch = '{batch}'" ); string result = $"Success mib_msfo_Reserve_OperGen {date} filial={filial} deal type={dtype}"; pipe.Write(ctx, result); sb.AppendLine(result); } catch (Exception ex) { string message = $"{LogLevels.Error} {ex.Message}\n{ex.StackTrace}"; pipe.Write(ctx, message); sb.AppendLine(message); } } } } return(ai.GetOutputParamTable() .AddNew() .SetValue("@Result", sb.ToString()) .Update()); }
public override async Task DeleteSchemaAsync(string objectName) { JMXObjectName name = objectName; MdbContext mdb = Factory.GetMdbContext(); var schema = await GetSchemaAsync(mdb, name.AreaName, name.ObjectName); await mdb.ExecuteAsync(SQLite.DelSysSchemas, new MdbParameter("@ID", schema.ID)); _schemaCache.Remove(name.ToString()); }
private async Task CreateDbSchemaAsync() { MdbContext mdb = Factory.GetMdbContext(); try { await mdb.BeginTransactionAsync(); var scripts = (await SqlServerHelper.IsSql17(mdb)) ? SqlServer.CreateSchemaObjects.Split(new string[] { "--go", "--GO" }, StringSplitOptions.RemoveEmptyEntries) : SqlServer.CreateSchemaObjects_12.Split(new string[] { "--go", "--GO" }, StringSplitOptions.RemoveEmptyEntries); foreach (string statement in scripts) { await mdb.ExecuteAsync(statement); } Logger.LogDebug($"Schema SysCat was created in database {mdb.DbName}"); Logger.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema"); string owner = await GetDefaultDbSchemaAsync(); int id = await mdb.ExecuteAsync <int>(SqlServer.AddSysAreas, new MdbParameter("@SchemaName", owner), new MdbParameter("@SchemaOwner", owner), new MdbParameter("@SchemaVersion", schema_version)); id = await mdb.ExecuteAsync <int>(SqlServer.AddSysAreas, new MdbParameter("@SchemaName", "SysCat"), new MdbParameter("@SchemaOwner", owner), new MdbParameter("@SchemaVersion", schema_version)); await mdb.CommitAsync(); } catch (Exception e) { mdb.RollBack(); Logger.LogError($"CreateSchema error: {e.Message}"); throw; } }
private static async Task RecreateSchemaAsync(MdbContext mdb, SQLStatementWriter sb, JMXSchema schema, JMXSchema fromDbSchema) { int recCount = await mdb.ExecuteAsync <int>($"select count(*) from {fromDbSchema.DbObjectName}"); foreach (var fk in fromDbSchema.ParentRelations) { sb.WriteDropParentRelationStatement(fk); } foreach (var fk in fromDbSchema.ForeignKeys) { sb.WriteDropFKStatement(fk, fromDbSchema); } string tmpTableName = fromDbSchema.DbObjectName.ObjectName + "_" + DateTime.Now.Subtract(vbo.Date()).Seconds.ToString(); if (recCount > 0) { sb.WriteRenameTableStatement(tmpTableName, fromDbSchema); } else { sb.WriteDropTableStatement(null, fromDbSchema); } sb.WriteCreateTableStatements(schema); if (recCount > 0) { sb.WriteInsertRowsStatement(tmpTableName, schema); sb.WriteDropTableStatement(tmpTableName, fromDbSchema); } sb.WriteCreatePKStatement(schema); foreach (var att in schema.Attributes) { sb.WriteCreateConstraintStatement(att, schema); } foreach (var index in schema.Indexes) { sb.WriteCreateIndexStatement(index, schema); } foreach (var fk in schema.ForeignKeys) { sb.WriteCreateFKStatement(fk, schema); } /// Need test foreach (var fk in fromDbSchema.ParentRelations) { sb.WriteCreateParentRelationStatement(fk); } }
private static async Task DropDbSchemaAsync(MdbContext mdb, ILogger log) { var test = await mdb.ExecuteAsync <string>(SQLite.TestSchema); if (!test.IsEmpty()) { try { await mdb.ExecuteAsync(SQLite.DropSchema); log.LogDebug($"Schema SysCat was deleted from database {mdb.DbName}"); } catch (Exception e) { log.LogError($"Delete Schema error: {e.Message}"); throw; } } else { log.LogDebug($"Schema SysCat not exists in database {mdb.DbName}"); } }
static async Task createTestTableAsync(MdbContext ctx, FileLogger l) { l.Debug("Create Test Table Start"); int i = 0; string sql = @"Create Table TestTable( ID uniqueidentifier not null primary key, Name varchar(128) not null, DateOper datetime not null, Notes varchar(256) null, Handle int not null)" ; await ctx.BeginTransactionAsync(); i = await ctx.ExecuteAsync(sql); await ctx.CommitAsync(); }
public static async Task <string> GetSqlVersion(MdbContext mdb) { if (_sqlVersion.IsEmpty()) { _sqlVersion = await mdb.ExecuteAsync <string>(SQLite.SQLVersion); if (_sqlVersion.IsEmpty()) { _sqlVersion = "3"; } else { _sqlVersion = _sqlVersion.GetToken(0, "."); } } return(_sqlVersion); }
public async Task <DataPackage> InvokeAsync(ActionInfo ai, DataPackage dp) { string objectName = (string)dp["@ObjectName"]; long[] ids = ((string)dp["@IDs"]) .Split(new char[] { ',', ';' }) .Select(s => s.ToLongOrDefault()) .ToArray(); var ctx = ai.GetContext(); var cs = ctx.Services .GetRequiredService <IConfiguration>() .GetSection($"connectionStrings:{ctx.ConnectionName}").Get <ConnectInfo>(); var pipe = ctx.Services .GetRequiredService <PipeQueue>(); StringBuilder sb = new StringBuilder(); foreach (long i in ids) { using (MdbContext mdb = new MdbContext(cs)) { try { string result = await mdb.ExecuteAsync <string>($@" select 'Session ID = ' + cast(@@SPID as varchar(10)) + ' User Name = ' + '{ctx.UserName}' + ' Connection Name = ' + '{ctx.ConnectionName}' + ' Param Value = ' + '{i}'" ); pipe.Write(ctx, result); sb.AppendLine(result); } catch (Exception ex) { string message = $"{LogLevels.Error} {ex.Message}\n{ex.StackTrace}"; } } } return(ai.GetOutputParamTable() .AddNew() .SetValue("@Result", sb.ToString()) .Update()); }
private static async Task <string> GetSqlVersion(MdbContext mdb) { int mdbHash = mdb.MdbHash(); if (!_versions.TryGetValue(mdbHash, out string sqlVersion)) { sqlVersion = await mdb.ExecuteAsync <string>(SqlServer.SQLVersion); if (sqlVersion.IsEmpty()) { sqlVersion = "10"; } else { sqlVersion = sqlVersion.GetToken(0, "."); } _versions.Add(mdbHash, sqlVersion); } return(sqlVersion); }
static async Task insertTestTableAsync(MdbContext ctx, FileLogger l) { l.Debug("Insert Test Table Start"); string sql = @"Insert Into TestTable (ID, Name, DateOper, Handle) Values(@ID, @Name, @DateOper, @Handle)" ; await ctx.BeginTransactionAsync(); int i = 0; for (; i < 100; i++) { await ctx.ExecuteAsync(sql, new MdbParameter("@ID", Guid.NewGuid()), new MdbParameter("@Name", "Тестовая строка № " + i.ToString()), new MdbParameter("@DateOper", vbo.Date().AddDays(i - 1000)), new MdbParameter("@Handle", i) { NullIfEmpty = false });; } await ctx.CommitAsync(); l.Debug($"Insert Test Table Finish rows result {i}"); }
private async Task DropSchemaAsync(string objectName) { MdbContext mdb = Factory.GetMdbContext(); var schema = await _schemaRepo.GetSchemaAsync(objectName); var schemaFromDb = await GetObjectSchemaAsync(schema.DbObjectName.ToString()); string[] sqlList; if (schemaFromDb == null) { sqlList = Array.Empty <string>(); } else { sqlList = await DropSchemaStatementsAsync(schemaFromDb); } if (sqlList.Length > 0) { await mdb.BeginTransactionAsync(); try { foreach (var sql in sqlList) { Logger.LogDebug(sql); await mdb.ExecuteAsync(sql); } await mdb.CommitAsync(); } catch { mdb.RollBack(); throw; } } }
public async Task CreateDbSchemaAsync(string dbName = db_name) { var s = SQLCat.GetCatalog(_mdb.ProviderName); var test = await _mdb.ExecuteAsync <string>(s["TestSchema"]); if (test.IsEmpty()) { try { await _mdb.BeginTransactionAsync(); foreach (string statement in s["CreateSchemaObjects"].Split("--go", StringSplitOptions.RemoveEmptyEntries)) { await _mdb.ExecuteAsync(statement); } _log.LogDebug($"Schema SysCat was created in database {_mdb.DbName}"); _log.LogDebug("Schema tables SysCat.SysAreas and SysCat.SysSchemas was created in SysCat Schema"); string owner = _defaultDbSchema; //await _mdb.ExecuteAsync<string>(s["GetCurrentSchema"]); int id = await _mdb.ExecuteAsync <int>(s["AddSysAreas"], new MdbParameter("@SchemaName", owner), new MdbParameter("@SchemaOwner", owner), new MdbParameter("@SchemaVersion", schema_version)); await _mdb.CommitAsync(); } catch (Exception e) { _mdb.RollBack(); _log.LogError($"CreateSchema error: {e.Message}"); throw; } } else { _log.LogDebug("Schema SysCat already exists in current database"); } }