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