一、介绍Dapper
轻量级的ORM,支持net4.0++,由于公司某些原因,开发环境只能是net4.0,所有很难找到支持4.0环境的orm框架,公司内使用的一款CYQ的框架,使用起来很不爽,所以想尝试寻找一个新的ORM,
github地址:https://github.com/StackExchange/Dapper
DEMO:https://github.com/czjnoe/GitHubDemo/tree/master/DapperDemo
gitHub下载慢?参考:https://blog.csdn.net/czjnoe/article/details/106321174
二、前期准备
1.创建一个工厂帮助类,支持多数据库连接,获取IDbConnection对象
public static IDbConnection GetConnection(MyDbType type, string connStr)
{
IDbConnection dbConnection = null;
if (type== MyDbType.MySQL)
{
dbConnection = new MySqlConnection(connStr);
}
else if (type == MyDbType.Oracle)
{
dbConnection = new OracleConnection(connStr);
}
else if (type == MyDbType.SqlServer)
{
dbConnection = new SqlConnection(connStr);
}
else if (type == MyDbType.SQlite)
{
dbConnection = new SQLiteConnection(connStr);
}
return dbConnection;
}
public enum MyDbType
{
SqlServer = 1,
MySQL = 2,
Oracle = 3,
SQlite = 4
}
2.创建实体类:万金油的学生类
public class Student
{
public string ID { get; set; }
public string NAME { get; set; }
public int AGE { get; set; }
public DateTime TIME { get; set; }
}
三、使用教程
1.插入:
Random rand = new Random();
using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer,"Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
{
string id = rand.Next(1, 2000000000).ToString();
Student stu = new Student { ID = id, NAME = "春夏之交", AGE=25, TIME = DateTime.Now };
//单个插入
var effectRows=db.Execute("insert into Student(ID,NAME,TIME) values(@ID,@NAME,@TIME)", stu);
//多个插入
var stuList = Enumerable.Range(0, 10).Select(i => new Student()
{
ID = rand.Next(1, 2000000000).ToString(),
NAME = "秋雨雁南飞",
AGE = i,
TIME=DateTime.Now
});
var result = db.Execute("insert into Student(ID,NAME,TIME,AGE) values(@ID,@NAME,@TIME,@AGE)", stuList);
}
2.更新:
using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer,"Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
{
var effectRows = db.Execute("UPDATE Student SET NAME=@NAME WHERE ID =@ID", new {ID="1"});
}
3.删除:
using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer,"Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
{
var effectRows = db.Execute("DELETE FROM Student WHERE ID = @ID", new {ID="1"});
}
4.查询:
using (IDbConnection db = DapperFactory.GetConnection(Enums.MyDbType.SqlServer,"Data Source=localhost;Initial Catalog=test;Integrated Security=True"))
{
//单个查询
{
//var stu4 = db.QuerySingle<Student>("select * from Student where ID=@ID", new { ID = "1" });//数据超过一条会报错,没有数据会报错
var stu5 = db.QuerySingleOrDefault<Student>("select * from Student where ID=@ID", new { ID = "1" });//数据超过一条会报错,没有数据是默认为null
var stu6 = db.QueryFirstOrDefault<Student>("select * from Student where ID=@ID", new { ID = "1" });//没有数据时默认为null
var stu7 = db.QueryFirst<Student>("select * from Student where ID=@ID", new { ID = "1" });//没有数据会报错
}
//多个查询
{
var list = db.Query<Student>("SELECT * FROM Student").ToList();
}
}
四、补充
入参类型:
1.匿名类,如:new {ID="1",NAME="1122"} 、new { IDS = new String[] { "1786863176", "1963140912" } }
2.实体类,如:new Student {ID="1",NAME="1122"}
3.字典,如:Dictionary<string,object> dic=new
Dictionary<string,object>();
dic["ID"]="1";
dic["NAME"]="1122";