public void SqlServer_Benchmark_SelectPerformanceTest() { var ordersList = new List<Orders> { new Orders { OrdersID = 21 } }; var profile = ProfileSession.StartSession() .Task(() => { var provider = new SqlContextProvider("Not a valid connectionstring"); provider.Interceptor<Orders>().AsExecute(cq => ordersList); using (var context = provider.Open()) { var orders = context.From<Customers>() .Join<Employee>((e, c) => e.EmployeeID == c.EmployeeID) .And<Customers>((e, c) => e.EmployeeID == c.EmployeeID) .Join<Orders>((o, e) => o.EmployeeID == e.EmployeeID) .Select<Orders>(); } }) .SetIterations(20) .AddCondition(p => p.AverageMilliseconds < 27) .RunSession(); Assert.IsTrue(profile.AverageMilliseconds < 27); }
public void FromWithIncludeWithAliasMapWithoutAlias() { var dbConnection = new SqlContextProvider(ConnectionString); using (var context = dbConnection.Open()) { var query = context .From<Orders>("ord") .Map(p => p.OrdersID) //TODO: Join has to check the previous for the alias? "ord" .Join<OrderDetails>((det, order) => det.OrdersID == order.OrdersID, source: "ord") .Join<Products>((product, det) => product.ProductID == det.ProductID) .Map(p => p.ProductID) .Map(p => p.UnitPrice); var sql = query.CompileQuery<OrderDetails>().Flatten(); var expected = "SELECT ord.OrdersID, Products.ProductID, Products.UnitPrice, Quantity, Discount FROM Orders ord JOIN OrderDetails ON (OrderDetails.OrdersID = ord.OrdersID) JOIN Products ON (Products.ProductID = OrderDetails.ProductID)"; // check the compiled sql Assert.AreEqual(sql, expected); // execute the query var orders = query.Select<OrderDetails>(); Assert.IsTrue(orders.Any()); } }
public void ProcedureWithoutResultWithRetval() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { /* *Using Output compiles to* declare @p1 int set @p1=1 declare @p2 varchar(max) set @p2='tmp' exec SalesByYear @Date='2012-01-01 00:00:00',@param1=@p1 output,@param2=@p2 output select @p1 as p1, @p2 as p2 */ int returnvalue1 = 1; string returnvalue2 = "tmp"; // proc without resultset with output parameter with names context.Procedure("SalesOfYear") .AddParameter("Date", () => new DateTime(1998, 1, 1)) .AddParameter("outputparam1", () => returnvalue1, r => returnvalue1 = r) .AddParameter("outputparam2", () => returnvalue2, r => returnvalue2 = r) .Execute(); Assert.IsTrue(returnvalue1 != 1); Assert.IsTrue(returnvalue2 != "tmp"); } }
public void SqlServer_LocalDb_CreateLocalDbWithDbFilePath_Test() { var databaseName = "WarriorDB"; var outputFolder = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "Data"); var mdfFilename = $"{databaseName}.mdf"; var databaseMdfPath = Path.Combine(outputFolder, mdfFilename); // Create Data Directory If It Doesn't Already Exist. if (!Directory.Exists(outputFolder)) { Directory.CreateDirectory(outputFolder); } var connectionString = $"Data Source=(LocalDB)\\mssqllocaldb;AttachDBFileName={databaseMdfPath};Initial Catalog={databaseName};Integrated Security=True;"; var provider = new SqlContextProvider(connectionString); using (var context = provider.Open()) { context.Database.Create(); context.Commit(); } using (var context = provider.Open()) { var database = context.Execute($"SELECT * FROM Master.sys.databases WHERE Name = '{databaseName}'", () => new { Name = "" }); Assert.IsTrue(database.Any()); } RemoveDatabase(provider); }
public void Interceptor_BeforeCompile_FromTest() { var query = string.Empty; var where = new DelegateQueryPart(OperationType.Where, () => "ID = 2"); var provider = new SqlContextProvider("connectionstring"); provider.Interceptor(() => new { ID = 0 }).BeforeExecute(q => query = q.QueryString).AsExecute(e => _warriors.Select(w => new { ID = w.ID })); provider.Interceptor<Warrior>().BeforeCompile(c => c.Parts.First(p => p.OperationType == OperationType.From).Add(where)); using (var context = provider.Open()) { context.From<Warrior>().Select(() => new { ID = 0 }); Assert.AreEqual(query.Flatten(), "SELECT ID FROM Warrior WHERE ID = 2"); } }
public void AlterTableAddColumn() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); using (var context = provider.Open()) { context.Database.Table<Weapon>() .Key(wpn => wpn.ID) .Create(); context.Database.Table<Warrior>() .Key(wrir => wrir.ID) .ForeignKey<Weapon>(wrir => wrir.WeaponID, wpn => wpn.ID) .Ignore(wrir => wrir.Race) .Create(); context.Commit(); //TODO: Check table definition context.Database.Table<Warrior>() .Column(wrir => wrir.Race, FieldOperation.Add) .Alter(); context.Commit(); //TODO: Check table definition var tables = GetTables(context); Assert.IsTrue(tables.Any(t => t.Name == typeof(Warrior).Name)); Assert.IsTrue(tables.Any(t => t.Name == typeof(Weapon).Name)); } }
public void AssemblyCleanup() { var connectionString = ConfigurationManager.ConnectionStrings["PersistenceMap.Test.Properties.Settings.ConnectionString"].ConnectionString; var provider = new SqlContextProvider(connectionString); using (var ctx = provider.Open()) { ctx.Database.Drop(); ctx.Commit(); } }
static void GettingContextProvider() { // default provider (for most cases) var sqlProvider1 = SqlContextProvider.DefaultInstance; var postgreProvider1 = NpgsqlContextProvider.DefaultInstance; // specific provider var sqlProvider2 = new SqlContextProvider(); var postgreProvider2 = new NpgsqlContextProvider(); }
public void AddFieldByStringFail() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); using (var context = provider.Open()) { context.Database.Table<Warrior>().Ignore(wrir => wrir.Race).Create(); Assert.Throws<ArgumentNullException>(() => context.Database.Table<Warrior>().Column("Race", FieldOperation.Add).Alter()); } }
public void JoinWithIndexerInMember() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { // product with indexer (this[string]) var products = context.From<Products>().Select<ProductsWithIndexer>(); Assert.IsTrue(products.Any()); } }
public void ProcedureWithoutResultWithParamNamesContainingAt() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { // proc without resultset with parameter names and @ before name context.Procedure("SalesByYear") .AddParameter("@BeginDate", () => new DateTime(1970, 1, 1)) .AddParameter("@EndDate", () => DateTime.Today) .Execute(); } }
public void ProcedureWithoutResultWithoutParamNames() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { // proc without resultset without parameter names context.Procedure("SalesByYear") .AddParameter(() => new DateTime(1970, 1, 1)) .AddParameter(() => DateTime.Today) .Execute(); } }
public void AddFieldByString() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); using (var context = provider.Open()) { context.Database.Table<Warrior>().Ignore(wrir => wrir.Race).Create(); context.Database.Table<Warrior>().Column("Race", FieldOperation.Add, typeof(string)).Alter(); context.Commit(); } }
public void UpdateIntegrationTest() { var logger = new MessageStackLogger(); var provider = new SqlContextProvider(ConnectionString); provider.Settings.AddLogger(logger); using (var context = provider.Open()) { context.Update<Orders>(() => new { Freight = 20 }, o => o.OrdersID == 10000000); context.Commit(); Assert.AreEqual(logger.Logs.First().Message.Flatten(), "UPDATE Orders SET Freight = 20 WHERE (Orders.OrdersID = 10000000)"); } }
public void ExecuteUpateStatement() { var logger = new MessageStackLogger(); var provider = new SqlContextProvider(ConnectionString); provider.Settings.AddLogger(logger); using (var context = provider.Open()) { // select with string select statement context.Execute("UPDATE Orders SET Freight = 20 WHERE OrdersID = 10000000"); Assert.AreEqual(logger.Logs.First().Message.Flatten(), "UPDATE Orders SET Freight = 20 WHERE OrdersID = 10000000"); } }
public void ExecuteSelectStatement() { var logger = new MessageStackLogger(); var provider = new SqlContextProvider(ConnectionString); provider.Settings.AddLogger(logger); using (var context = provider.Open()) { // select with string select statement var orders = context.Execute<Orders>("SELECT * FROM Orders"); Assert.IsTrue(orders.Any()); Assert.AreEqual(logger.Logs.First().Message.Flatten(), "SELECT * FROM Orders"); } }
public void IncludeWithWrongLambdaExpressionFailTest() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { // fail test because Include doesn't return a property witch ends in a wrong sql statement var query = context.From<Orders>() .Join<OrderDetails>((detail, order) => detail.OrdersID == order.OrdersID) // this has to fail! .Map(i => i.OrdersID != 1); Assert.Throws<System.Data.DataException>(() => query.Select<OrderWithDetailExtended>()); } }
public void AssemblyInit() { var connectionString = ConfigurationManager.ConnectionStrings["PersistenceMap.Test.Properties.Settings.ConnectionString"].ConnectionString; var provider = new SqlContextProvider(connectionString); using (var ctx = provider.Open()) { ctx.Database.Create(); ctx.Commit(); var file = new FileInfo(@"AppData\Nothwind.SqlServer.sql"); string script = file.OpenText().ReadToEnd(); ctx.Execute(script); } }
public void ConverterTests_ConvertValueToEnumWithMethodTest() { var dbConnection = new SqlContextProvider(ConnectionString); using (var context = dbConnection.Open()) { var orders = context.From<Orders>() .Map<double>(o => o.Freight, "Freight", v => Converter(v)) .Select<FreightOrders>(); Assert.IsNotNull(orders); Assert.IsTrue(orders.Any()); Assert.IsTrue(orders.First().Freight == FreightType.Ship); } }
public void ConverterTests_ConvertValueToEnumWithLambdaTest() { var dbConnection = new SqlContextProvider(ConnectionString); using (var context = dbConnection.Open()) { var orders = context.From<Orders>() .Map(o => o.Freight, converter: value => value > 0 ? FreightType.Ship : FreightType.Plane) .Select<FreightOrders>(); Assert.IsNotNull(orders); Assert.IsTrue(orders.Any()); Assert.IsTrue(orders.First().Freight == FreightType.Ship); } }
public void SelectAnonymousObjectWithFalseConverterInvalidCast() { var provider = new SqlContextProvider(ConnectionString); using (var context = provider.Open()) { Assert.Throws<InvalidConverterException>(() => context.From<Orders>() .Map(o => o.OrderDate, "Date") .Map(o => o.OrderDate, converter: date => date.Month >= 6 ? true : false) .For(() => new { Date = DateTime.MinValue, OrderDate = false, InvalidCast = "" }) .Map<Orders>(d => d.EmployeeID, d => d.InvalidCast, d => ((bool)d) == false) .Select()); } }
public void SqlServer_LocalDb_CreateLocalDb_Test() { var databaseName = "WarriorDB"; var connectionString = $"Data Source=(LocalDB)\\mssqllocaldb;Initial Catalog={databaseName};Integrated Security=True;"; var provider = new SqlContextProvider(connectionString); using (var context = provider.Open()) { context.Database.Create(); context.Commit(); } using (var context = provider.Open()) { var database = context.Execute($"SELECT * FROM Master.sys.databases WHERE Name = '{databaseName}'", () => new { Name = "" }); Assert.IsTrue(database.Any()); } RemoveDatabase(provider); }
public void Interceptor_AddMultipleSameIntersectorTest() { string beforeExecute = null; var ordersList = new List<Order> { new Order { OrdersID = 21 } }; var provider = new SqlContextProvider("Not a valid connectionstring"); provider.Interceptor<Order>().BeforeExecute(cq => beforeExecute = cq.QueryString); provider.Interceptor<Order>().AsExecute(cq => ordersList); using (var context = provider.Open()) { var orders = context.Select<Order>(); Assert.AreEqual("SELECT OrdersID \r\nFROM Order", beforeExecute); Assert.AreSame(orders.First(), ordersList.First()); } }
public void Interceptor_BeforeCompileTest() { string beforeExecute = null; var ordersList = new List<Order> { new Order { OrdersID = 21 } }; var provider = new SqlContextProvider("Not a valid connectionstring"); provider.Interceptor<Order>().BeforeCompile(cq => cq.Parts.FirstOrDefault(p => p.OperationType == OperationType.Select).Add(new DelegateQueryPart(OperationType.Where, () => "TestWhere"))) .BeforeExecute(cq => beforeExecute = cq.QueryString) .AsExecute(cq => ordersList); using (var context = provider.Open()) { context.Select<Order>(); Assert.IsTrue(beforeExecute.Contains("TestWhere")); } }
public void TestWithLocalDbTest() { var provider = new SqlContextProvider(_localDbManager.ConnectionString); using (var context = provider.Open()) { var file = new FileInfo(@"AppData\Nothwind.SqlServer.sql"); string script = file.OpenText().ReadToEnd(); context.Execute(script); var query = context.From<Orders>().Map(o => o.OrdersID).Join<OrderDetails>((d, o) => d.OrdersID == o.OrdersID); var sql = "SELECT Orders.OrdersID, ProductID, UnitPrice, Quantity, Discount FROM Orders JOIN OrderDetails ON (OrderDetails.OrdersID = Orders.OrdersID)"; var expected = query.CompileQuery(); // check the compiled sql Assert.AreEqual(expected.Flatten(), sql); // execute the query var orders = query.Select(); Assert.IsNotNull(orders); Assert.IsTrue(orders.Any()); } }
private void CreateDatabaseIfNotExists() { var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); var database = provider.ConnectionProvider.Database; provider.ConnectionProvider.Database = "Master"; using (var context = provider.Open()) { try { if (context.Execute(string.Format("SELECT * FROM Master.sys.databases WHERE Name = '{0}'", database), () => new { Name = "" }).Any() == false) { provider.ConnectionProvider.Database = database; context.Database.Create(); context.Commit(); } } catch (SqlException) { } } }
public void Setup() { var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); var database = provider.ConnectionProvider.Database; provider.ConnectionProvider.Database = "Master"; using (var context = provider.Open()) { try { if (context.Execute(string.Format("SELECT * FROM Master.sys.databases WHERE Name = '{0}'", database), () => new { Name = "" }).Any()) { //context.Execute(string.Format("DROP DATABASE {0}", database)); provider.ConnectionProvider.Database = database; var tables = GetTables(context); if (tables.Any(t => t.Name == typeof(Warrior).Name)) { context.Database.Table<Warrior>().Drop(); } if (tables.Any(t => t.Name == typeof(Weapon).Name)) { context.Database.Table<Weapon>().Drop(); } } } catch (SqlException) { } catch (DataException) { } } }
public void DropTable() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); using (var context = provider.Open()) { // create a table to drop later in the test context.Database.Table<Weapon>().Key(wpn => wpn.ID).Create(); context.Commit(); var tables = GetTables(context); Assert.IsTrue(tables.Any(t => t.Name == typeof(Weapon).Name)); // drop the table context.Database.Table<Weapon>().Drop(); context.Commit(); tables = GetTables(context); Assert.IsFalse(tables.Any(t => t.Name == typeof(Weapon).Name)); } }
public void DropDatabaseIfExists() { var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); var database = provider.ConnectionProvider.Database; provider.ConnectionProvider.Database = "Master"; using (var context = provider.Open()) { try { if (context.Execute(string.Format("SELECT * FROM Master.sys.databases WHERE Name = '{0}'", database), () => new { Name = "" }).Any()) { context.Execute(string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", database)); context.Execute(string.Format("DROP DATABASE {0}", database)); } } catch (SqlException e) { System.Diagnostics.Trace.WriteLine(e); } catch (DataException e) { System.Diagnostics.Trace.WriteLine(e); } } }
public void DeleteField() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); using (var context = provider.Open()) { context.Database.Table<Warrior>().Create(); context.Database.Table<Warrior>().Column("Race", FieldOperation.Drop).Alter(); context.Commit(); } }
public void CreateTableNotNullableColumn() { CreateDatabaseIfNotExists(); var provider = new SqlContextProvider(GetConnectionString("WarriorDB")); var logger = new MessageStackLogger(); provider.Settings.AddLogger(logger); using (var context = provider.Open()) { // table with a foreign key context.Database.Table<Warrior>() .Column(wrir => wrir.ID, isNullable: false) .Column(wrir => wrir.Race, isNullable: false) .Create(); context.Commit(); Assert.AreEqual(logger.Logs.First().Message.Flatten(), "CREATE TABLE Warrior (ID int NOT NULL, Race varchar(max) NOT NULL, Name varchar(max), WeaponID int NOT NULL, SpecialSkill varchar(max))"); } }