public static void InitializeDb(TestContext context)
{
LocalDb.TryDropDatabaseIfExists(dbName, out _);
DataModel.CreateTablesAsync(new Type[]
{
typeof(Item),
typeof(Workspace),
typeof(WorkspaceUser),
typeof(UserProfile),
typeof(ColumnHistory),
typeof(RowVersion)
}, LocalDb.GetConnection(dbName)).Wait();
using (var cn = LocalDb.GetConnection(dbName))
{
GetProvider().Insert(cn, new Workspace()
{
Name = wsName,
CreatedBy = userName,
DateCreated = DateTime.UtcNow
});
GetProvider().Insert(cn, GetUser());
}
}
public void BuildSampleModel()
{
using (var cn = LocalDb.GetConnection("Hs5"))
{
var model = new SqlServerModelBuilder().GetDataModelAsync(cn).Result;
}
}
public override async Task <Job> GetAsync(int key)
{
using (var cn = LocalDb.GetConnection(DbName))
{
return(await cn.GetAsync <Job>(key));
}
}
private static void DeleteWorkspace(string name)
{
using (var cn = LocalDb.GetConnection(DbName))
{
cn.Execute("DELETE [dbo].[Workspace] WHERE [Name]=@name", new { name });
}
}
public static void Initialize(TestContext context)
{
LocalDb.TryDropDatabase(dbName, out _);
using (var cn = LocalDb.GetConnection(dbName, SampleObjects()))
{
var tdg = new TestDataGenerator();
tdg.Generate <TypicalQueryResult>(1000, (result) =>
{
result.FirstName = tdg.Random(Source.FirstName);
result.SomeDate = tdg.RandomInRange(-1000, 1000, (i) => DateTime.Today.AddDays(i));
result.Weight = tdg.RandomInRange <decimal>(50, 150, (d) => d);
result.Notes = RandomPhrase(4, 8);
}, (results) =>
{
// AppVeyor seems to need this
if (cn.State == ConnectionState.Closed)
{
cn.Open();
}
var dataTable = results.ToDataTable();
BulkInsert.ExecuteAsync(dataTable, cn as SqlConnection, DbObject.Parse("dbo.SampleTable"), 50, new BulkInsertOptions()
{
SkipIdentityColumn = "Id"
}).Wait();
});
}
}
public void SyncCallNoParams()
{
using (var cn = LocalDb.GetConnection(DbName))
{
var dataTable = cn.QueryTable("SELECT * FROM [sys].[tables]");
}
}
public static void Initialize(TestContext context)
{
using (var cn = LocalDb.GetConnection(dbName))
{
LocalDb.ExecuteInitializeStatements(cn, DbObjects.CreateObjects());
}
}
public void CaptureChanges()
{
using (var cn = LocalDb.GetConnection(dbName))
{
var emp = new Employee()
{
LastName = "Herbert",
FirstName = "Yavad",
HireDate = new DateTime(1990, 1, 1),
IsExempt = true
};
// need to make sure record doesn't exist
try { cn.Execute("TRUNCATE TABLE [changes].[RowVersion]"); } catch { /*do nothing */ }
try { cn.Execute("TRUNCATE TABLE [changes].[ColumnHistory]"); } catch { /* do nothing */ }
cn.Execute("DELETE [dbo].[Employee] WHERE [FirstName]=@firstName AND [LastName]=@lastName", emp);
var provider = new SqlServerCrudProvider <int>((id) => Convert.ToInt32(id));
provider.SaveAsync(cn, emp).Wait();
LoggedChangeTracker <Employee, long> ct = GetLoggedChangeTracker(cn, emp);
emp.FirstName = "Javad";
emp.Status = Status.Inactive;
provider.SaveAsync(cn, emp, ct).Wait();
Assert.IsTrue(cn.RowExistsAsync("[changes].[RowVersion] WHERE [TableName]='Employee' AND [RowId]=1 AND [Version]=1").Result);
Assert.IsTrue(cn.RowExistsAsync("[changes].[ColumnHistory] WHERE [TableName]='Employee' AND [RowId]=1 AND [Version]=1 AND [ColumnName]='FirstName' AND [OldValue]='Yavad' AND [NewValue]='Javad'").Result);
Assert.IsTrue(cn.RowExistsAsync("[changes].[ColumnHistory] WHERE [TableName]='Employee' AND [RowId]=1 AND [Version]=1 AND [ColumnName]='Status' AND [OldValue]='Active' AND [NewValue]='Inactive'").Result);
}
}
public void GetEmployeeWhereLambda()
{
using (var cn = LocalDb.GetConnection("DapperCX"))
{
string lastName = "Yarga";
var emp = new EmployeeCustom()
{
FirstName = lastName,
LastName = GetLastName(),
IsExempt = true,
HireDate = DateTime.Today,
Value = OtherEnum.Other
};
var provider = GetProvider();
int id = provider.SaveAsync(cn, emp).Result;
emp = provider.GetWhereAsync <EmployeeCustom>(cn,
e => e.FirstName == lastName,
e => e.LastName == GetLastName()).Result;
Assert.IsTrue(emp.Id == id);
}
}
public void QuerySchemaTableAsync()
{
using (var cn = LocalDb.GetConnection(DbName))
{
var schemaTable = cn.QuerySchemaTableAsync("SELECT * FROM [sys].[tables]").Result;
Assert.IsTrue(schemaTable.Columns.Contains("ColumnName"));
}
}
public void AsyncCallWithAnonObjParam()
{
using (var cn = LocalDb.GetConnection(DbName))
{
var dataTable = cn.QueryTableAsync("SELECT * FROM [sys].[objects] WHERE [name]=@name", new { name = "sysfos" }).Result;
Assert.IsTrue(dataTable.Rows.Count == 1);
}
}
public void AsyncCallNoParams()
{
using (var cn = LocalDb.GetConnection(DbName))
{
var dataTable = cn.QueryTableAsync("SELECT * FROM [sys].[objects]").Result;
Assert.IsTrue(dataTable.Rows.Count > 0);
}
}
public override async Task <Job> SaveAsync(Job model)
{
using (var cn = LocalDb.GetConnection(DbName))
{
await cn.SaveAsync(model);
return(model);
}
}
public void CreateTable()
{
using (var cn = LocalDb.GetConnection(DbName))
{
var createTable = cn.SqlCreateTableAsync("dbo", "SampleTable", "SELECT * FROM [sys].[tables]").Result;
cn.Execute(createTable);
cn.Execute("DROP TABLE [dbo].[SampleTable]");
}
}
public void QueryWithTrace()
{
using (var cn = LocalDb.GetConnection(dbName))
{
List <QueryTrace> traces = new List <QueryTrace>();
var results = new TypicalQuery().ExecuteAsync(cn, traces: traces).Result;
Assert.IsTrue(traces.Count() == 1);
}
}
public static void Initialize(TestContext context)
{
LocalDb.TryDropDatabase("DapperCX", out _);
using (var cn = LocalDb.GetConnection("DapperCX"))
{
LocalDb.ExecuteInitializeStatements(cn, DbObjects.CreateObjects());
}
}
public void GetTableFunctions()
{
using (var cn = LocalDb.GetConnection("Ginseng8"))
{
var a = new SqlServerAnalyzer();
var functions = a.GetDbObjectsAsync(cn).Result.OfType <TableFunction>();
Assert.IsTrue(functions.Any());
}
}
public void GetViews()
{
using (var cn = LocalDb.GetConnection("Ginseng8"))
{
var a = new SqlServerAnalyzer();
var views = a.GetDbObjectsAsync(cn).Result.OfType <View>();
Assert.IsTrue(views.Any());
}
}
public void Analyze()
{
using (var cn = LocalDb.GetConnection("CycleLog2"))
{
var a = new SqlServerAnalyzer();
var objects = a.GetDbObjectsAsync(cn).Result;
Assert.IsTrue(objects.Any());
}
}
public void SampleModelCompare()
{
using (var cn = LocalDb.GetConnection("Hs5"))
{
var asm = Assembly.LoadFile(@"C:\Users\Adam\Source\Repos\ModelSync.WinForms\SampleModel\bin\Debug\netstandard2.0\SampleModel.dll");
var srcModel = new AssemblyModelBuilder().GetDataModel(asm);
var destModel = new SqlServerModelBuilder().GetDataModelAsync(cn).Result;
var diff = DataModel.Compare(srcModel, destModel);
}
}
private void DropCacheTable(DbCache cache)
{
using (var cn = LocalDb.GetConnection(dbName))
{
var obj = ObjectName.FromName(cache.TableName);
if (cn.TableExistsAsync(obj.Schema, obj.Name).Result)
{
cn.Execute($"DROP TABLE {cache.TableName}");
}
}
}
public void SyncCallWithAnonObjParams()
{
const string objName = "sysfos";
using (var cn = LocalDb.GetConnection(DbName))
{
var dataTable = cn.QueryTable("SELECT * FROM [sys].[objects] WHERE [name]=@name", new { name = objName });
Assert.IsTrue(dataTable.Rows.Count == 1);
Assert.IsTrue(dataTable.Rows[0].Field <string>("name").Equals(objName));
}
}
public void SyncCallWithDictionaryParam()
{
using (var cn = LocalDb.GetConnection(DbName))
{
// won't be any data, just want to make sure no exception using parameter
var dataTable = cn.QueryTable("SELECT * FROM [sys].[tables] WHERE [name]=@name", new Dictionary <string, object>()
{
{ "name", "fred" }
});
}
}
public void OffsetQueryNewPageSize()
{
using (var cn = LocalDb.GetConnection(dbName))
{
var results = new TypicalQuery()
{
PageNumber = 4
}.ExecuteAsync(cn, newPageSize: 10).Result;
Debug.Print($"result count = {results.Count()}");
Assert.IsTrue(results.Count() == 10);
}
}
public void OffsetQuery()
{
using (var cn = LocalDb.GetConnection(dbName))
{
var results = new TypicalQuery()
{
PageNumber = 4
}.Execute(cn);
Debug.Print($"result count = {results.Count()}");
Assert.IsTrue(results.Count() == 20);
}
}
public static void Initialize(TestContext context)
{
LocalDb.TryDropDatabaseIfExists(DbName, out _);
using (var cn = LocalDb.GetConnection(DbName, GetInitializeStatements()))
{
for (int i = 0; i < 10; i++)
{
cn.Execute("INSERT INTO [dbo].[SomeTable] ([Quantity]) VALUES (@value)", new { value = i * 5 });
}
}
}
public void PhraseQuery()
{
using (var cn = LocalDb.GetConnection(dbName))
{
var qry = new TypicalQuery()
{
NotesContain = "this whatever"
};
var results = qry.Execute(cn);
Debug.Print(qry.ResolvedSql); // for my own curiosity when running locally
Assert.IsTrue(results.Any());
}
}
public void Case01()
{
using (var cn = LocalDb.GetConnection(dbName))
{
CreateSchema(cn);
var loader = new ExcelLoader();
using (var stream = GetResource("case01.xlsx"))
{
loader.SaveAsync(stream, cn, "upload", "Case01").Wait();
}
}
}
public void GetProcedures()
{
using (var cn = LocalDb.GetConnection("Ginseng8"))
{
var a = new SqlServerAnalyzer();
var procs = a.GetDbObjectsAsync(cn).Result.OfType <Procedure>();
Assert.IsTrue(procs.Any());
var dictionary = procs.ToDictionary(item => item.Name);
Assert.IsTrue(dictionary["PostInvoice"].Arguments.Any(arg => arg.Name.Equals("@orgId")));
}
}
public void Case03()
{
using (var cn = LocalDb.GetConnection(dbName))
{
CreateSchema(cn);
using (var stream = GetResource("case03.xlsx"))
{
new ExcelLoader().SaveAsync(stream, cn).Wait();
Assert.IsTrue(cn.Query <int>("SELECT COUNT(1) FROM [dbo].[Companies]").Count() > 0);
Assert.IsTrue(cn.Query <int>("SELECT COUNT(1) FROM [dbo].[People]").Count() > 0);
}
}
}