.net Core+Dapper MySQL增删改查

新建一个用户表，以该有为例

1.Model层

 public class TuiUsers
    {
        public int id { get; set; }
        public string userName { get; set; }
        public string userPass { get; set; }
        public int userType { get; set; }
        public string company { get; set; }
        public string detail { get; set; }

        public DateTime create_time { get; set; }
        public DateTime edit_time { get; set; }

        public int pid { get; set; }

        public string alipayUrl { get; set; }
        public string taobaoUrl { get; set; }
        public string linkMan { get; set; }

        public string contactInfo { get; set; }
        public decimal priceRatio { get; set; }
        public int stateFlag { get; set; }

    }

 

2.Dal层

通用连接

   internal class ConnectionFactory
    {

        public static DbConnection GetOpenConnection(string connstr)
        {
            var connection = new MySql.Data.MySqlClient.MySqlConnection(connstr);
            connection.Open();
            return connection;

        }

    }

数据层

  public partial class TuiUsersDal
    {
        public string ConnStr { set; get; }

        public List<Entities.TuiUsers> GetListUser(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM `tuiusers` ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            List<Entities.TuiUsers> list = new List<Entities.TuiUsers>();
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                list = connection.Query<Entities.TuiUsers>(strSql.ToString()).ToList();

            }
            return list;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Entities.TuiUsers model)
        {
            DynamicParameters Parameters = new DynamicParameters();
            int cnt = 0;
            string sQuery = "INSERT INTO tuiusers (userName,userPass,userType,company,detail,create_time,edit_time,pid,alipayUrl,taobaoUrl,linkMan,contactInfo,priceRatio)"
                              + " VALUES(@userName,@userPass,@userType,@company,@detail,@create_time,@edit_time,@pid,@alipayUrl,@taobaoUrl,@linkMan,@contactInfo,@priceRatio)";
            Parameters.Add("alipayUrl", model.alipayUrl);
            Parameters.Add("company", model.company);
            Parameters.Add("contactInfo", model.contactInfo);
            Parameters.Add("create_time", model.create_time);
            Parameters.Add("detail", model.detail);
            Parameters.Add("edit_time", model.edit_time);
            Parameters.Add("linkMan", model.linkMan);
            Parameters.Add("pid", model.pid);
            Parameters.Add("priceRatio", model.priceRatio);
            Parameters.Add("stateFlag", model.stateFlag);
            Parameters.Add("taobaoUrl", model.taobaoUrl);
            Parameters.Add("userName", model.userName);
            Parameters.Add("userPass", model.userPass);
            Parameters.Add("userType", model.userType);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {

                cnt = connection.Execute(sQuery, Parameters);
            }

            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            DynamicParameters Parameters = new DynamicParameters();
            int cnt = 0;
            string sQuery = "Delete FROM tuiusers " + "WHERE Id=@Id";
            Parameters.Add("Id", id);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                cnt = connection.Execute(sQuery, Parameters);
            }
            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Entities.TuiUsers model)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "UPDATE tuiusers SET userPass=@userPass,userType=@userType,company=@company,detail=@detail,edit_time=@edit_time,pid=@pid,alipayUrl=@alipayUrl,taobaoUrl=@taobaoUrl,linkMan=@linkMan,contactInfo=@contactInfo,priceRatio=@priceRatio where userName=@userName";
            Parameters.Add("alipayUrl", model.alipayUrl);
            Parameters.Add("company", model.company);
            Parameters.Add("contactInfo", model.contactInfo);
            Parameters.Add("create_time", model.create_time);
            Parameters.Add("detail", model.detail);
            Parameters.Add("edit_time", model.edit_time);
            Parameters.Add("linkMan", model.linkMan);
            Parameters.Add("pid", model.pid);
            Parameters.Add("priceRatio", model.priceRatio);
            Parameters.Add("stateFlag", model.stateFlag);
            Parameters.Add("taobaoUrl", model.taobaoUrl);
            Parameters.Add("userName", model.userName);
            Parameters.Add("userPass", model.userPass);
            Parameters.Add("userType", model.userType);

            int cnt = 0;
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                cnt = connection.Execute(sQuery, Parameters);
            }
            if (cnt > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 根据ID获取实体对象
        /// </summary>
        public Entities.TuiUsers GetModel(int id)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "SELECT * FROM tuiusers " + "WHERE id = @id";
            Parameters.Add("id", id);
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                return connection.Query<Entities.TuiUsers>(sQuery, Parameters).FirstOrDefault();
            }
        }


        /// <summary>
        /// 根据userName获取实体对象
        /// </summary>
        public Entities.TuiUsers GetModelByUserName(string userName)
        {
            DynamicParameters Parameters = new DynamicParameters();
            string sQuery = "SELECT * FROM tuiusers " + "WHERE userName = @userName";
            Parameters.Add("userName", userName);


            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                
                return connection.Query<Entities.TuiUsers>(sQuery, Parameters).FirstOrDefault();
            }
        }


        /// <summary>分页获取数据列表
        /// 
        /// </summary>
        public List<Entities.TuiUsers> GetListArray(string fileds, string orderstr, int PageSize, int PageIndex, string strWhere)
        {

            string cond = string.IsNullOrEmpty(strWhere) ? "" : string.Format(" where {0}", strWhere);

            string sql = string.Format("select {0} from `tuiusers` {1} order by {2} limit {3},{4}", fileds, cond, orderstr, (PageIndex - 1) * PageSize, PageSize);


            List<Entities.TuiUsers> list = new List<Entities.TuiUsers>();
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                list = connection.Query<Entities.TuiUsers>(sql).ToList();

            }
            return list;
        }



        /// <summary>计算记录数
        /// 
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int CalcCount(string where)
        {
            string sql = "select count(1) from `tuiusers`";
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            using (var connection = ConnectionFactory.GetOpenConnection(ConnStr))
            {
                int i = connection.QuerySingle<int>(sql);
                return i;

            }
        }



    }

 

3.Startup注入

        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            //取出appsetting.json中的数据库连接字符串
            string connStr = Configuration.GetSection("ConnStr").Value;

            //注入
            services.AddSingleton<DAL.UsersDAL>(new DAL.UsersDAL() { ConnStr = connStr });
            services.AddSingleton<DAL.TuiUsersDal>(new DAL.TuiUsersDal() { ConnStr = connStr });


            //解决中文被编码
            services.AddSingleton(HtmlEncoder.Create(UnicodeRanges.All));

            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });


            //开启授权认证相关模块（中间件）
            services.AddAuthentication(
            options =>
            {
               options.DefaultChallengeScheme = CookieAuthenticationDefaults.AuthenticationScheme;
               options.DefaultAuthenticateScheme = CookieAuthenticationDefaults.AuthenticationScheme;
            })
           .AddCookie(options =>
           {
            options.LoginPath = "/Account/";
            options.Cookie.HttpOnly = true;
            });
            services.AddTransient<HttpContextAccessor>();
            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);




        }

 

4.配置文件

{
    "ConnStr": "server=localhost;database=phone_card;uid=root;pwd=123456;charset=utf8;SslMode=None",
    "Logging": {
        "LogLevel": {
            "Default": "Warning"
        }
    },
    "AllowedHosts": "*"
}