ORM框架-Dapper常用示例

1.安装Dapper的NuGet包

image

2.示例代码如下

 1             string connString = "YourConnectionStringHere";
 2             IDbConnection db = new SqlConnection(connString);
 3             //查询数据
 4             string sql = "SELECT * FROM Base_User";
 5             var users = db.Query<Base_User>(sql,buffered:true).ToList();//buffered:true默认是true，表示将结果集全部加载到内存中，false则是逐行读取
 6 
 7             //动态对象查询   dynamicUsers = db.Query<dynamic>(sql).ToList();
 8             sql = "SELECT * FROM Base_User";
 9             var dynamicUsers = db.Query(sql).ToList();
10 
11             //插入数据
12             string insertSql = "INSERT INTO Base_User (UserId,UserName,Password) VALUES (@UserId,@UserName,@Password)";
13             db.Execute(insertSql, new { UserId = Guid.NewGuid().ToString(), UserName = "newuser", Password = "password123" });
14 
15             //更新数据
16             string updateSql = "UPDATE Base_User SET Password = @Password WHERE UserName = @UserName";
17             db.Execute(updateSql, new { Password = "newpassword", UserName = "newuser" });
18 
19             //删除数据
20             string deleteSql = "DELETE FROM Base_User WHERE UserName = @UserName";
21             db.Execute(deleteSql, new { UserName = "newuser" });
22 
23             //执行存储过程
24             string procName = "GetEEDFile";
25             var result = db.Query(procName, new { FileName = "03.814.102.004 F302" }, commandType: CommandType.StoredProcedure).ToList();
26 
27             //参数化查询
28             sql = "SELECT * FROM Base_User WHERE Birthday < @NowDay";
29             result = db.Query<dynamic>(sql, new { NowDay = DateTime.Now }).ToList();
30 
31             //事务处理
32             using (IDbTransaction transaction = db.BeginTransaction())
33             {
34                 insertSql = "INSERT INTO Base_User (UserId,UserName, Password) VALUES (@UserId, @UserName,@Password)";
35                 db.Execute(insertSql, new { UserId = Guid.NewGuid().ToString(), UserName = "transuser1", Password = "password123" },transaction,5000,CommandType.Text);
36 
37                 deleteSql = "DELETE FROM Base_User WHERE UserName = @UserName";
38                 db.Execute(deleteSql, new { UserName = "transuser1" }, transaction, 5000, CommandType.Text);
39 
40                 transaction.Commit();//提交事务
41                 transaction.Rollback();//回滚事务
42             }
43 
44             //批量操作
45             var userList = new List<Base_User>
46             {
47                 new Base_User { UserId = Guid.NewGuid().ToString(), UserName = "batchuser1", Password = "password1" },
48                 new Base_User { UserId = Guid.NewGuid().ToString(), UserName = "batchuser2", Password = "password2" },
49                 new Base_User { UserId = Guid.NewGuid().ToString(), UserName = "batchuser3", Password = "password3" }
50             };
51             insertSql = "INSERT INTO Base_User (UserId,UserName, Password) VALUES (@UserId, @UserName,@Password)";
52             db.Execute(insertSql, userList);
1     public class Base_User
2     {
3         public string UserId { get; set; }
4         public string UserName { get; set; }
5         public string Password { get; set; }
6     }

 

posted @ 2026-02-04 16:10  田宏伟_thw  阅读(0)  评论(0)    收藏  举报