public void Test_Type_Insert_Select()
{
Runnable table = new Runnable();
table.Sql(@"
DROP TABLE IF EXISTS jerry_types;
CREATE TABLE jerry_types(
""Integer"" integer NOT NULL,
""Real"" real NOT NULL,
""Text"" text NOT NULL,
""Blob"" blob NOT NULL
);");
Runner.Command(table);
Runnable <TypeModel, object> insert = new Runnable <TypeModel, object>(TypeModel.GetSample());
insert.Sql("INSERT INTO jerry_types ( ");
insert.M(
p =>
p.ColNames()
);
insert.Sql(" ) VALUES ( ");
insert.M(p => p.Pars());
insert.Sql(");");
Runner.Command(insert);
Runnable <object, TypeModel> select = new Runnable <object, TypeModel>();
select.Sql("SELECT ");
select.R(p => p.Star());
select.Sql(" FROM jerry_types ");
select.R(p => p.Ali());
select.Sql(";");
TypeModel sample = TypeModel.GetSample();
TypeModel fromDb = Runner.Query(select).FirstOrDefault();
this.CompareTypeModels(fromDb, sample);
TypeModel fromDb2 = new TypeModel();
Runnable <TypeModel, object> bind = new Runnable <TypeModel, object>(fromDb2);
bind.Sql("SELECT ");
bind.M(p => p.Cols().As().Props());
bind.Sql(" FROM jerry_types ");
bind.M(p => p.Ali());
bind.Sql(";");
Runner.Command(bind);
this.CompareTypeModels(fromDb2, sample);
}
public void JsonParameters_GetCorrectNpgsqlType()
{
Runnable table = new Runnable();
table.Sql(@"
DROP TABLE IF EXISTS jerry_json;
CREATE TABLE jerry_json(
""Json"" json NOT NULL,
""JsonB"" jsonb NOT NULL
);");
Runner.Command(table);
TestModel sample = new TestModel()
{
Json = new JsonModel()
{
Value1 = 1, Value3 = 2
},
JsonB = new JsonModel()
{
Value1 = 3, Value3 = 4
},
};
Runnable <TestModel, object> insert = new Runnable <TestModel, object>(sample);
insert.Sql("INSERT INTO jerry_json ( ");
insert.M(p => p.ColNames());
insert.Sql(" ) VALUES ( ");
insert.M(p => p.Pars());
insert.Sql(");");
Runner.Command(insert);
Runnable <object, JsonView> select = new Runnable <object, JsonView>();
select.Sql("SELECT ");
select.R(p => p.Star());
select.Sql(" FROM jerry_json ");
select.R(p => p.Ali());
select.Sql(";");
JsonView fromDb = Runner.Query(select).FirstOrDefault();
fromDb.Json.ShouldNotBeNull();
fromDb.JsonB.ShouldNotBeNull();
fromDb.Json.Value1.ShouldBe(sample.Json.Value1);
fromDb.Json.Value3.ShouldBe(sample.Json.Value3);
fromDb.JsonB.Value1.ShouldBe(sample.JsonB.Value1);
fromDb.JsonB.Value3.ShouldBe(sample.JsonB.Value3);
}
public void TableValuedParameters_AreBoundCorrectly()
{
Runnable <object, object> table = new Runnable <object, object>();
table.Sql(@"
IF type_id('jerry_tt') IS NOT NULL
DROP TYPE jerry_tt;
CREATE TYPE jerry_tt AS TABLE
(
Num1 int NOT NULL,
Num2 int NOT NULL
);");
Runner.Command(table);
TestModel testModel = new TestModel()
{
Tvp = new List <TvpModel>()
{
new TvpModel()
{
Num1 = 1, Num2 = 1
},
new TvpModel()
{
Num1 = 2, Num2 = 2
},
new TvpModel()
{
Num1 = 4, Num2 = 4
},
new TvpModel()
{
Num1 = 8, Num2 = 8
},
new TvpModel()
{
Num1 = 16, Num2 = 16
},
}
};
Runnable <TestModel, int> select = new Runnable <TestModel, int>(testModel);
select.Sql("SELECT ");
select.M(p => p.Open(m => m.Tvp).Col(m => m.Num1));
select.Sql(" * ");
select.M(p => p.Open(m => m.Tvp).Col(m => m.Num2));
select.Sql(" AS ");
select.R(p => p.Prop());
select.Sql(" FROM ");
select.M(p => p.Tvp(m => m.Tvp));
IList <int> result = Runner.Query(select);
result.ShouldBe(new[] { 1, 4, 16, 64, 256 });
}
public void Test_EfCore_Crud()
{
Runnable <object, AddressView> table = new Runnable <object, AddressView>();
table.Sql("CREATE TABLE IF NOT EXISTS ");
table.R(p => p.TblName());
table.Sql("( ");
table.R(p => p.ColName(m => m.Id));
table.Sql(" );");
table.Sql("DELETE FROM ");
table.R(p => p.TblName());
table.Sql(";");
table.Sql("INSERT INTO ");
table.R(p => p.TblName());
table.Sql(" VALUES (12);");
table.Sql("SELECT ");
table.R(p => p.Col(m => m.Id));
table.Sql(" AS ");
table.R(p => p.Prop(m => m.Id));
table.Sql(" FROM ");
table.R(p => p.Tbl());
table.Sql(";");
IList <AddressView> addresses = Runner.Query(table);
addresses.ShouldNotBeNull();
addresses.Count.ShouldBe(1);
addresses[0].Id.ShouldBe(12);
}
public void Test_MultiSelect_WithRefcursorsOnRazor()
{
Runnable <object, int> select = new Runnable <object, int>();
select.Sql("BEGIN OPEN ");
select.R(m => m.Refcursor());
select.Sql(" FOR SELECT 1 AS ");
select.R(m => m.Prop());
select.Sql(" FROM dual; OPEN ");
select.R(m => m.Refcursor());
select.Sql(" FOR SELECT 2 AS ");
select.R(m => m.Prop());
select.Sql(" FROM dual; END;");
IList <int> result1 = Runner.Query(select);
result1.ShouldBe(new[] { 1, 2 });
}
public void Test_Razor_Star()
{
var runner = new Runner();
var model = new Runnable <object, BlogView>(separator: ",");
model.R(p => p.Star());
model.Sql(";");
model.R(p => p.Star(m => m.Posts));
model.Sql(";");
model.R(p => p.Cols().As().Props());
var expected1 = @"T0.""Id"" AS ""Item.Id"",T0.""Title"" AS ""Item.Title"",T0.""CategoryId"" AS ""Item.CategoryId""";
var expected2 = @"T1.""Id"" AS ""Item.Posts.Item.Id"",T1.""BlogId"" AS ""Item.Posts.Item.BlogId"",T1.""CreatedOn"" AS ""Item.Posts.Item.CreatedOn""," +
@"T1.""Headline"" AS ""Item.Posts.Item.Headline"",T1.""Content"" AS ""Item.Posts.Item.Content""";
var result = runner.Sql(model);
result.ShouldBe($"{expected1};{expected2};{expected1}");
}
public string Sql <TResult>(Func <IProjection <TResult>, ISqlWritable> func)
{
Runnable <object, TResult> runnable = new Runnable <object, TResult>();
runnable.R(p => func(p.With(options: new ProjectionOptions(p.Options)
{
Separator = ","
})));
return(this.Sql(runnable));
}
public void Test_Razor_Tbls()
{
var runner = new Runner();
var model = new Runnable <object, BlogView>();
model.R(p => p.Tbl());
model.Sql(";");
model.R(p => p.Tbl(m => m.Posts));
var result1 = runner.Sql <BlogView>(p => p.Tbl());
var result2 = runner.Sql <BlogView>(p => p.Tbl(m => m.Posts));
var result3 = runner.Sql <BlogView>(p => p.Open(m => m.Posts).Tbl());
var result4 = runner.Sql <BlogView>(p => p.Tbl(m => m.Id));
var result5 = runner.Sql(model);
result1.ShouldBe(@"""dbo"".""Blog"" T0");
result2.ShouldBe(@"""dbo"".""BlogPost"" T0");
result3.ShouldBe(@"""dbo"".""BlogPost"" T0");
result4.ShouldBe(@"""dbo"".""Blog"" T0");
result5.ShouldBe(@"""dbo"".""Blog"" T0;""dbo"".""BlogPost"" T1");
}
public void Test_Razor_Cols()
{
var runner = new Runner();
var model = new Runnable <object, BlogView>(separator: ",");
model.R(p => p.Col(m => m.Title));
model.Sql(";");
model.R(p => p.Open(m => m.Posts).Col(m => m.Headline));
var result1 = runner.Sql <BlogView>(p => p.Col(m => m.Title));
var result2 = runner.Sql <BlogView>(p => p.Cols());
var result3 = runner.Sql <BlogView>(p => p.Cols(m => m.Posts));
var result4 = runner.Sql <BlogView>(p => p.Open(m => m.Posts).Cols());
var result5 = runner.Sql(model);
result1.ShouldBe(@"T0.""Title""");
result2.ShouldBe(@"T0.""Id"",T0.""Title"",T0.""CategoryId""");
result3.ShouldBe(@"T0.""Id"",T0.""BlogId"",T0.""CreatedOn"",T0.""Headline"",T0.""Content""");
result4.ShouldBe(result3);
result5.ShouldBe(@"T0.""Title"";T1.""Headline""");
}
public void Test_EfCore_Query_OneToMany()
{
Runnable <object, Order> table = new Runnable <object, Order>();
table.Sql("SELECT ");
table.Sql("1 AS "); table.R(p => p.Prop(m => m.Id));
table.Sql(",1 AS "); table.R(p => p.Prop(m => m.BillingAddress.Id));
table.Sql(",1 AS "); table.R(p => p.Prop(m => m.ShippingAddress.Id));
table.Sql(" UNION ALL SELECT ");
table.Sql("2 AS "); table.R(p => p.Prop(m => m.Id));
table.Sql(",2 AS "); table.R(p => p.Prop(m => m.BillingAddress.Id));
table.Sql(",NULL AS "); table.R(p => p.Prop(m => m.ShippingAddress.Id));
table.Sql(";SELECT ");
table.Sql("1 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Id));
table.Sql(",1 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.OrderId));
table.Sql(",'Product 1' AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Product));
table.Sql(" UNION ALL SELECT ");
table.Sql("2 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Id));
table.Sql(",1 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.OrderId));
table.Sql(",'Product 2' AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Product));
table.Sql(" UNION ALL SELECT ");
table.Sql("3 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Id));
table.Sql(",1 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.OrderId));
table.Sql(",'Product 3' AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Product));
table.Sql(" UNION ALL SELECT ");
table.Sql("4 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Id));
table.Sql(",2 AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.OrderId));
table.Sql(",'Product 1' AS "); table.R(p => p.Open(m => m.OrderLine).Prop(m => m.Product));
IList <Order> orders = Runner.Query(table);
orders.Count.ShouldBe(2);
orders[0].BillingAddress.ShouldNotBeNull();
orders[0].ShippingAddress.ShouldNotBeNull();
orders[0].OrderLine.ShouldNotBeNull();
orders[0].OrderLine.Count.ShouldBe(3);
orders[1].BillingAddress.ShouldNotBeNull();
orders[1].ShippingAddress.ShouldBeNull();
orders[1].OrderLine.ShouldNotBeNull();
orders[1].OrderLine.Count.ShouldBe(1);
}
public void TypesAndParameters_AreBoundProperly()
{
Runnable <object, object> table = new Runnable <object, object>();
table.Sql(@"
DROP TABLE IF EXISTS jerry_types;
CREATE TABLE jerry_types(
`BigInt` bigint NOT NULL,
`TinyInt` tinyint NOT NULL,
`Double` double NOT NULL,
`Int` int SIGNED NOT NULL,
`Float` float NOT NULL,
`Date` date NOT NULL,
`Decimal` decimal NOT NULL,
`DateTime` datetime NOT NULL,
`TimeStamp` timestamp NOT NULL,
`Time` time NOT NULL,
`Char` char(20) NOT NULL,
`VarChar` varchar(20) NOT NULL,
`Text` text NOT NULL,
`Blob` blob NOT NULL,
`LongBlob` longblob NOT NULL,
`MediumBlob` mediumblob NOT NULL,
`TinyBlob` tinyblob NOT NULL,
`MediumInt` mediumint NOT NULL,
`LongText` longtext NOT NULL,
`MediumText` mediumtext NOT NULL,
`TinyText` tinytext NOT NULL,
`SmallInt` smallint NOT NULL,
`UBigInt` bigint UNSIGNED NOT NULL,
`UInt` int UNSIGNED NOT NULL,
`UMediumInt` mediumint UNSIGNED NOT NULL,
`USmallInt` smallint UNSIGNED NOT NULL,
`UTinyInt` tinyint UNSIGNED NOT NULL,
`Year` year NOT NULL,
`Binary` binary(20) NOT NULL,
`VarBinary` varbinary(20) NOT NULL,
`Enum` enum('Jerrycurl', 'EF') NOT NULL,
`Set` set('Jerrycurl', 'EF') NOT NULL
);");
Runner.Command(table);
Runnable <TypeModel, object> insert = new Runnable <TypeModel, object>(TypeModel.GetSample());
insert.Sql("INSERT INTO jerry_types ( ");
insert.M(p => p.ColNames());
insert.Sql(" ) VALUES ( ");
insert.M(p => p.Pars());
insert.Sql(")");
Runner.Command(insert);
Runnable <object, TypeModel> select = new Runnable <object, TypeModel>();
select.Sql("SELECT ");
select.R(p => p.Star());
select.Sql(" FROM jerry_types ");
select.R(p => p.Ali());
TypeModel sample = TypeModel.GetSample();
TypeModel fromDb = Runner.Query(select).FirstOrDefault();
this.CompareTypeModels(fromDb, sample);
TypeModel fromDb2 = new TypeModel();
Runnable <TypeModel, object> bind = new Runnable <TypeModel, object>(fromDb2);
bind.Sql("SELECT ");
bind.M(p => p.Cols().As().Props());
bind.Sql(" FROM jerry_types ");
bind.M(p => p.Ali());
Runner.Command(bind);
this.CompareTypeModels(fromDb2, sample);
}
public void JsonValue_IsReferencedCorrectly()
{
Runnable table = new Runnable();
table.Sql(@"
DROP TABLE IF EXISTS jerry_json;
CREATE TABLE jerry_json(
[Json] nvarchar(MAX) NOT NULL
);");
Runner.Command(table);
List <TestModel> testModels = new List <TestModel>()
{
new TestModel()
{
Json = new JsonModel()
{
Value1 = 10, Value3 = 20
}
},
new TestModel()
{
Json = new JsonModel()
{
Value1 = 20, Value3 = 30
}
},
};
Runnable <TestModel, object> insert1 = new Runnable <TestModel, object>(testModels[0]);
Runnable <TestModel, object> insert2 = new Runnable <TestModel, object>(testModels[1]);
insert1.Sql("INSERT INTO jerry_json ( [Json] ) VALUES ( ");
insert1.M(p => p.Par(m => m.Json));
insert1.Sql(" );");
insert2.Sql("INSERT INTO jerry_json ( [Json] ) VALUES ( ");
insert2.M(p => p.Par(m => m.Json));
insert2.Sql(" );");
Runner.Command(insert1);
Runner.Command(insert2);
Runnable <object, JsonView> select = new Runnable <object, JsonView>();
select.Sql("SELECT [Json] AS ");
select.R(p => p.Prop(m => m.Json));
select.Sql(", [Json] AS ");
select.R(p => p.Prop(m => m.JsonString));
select.Sql(" FROM jerry_json ");
select.R(p => p.Ali());
select.Sql(" WHERE ");
select.R(p => p.Json(m => m.Json.Value1));
select.Sql(" = 10 AND ");
select.R(p => p.Json(m => m.Json.Value3));
select.Sql(" = 20 ");
IList <JsonView> result = Runner.Query(select);
result.Count.ShouldBe(1);
result[0].Json.Value1.ShouldBe(10);
result[0].Json.Value3.ShouldBe(20);
}
public void Test_Tvp_Select()
{
Runnable table = new Runnable();
table.Sql(@"
IF type_id('jerry_tt') IS NOT NULL
DROP TYPE jerry_tt;
CREATE TYPE jerry_tt AS TABLE
(
""Bool"" bit,
""Int16"" smallint,
""Int32"" int,
""Int64"" bigint,
""Float"" real,
""Double"" float,
""Decimal"" decimal(10, 3),
""DateTimeOffset"" datetimeoffset(7),
""DateTime"" datetime,
""DateTime2"" datetime2(7),
""Time"" time(7),
""String"" nvarchar(MAX),
""Bytes"" varbinary(MAX),
""Guid"" uniqueidentifier
);");
Runner.Command(table);
var inputModel = new TestModel()
{
Tvp = new List <TvpModel>()
{
new TvpModel()
{
Bool = true,
Int16 = 1658,
Int32 = 4582717,
Int64 = 9237938798572,
Float = 16.6f,
Double = 16.8d,
Decimal = 2352.235m,
DateTime = new DateTime(1819, 1, 24, 7, 22, 44),
DateTime2 = new DateTime(0019, 1, 23, 6, 22, 59),
DateTimeOffset = new DateTimeOffset(1819, 1, 22, 13, 11, 22, TimeSpan.FromHours(2)),
Time = new TimeSpan(1, 2, 3),
String = "Long",
Guid = Guid.NewGuid(),
Bytes = new byte[] { 1, 2, 3 },
},
new TvpModel()
{
Bool = true,
Int16 = 1458,
Int32 = 45487317,
Int64 = 9279238798572,
Float = 16.63f,
Double = 16.84d,
Decimal = 1618616.888m,
DateTime = new DateTime(1819, 1, 24, 7, 22, 44),
DateTime2 = new DateTime(0019, 1, 23, 6, 22, 59),
DateTimeOffset = new DateTimeOffset(1819, 1, 22, 18, 11, 22, TimeSpan.FromHours(2)),
Time = new TimeSpan(1, 2, 3),
String = "Longer",
Guid = Guid.NewGuid(),
Bytes = new byte[] { 1, 2, 3, 4, 5, 6 },
},
}
};
Runnable <TestModel, TestModel> select = new Runnable <TestModel, TestModel>(inputModel);
select.Sql("SELECT ");
select.R(p => p.Star(m => m.Tvp, "X"));
select.Sql(" FROM ");
select.M(p => p.Tvp(m => m.Tvp, "X"));
TestModel result = Runner.Aggregate(select);
inputModel.Tvp.ShouldBeSameAsJson(result.Tvp);
}
public void TypesAndParameters_AreBoundProperly()
{
Runnable <object, object> drop1 = new Runnable <object, object>();
Runnable <object, object> drop2 = new Runnable <object, object>();
Runnable <object, object> create1 = new Runnable <object, object>();
Runnable <object, object> create2 = new Runnable <object, object>();
drop1.Sql(@"
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ""jerry_types""';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;");
drop2.Sql(@"
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ""jerry_types2""';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;");
create1.Sql(@"
CREATE TABLE ""jerry_types""(
""Char"" char(20) NOT NULL,
""VarChar"" varchar(20) NOT NULL,
""VarChar2"" varchar2(20) NOT NULL,
""Clob"" clob NOT NULL,
""NClob"" nclob NOT NULL,
""NChar"" nchar(20) NOT NULL,
""NVarChar2"" nvarchar2(20) NOT NULL,
""Long"" long NOT NULL,
""Date"" date NOT NULL,
""Number"" number NOT NULL,
""Blob"" blob NOT NULL,
""Raw"" raw(20) NOT NULL,
""TimeStamp"" timestamp NOT NULL,
""TimeStampTz"" timestamp with time zone NOT NULL,
""TimeStampLz"" timestamp with local time zone NOT NULL,
""IntervalDS"" interval day to second NOT NULL
)");
create2.Sql(@"
CREATE TABLE ""jerry_types2""(
""LongRaw"" long raw NOT NULL
)");
Runner.Command(drop1);
Runner.Command(drop2);
Runner.Command(create1);
Runner.Command(create2);
Runnable <TypeModel, object> insert1 = new Runnable <TypeModel, object>(TypeModel.GetSample());
Runnable <TypeModel2, object> insert2 = new Runnable <TypeModel2, object>(TypeModel2.GetSample());
insert1.Sql(@"INSERT INTO ""jerry_types"" ( ");
insert1.M(p => p.ColNames());
insert1.Sql(" ) VALUES ( ");
insert1.M(p => p.Pars());
insert1.Sql(")");
insert2.Sql(@"INSERT INTO ""jerry_types2"" ( ");
insert2.M(p => p.ColNames());
insert2.Sql(" ) VALUES ( ");
insert2.M(p => p.Pars());
insert2.Sql(")");
Runner.Command(insert1);
Runner.Command(insert2);
Runnable <object, TypeModel> select1 = new Runnable <object, TypeModel>();
Runnable <object, TypeModel2> select2 = new Runnable <object, TypeModel2>();
select1.Sql("SELECT ");
select1.R(p => p.Star());
select1.Sql(@" FROM ""jerry_types"" ");
select1.R(p => p.Ali());
select2.Sql("SELECT ");
select2.R(p => p.Star());
select2.Sql(@" FROM ""jerry_types2"" ");
select2.R(p => p.Ali());
TypeModel sample1 = TypeModel.GetSample();
TypeModel2 sample2 = TypeModel2.GetSample();
TypeModel fromDb1 = Runner.Query(select1).FirstOrDefault();
TypeModel2 fromDb2 = Runner.Query(select2).FirstOrDefault();
this.CompareTypeModels(fromDb1, sample1);
this.CompareTypeModels(fromDb2, sample2);
TypeModel fromDb3 = new TypeModel();
TypeModel2 fromDb4 = new TypeModel2();
Runnable <TypeModel, object> bind1 = new Runnable <TypeModel, object>(fromDb3);
Runnable <TypeModel2, object> bind2 = new Runnable <TypeModel2, object>(fromDb4);
bind1.Sql("SELECT ");
bind1.M(p => p.Cols().As().Props());
bind1.Sql(@" FROM ""jerry_types"" ");
bind1.M(p => p.Ali());
bind2.Sql("SELECT ");
bind2.M(p => p.Cols().As().Props());
bind2.Sql(@" FROM ""jerry_types2"" ");
bind2.M(p => p.Ali());
Runner.Command(bind1);
Runner.Command(bind2);
this.CompareTypeModels(fromDb3, sample1);
this.CompareTypeModels(fromDb4, sample2);
}
public void JsonValue_IsReferencedCorrectly()
{
Runnable drop = new Runnable();
Runnable create = new Runnable();
drop.Sql(@"
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ""jerry_json""';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;");
create.Sql(@"
CREATE TABLE ""jerry_json""(
""Json"" varchar2(8000) NOT NULL
)");
Runner.Command(drop);
Runner.Command(create);
List <TestModel> testModels = new List <TestModel>()
{
new TestModel()
{
Json = new JsonModel()
{
Value1 = 10, Value3 = 20
}
},
new TestModel()
{
Json = new JsonModel()
{
Value1 = 20, Value3 = 30
}
},
};
Runnable <TestModel, object> insert1 = new Runnable <TestModel, object>(testModels[0]);
Runnable <TestModel, object> insert2 = new Runnable <TestModel, object>(testModels[1]);
insert1.Sql(@"INSERT INTO ""jerry_json"" ( ""Json"" ) VALUES ( ");
insert1.M(p => p.Par(m => m.Json));
insert1.Sql(" )");
insert2.Sql(@"INSERT INTO ""jerry_json"" ( ""Json"" ) VALUES ( ");
insert2.M(p => p.Par(m => m.Json));
insert2.Sql(" )");
Runner.Command(insert1);
Runner.Command(insert2);
Runnable <object, JsonView> select = new Runnable <object, JsonView>();
select.Sql(@"SELECT ""Json"" AS ");
select.R(p => p.Prop(m => m.Json));
select.Sql(@" FROM ""jerry_json"" ");
select.R(p => p.Ali());
select.Sql(" WHERE ");
select.R(p => p.Json(m => m.Json.Value1));
select.Sql(" = 10 AND ");
select.R(p => p.Json(m => m.Json.Value3));
select.Sql(" = 20 ");
IList <JsonView> result = Runner.Query(select);
result.Count.ShouldBe(1);
result[0].Json.Value1.ShouldBe(10);
result[0].Json.Value3.ShouldBe(20);
}
public void Test_Type_Insert_Select()
{
Runnable table = new Runnable();
table.Sql(@"
DROP TABLE IF EXISTS jerry_types;
CREATE TABLE jerry_types(
[BigInt] bigint NOT NULL,
[Bit] bit NOT NULL,
[SmallInt] smallint NOT NULL,
[Real] real NOT NULL,
[Int] int NOT NULL,
[Float] float NOT NULL,
[Decimal] decimal(20, 9),
[DateTimeOffset] datetimeoffset NOT NULL,
[Date] date NOT NULL,
[DateTime] datetime NOT NULL,
[DateTime2] datetime2 NOT NULL,
[SmallDateTime] smalldatetime NOT NULL,
[Time] time NOT NULL,
[NChar] nchar(20) NOT NULL,
[NVarChar] nvarchar(20) NOT NULL,
[NText] ntext NOT NULL,
[Char] char(20) NOT NULL,
[VarChar] varchar(20) NOT NULL,
[Text] text NOT NULL,
[Xml] xml NOT NULL,
[Image] image NOT NULL,
[Binary] binary(20) NOT NULL,
[VarBinary] varbinary(20) NOT NULL,
[UniqueIdentifier] uniqueidentifier NOT NULL
);");
Runner.Command(table);
Runnable <TypeModel, object> insert = new Runnable <TypeModel, object>(TypeModel.GetSample());
insert.Sql("INSERT INTO jerry_types ( ");
insert.M(p => p.ColNames());
insert.Sql(" ) VALUES ( ");
insert.M(p => p.Pars());
insert.Sql(")");
Runner.Command(insert);
Runnable <object, TypeModel> select = new Runnable <object, TypeModel>();
select.Sql("SELECT ");
select.R(p => p.Star());
select.Sql(" FROM jerry_types ");
select.R(p => p.Ali());
TypeModel sample = TypeModel.GetSample();
TypeModel fromDb = Runner.Query(select).FirstOrDefault();
this.CompareTypeModels(fromDb, sample);
TypeModel fromDb2 = new TypeModel();
Runnable <TypeModel, object> bind = new Runnable <TypeModel, object>(fromDb2);
bind.Sql("SELECT ");
bind.M(p => p.Cols().As().Props());
bind.Sql(" FROM jerry_types ");
bind.M(p => p.Ali());
Runner.Command(bind);
this.CompareTypeModels(fromDb2, sample);
}
public void TypesAndParameters_AreBoundProperly()
{
Runnable <object, object> table = new Runnable <object, object>();
table.Sql(@"
DROP TABLE IF EXISTS jerry_types;
CREATE TABLE jerry_types(
""Char"" char(20) NOT NULL,
""VarChar"" varchar(20) NOT NULL,
""Text"" text NOT NULL,
""SmallInt"" smallint NOT NULL,
""Integer"" integer NOT NULL,
""Real"" float NOT NULL,
""Double"" double precision NOT NULL,
""Numeric"" numeric NOT NULL,
""Date"" date NOT NULL,
""TimeStamp"" timestamp NOT NULL,
""TimeStampTz"" timestamptz NOT NULL,
""Time"" time NOT NULL,
""Interval"" interval NOT NULL,
""Uuid"" uuid NOT NULL,
""ArrayOfInt"" int[] NOT NULL,
""ArrayOfVarChar"" varchar(20)[] NOT NULL,
""Bytea"" bytea NOT NULL,
""BigInt"" bigint NOT NULL,
""Money"" money NOT NULL,
""Xml"" xml NOT NULL,
""Boolean"" boolean NOT NULL,
""Macaddr"" macaddr NOT NULL,
""Cidr"" cidr NOT NULL,
""Inet"" inet NOT NULL
);");
Runner.Command(table);
Runnable <TypeModel, object> insert = new Runnable <TypeModel, object>(TypeModel.GetSample());
insert.Sql("INSERT INTO jerry_types ( ");
insert.M(p => p.ColNames());
insert.Sql(" ) VALUES ( ");
insert.M(p => p.Pars());
insert.Sql(");");
Runner.Command(insert);
Runnable <object, TypeModel> select = new Runnable <object, TypeModel>();
select.Sql("SELECT ");
select.R(p => p.Star());
select.Sql(" FROM jerry_types ");
select.R(p => p.Ali());
select.Sql(";");
TypeModel sample = TypeModel.GetSample();
TypeModel fromDb = Runner.Query(select).FirstOrDefault();
this.CompareTypeModels(fromDb, sample);
TypeModel fromDb2 = new TypeModel();
Runnable <TypeModel, object> bind = new Runnable <TypeModel, object>(fromDb2);
bind.Sql("SELECT ");
bind.M(p => p.Cols().As().Props());
bind.Sql(" FROM jerry_types ");
bind.M(p => p.Ali());
bind.Sql(";");
Runner.Command(bind);
this.CompareTypeModels(fromDb2, sample);
}