php dapper,.NET Core开发日志——Dapper与MySQL

Dapper作为.NET生态中广为人知的轻量级ORM类库在.NET Core里仍能被有效利用，并且其不但可以连通SQL Server数据库还提供对其它数据库，比如MySQL的支持。这里试验了一下通过Dapper连接MySQL的方法。

MySQL

可以选择直接安装在原生系统中或是Docker里。

Official

Docker

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 Get10Cities()

{

List 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(sql,

(city, country) => { city.Country = country; return city; }, splitOn: "Code").ToList();

}

return result;

}

}

Teststatic void Main(string[] args)

{

var repository = new CityRepository();

var cities = repository.Get10Cities();

cities.ForEach(e=>{

System.Console.WriteLine(e);

});

}

程序运行的结果如下，可以看到成功借助Dapper的力量从MySQL数据库里获取了所需的数据。