Dapper 作为. NET 生态中广为人知的轻量级 ORM 类库在. NET Core 里仍能被有效利用, 并且其不但可以连通 SQL Server 数据库还提供对其它数据库, 比如 MySQL 的支持. 这里试验了一下通过 Dapper 连接 MySQL 的方法.
MySQL
可以选择直接安装在原生系统中或是 Docker 里.
- Official https://dev.MySQL.com/downloads/MySQL/
- Docker https://hub.docker.com/_/MySQL/
- Table
在 MySQL 中建立两张表.
city 表:
- CREATE TABLE `city` (
- `ID` int(11) NOT NULL AUTO_INCREMENT,
- `Name` char(35) NOT NULL DEFAULT '',
- `CountryCode` char(3) NOT NULL DEFAULT '',
- `District` char(20) NOT NULL DEFAULT '',
- `Population` int(11) NOT NULL DEFAULT '0',
- PRIMARY KEY (`ID`),
- KEY `CountryCode` (`CountryCode`),
- CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`code`)
- ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
country 表:
- CREATE TABLE `country` (
- `Code` char(3) NOT NULL DEFAULT '',
- `Name` char(52) NOT NULL DEFAULT '',
- `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
- `Region` char(26) NOT NULL DEFAULT '',
- `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
- `IndepYear` smallint(6) DEFAULT NULL,
- `Population` int(11) NOT NULL DEFAULT '0',
- `LifeExpectancy` float(3,1) DEFAULT NULL,
- `GNP` float(10,2) DEFAULT NULL,
- `GNPOld` float(10,2) DEFAULT NULL,
- `LocalName` char(45) NOT NULL DEFAULT '',
- `GovernmentForm` char(45) NOT NULL DEFAULT '',
- `HeadOfState` char(60) DEFAULT NULL,
- `Capital` int(11) DEFAULT NULL,
- `Code2` char(2) NOT NULL DEFAULT '',
- PRIMARY KEY (`Code`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- Package
应用程序工程中需要添加 Dapper 以及 MySQL.Data 类库.
- dotnet add package Dapper
- dotnet add package MySQL.Data
- Entity
编写两个实体类, 用于映射 city 与 country 表.
- public class CityEntity
- {
- public int ID { get; set; }
- public string Name { get; set; }
- public string CountryCode { get; set; }
- public string District { get; set; }
- public int Population { get; set; }
- public CountryEntity Country { get; set; }
- public override string ToString()
- {
- return $"ID: {ID}, Name: {Name}, CountryCode: {CountryCode}, District: {District}, Population: {Population}, Country: {Country}";
- }
- }
- public class CountryEntity
- {
- public string Code { get; set; }
- public string Name { get; set; }
- public string Continent { get; set; }
- public string Region { get; set; }
- public decimal SurfaceArea { get; set; }
- public int IndepYear { get; set; }
- public int Population { get; set; }
- public decimal LifeExpectancy { get; set; }
- public decimal GNP { get; set; }
- public decimal GNPOld { get; set; }
- public string LocalName { get; set; }
- public string GovernmentForm { get; set; }
- public string HeadOfState { get; set; }
- public int Capital { get; set; }
- public string Code2 { get; set; }
- public override string ToString()
- {
- return $"Code: {Code}, Name: {Name}, Continent: {Continent}, Region: {Region}, SurfaceArea: {SurfaceArea}";
- }
- }
- Repository
仓库类中新加获取 10 个城市数据的方法. 这里 Dapper 的 Query 方法有三个参数, 第一个是需要执行的 SQL 语句, 第二个是对象之间的对应关系 (这个例子中 city 与 country 为一对一关系), 并确定最终返回的对象类型, 最后的 SplitOn 参数会告诉 Dapper 在结果集中两张表之间以哪个字段进行分界.
- public class CityRepository
- {
- public List<CityEntity> Get10Cities()
- {
- List<CityEntity> result;
- using (var conn = new MySqlConnection("Host=localhost;Port=3306;Database=world;Uid=admin;pwd=admin"))
- {
- var sql = "SELECT * FROM city INNER JOIN country ON city.CountryCode = country.Code LIMIT 10";
- result = conn.Query<CityEntity, CountryEntity, CityEntity>(sql,
- (city, country) => { city.Country = country; return city; }, splitOn: "Code").ToList();
- }
- return result;
- }
- }
- Test
- static void Main(string[] args)
- {
- var repository = new CityRepository();
- var cities = repository.Get10Cities();
- cities.ForEach(e=>{
- System.Console.WriteLine(e);
- });
- }
程序运行的结果如下, 可以看到成功借助 Dapper 的力量从 MySQL 数据库里获取了所需的数据.
来源: https://www.cnblogs.com/kenwoo/p/9735964.html