public static void Test(string connString)
{
int count = 0;
var user = new User {
UniqueId = 1, UserName = "******", Age = 28, Sex = Sex.Male, DeptId = 1, UpdatedAt = DateTime.Now
};
var user1 = new User {
UniqueId = 2, UserName = "******", Age = 24, Sex = Sex.Female, DeptId = 2, UpdatedAt = DateTime.Now
};
var repository = new Repository <User>(connString);
//删除
count = repository.Delete(user);
count = repository.Delete(user1);
//创建
count = repository.Create(user);
count = repository.Create(user1);
//获取
user = repository.QueryFirst("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id=@UniqueId", user);
user = repository.Get(user);
//更新
user.UserName = "******";
user.Sex = Sex.Female;
count = repository.Update(f => f.Sex, user);
count = repository.Update(f => new { f.UserName, f.Sex }, user);
//动态SQL更新
var builder = new SqlBuilder();
builder.RawSql("UPDATE Coin_User SET UserName=@UserName")
.AddField(user.Sex.HasValue, "Sex=@Sex")
.AddSql("WHERE Id=@UniqueId")
.Where(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt");
count = repository.Update(builder.BuildSql(), user);
count = repository.Update(f => f.RawSql("UPDATE Coin_User SET UserName=@UserName")
.AddField(user.Sex.HasValue, "Sex=@Sex")
.AddSql("WHERE Id=@UniqueId")
.Where(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt"), user);
//查询&动态SQL
var list = repository.Query(f => f.RawSql("SELECT * FROM Coin_User")
.Where(user.Sex.HasValue, "Sex=@Sex")
.Where(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt")
.AddSql("ORDER BY UpdatedAt DESC"), user);
//分页
var userInfoList = repository.QueryPage <UserInfo>("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id>@UniqueId", 0, 10, "ORDER BY Id", user);
//事务
var context = new RepositoryContext(connString);
try
{
var repository1 = context.RepositoryFor();
var repositoryUser = context.RepositoryFor <User>();
var repositoryDept = context.RepositoryFor <Dept>();
context.Begin();
var deptInfo = repository1.QueryFirst <DeptInfo>("SELECT A.DeptId,B.PersonTotal FROM Coin_User A,Coin_Dept B WHERE A.DeptId=B.Id AND A.Id=@UniqueId", new { UniqueId = 1 });
repositoryUser.Delete(new User {
UniqueId = 1
});
repositoryDept.Update(f => f.PersonTotal, new Dept {
UniqueId = deptInfo.DeptId, PersonTotal = deptInfo.PersonTotal - 1
});
context.Commit();
}
catch (Exception ex)
{
context.Rollback();
}
//多结果集
var order = new Order {
Id = 1
};
var orderRepository = new Repository <Order>(connString);
var sql = "SELECT * FROM Coin_Order WHERE Id=@Id;SELECT * FROM Coin_OrderLine WHERE OrderId=@Id";
var reader = orderRepository.QueryMultiple(sql, order);
order = reader.Read <Order>();
order.Lines = reader.ReadList <OrderLine>();
order = orderRepository.QueryMap(map =>
{
var result = map.Read();
result.Lines = map.ReadList <OrderLine>();
return(result);
}, sql, order);
order.Number = "123456789";
orderRepository.Update(f => f.Number, order);
}
public static async Task TestAsync(string connString)
{
int count = 0;
var user = new User {
UniqueId = 1, UserName = "******", Age = 28, Sex = Sex.Male, DeptId = 1, UpdatedAt = DateTime.Now
};
var user1 = new User {
UniqueId = 2, UserName = "******", Age = 24, Sex = Sex.Female, DeptId = 2, UpdatedAt = DateTime.Now
};
var dept = new Dept {
UniqueId = 1, DeptName = "IT", PersonTotal = 1, UpdatedAt = DateTime.Now
};
var dept1 = new Dept {
UniqueId = 2, DeptName = "HR", PersonTotal = 1, UpdatedAt = DateTime.Now
};
var repository = new Repository <User>(connString);
//删除
count = await repository.DeleteAsync(user);
count = await repository.DeleteAsync(user1);
//创建
count = await repository.CreateAsync(user);
count = await repository.CreateAsync(user1);
//获取
user = await repository.QueryFirstAsync("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id=@UniqueId", user);
user = await repository.GetAsync(user);
//全量更新
await repository.UpdateAsync(user);
//更新
user.UserName = "******";
user.Sex = Sex.Female;
count = await repository.UpdateAsync(f => f.Sex, user);
count = await repository.UpdateAsync(f => new { f.UserName, f.Sex }, user);
count = await repository.UpdateAsync(user);
//动态SQL更新
var builder = new SqlBuilder();
builder.RawSql("UPDATE Coin_User SET UserName=@UserName", user.UserName)
.AddField(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.RawSql("WHERE Id=@UniqueId", user.UniqueId)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt);
count = await repository.UpdateAsync(builder.BuildSql(), user);
count = await repository.UpdateAsync(f =>
f.RawSql("UPDATE Coin_User SET UserName=@UserName", user.UserName)
.AddField(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.RawSql("WHERE Id=@UniqueId", user.UniqueId)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt));
//查询&动态SQL
var list = await repository.QueryAsync(f =>
{
f.RawSql("SELECT * FROM Coin_User")
.AndWhere(user.Sex.HasValue, "Sex=@Sex", user.Sex)
.AndWhere(user.UpdatedAt.HasValue, "UpdatedAt>@UpdatedAt", user.UpdatedAt)
.RawSql("ORDER BY UpdatedAt DESC");
});
//分页
var userInfoList = await repository.QueryPageAsync <UserInfo>("SELECT Id UniqueId,UserName,Sex FROM Coin_User WHERE Id>@UniqueId", 0, 10, "ORDER BY Id", user);
//事务
var context = new RepositoryContext(connString);
try
{
var repository1 = context.RepositoryFor();
var repositoryUser = context.RepositoryFor <User>();
var repositoryDept = context.RepositoryFor <Dept>();
context.Begin();
count = await repositoryDept.CreateAsync(dept);
count = await repositoryDept.CreateAsync(dept1);
var deptInfo = await repository1.QueryFirstAsync <DeptInfo>("SELECT A.DeptId,B.PersonTotal FROM Coin_User A,Coin_Dept B WHERE A.DeptId=B.Id AND A.Id=@UniqueId", new { UniqueId = 1 });
count = await repositoryUser.DeleteAsync(new User { UniqueId = 1 });
count = await repositoryDept.UpdateAsync(f => f.PersonTotal, new Dept { UniqueId = deptInfo.DeptId, PersonTotal = deptInfo.PersonTotal - 1 });
context.Commit();
}
catch
{
context.Rollback();
}
//多结果集
var order = new Order {
Id = 1
};
var orderRepository = new Repository <Order>(connString);
var sql = "SELECT * FROM Coin_Order WHERE Id=@Id;SELECT * FROM Coin_OrderLine WHERE OrderId=@Id";
List <OrderLine> orderLineList = new List <OrderLine>();
orderLineList.Add(new OrderLine
{
LineId = 1,
ProductId = 1,
Price = 15.5,
Quantity = 2,
Amount = 15.5 * 2
});
orderLineList.Add(new OrderLine
{
LineId = 2,
ProductId = 2,
Price = 20,
Quantity = 2,
Amount = 40
});
var order1 = new Order {
Id = 1, BuyerId = 1, Number = "12345", Lines = orderLineList
};
count = await orderRepository.DeleteAsync(order1);
count = await orderRepository.CreateAsync(order1);
var reader = await orderRepository.QueryMultipleAsync(sql, order);
order = reader.Read <Order>();
order.Lines = reader.ReadList <OrderLine>();
order = await orderRepository.QueryMapAsync(map =>
{
var result = map.Read <Order>();
result.Lines = map.ReadList <OrderLine>();
return(result);
}, sql, order);
order.Number = "123456789";
await orderRepository.UpdateAsync(f => f.Number, order);
var dict = repository.QueryDictionaryAsync <int, string>("SELECT Id [Key],UserName [Value] FROM Coin_User");
}