1、安装引用MySQL和Dapper
MySQL的NuGet地址:https://www.nuget.org/packages/MySql.Data
使用命令安装
Install-Package MySql.Data
Install-Package Dapper
或
Nuget管理器中搜索"MySql.Data
"和"Dapper
",然后分别点击“安装
”
相关文档:VS(Visual Studio)中Nuget的使用
2、创建MySQL测试库脚本
CREATE DATABASE `cjavapy`
CREATE TABLE `user` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`UserName` varchar(255) DEFAULT NULL,
`Age` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
3、使用Dapper执行SQL语句增删改查
用到的User实现类:
public class User
{
publicint Id { get; set; }
publicstring UserName { get; set; }
publicint Age { get; set; }
}
执行SQL语句增删改查:
public static void Main(string[] args)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
MySqlConnection con = new MySqlConnection("server=127.0.0.1;database=test;uid=root;pwd=;charset='gbk';SslMode=None");
//连接字符串要加上 SslMode=None 不然会报错误:SSL not supported in this WinRT release.
//新增数据
con.Execute("insert into user values(null, 'cjavapy', 30)");
//新增数据返回自增id
var id = con.QueryFirst<int>("insert into user values(null, 'cjavapy', 38);select last_insert_id();");
//修改数据
con.Execute("update user set UserName = 'cjavapy' where Id = @Id", new { Id = id });
//查询数据
var list = con.Query<User>("select * from user");
foreach (var item in list)
{
Console.WriteLine($"用户名:{item.UserName} 年龄:{item.Age}");
}
//删除数据
con.Execute("delete from user where Id = @Id", new { Id = id });
Console.WriteLine("删除数据后");
list = con.Query<User>("select * from user");
foreach (var item in list)
{
Console.WriteLine($"用户名:{item.UserName} 年龄:{item.Age}");
}
Console.ReadKey();
}