1.前言
Dapper是一个轻量级的orm框架，上手也非常的简单，它可以实体映射，所以先准备实体如下：
public class Couser
{
public int id { get; set; }
public string courseName { get; set; }
}
public partial class Score
{
public int id { get; set; }
public int score { get; set; }
public int courseId { get; set; }
public int studentId { get; set; }
public Student student { get; set; }
public Couser couser { get; set; }
}
public partial class Student
{
public int id { get; set; }
public string name { get; set; }
public int sex { get; set; }
public string tel { get; set; }
public string other { get; set; }
public Score scoreModel { get; set; }
}
2.查询
1.QueryFist查询单个实体
Dapper会自动匹配class的属性和sql查询出来的字段进行数据的组装，这里的属性可以不是表中存在的而是自己定义添加的。但是不会对class中的类属性里的属性进行赋值，比如Student里面有个public Score scoreModel { get; set; }就不会对Score这个类里面的属性进行赋值。反而会对scoreModel进行赋值，加入sql语句返回了一个int类型的scoreModel字段的数据，但是和scoreModel的类型不匹配无法转换就会报错。
public Student QueryFirst_1()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
return Dapper.SqlMapper.QueryFirstOrDefault<Student>(conn, "select a.*,b.score as other,b.* from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = });
//return Dapper.SqlMapper.QueryFirst<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId ");
//return Dapper.SqlMapper.QueryFirst<Student>(conn, "select * from Student where id=123");
}
}
其实CommandDefinition传入的效果和直接传递参数效果相同，只是把原先要传递的参数先传到CommandDefinition中
public Student QueryFist_2()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
string strsql = "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id";
CommandDefinition command = new CommandDefinition(strsql, new { id = });
return Dapper.SqlMapper.QueryFirstOrDefault<Student>(conn, command);
}
}
2.QuerySingle查询单个实体
和QueryFist一样是查询单条数据，差别是QueryFist在返回多条数据的情况下会默认获取第一条，QuerySingle返回多条数据的话就会报错
public Student QuerySingle_1()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
return Dapper.SqlMapper.QuerySingleOrDefault<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = });
}
}
3.Query查询多条数据，单表查询
public List<Student> Query_1()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
return Dapper.SqlMapper.Query<Student>(conn, "select a.*,b.score as other from Student a left join Score b on a.id = b.studentId where a.id=@id ", new { id = }).ToList<Student>();
}
}
4.Query查询多条数据，多表的映射
splitOn默认值是id，它是用来切割将不同的类的属性匹配到相应的类中
public List<Student> Query_2()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
return Dapper.SqlMapper.Query<Student,Score,Student>(conn, "select a.*,b.* from Student a left join Score b on a.id = b.studentId where a.id=@id ",
(Student, Score) => { Student.scoreModel = Score; return Student; },
new { id = }, //参数
null, //事务
true, //是否缓存
"id",
null,//超时时间
null//执行类型，如存储过程等
).ToList<Student>();
}
}
3.增删改和事务
增删改的操作其实是差不多的，所以用一下代码为实例：
public void Add()
{
using (IDbConnection conn = new SqlConnection(sqlconnection))
{
string inesrtSql = "insert into Student(name,sex,tel) values (@name,@sex,@tel)";
Student student_1 = new Student();
student_1.name = "名字哦";
student_1.sex = ;
student_1.tel = "";
Student student_2 = new Student();
student_2.name = "名字哦1";
student_2.sex = ;
student_2.tel = "";
List<Student> list = new List<Student>();
list.Add(student_1);
list.Add(student_2);
conn.Open();//使用事务前必须打开链接
IDbTransaction tran = conn.BeginTransaction();
try
{
if (Dapper.SqlMapper.Execute(conn, inesrtSql, list, tran) > )//多条插入，不使用事务的话，执行错误会导致脏数据
{
tran.Commit();
//成功
}
else
{
//失败
}
}
catch (Exception ex)
{
tran.Rollback();
}
//if (Dapper.SqlMapper.Execute(conn, inesrtSql, student_1) > 0)//单条插入
//{
// //成功
//}
//else
//{
// //失败
//}
}
}
4.存储过程和函数
简单实例如下：
public void StoredProcedureAndFun()
{
using(IDbConnection conn = new SqlConnection(sqlconnection))
{
var para = new DynamicParameters();
para.Add("@id", );
para.Add("@res", , DbType.Int32, ParameterDirection.ReturnValue);//定义返回值
var res1 = conn.Query("storedProcedure", para, null, true, null, CommandType.StoredProcedure).FirstOrDefault(); //只要using Dapper就可以直接用conn.Query()的形式
para.Get<int>("@res");//获取返回值
}
}