private void QueryAll()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
var d2 = CreateDapperTestEntity(2);
var d3 = CreateDapperTestEntity(3);
try
{
var p = _dapper.QueryFirstOrDefault(m => m.char_null_value == "x" || m.varchar_null_value == "y");
var result = _dapper.QueryAll() ?? new List <DapperTestEntity>();
var sql = "select * from dapper_all_test";
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.Where(x => x.id == Convert.ToInt32(reader["id"])).FirstOrDefault());
}
});
}
finally
{
ClearDapperTestEntity();
}
}
public void UpdateSpeParamSync()
{
ClearDapperCompisiteKeyEntity();
var d1 = CreateDapperCompisiteKeyEntity();
try
{
var param = new List <KeyValuePair <string, object> >
{
new KeyValuePair <string, object>("products_name", $"{(d1.products_id + 100).ToString()}"),
new KeyValuePair <string, object>("products_description", $"{(d1.products_id + 100).ToString()}"),
new KeyValuePair <string, object>("products_viewed", (d1.products_id + 100))
};
_dapper.UpdateAsync(d1, param).GetAwaiter().GetResult();
d1.products_name = (d1.products_id + 100).ToString();
d1.products_description = (d1.products_id + 100).ToString();
d1.products_viewed = d1.products_id + 100;
var sql = $"select * from dapper_compisite_key where products_id = {d1.products_id} and language_id = {d1.language_id}";
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
private void QueryAsync()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
var d2 = CreateDapperTestEntity(2);
var d3 = CreateDapperTestEntity(3);
try
{
var dapperInstance = _dapper;
var filter = new SingleQueryFilter().AddEqual(nameof(DapperTestEntity.dec_value), 1);
var result = dapperInstance.QueryAsync(filter).GetAwaiter().GetResult();
helper.ExecuteReader("select * from dapper_all_test where id=1", reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.Where(x => x.id == Convert.ToInt32(reader["id"])).FirstOrDefault());
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryCollect()
{
ClearDapperCompisiteKeyEntity();
var d1 = CreateDapperCompisiteKeyEntity();
var d2 = CreateDapperCompisiteKeyEntity(2, 2);
var d3 = CreateDapperCompisiteKeyEntity(3, 3);
try
{
var dapperInstance = _dapper;
SingleQueryFilter filter = new SingleQueryFilter().AddGreater("products_id", 1).AddLess("products_id", 3);
var dapperQueryResult = dapperInstance.Query(filter);
CompareHelper.Compare(d2, dapperQueryResult.Where(s => s.products_id == 2).FirstOrDefault());
Assert.Equal(dapperQueryResult.Count(), 1);
SingleQueryFilter filter2 = new SingleQueryFilter().AddEqual("products_id", 1);
var comFilter1 = QueryFilter.CombineOr(filter, filter2);
dapperQueryResult = dapperInstance.Query(comFilter1);
CompareHelper.Compare(d2, dapperQueryResult.Where(s => s.products_id == 2).FirstOrDefault());
CompareHelper.Compare(d1, dapperQueryResult.Where(s => s.products_id == 1).FirstOrDefault());
Assert.Equal(dapperQueryResult.Count(), 2);
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
public void InsertSync()
{
ClearDapperCompisiteKeyEntity();
try
{
var d1 = new DapperCompisiteKeyEntity
{
language_id = 1,
products_description = "1",
products_id = 1,
products_name = "1",
products_short_description = "1",
products_url = "1",
products_viewed = 1
};
_dapper.InsertAsync(d1).GetAwaiter().GetResult();
var sql = string.Format("select * from dapper_compisite_key where products_id = {0} and language_id = {1}", d1.products_id, d1.language_id);
helper.ExecuteReader(sql, reader =>
{
if (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
public void Insert()
{
var dapperInstance = _dapper;
#region 实体对象赋值
var entity = new DapperTestEntity()
{
bigint_value = 1,
bit_value = true,
bool_value = true,
boolean_value = true,
date_value = DateTime.Now.Date,
char_null_value = null,
dec_value = 1,
decimal_value = 1,
double_value = 1,
fix_value = 1,
float_value = 1,
int_value = 1,
integer_value = 1,
longtext_null_value = null,
mediumint_value = 1,
mediumint_null_value = null,
nchar_null_value = null,
nvarchar_null_value = null,
numeric_value = 1,
real_value = 1,
smallint_value = 1,
text_null_value = "",
tinytext_null_value = null,
tinyint_value = 1,
varbinary_null_value = new byte[] { 0x01, 0x02, 0x03 },
binary_null_value = new byte[] { 0x01, 0x02, 0x03 },
id = 1
};
#endregion
try
{
ClearDapperTestEntity();
//Dapper插入对象
dapperInstance.Insert(entity);
string sql = string.Format("select * from dapper_all_test where id = '{0}'", entity.id);
helper.ExecuteReader("select * from dapper_all_test where id=1", reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, entity);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
public void InsertSync()
{
ClearTestEntity();
var dapperInstance = _dapper;
#region 实体对象赋值
var entity = new DapperAllTest()
{
BigintValue = 1,
BitValue = true,
BoolValue = true,
BooleanValue = true,
DateValue = DateTime.Now.Date,
CharNullValue = null,
DecValue = 1,
DecimalValue = 1,
DoubleValue = 1,
FixValue = 1,
FloatValue = 1,
IntValue = 1,
IntegerValue = 1,
LongtextNullValue = null,
MediumintValue = 1,
MediumintNullValue = null,
NcharNullValue = null,
NvarcharNullValue = null,
NumericValue = 1,
RealValue = 1,
SmallintValue = 1,
TextNullValue = "",
TinytextNullValue = null,
TinyintValue = 1,
VarbinaryNullValue = new byte[] { 0x01, 0x02, 0x03 },
BinaryNullValue = new byte[] { 0x01, 0x02, 0x03 },
Id = 1
};
#endregion
try
{
//Dapper插入对象
dapperInstance.InsertAsync(entity).GetAwaiter().GetResult();
string sql = string.Format("select * from dapper_all_test where id = '{0}'", entity.Id);
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, entity);
}
});
}
finally
{
ClearTestEntity();
}
}
public void InsertSync()
{
var dapperInstance = _dapper;
#region 实体对象赋值
var entity = new SQLiteDapperTestEntity
{
id = 22,
bigint_value = 2,
datetime_value = new DateTime(1988, 11, 12, 17, 34, 21),
date_value = new DateTime(1988, 11, 12),
decimal_value = 100m,
dec_value = 20.3m,
double_value = 300d,
fix_value = 500.5m,
float_value = 610f,
integer_value = 76543,
int_value = 888,
mediumint_value = 999,
numeric_value = 10101,
real_value = 12312,
smallint_value = 23,
tinyint_value = 11,
longtext_null_value = "codemonk"
};
#endregion
try
{
ClearDapperTestEntity();
var result = SQLiteHelper.GetConnection(x => x.Execute(@"insert into ""test_table""(""CREATE_TIME"")values(:ct)", new { ct = DateTime.UtcNow }));
Assert.True(result > 0);
//Dapper插入对象
dapperInstance.InsertAsync(entity).GetAwaiter().GetResult();
var sql = $@"select * from ""dapper_all_test"" where ""id"" = '{entity.id}'";
SQLiteHelper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, entity);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
public void UpdateSync()
{
ClearTestEntity();
var d1 = CreateTestEntity();
try
{
d1.BigintValue = 1;
d1.BitValue = true;
d1.BoolValue = true;
d1.BooleanValue = true;
d1.DateValue = DateTime.Now.AddDays(1);
d1.CharNullValue = null;
d1.DecValue = 8888;
d1.DecimalValue = 8888;
d1.DoubleValue = 888;
d1.FixValue = 8881;
d1.FloatValue = 888;
d1.IntValue = 888;
d1.IntegerValue = 8888;
d1.LongtextNullValue = null;
d1.MediumintValue = 8888;
d1.MediumintNullValue = null;
d1.NcharNullValue = null;
d1.NvarcharNullValue = null;
d1.NumericValue = 8888;
d1.RealValue = 8888;
d1.SmallintValue = 8888;
d1.TextNullValue = "";
d1.TinytextNullValue = null;
d1.TinyintValue = 127;
d1.VarbinaryNullValue = new byte[] { 0x01, 0x02, 0x03 };
d1.BinaryNullValue = new byte[] { 0x01, 0x02, 0x03 };
_dapper.UpdateAsync(d1).GetAwaiter().GetResult();
string sql = string.Format("select * from dapper_all_test where id = {0}", d1.Id);
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearTestEntity();
}
}
public void UpdateSync()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
try
{
d1.bigint_value = 1;
d1.bit_value = true;
d1.bool_value = true;
d1.boolean_value = true;
d1.date_value = DateTime.Now.AddDays(1);
d1.char_null_value = null;
d1.dec_value = 8888;
d1.decimal_value = 8888;
d1.double_value = 888;
d1.fix_value = 8881;
d1.float_value = 888;
d1.int_value = 888;
d1.integer_value = 8888;
d1.longtext_null_value = null;
d1.mediumint_value = 8888;
d1.mediumint_null_value = null;
d1.nchar_null_value = null;
d1.nvarchar_null_value = null;
d1.numeric_value = 8888;
d1.real_value = 8888;
d1.smallint_value = 8888;
d1.text_null_value = "";
d1.tinytext_null_value = null;
d1.tinyint_value = 127;
d1.varbinary_null_value = new byte[] { 0x01, 0x02, 0x03 };
d1.binary_null_value = new byte[] { 0x01, 0x02, 0x03 };
_dapper.UpdateAsync(d1).GetAwaiter().GetResult();
string sql = string.Format("select * from dapper_all_test where id = {0}", d1.id);
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryFirstOrDefaultAsync()
{
ClearDapperCompisiteKeyEntity();
var d1 = CreateDapperCompisiteKeyEntity();
try
{
var ins = _dapper;
SingleQueryFilter filter = new SingleQueryFilter();
filter.AddEqual("products_id", d1.products_id);
var d2 = ins.QueryFirstOrDefaultAsync(filter).GetAwaiter().GetResult();
CompareHelper.Compare(d1, d2);
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
private void QueryAsync()
{
var dapperInstance = _dapper;
var id = helper.GetValue("SELECT ifnull((SELECT MAX(products_id) FROM test.dapper_compisite_key),0)", Convert.ToInt32);
var insertId = helper.GetValue($"INSERT INTO test.dapper_compisite_key VALUES ({++id},{id}, '{id.ToString()}', '{id.ToString()}', '{id.ToString()}', '{id.ToString()}', 2); SELECT MAX(products_id) FROM dapper_compisite_key dck;select {id};", Convert.ToInt32);
SingleQueryFilter filter = new SingleQueryFilter().AddEqual("products_id", insertId);
var result = dapperInstance.QueryAsync(filter).GetAwaiter().GetResult() ?? new List <DapperCompisiteKeyEntity>();
helper.ExecuteReader($"select * from dapper_compisite_key where products_id={insertId}", reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.Where(x => x.products_id == Convert.ToInt32(reader["products_id"] ?? 0)).FirstOrDefault());
}
});
helper.ExecuteSQL($"delete from test.dapper_compisite_key where products_id = {insertId}", cmd => cmd.ExecuteNonQuery(), false);
}
public void UpdateSync()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
try
{
#region 实体对象赋值
d1.id = 22;
d1.bigint_value = 2;
d1.datetime_value = new DateTime(1988, 11, 12, 17, 34, 21);
d1.date_value = new DateTime(1988, 11, 12);
d1.decimal_value = 100m;
d1.dec_value = 20.3m;
d1.double_value = 300d;
d1.fix_value = 500.5m;
d1.float_value = 600.6f;
d1.integer_value = 76543;
d1.int_value = 888;
d1.mediumint_value = 999;
d1.numeric_value = 10101;
d1.real_value = 12312;
d1.smallint_value = 23;
d1.tinyint_value = 11;
d1.longtext_null_value = "codemonk";
#endregion
_dapper.UpdateAsync(d1).GetAwaiter().GetResult();
var sql = $@"select * from ""dapper_all_test"" where ""id""={d1.id}";
SQLiteHelper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryLess()
{
ClearDapperCompisiteKeyEntity();
var d1 = CreateDapperCompisiteKeyEntity();
var d2 = CreateDapperCompisiteKeyEntity(2, 2);
var d3 = CreateDapperCompisiteKeyEntity(3, 3);
try
{
var dapperInstance = _dapper;
SingleQueryFilter filter = new SingleQueryFilter().AddLess("products_id", 2);
var dapperQueryResult = dapperInstance.Query(filter);
CompareHelper.Compare(d1, dapperQueryResult.Where(s => s.products_id == 1).FirstOrDefault());
Assert.Equal(1, dapperQueryResult.Count());
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
private void QueryFirstOrDefaultAsync()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
try
{
var filter = new SingleQueryFilter().AddEqual("id", d1.id);
var result = _dapper.QueryFirstOrDefaultAsync(filter).GetAwaiter().GetResult();
var sql = string.Format("select * from dapper_all_test where id={0}", d1.id);
helper.ExecuteReader(sql, reader =>
{
if (reader.Read())
{
CompareHelper.Compare(reader, result);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
public void UpdateSync()
{
ClearDapperCompisiteKeyEntity();
var d1 = CreateDapperCompisiteKeyEntity();
try
{
d1.products_name = "testtest";
_dapper.UpdateAsync(d1).GetAwaiter().GetResult();
var sql = $"select * from dapper_compisite_key where products_id = {d1.products_id} and language_id = {d1.language_id}";
helper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, d1);
}
});
}
finally
{
ClearDapperCompisiteKeyEntity();
}
}
private void QueryAllAsync()
{
ClearDapperTestEntity();
CreateDapperTestEntity();
CreateDapperTestEntity(2);
CreateDapperTestEntity(3);
try
{
var result = _dapper.QueryAllAsync().GetAwaiter().GetResult() ?? new List <SQLiteDapperTestEntity>();
var sql = @"select * from ""dapper_all_test""";
SQLiteHelper.ExecuteReader(sql, reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.FirstOrDefault(x => x.id == Convert.ToInt32(reader["id"])));
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryFirstOrDefault()
{
ClearDapperTestEntity();
var d1 = CreateDapperTestEntity();
try
{
var dapperInstance = _dapper;
var filter = new SingleQueryFilter().AddEqual("id", d1.id);
var result = dapperInstance.QueryFirstOrDefault(filter);
var sql = $@"select * from ""dapper_all_test"" where ""id""={d1.id}";
SQLiteHelper.ExecuteReader(sql, reader =>
{
if (reader.Read())
{
CompareHelper.Compare(reader, result);
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryAsync()
{
ClearDapperTestEntity();
CreateDapperTestEntity();
CreateDapperTestEntity(2);
CreateDapperTestEntity(3);
try
{
var dapperInstance = _dapper;
var filter = new SingleQueryFilter().AddEqual(nameof(DapperEntityWithNoBool.dec_value), 200m);
var result = dapperInstance.QueryAsync(filter).GetAwaiter().GetResult();
SQLiteHelper.ExecuteReader(@"select * from ""dapper_all_test"" where ""id""=1", reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.FirstOrDefault(x => x.id == Convert.ToInt32(reader["id"])));
}
});
}
finally
{
ClearDapperTestEntity();
}
}
private void QueryAllAsync()
{
ClearTestEntity();
var d1 = CreateTestEntity();
var d2 = CreateTestEntity(2);
var d3 = CreateTestEntity(3);
try
{
var dapperInstance = _dapper;
var result = dapperInstance.QueryAllAsync().GetAwaiter().GetResult();
helper.ExecuteReader("select * from dapper_all_test", reader =>
{
while (reader.Read())
{
CompareHelper.Compare(reader, result.Where(x => x.Id == Convert.ToInt32(reader["id"] ?? 0)).FirstOrDefault());
}
});
}
finally
{
ClearTestEntity();
}
}
