C#批量处理参数化SQL

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

我们都知道ORM全称叫做Object Relationship Mapper，也就是可以用object来map我们的db，而且市面上的orm框架有很多，其中有一个框架

叫做dapper，而且被称为the king of ORM。

一：为什么选择Dapper

1. 性能优越：

其实在各大网站上，我们大概都会看到这样的一个对比效果图，在超过500次poco serialization的过程中所表现的性能，我们发现dapper是第二名，

当然第一名谁也无法超越，越底层的当然久越快，同时也就越麻烦。

2.实测代码

创造测试数据

CREATE TABLE [dbo].[Users](
    [UserID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [varchar](50) NULL,
    [Email] [varchar](100) NULL,
    [Address] [varchar](100) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
    [UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

1.webconfig配置数据库连接串

2.贴代码

    public class DapperController : Controller
    {
        // GET: Dapper
        public ActionResult Index()
        {
            string connetionString = ConfigurationManager.ConnectionStrings["conn"].ToString();
            IDbConnection connection = new SqlConnection(connetionString);
            //var result = connection.Execute("Insert into Users values (@UserName, @Email, @Address)",new { UserName = "李四", Email = "[email protected]", Address = "上海" });
            //批量插入
            //InsertBulk(connection);
            //批量修改
            //UpdateBulk(connection);
            //查询
            //Query(connection);
            //删除
            //Del(connection);
            return View();
        }
        /// <summary>
        /// 批量插入
        /// </summary>
        public void InsertBulk(IDbConnection connection)
        {
            var usersList = Enumerable.Range(0, 10).Select(i => new Users()
            {
                Email = i + "qq.com",
                Address = "测试地址",
                UserName = i + "测试"
            });
            var result = connection.Execute("Insert into Users values (@UserName, @Email, @Address)", usersList);
        }
        /// <summary>
        /// 批量修改
        /// </summary>
        public void UpdateBulk(IDbConnection connection)
        {
            List<Users> usersList = new List<Users>();
         
            for (int i = 1; i < 11; i++)
            {
                Users users = new Users();
                users.UserID = i;
                users.UserName = i + "张三";
                users.Email = i+"@163.com";
                users.Address = i + "张三的住址";
                usersList.Add(users);
            }
            var result = connection.Execute("update Users set UserName=@UserName,Email=@Email,Address=@Address where UserID=@UserID", usersList);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="connection"></param>
        public void Query(IDbConnection connection)
        {
            int[] myArr = new int[] { 1, 2, 3,4,5,6,7,8,9,10 };
            var query = connection.Query<Users>("select * from Users where UserID IN @UserID",new { UserID = myArr });
        }
        /// <summary>
        /// 批量进行修改
        /// </summary>
        /// <param name="connection"></param>
        public void Del(IDbConnection connection) {
            int[] myArr = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
            var result = connection.Execute("delete from Users where UserID IN @UserID", new { UserID = myArr });
        }
    }

    public class Users {
        public int UserID { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string UserName { get; set; }

    }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="connection"></param>
        public void Query(IDbConnection connection)
        {
            int[] myArr = new int[] { 1, 2, 3,4,5,6,7,8,9,10 };
            var query = connection.Query<Users>("select * from Users where UserID IN @UserID",new { UserID = myArr });
            connection.Query<Users>("select * from Users where UserID IN @UserID",new { UserID = myArr }).ToList();
            connection.Query<Users>("select * from Users where UserID IN @UserID",new { UserID = myArr }).SingleOrDefault();
        }

其他待补充