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(); }