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);
}
}
}
