public async void MssqlTestMethod() { //Add some monsters before querying _context.Add(new BackupLog { ClientIP = "192.168.1.100", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK" }); _context.Add(new BackupLog { ClientIP = "192.168.1.100", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK" }); _context.Add(new BackupLog { ClientIP = "192.168.1.101", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK" }); _context.Add(new BackupLog { ClientIP = "192.168.1.102", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK" }); _context.Add(new BackupLog { ClientIP = "192.168.1.103", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK" }); _context.SaveChanges(); //Execute the query var res = _context.Logs.FromSql("SELECT ID, Description, ClientIP, ConfigurationID, LogError, Status, DateTime, RowVersion FROM Logs").ToList(); //Verify the results Assert.Equal(5, res.Count()); }
public EfInMemoryContext() { options = new DbContextOptionsBuilder <BackupsContext>() .UseInMemoryDatabase() .Options; // Create the schema in the database using (var context = new BackupsContext(options)) { context.Database.EnsureCreated(); } // Insert seed data into the database using one instance of the context using (var context = new BackupsContext(options)) { int id = 1; context.Add(new BackupLog { ClientIP = "192.168.1.100", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK", ID = id++ }); context.Add(new BackupLog { ClientIP = "192.168.1.100", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK", ID = id++ }); context.Add(new BackupLog { ClientIP = "192.168.1.101", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK", ID = id++ }); context.Add(new BackupLog { ClientIP = "192.168.1.102", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK", ID = id++ }); context.Add(new BackupLog { ClientIP = "192.168.1.103", Description = $"Playing with some test data {Guid.NewGuid()}", Status = "OK", ID = id++ }); context.SaveChanges(); } _context = new BackupsContext(options); }
public void SetTable(DataTable table) { using (var tx = connect.BeginTransaction()) { try { if ("tenants_tenants".Equals(table.TableName, StringComparison.InvariantCultureIgnoreCase)) { // remove last tenant var tenant = backupsContext.Tenants.LastOrDefault(); if (tenant != null) { backupsContext.Tenants.Remove(tenant); backupsContext.SaveChanges(); } /* var tenantid = CreateCommand("select id from tenants_tenants order by id desc limit 1").ExecuteScalar(); * CreateCommand("delete from tenants_tenants where id = " + tenantid).ExecuteNonQuery();*/ if (table.Columns.Contains("mappeddomain")) { foreach (var r in table.Rows.Cast <DataRow>()) { r[table.Columns["mappeddomain"]] = null; if (table.Columns.Contains("id")) { var tariff = backupsContext.Tariffs.FirstOrDefault(t => t.Tenant == tenant.Id); tariff.Tenant = (int)r[table.Columns["id"]]; // CreateCommand("update tenants_tariff set tenant = " + r[table.Columns["id"]] + " where tenant = " + tenantid).ExecuteNonQuery(); backupsContext.Entry(tariff).State = EntityState.Modified; backupsContext.SaveChanges(); } } } } var sql = new StringBuilder("replace into " + Quote(table.TableName) + "("); var tableColumns = GetColumnsFrom(table.TableName) .Intersect(table.Columns.Cast <DataColumn>().Select(c => c.ColumnName), StringComparer.InvariantCultureIgnoreCase) .ToList(); tableColumns.ForEach(column => sql.AppendFormat("{0}, ", Quote(column))); sql.Replace(", ", ") values (", sql.Length - 2, 2); var insert = connect.CreateCommand(); tableColumns.ForEach(column => { sql.AppendFormat("@{0}, ", column); var p = insert.CreateParameter(); p.ParameterName = "@" + column; insert.Parameters.Add(p); }); sql.Replace(", ", ")", sql.Length - 2, 2); insert.CommandText = sql.ToString(); foreach (var r in table.Rows.Cast <DataRow>()) { foreach (var c in tableColumns) { ((IDbDataParameter)insert.Parameters["@" + c]).Value = r[c]; } insert.ExecuteNonQuery(); } tx.Commit(); } catch (Exception e) { log.ErrorFormat("Table {0}: {1}", table, e); } } }