Dapper
原文地址：https://www.cnblogs.com/dotnet261010/category/1137015.html
https://esofar.gitbooks.io/dapper-tutorial-cn/utilities/stored-procedure.html?q=
在安装的时候报错：
“Dapper”已拥有为“System.Data.SqlClient”定义的依赖项。
换一个低版本的dapper就可以了（当前.net framework版本是4.5）——install-package dapper -version 1.50.2
#region 匿名类插入 IDbConnection connection = new SqlConnection(connStr); var result = connection.Execute("insert into [User] (UserId,UserName,Email,Address) values(@UserId,@UserName,@Email,@Address)", new { UserId = 3, UserName = "Cindy", Email = "376612827@qq.com", Address = "东京" }); #endregion #region 实体类插入 string sqlCommandText = "insert into [User](UserId,UserName,Email,Address) values(@UserId,@UserName,@Email,@Address)"; using (IDbConnection connection = new SqlConnection(connStr)) { User user = new User() { UserId = 4, UserName = "tim", Email = "78415155@qq.com", Address = "西京" }; int result = connection.Execute(sqlCommandText, user); if (result > 0) { MessageBox.Show("插入成功！"); } else { MessageBox.Show("插入失败！"); } } #endregion #region 批量插入 string sqlCommandText = "insert into [User](UserId,UserName,Email,Address) values(@UserId,@UserName,@Email,@Address)"; using (IDbConnection connection = new SqlConnection(connStr)) { List<User> users = new List<User>() { new User { UserId=5,UserName="stephen",Email="step@qq.com",Address="益州"}, new User { UserId=6,UserName="loly",Email="loly@qq.com",Address="荆州"}, new User { UserId=7,UserName="gay",Email="gay@qq.com",Address="兖州"} }; int result = connection.Execute(sqlCommandText, users); if (result > 0) { MessageBox.Show("插入成功！"); } else { MessageBox.Show("插入失败！"); } } #endregion
using (IDbConnection connection = new SqlConnection(connStr)) { User user = new User() { UserId = 5 };//使用实体进行查询。 var users = connection.Query<User>("select * from [User] where UserId>@UserId", new User() { UserId = 5 });//user List<User> userss = users.AsList<User>(); users.AsList().ForEach(p => { string result = string.Format("ID:{0},UserName:{1},Email:{2},Address:{3}", p.UserId, p.UserName, p.Email, p.Address); MessageBox.Show(result); }); }
#region 匿名类更新数据 //using (IDbConnection connection = new SqlConnection(connStr)) //{ // var result = connection.Execute("update [User] set UserName='randy',Address='上海' where UserId=@UserId", new { UserId = 2 }); // if (result > 0) // { // MessageBox.Show("更新成功！"); // } // else { // MessageBox.Show("更新失败！"); // } //} #endregion #region 实体类更新数据 //User user = new User() {UserId=4,Email="tim@qq.com" }; //using (IDbConnection connection = new SqlConnection(connStr)) //{ // var result = connection.Execute("update [User] set Email=@Email where UserId=@UserId", user); // if (result > 0) // { // MessageBox.Show("更新成功！"); // } // else // { // MessageBox.Show("更新失败！"); // } //} #endregion #region 使用键值对更新数据 using (IDbConnection connection = new SqlConnection(connStr)) { List<KeyValuePair<string, object>> keys = new List<KeyValuePair<string, object>>() { new KeyValuePair<string, object>("@UserName","Max"), new KeyValuePair<string, object>("@UserId",4), new KeyValuePair<string, object> ("@Address","扬州") }; var result = connection.Execute("update [User] set UserName=@UserName,Address=@Address where UserId=@UserId", keys); if (result > 0) { MessageBox.Show("更新成功！"); } else { MessageBox.Show("更新失败！"); } } #endregion
#region 使用匿名类进行删除操作 //using (IDbConnection connection = new SqlConnection(connStr)) //{ // var result = connection.Execute("delete [User] where UserId=@UserId", new { UserId = 7 }); // if (result > 0) // { // MessageBox.Show("删除成功！"); // } // else // { // MessageBox.Show("删除失败！"); // } //} #endregion #region 使用实体删除数据 User user = new User(); user.UserId = 6; using (IDbConnection connection = new SqlConnection(connStr)) { var result = connection.Execute("delete [User] where UserId=@UserId", user); if (result > 0) { MessageBox.Show("删除成功！"); } else { MessageBox.Show("删除失败！"); } } #endregion
using (IDbConnection connection = new SqlConnection(connStr)) { var sql = "select * from [User] where Email in @emails"; var result = connection.Query<User>(sql, new { emails = new string[2] { "cindy@qq.com", "randy@qq.com" } }); List<User> users = result.AsList<User>(); result.AsList().ForEach(p => { Console.WriteLine("Id:" + p.UserId + " UserName:" + p.UserName + " Email:" + p.Email + " Address:" + p.Address); }); }
using (IDbConnection connection = new SqlConnection(connStr)) { var sqlCmd = @"select * from [User] where UserId=@UserId;select * from [provinceInfo] where provinceid=@provinceid; "; var result = connection.QueryMultiple(sqlCmd, new { UserId = 2, provinceid = 10 }); //下面这两句read，一定要与上面的sqlCmd语句中的select语句的顺序一样，即：我要先read User表中的数，那么就要将select User表的语句放在前面。 var users = result.Read<User>(); var provinces = result.Read<ProvinceInfo>(); users.AsList().ForEach(p => { MessageBox.Show("名字叫：" + p.UserName + "； 居住地：" + p.Address); }); provinces.AsList().ForEach(p => { MessageBox.Show("省份为：" + p.province + "； id为：" + p.provinceid); }); }
using (IDbConnection connection = new SqlConnection(connStr)) { var result = connection.Query<User>("存储过程的名字", new { UserId = 4 }, commandType: CommandType.StoredProcedure);//参数2是存储过程的输入参数。参数3指定本次query使用存储过程 result.AsList<User>().ForEach(p => { MessageBox.Show(""); }); } #region 调用带有输出参数的存储过程 //CREATE proc procWithOutPara //@num1 int, //@num2 int, //@sum int output //as //begin // set @sum = @num1 + @num2 //end //GO using (IDbConnection connection = new SqlConnection(connStr)) { DynamicParameters paras = new DynamicParameters(); paras.Add("@num1", 23); paras.Add("@num2", 45); paras.Add("@summary", 0, DbType.Int32, ParameterDirection.Output); connection.Execute("procWithOutPara", paras, commandType: CommandType.StoredProcedure); int sum = paras.Get<int>("@summary"); } #endregion
public void ExecuteTransaction() { using (IDbConnection con = new SqlConnection(conStr)) { string strSQL = "DELETE FROM Users WHERE userID=@userID"; DynamicParameters paras = new DynamicParameters(); paras.Add("@userID", 10086); // 开启事物 IDbTransaction trans = con.BeginTransaction(); try { con.Execute(strSQL, paras, transaction: trans); // 提交事务 trans.Commit(); } catch (Exception ex) { // 回滚事物 trans.Rollback(); } } }
