using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){string sql ="select * from Person where last_name=@last_name";IEnumerable<Person> list = db.Query<Person>(sql,new{ last_name = lastName });return list.ToList();}
新增
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){string sql ="insert into Person values(@first_name,@last_name,@email,@gender,@createdon);";int result = db.Execute(sql, person);return result >0;}
修改
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){string sql ="update Person set first_name=@first_name,last_name=@last_name,email=@email where id=@id;";int result = db.Execute(sql, person);return result >0;}
删除
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){string sql ="delete from Person where id=@id;";int result = db.Execute(sql,new{ id = ID });return result >0;}
存储过程
无参
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){IEnumerable<Person> list = db.Query<Person>("dbo.P_Person",null,null,true,null, CommandType.StoredProcedure);return list.ToList();}
有参
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){var p =newDynamicParameters();p.Add("@first_name", name);p.Add("@gender", sex);p.Add("@countNum",0, DbType.Int32, ParameterDirection.Output);db.Execute("dbo.P_PersonParams", p,null,null, CommandType.StoredProcedure);return p.Get<int>("@countNum");}
事务
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){db.Open();IDbTransaction trans = db.BeginTransaction();try{string sql ="delete from Person where id=@id";db.Execute(sql,new{ id = ID }, trans,null,null);trans.Commit();returntrue;}catch(Exception ex){trans.Rollback();Console.WriteLine(ex.ToString());returnfalse;}finally{db.Close();}}
多映射
一对一
using(IDbConnection db =newSqlConnection(DBHelp.ConnStrings)){string sql ="select * from Person p inner join Students s on p.first_name=s.Name";var list = db.Query<Person, Student, Person>(sql,(person, student)=>{return person;},null,null,true,"Id");return list;}