Dapper的使用

转载：

首先使用NuGet包管理器搜索dapper安装到你所要使用的项目中

直接上代码，有写过dbhelper的看这个应该比较简单 

数据库代码：无注释这些是创建数据库最基础的

use master
go
create database DapperTest
go
use DapperTest
go
create table UserInfo(
     Id int primary key identity(1,1),
     LoginName nvarchar(20)  null,
     LoginPWD nvarchar(20) null,
     URealName nvarchar(20) null,
     uStatus [int] NOT NULL,
     uRemark [nvarchar](max) NULL,
    uCreateTime [datetime] NOT NULL,
)
insert into UserInfo values('admin','admin','admin',1,'admin的一天生活',2020-1-1)
insert into UserInfo values('a','a','a',1,'admin的言论',2020-1-1)
go
update  UserInfo set uStatus=0 where Id=1
go
select * from UserInfo

Vs中的代码：

数据模型数据表对应的实体对象。

 public class UserInfo
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int Id { get; set; }
        /// <summary>
        /// 用户名
        /// </summary>
        public string LoginName { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        public string LoginPWD  { get; set; }
        /// <summary>
        /// 真实名字
        /// </summary>
        public string URealName { get; set; }
        /// <summary>
        /// 状态，0表示正常，1表示删除
        /// </summary>
        public int uStatus { get; set; }
        /// <summary>
        /// 评论
        /// </summary>
        public string uRemark { get; set; }
        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime uCreateTime { get; set; }
    }

　写对应的增删改查

public class DapperHelper
    {
        //第一种写法
        private readonly string sqlconnection = "Data Source=.;Initial Catalog=DapperTest;User Id=sa;Password=sa;";
        //第二种写法
        private readonly string sqlcon = "server=.;database=DapperTest;uid=sa;pwd=sa;";
        //第三种写法在App.config中写入

        //获取Sql Server的连接数据库对象。SqlConnection
        public SqlConnection sqlCon()
        {
            SqlConnection sql = new SqlConnection(sqlconnection);
            sql.Open();
            return sql;
        }
        /// <summary>
        /// 添加单条数据
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public  int InsertUserInser(UserInfo user)
        {
            using (var db=sqlCon())
            {
                string query = "insert into UserInfo values(@LoginName,@LoginPWD,@URealName,@uStatus,@uRemark,@uCreateTime)";
                return db.Execute(query, user);
            }
        }
        /// <summary>
        /// 添加多条数据
        /// </summary>
        /// <param name="ulist"></param>
        /// <returns></returns>
        public int MultInsertUser(List<UserInfo> ulist)
        {
            using (var db = new SqlConnection(sqlconnection))
            {
                string query = "insert into UserInfo values(@LoginName,@LoginPWD,@URealName,@uStatus,@uRemark,@uCreateTime)";
                return db.Execute(query, ulist);
            }
        }
        /// <summary>
        /// 删除  真删除 可以增加一个伪删除
        /// </summary>
        /// <param name="Uid"></param>
        /// <returns></returns>
        public int UserDelete(int Uid)
        {
            using (var db = new SqlConnection(sqlcon))
            {
                string query = "delete  UserInfo where Id=@Uid";
                var param = new DynamicParameters();
                param.Add("Uid", Uid);
                return db.Execute(query, param);

            }
        }
        public int MultDeleteUser(List<UserInfo> ulist)
        {
            using (var db = new SqlConnection(sqlconnection))
            {
                string query = "delete  UserInfo where Id=@Id";
                return db.Execute(query, ulist);
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int UserUpdate(UserInfo user)//int Id
        {
            //@LoginName,@LoginPWD,@URealName,@uStatus,@uRemark,@uCreateTime
            using (var db = new SqlConnection(sqlconnection))
            {
                //string query = $"update UserInfo set LoginName=@LoginName,LoginPWD=@LoginPWD,URealName=@URealName,uStatus=@uStatus,uRemark=@uRemark,uCreateTime=@uCreateTime, where Id={Id}";
                string query = $"update UserInfo set LoginName=@LoginName,LoginPWD=@LoginPWD,URealName=@URealName,uStatus=@uStatus,uRemark=@uRemark,uCreateTime=@uCreateTime  where Id=@Id";
                return db.Execute(query, user);
            }
        }
        public List<UserInfo> GetAll()
        {
            using (var db = new SqlConnection(sqlconnection))
            {
                string query = "select * from UserInfo";
                return db.Query<UserInfo>(query).ToList();

            }
        }
        /// <summary>
        /// 查询指定的数据
        /// </summary>
        /// <param name="uids">查询条件</param>
        /// <returns></returns>
        public List<UserInfo> GetAllIn2(int Id)
        {
            using (var db = new SqlConnection(sqlconnection))
            {
                string query = $"select * from UserInfo where Id={Id}";
                return db.Query<UserInfo>(query).ToList();
            }
        }

在main中调用测试的方法，和数据库里面的数据同步

class Program
    {
        public static DapperHelper db = new DapperHelper();
       
        static void Main(string[] args)
        {
            //DapperHelper db = new DapperHelper();
            //var data=db.InsertUserInser(new UserInfo()
            //{
            //    LoginName = "admin123",
            //    LoginPWD = "admin123",
            //    URealName = "张三",
            //    uStatus = 0,
            //    uRemark = "admin123发表的言论",
            //    //时间格式转换
            //    //uCreateTime = DateTime.Parse("2020-07-25")
            //    uCreateTime=DateTime.Now
            //});
            //已下两种都是可以修改成功的
            //int Id = int.Parse(Console.ReadLine());
            //var data = db.UserUpdate(new UserInfo()
            //{
            //    LoginName = "admin123",
            //    LoginPWD = "admin123",
            //    URealName = "张三2",
            //    uStatus = 0,
            //    uRemark = "admin123发表的言论",
            //    //时间格式转换
            //    //uCreateTime = DateTime.Parse("2020-07-25")
            //    uCreateTime = DateTime.Now,
            //    Id=Id
            //}, Id);
            //int Id = int.Parse(Console.ReadLine());
            //var data = db.UserUpdate(new UserInfo()
            //{
            //    LoginName = "admin123",
            //    LoginPWD = "admin123",
            //    URealName = "张三2",
            //    uStatus = 0,
            //    uRemark = "admin123发表的言论",
            //    //时间格式转换
            //    //uCreateTime = DateTime.Parse("2020-07-25")
            //    uCreateTime = DateTime.Now,
            //    Id = Id
            //});
            //if (data > 0)
            //{
            //    Console.WriteLine("修改成功");
            //}
            //查看
            //var data2 = db.GetAll();
            //foreach (var item in data2)
            //{
            //    Console.WriteLine("用户名："+item.URealName+"    "+"账号："+item.LoginName);
            //}
            //while (true)
            //{
            //    int Id = int.Parse(Console.ReadLine());
            //    var data3 = db.GetAllIn2(Id);
            //    foreach (var item in data3)
            //    {
            //        Console.WriteLine("用户名：" + item.URealName + "    " + "账号：" + item.LoginName);
            //    }

            //    
            //}
            //删除
            int Id = int.Parse(Console.ReadLine());
            var data4 = db.UserDelete(Id);
            if (data4 > 0)
            {
                Console.WriteLine("删除成功");
            }
        }
    }