public void SingleIntoWorksAsExpected()
{
// --- Arrange
var db = new FbDatabase(DB_CONN);
db.BeginTransaction();
db.Execute(@"create table ""sample"" (""Id"" int not null, ""Name"" varchar(50))");
db.CompleteTransaction();
db.Execute(@"insert into ""sample"" values(1, 'First')");
db.Execute(@"insert into ""sample"" values(2, 'Second')");
// --- Act
var instance = new SampleRecord {
Id = 3
};
var row1 = db.SingleInto(instance, @"select ""Name"" from ""sample"" where ""Id"" = 1");
var row2 = db.SingleInto(instance, SqlExpression.CreateFrom(
@"select ""Name"" from ""sample"" where ""Id"" = 1"));
// --- Assert
row1.Id.ShouldEqual(3);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(3);
row2.Name.ShouldEqual("First");
}
public async Task FetchMultipleWorksWithMapping7Sets()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
await db.ExecuteAsync(@"create table sample(Id int not null, Name varchar(50) null)");
await db.ExecuteAsync("insert into sample values(1, 'First')");
await db.ExecuteAsync("insert into sample values(2, 'Second')");
await db.ExecuteAsync("insert into sample values(3, 'Third')");
await db.ExecuteAsync("insert into sample values(4, 'Fourth')");
await db.ExecuteAsync("insert into sample values(5, 'Fifth')");
await db.ExecuteAsync("insert into sample values(6, 'Sixth')");
await db.ExecuteAsync("insert into sample values(7, 'Seventh')");
// --- Act
var rows = await db.FetchMultipleAsync <SampleRecord, SampleRecord, SampleRecord, SampleRecord, SampleRecord, SampleRecord, SampleRecord, int>(
SqlExpression.CreateFrom(
@"select * from sample where Id = 1
select * from sample where Id = 2 or Id = 3
select Name, Id from sample where Id >=4 and Id <= 6
select * from sample where Id > 6
select * from sample where Id = 1
select * from sample where Id = 2 or Id = 3
select Name, Id from sample where Id >=4 and Id <= 6"),
(l1, l2, l3, l4, l5, l6, l7) => l1.Count + l2.Count + l3.Count + l4.Count + l5.Count + l6.Count + l7.Count);
// --- Assert
rows.ShouldEqual(13);
}
public void FirstOrDefaultIntoWorksAsExpected()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
db.Execute(@"create table sample(Id int not null, Name varchar(50) null)");
db.Execute("insert into sample values(1, 'First')");
db.Execute("insert into sample values(2, 'Second')");
// --- Act
var instance = new SampleRecord {
Id = 3
};
var row1 = db.FirstOrDefaultInto(instance, "select Name from sample order by Id");
var row2 = db.FirstOrDefaultInto(instance, SqlExpression.CreateFrom("select Name from sample order by Id"));
var row3 = db.FirstOrDefaultInto(instance, "select Name from sample where Id = 3");
var row4 = db.FirstOrDefaultInto(instance, SqlExpression.CreateFrom("select Name from sample where Id = 3"));
// --- Assert
row1.Id.ShouldEqual(3);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(3);
row2.Name.ShouldEqual("First");
row3.ShouldBeNull();
row4.ShouldBeNull();
}
public async Task FirstAsyncIntoWorksAsExpected()
{
// --- Arrange
var db = new FbDatabase(DB_CONN);
await db.BeginTransactionAsync();
await db.ExecuteAsync(@"create table ""sample"" (""Id"" int not null, ""Name"" varchar(50))");
await db.CompleteTransactionAsync();
await db.ExecuteAsync(@"insert into ""sample"" values(1, 'First')");
await db.ExecuteAsync(@"insert into ""sample"" values(2, 'Second')");
// --- Act
var instance = new SampleRecord {
Id = 3
};
var row1 = await db.FirstIntoAsync(instance,
@"select ""Name"" from ""sample"" order by ""Id""");
var row2 = await db.FirstIntoAsync(instance, SqlExpression.CreateFrom(
@"select ""Name"" from ""sample"" order by ""Id"""));
// --- Assert
row1.Id.ShouldEqual(3);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(3);
row2.Name.ShouldEqual("First");
}
public void FirstOrDefaultWorksAsExpected()
{
// --- Arrange
var db = new FbDatabase(DB_CONN);
db.BeginTransaction();
db.Execute(@"create table ""sample"" (""Id"" int not null, ""Name"" varchar(50))");
db.CompleteTransaction();
db.Execute(@"insert into ""sample"" values(1, 'First')");
db.Execute(@"insert into ""sample"" values(2, 'Second')");
// --- Act
var row1 = db.FirstOrDefault <SampleRecord>(@"order by ""Id""");
var row2 = db.FirstOrDefault <SampleRecord>(SqlExpression.CreateFrom(
@"select * from ""sample"" order by ""Id"""));
var row3 = db.FirstOrDefault <SampleRecord>(@"where ""Id"" = @0", 3);
var row4 = db.FirstOrDefault <SampleRecord>(SqlExpression.CreateFrom(
@"select * from ""sample"" where ""Id"" = @0", 3));
// --- Assert
row1.Id.ShouldEqual(1);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(1);
row2.Name.ShouldEqual("First");
row3.ShouldBeNull();
row4.ShouldBeNull();
}
public void FetchMultipleExpressionWorksWith4Sets()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
db.Execute(@"create table sample(Id int not null, Name varchar(50) null)");
db.Execute("insert into sample values(1, 'First')");
db.Execute("insert into sample values(2, 'Second')");
db.Execute("insert into sample values(3, 'Third')");
db.Execute("insert into sample values(4, 'Fourth')");
db.Execute("insert into sample values(5, 'Fifth')");
db.Execute("insert into sample values(6, 'Sixth')");
db.Execute("insert into sample values(7, 'Seventh')");
// --- Act
var rows = db.FetchMultiple <SampleRecord, SampleRecord, SampleRecord, SampleRecord>(
SqlExpression.CreateFrom(
@"select * from sample where Id = 1
select * from sample where Id = 2 or Id = 3
select Name, Id from sample where Id >=4 and Id <= 6
select * from sample where Id > 6"));
var set1 = rows.Item1;
var set2 = rows.Item2;
var set3 = rows.Item3;
var set4 = rows.Item4;
// --- Assert
set1.ShouldHaveCountOf(1);
set2.ShouldHaveCountOf(2);
set3.ShouldHaveCountOf(3);
set4.ShouldHaveCountOf(1);
}
public async Task FirstOrDefaultAsyncWorksAsExpected()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
await db.ExecuteAsync(@"create table sample(Id int not null, Name varchar(50) null)");
await db.ExecuteAsync("insert into sample values(1, 'First')");
await db.ExecuteAsync("insert into sample values(2, 'Second')");
// --- Act
var row1 = await db.FirstOrDefaultAsync <SampleRecord>("order by Id");
var row2 = await db.FirstOrDefaultAsync <SampleRecord>(SqlExpression.CreateFrom("select * from sample order by Id"));
var row3 = await db.FirstOrDefaultAsync <SampleRecord>("where Id = 3");
var row4 = await db.FirstOrDefaultAsync <SampleRecord>(SqlExpression.CreateFrom("select * from sample where Id = 3"));
// --- Assert
row1.Id.ShouldEqual(1);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(1);
row2.Name.ShouldEqual("First");
row3.ShouldBeNull();
row4.ShouldBeNull();
}
public void CreateFromWorks()
{
// --- Arrange
const string EXPR = "select @0 from x";
const int ARG = 23;
// --- Act
var expression = SqlExpression.CreateFrom(EXPR, new List <object> {
ARG
});
// --- Assert
expression.SqlText.ShouldEqual(EXPR);
expression.Arguments.ShouldHaveCountOf(1);
expression.Arguments[0].ShouldEqual(ARG);
}
public void FetchMultipleWorksWithMapping2Sets()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
db.Execute(@"create table sample(Id int not null, Name varchar(50) null)");
db.Execute("insert into sample values(1, 'First')");
db.Execute("insert into sample values(2, 'Second')");
db.Execute("insert into sample values(3, 'Third')");
// --- Act
var rows = db.FetchMultiple <SampleRecord, SampleRecord, int>(SqlExpression.CreateFrom(
@"select * from sample where Id = 1
select * from sample where Id > 1"), (l1, l2) => l1.Count + l2.Count);
// --- Assert
rows.ShouldEqual(3);
}
public void FetchMultipleExpressionWorksWith2Sets()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
db.Execute(@"create table sample(Id int not null, Name varchar(50) null)");
db.Execute("insert into sample values(1, 'First')");
db.Execute("insert into sample values(2, 'Second')");
db.Execute("insert into sample values(3, 'Third')");
// --- Act
var rows = db.FetchMultiple <SampleRecord, SampleRecord>(SqlExpression.CreateFrom(
@"select * from sample where Id = 1
select * from sample where Id > 1"));
var set1 = rows.Item1;
var set2 = rows.Item2;
// --- Assert
set1.ShouldHaveCountOf(1);
set2.ShouldHaveCountOf(2);
}
public async Task SingleIntoAsyncWorksAsExpected()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
await db.ExecuteAsync(@"create table sample(Id int not null, Name varchar(50) null)");
await db.ExecuteAsync("insert into sample values(1, 'First')");
await db.ExecuteAsync("insert into sample values(2, 'Second')");
// --- Act
var instance = new SampleRecord {
Id = 3
};
var row1 = await db.SingleIntoAsync(instance, "select Name from sample where Id = 1");
var row2 = await db.SingleIntoAsync(instance, SqlExpression.CreateFrom("select Name from sample where Id = 1"));
// --- Assert
row1.Id.ShouldEqual(3);
row1.Name.ShouldEqual("First");
row2.Id.ShouldEqual(3);
row2.Name.ShouldEqual("First");
}
public async Task FetchMultipleAsyncExpressionWorksWith3Sets()
{
// --- Arrange
var db = new SqlDatabase(DB_CONN);
await db.ExecuteAsync(@"create table sample(Id int not null, Name varchar(50) null)");
await db.ExecuteAsync("insert into sample values(1, 'First')");
await db.ExecuteAsync("insert into sample values(2, 'Second')");
await db.ExecuteAsync("insert into sample values(3, 'Third')");
await db.ExecuteAsync("insert into sample values(4, 'Fourth')");
await db.ExecuteAsync("insert into sample values(5, 'Fifth')");
// --- Act
var rows = await db.FetchMultipleAsync <SampleRecord, SampleRecord, SampleRecord>(SqlExpression.CreateFrom(
@"select * from sample where Id = 1
select * from sample where Id = 2
select Name, Id from sample where Id > 2"));
var set1 = rows.Item1;
var set2 = rows.Item2;
var set3 = rows.Item3;
// --- Assert
set1.ShouldHaveCountOf(1);
set2.ShouldHaveCountOf(1);
set3.ShouldHaveCountOf(3);
}
public void ProcessParameterWithWrongIndexRaisesException()
{
// --- Act
// ReSharper disable once UnusedVariable
var result = SqlExpression.CreateFrom("@2, @0", 1, 2).SqlText;
}
