Dapper基本用法--MySql

NuGet:

• Dapper 2.0.35
• MySql.Data
• System.Data

實體（Entity）

1    public class student
2     {
3         public int Id { get; set; }
4         public string RealName { get; set; }
5         public DateTime EnrollmenDate { get; set; }
6     }

配置文件資料庫連接

  <connectionStrings>
    <add name="mysqlConn" connectionString="server=127.0.0.1;database=資料庫;User ID=root;password=密碼"/>
  </connectionStrings>

MySql連接字元串

1 private static string connStr = string.Empty;
2         public MySqlOperation()
3         {
4             connStr = System.Configuration.ConfigurationManager.ConnectionStrings["mysqlConn"].ConnectionString;
5         }

查詢語句

 1         /// <summary>
 2         /// 查詢所有
 3         /// </summary>
 4         /// <returns>lists</returns>
 5         public List<student> Query()
 6         {
 7             if (string.IsNullOrEmpty(connStr))
 8             {
 9                 return new List<student>(); ;
10             }
11             try
12             {
13                 using (IDbConnection db = new MySqlConnection(connStr))
14                 {
15                     db.Open();//打開資料庫
16                     string mysqlStr = "select * from  student";
17                     List<student> lists = db.Query<student>(mysqlStr).ToList();
18                     db.Close();//關閉資料庫
19                     return lists;
20                 }
21             }
22             catch (Exception)
23             {
24                 return new List<student>();
25             }
26         }
27         /// <summary>
28         /// 根據Id查詢
29         /// </summary>
30         /// <param name="id"></param>
31         /// <returns>lists</returns>
32         public List<student> Query(string id)
33         {
34             if (string.IsNullOrEmpty(connStr))
35             {
36                 return new List<student>();
37             }
38             try
39             {
40                 using (IDbConnection db = new MySqlConnection(connStr))
41                 {
42                     db.Open();//打開資料庫
43                     string mysqlStr = "select * from  student where Id=@id";
44                     List<student> lists = db.Query<student>(mysqlStr, new { Id = id }).ToList();
45                     db.Close();//關閉資料庫
46                     return lists;
47                 }
48             }
49             catch (Exception)
50             {
51                 return new List<student>();
52             }
53         }

插入語句

 1         /// <summary>
 2         /// 插入數據
 3         /// </summary>
 4         /// <param name="student">student</param>
 5         /// <returns>-1:連接字元串為空，-2：新增失敗</returns>
 6         public int Insert(student student)
 7         {
 8             if (string.IsNullOrEmpty(connStr))
 9             {
10                 return -1;
11             }
12             try
13             {
14                 using (IDbConnection db = new MySqlConnection(connStr))
15                 {
16                     db.Open();//打開資料庫
17                     string mysqlStr = "insert into student(Id,RealName,EnrollmenDate) values(@Id,@RealName,@EnrollmenDate)";
18                     int list = db.Execute(mysqlStr, student);
19                     db.Close();//關閉資料庫
20                     return list;
21                 }
22             }
23             catch (Exception)
24             {
25                 return -2;
26             }
27         }

刪除數據

 1         /// <summary>
 2         /// 刪除數據
 3         /// </summary>
 4         /// <param name="id"></param>
 5         /// <returns></returns>
 6         public int Delect(string id)
 7         {
 8             if (string.IsNullOrEmpty(connStr))
 9             {
10                 return -1;
11             }
12             try
13             {
14                 using (IDbConnection db = new MySqlConnection(connStr))
15                 {
16                     db.Open();//打開資料庫
17                     string mysqlStr = "delete from student where Id=@id";
18                     int list = db.Execute(mysqlStr, new { Id = id });
19                     db.Close();//關閉資料庫
20                     return list;
21                 }
22             }
23             catch (Exception)
24             {
25                 return -2;
26             }
27         }

更新數據

 1         /// <summary>
 2         /// 更新信息
 3         /// </summary>
 4         /// <param name="student"></param>
 5         /// <returns>-1:連接字元串為空，-2：更新失敗</returns>
 6         public int Update(student student)
 7         {
 8             if (string.IsNullOrEmpty(connStr))
 9             {
10                 return -1;
11             }
12             try
13             {
14                 using (IDbConnection db = new MySqlConnection(connStr))
15                 {
16                     db.Open();//打開資料庫
17                     string mysqlStr = "update student set RealName=@RealName where Id=@Id";
18                     int list = db.Execute(mysqlStr, student);
19                     db.Close();//關閉資料庫
20                     return list;
21                 }
22             }
23             catch (Exception)
24             {
25                 return -2;
26             }
27         }