Dapper的使用
宦烈
Dapper.NET是个开源的轻型ORM。它扩展了IDbConnection接口的功能，可以支持MS SQL Server、Oracle、MySQL、PostgreSQL、SQLite、SqlCe、Firebird等。
它的运行速度非常快，语法简单，能用很少的代码就能实现多条SQL语句一起被执行以及批量增、删、改。
下面列出其基本的用法：
首先创建对应的实体
public class DapperDemoEntity
{
public int ID { get; set; }
public string Test{ get; set; }
}新增单条记录（dapperDemo就是要填写的参数）
public int InsertDapperDemo(DapperDemoEntity dapperDemo)
{
using (IDbConnection connection = Common.OpenConnection())
{
const string sql =
@"INSERT INTO dbo.DapperNETDemo(Test) VALUES (@Test);
SELECT CAST(SCOPE_IDENTITY() AS INT)";
int dapperDemoID = connection.Query<int>(sql, dapperDemo).Single();
return dapperDemoID;
}
}
using (IDbConnection connection = Common.OpenConnection())
{
const string sql =
@"UPDATE dbo.DapperNETDemo
SET ParentID = @ParentID,
WHERE ID = @ID";
return connection.Execute(sql, dapperDemo);
}
批量插入,dapper支持list集合作为参数，批量的插入操作只需要一条sql语句即可，方便简单
public int InsertDapperDemoList(IList<DapperDemoEntity> list)
{
using (IDbConnection connection = Common.OpenConnection())
{
var transaction = connection.BeginTransaction();
var rowsAffectd =0;
const string sql = @"INSERT INTO dbo.DapperNETDemo(Test) VALUES (@Test)";
try
{
rowsAffectd = connection.Execute(sql, list, transaction);
transaction.Commit();
return rowsAffectd;
}
catch (Exception)
{
transaction.Rollback();
throw;
}
}
}批量删除 ，（这里介绍使用多条sql语句，匿名参数）
public int DeleteDapperDemoList(int id)
{
using (IDbConnection connection = Common.OpenConnection())
{
const string deleteChildSQL = @"DELETE FROM dbo.DapperNETDemo WHERE ID> 0 AND ID = @ID";
const string deleteParentSQL = @"DELETE FROM dbo.DapperNETDemo WHERE ParentID < 1 AND ID = @ID";
IDbTransaction transaction = connection.BeginTransaction();
int rowsAffected = connection.Execute(deleteChildSQL, new { ID=id }, transaction);
rowsAffected += connection.Execute(deleteParentSQL, new { ID = id }, transaction);
transaction.Commit();
return rowsAffected;
}
} 查询某个指定的字段,(该字段属于什么类型，在<>里填写对应类型即可)
public int GetChildDapperDemoCount()
{
using (IDbConnection connection = Common.OpenConnection())
{
const string sql =
@"SELECT Test
FROM dbo.DapperNETDemo where ID=1";
return connection.ExecuteScalar<string>(sql);
}
}获取列表
public IEnumerable<DapperDemoEntity> GetDapperDemoList()
{
using (IDbConnection connection = Common.OpenConnection())
{
const string query =@"SELECT * FROM dbo.DapperNETDemo";
return connection.Query<DapperDemoEntity>(query);
}
}这里指出一种返回子父类的列表
public class DapperDemoEntity
{
public int ID { get; set; }
public int ParentID { get; set; }
public string DapperDemoName { get; set; }
public Common.Type Type { get; set; }
public DateTime ModifiedDate { get; set; }
public DapperDemoParentEntity ParentDapperDemo { get; set; }
}
public class DapperDemoParentEntity
{
public int ParentID { get; set; }
public string DapperDemoParentName { get; set; }
public Common.Type ParentType { get; set; }
} /// <summary>
/// 返回列表
/// </summary>
/// <returns></returns>
/// <remarks>
/// 若返回列表中的每个对象所包含的另1个对象也需要返回，则需要用到splitOn参数。
/// 然而，如果第2个对象的分割列为Id，则可省略splitOn参数。
/// </remarks>
public IEnumerable<DapperDemoEntity> GetChildDapperDemoWithParentList()
{
using (IDbConnection connection = Common.OpenConnection())
{
const string query =
@"SELECT child.ID, child.Name AS DapperDemoName, child.[Type], child.ModifiedDate,
parent.ID AS ParentID, parent.Name AS DapperDemoParentName, parent.[Type] AS ParentType
FROM dbo.DapperNETDemo child WITH(NOLOCK)
LEFT JOIN dbo.DapperNETDemo parent WITH(NOLOCK) ON parent.ID = child.ParentID
WHERE parent.ID IS NOT NULL
ORDER BY child.ModifiedDate DESC";
return connection.Query<DapperDemoEntity, DapperDemoParentEntity, DapperDemoEntity>(query
, (child, parent) => { child.ParentDapperDemo = parent; child.ParentID = parent.ParentID ;return child; }, splitOn: "ParentID");
}
}存储过程的执行
public dynamic ProcedureWithOutAndReturnParameter1()
{
int successCode = -1;
string resultMessage = string.Empty;
using (IDbConnection connection = Common.OpenConnection())
{
DynamicParameters parameter = new DynamicParameters();
string name = "test1";
parameter.Add("@Name", name);
parameter.Add("@SuccessCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
parameter.Add("@ResultMessage", dbType: DbType.String, direction: ParameterDirection.Output, size: 255);
parameter.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
IEnumerable<dynamic> result = connection.Query(sql: "DapperNETDemoSP1", param: parameter, commandType: CommandType.StoredProcedure);
successCode = parameter.Get<int>("SuccessCode");
resultMessage = parameter.Get<string>("ResultMessage");
int s = parameter.Get<int>("Result");
dynamic row = result.Single();
return row;
}
}ALTER PROCEDURE [dbo].[DapperNETDemoSP1]
@Name VARCHAR(10),
@SuccessCode INT OUTPUT,
@ResultMessage VARCHAR(255) OUTPUT
AS
BEGIN
SELECT @Name AS NameResult
SET @SuccessCode = 0
SET @ResultMessage = '完成执行'
RETURN 42
END /// <summary>
/// 返回多个结果集
/// </summary>
/// <returns></returns>
public IEnumerable<dynamic> ProcedureWithOutAndReturnParameter2()
{
using (IDbConnection connection = Common.OpenConnection())
{
DynamicParameters parameter = new DynamicParameters();
string name = "test2";
parameter.Add("@Name", name);
parameter.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
IEnumerable<dynamic> resultA = null;
IEnumerable<dynamic> resultB = null;
using (SqlMapper.GridReader grid = connection.QueryMultiple(sql: "DapperNETDemoSP2", param: parameter, commandType: CommandType.StoredProcedure))
{
resultA = grid.Read<dynamic>();
resultB = grid.Read<dynamic>();
}
List<dynamic> result = new List<dynamic>();
result.Add(resultA.Single());
result.Add(resultB.Single());
return result;
}
}ALTER PROCEDURE [dbo].[DapperNETDemoSP2]
@Name VARCHAR(10)
AS
BEGIN
SELECT @Name AS NameResult1
SELECT 2 AS NameResult2
RETURN 2
END
类似资料：