/// <summary>
/// 删除所有数据
/// </summary>
public void DeleteAll()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
var result = db.Delete <UserInfo>("", null);
}
/// <summary>
/// 修改数据
/// </summary>
public void Update()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
//根据修改字段参数及值、过滤SQL、过滤参数修改数据
var result = db.Update <UserInfo>(new { UserName = "******" }, "UserId=4", null);
result = db.Update <UserInfo>(new { UserName = "******" }, string.Format("UserId={0}UserId", dbOperator), new { UserId = 4 });
}
/// <summary>
/// 删除数据
/// </summary>
public void Delete()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
//根据实体主键参数值查询数据
var result = db.Delete <UserInfo>(1);
//根据过滤SQL、过滤参数查询数据列表1
result = db.Delete <UserInfo>("UserId=2", null);
result = db.Delete <UserInfo>(string.Format("UserId={0}UserId", dbOperator), new { UserId = 3 });
}
/// <summary>
/// 写入数据
/// </summary>
public void Insert()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
db.BeginTransaction();
db.IsShowSqlToConsole = _isShowSqlToConsole;
for (int i = 1; i <= 20; i++)
{
db.Insert <UserInfo>(new UserInfo()
{
UserId = i, UserName = "******" + i.ToString(), Age = 110, Email = "joyet" + i.ToString() + "@qq.com"
});
}
db.CommitTransaction();
}
/// <summary>
/// 查询数据
/// </summary>
public void Query()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
//根据实体主键参数值查询数据
var data = db.QueryById <UserInfo>(5);
//根据查询字段、过滤SQL、过滤参数查询数据列表
var dataList1 = db.Query <UserInfo>("UserId,UserName", "UserId=4", null);
var dataList2 = db.Query <UserInfo>("UserId,UserName", string.Format("UserId={0}UserId", dbOperator), new { UserId = 4 });
//根据查询参数化SQL、参数列表查询数据列表
var dataList3 = db.Query <UserInfo>("select * from UserInfo where UserId=4", null);
var dataList4 = db.Query <UserInfo>(string.Format("select * from UserInfo where UserId={0}UserId", dbOperator), new { UserId = 4 });
//分页查询列表
var pageDataList1 = db.QueryPageList <UserInfo>("*", "UserId>2", "UserId", "asc", 10, 1, null);
var pageDataList2 = db.QueryPageList <UserInfo>("UserId,b.UserName", string.Format("UserId>{0}UserId", dbOperator), "UserId", "asc", 10, 1, new { UserId = 2 });
}
/// <summary>
/// 执行非查询操作
/// </summary>
public void OrtherNoneQuery()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
var sql = "";
var paramSql = string.Format("delete from UserInfo where UserId={0}UserId", dbOperator);
//根据SQL语句、参数列表删除数据用法1
sql = "delete from UserInfo where UserId=16";
var result = db.ExecuteNoneQuery(sql, null);
var dbParams = new List <IDbDataParameter>();
dbParams.Add(dbFactory.GetDbParam("UserId", 17));
result = db.ExecuteNoneQuery(paramSql, dbParams);
//根据SQL语句、object参数列表删除数据用法2
sql = "delete from UserInfo where UserId=18";
result = db.ExecuteNoneQueryWithObjParam(sql);
result = db.ExecuteNoneQueryWithObjParam(paramSql, new { UserId = 19 });
}
/// <summary>
/// 执行查询操作
/// </summary>
public void OrtherQuery()
{
SqlDb db = new PostgreSqlDb(_connString);
db.IsShowSqlToConsole = _isShowSqlToConsole;
var dbFactory = db.DbBuilder.DbFactory;
var dbOperator = dbFactory.GetDbParamOperator();
var dbParams = new List <IDbDataParameter>();
var sql = "select * from UserInfo where UserId=4";
var paramSql = string.Format("select * from UserInfo where UserId={0}UserId", dbOperator);
//自定义查询,返回单条数据(无参)
UserInfo data;
using (var dataReader = db.ExecuteReaderWithObjParam(sql, null))
{
data = db.DataReaderToEntity <UserInfo>(dataReader);
}
using (var dataReader = db.ExecuteReader(sql, null))
{
data = db.DataReaderToEntity <UserInfo>(dataReader);
}
//自定义查询,返回单条数据(有参)
using (var dataReader = db.ExecuteReaderWithObjParam(paramSql, new { UserId = 4 }))
{
data = db.DataReaderToEntity <UserInfo>(dataReader);
}
dbParams = new List <IDbDataParameter>();
dbParams.Add(dbFactory.GetDbParam("UserId", 4));
using (var dataReader = db.ExecuteReader(paramSql, dbParams))
{
data = db.DataReaderToEntity <UserInfo>(dataReader);
}
//自定义查询,返回多条数据(无参)
sql = "select * from UserInfo where UserId>2";
List <UserInfo> dataList;
using (var dataReader = db.ExecuteReaderWithObjParam(sql, null))
{
dataList = db.DataReaderToEntityList <UserInfo>(dataReader);
}
using (var dataReader = db.ExecuteReader(sql, null))
{
dataList = db.DataReaderToEntityList <UserInfo>(dataReader);
}
//自定义查询,返回多条数据(有参)
paramSql = string.Format("select * from UserInfo where UserId>{0}UserId", dbOperator);
using (var dataReader = db.ExecuteReaderWithObjParam(paramSql, new { UserId = 2 }))
{
dataList = db.DataReaderToEntityList <UserInfo>(dataReader);
}
dbParams = new List <IDbDataParameter>();
dbParams.Add(dbFactory.GetDbParam("UserId", 2));
using (var dataReader = db.ExecuteReader(paramSql, dbParams))
{
dataList = db.DataReaderToEntityList <UserInfo>(dataReader);
}
}
