private static void Test_Person()
{
var model = new Person
{
Username = "******",
Password = "******",
Age = 1
};
var personDao=new PersonDao();
var isOkDelete = personDao.DeleteById(1);
var id = personDao.Add(model);
var isOkUpadte = personDao.UpdateById(new Person() {Username = "******"}, id);
var model01 = personDao.FindById(id);
var whereModel = new Person {Id = id};
var model02 = personDao.FindList(whereModel, "id=@id", 1);
//---
#region where--比较推荐的写法--可以提高程序的可读性
var where = new WhereEntity<Person>()
{
Model = new Person() { Id = id },
Sql = "id=@id",
OrderBy="id"
};
//
var model03 = personDao.FindListByPage(where.Model, where.Sql, where.OrderBy, 0, 10);
var count = personDao.Count(where.Model, where.Sql);
#endregion
}
//
public int Count(Person whereModel, string where)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(*) from [Person] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
var result = DbHelperSql.Count(strSql.ToString(), whereModel);
return result;
}
//
public IList<Person> FindList(Person whereModel, string where, int top)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select top {0} ", top);
strSql.Append("id,username,password,age,registerDate,address ");
strSql.Append("from [Person] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
var result = DbHelperSql.FindList<Person>
(strSql.ToString(), whereModel);
return result;
}
//
public int Add(Person model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into [Person](");
strSql.Append("username,password,age,registerDate,address)");
strSql.Append(" values (");
strSql.Append("@username,@password,@age,@registerDate,@address)");
//
var id = DbHelperSql.InsertReturnId(strSql.ToString(), model);
return id;
}
//
public IList<Person> FindListByPage(Person whereModel, string where, string orderBy, int pageIndex, int pageSize)
{
var startIndex = pageIndex * pageSize;
var size = pageSize;
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
strSql.Append("id,username,password,age,registerDate,address ");
strSql.Append("from [Person] ");
strSql.AppendFormat("where 1=1 and {0} ", where);
strSql.AppendFormat("ORDER BY {0} OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY", orderBy, startIndex, size);
var result = DbHelperSql.FindList<Person>(strSql.ToString(), whereModel);
return result;
}
//
public bool UpdateById(Person model, Int32 id)
{
model.Id = id;
StringBuilder strSql = new StringBuilder();
strSql.Append("update [Person] set ");
if (model.@Username != null)
strSql.Append("username=@Username,");
if (model.@Password != null)
strSql.Append("password=@Password,");
if (model.@Age != null)
strSql.Append("age=@Age,");
if (model.@RegisterDate != null)
strSql.Append("registerDate=@RegisterDate,");
if (model.@Address != null)
strSql.Append("address=@Address,");
int n = strSql.ToString().LastIndexOf(",", StringComparison.Ordinal);
if (n > 0) strSql.Remove(n, 1);
strSql.Append(" where id=@id ");
var flag = DbHelperSql.Update(strSql.ToString(), model);
return flag;
}