VS2019 .netCore swagger 接口

环境：

vs2019、swagger、IIS、mysql

目录：

1、CoreDb

using Dapper;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CoreDB
{
    public class MysqlDapperHelper
    {
        private string _connStr;
        public MysqlDapperHelper()
        {
          //_connStr = @"Data Source=XXXX;Database=erp;User ID=root;Password=www.XXX@XXX;allowPublicKeyRetrieval=true;pooling=true;CharSet=utf8;port=3306;sslmode=none;";
         _connStr = @"Data Source=XXXX.mysql.huhehaote.XXX.XXX.com;Database=erp;User ID=XXXX;Password=XXX!!XXXXX;allowPublicKeyRetrieval=true;pooling=true;CharSet=utf8;port=3306;sslmode=none;";
        }
        public MysqlDapperHelper(string connStr)
        {
            _connStr = connStr;
        }

        public async Task<List<T>> QueryAsync<T>(string sql)
        {
            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                var res = await connection.QueryAsync<T>(sql);
                return res.ToList();
            }
        }

        /// <summary>
        /// 存储过程查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryAsync<T>(string sql, object obj)
        {

            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                var res = await connection.QueryAsync<T>(sql, obj, commandType: CommandType.StoredProcedure);
                return res.ToList();
            }
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql)
        {
            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                var res = await connection.QueryFirstOrDefaultAsync<T>(sql);
                return res;
            }
        }

        public async Task<T> ExecuteScalarAsync<T>(string sql)
        {
            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                var res = await connection.ExecuteScalarAsync<T>(sql);
                return res;
            }
        }

        /// <summary>
        /// Execute 没开启事务
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> ExecuteAsync(string sql)
        {
            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                int res = 0;
                res = await connection.ExecuteAsync(sql);
                return res;
            }
        }

        /// <summary>
        /// Execute 开启了事务
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public async Task<int> ExecuteTranAsync(string sql)
        {
            using (IDbConnection connection = new MySqlConnection(_connStr))
            {
                connection.Open();
                int res = 0;
                using (IDbTransaction transaction = connection.BeginTransaction())
                {
                    res = await connection.ExecuteAsync(sql, null, transaction);
                    transaction.Commit();
                }
                return res;
            }
        }

    }
}
 

2、CoreModel

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Text;

namespace CoreModel
{
    /// <summary>
    /// 客户类
    /// </summary>
    public class CustomerAdd
    {
        /// <summary>
        /// /**用户id*/
        /// </summary>
        [Key]
        [Display(Name = "用户id")]
        [Column(TypeName = "int")]
        public int Id { get; set; }
        /// <summary>
        /// /**客户姓名 | ERP(purchasere)*/
        /// </summary>
        [Display(Name = "客户姓名")]
        [MaxLength(40)]
        [Column(TypeName = "nvarchar(40)")]
       // [Required(AllowEmptyStrings = false)]
        public string customerName { get; set; }

        /// <summary>
        /// /**客户号 | ERP(No)*/
        /// </summary>
        [Display(Name = "客户号")]
        [MaxLength(40)]
        [Column(TypeName = "nvarchar(40)")]
        [Required(AllowEmptyStrings = false)]
        public string customer_No { get; set; }

        /// <summary>
        **性别 0:女, 1:男 | ERP(sex)*/
        /// </summary>
        [Display(Name = "性别")]
        [Column(TypeName = "nvarchar(20)")]
        public string sex { get; set; }

        /// <summary>
        /**会员所属店铺 | (ERP(cusId关联， type=2 purchasere))*/
        /// </summary>
        [Display(Name = "会员所属店铺")]
        [Column(TypeName = "nvarchar(20)")]
        [Required(AllowEmptyStrings = false)]
        public string storeId { get; set; }

        /// <summary>
        **销售员\经办人Id*/
        /// </summary>
        [Display(Name = "销售员经办人Id")]
        [Column(TypeName = "nvarchar(20)")]
        [Required(AllowEmptyStrings = false)]
        public string salesManid { get; set; }

        /// <summary>
        **客户手机号 | ERP(mobile)*/
        /// </summary>
        [Display(Name = "客户手机号")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        [Required(AllowEmptyStrings = false)]
        public string phone { get; set; }

        /// <summary>
        **会员卡号*/
        /// </summary>
        [Display(Name = "会员卡号")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string vipNo { get; set; }

        // <summary>
        **会卡号*/
        /// </summary>
        [Display(Name = "会卡号")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string card_no { get; set; }

        // <summary>
        **会员代码*/
        /// </summary>
        [Display(Name = "会员代码")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string customer_code { get; set; }

        // <summary>
        **邮件地址*/
        /// </summary>
        [Display(Name = "邮件地址")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string email { get; set; }

        // <summary>
        **邮件地址*/
        /// </summary>
        [Display(Name = "**省ID")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string provinceName { get; set; }

        //province_id  city_id

        // <summary>
        **邮件地址*/
        /// </summary>
        [Display(Name = "**生日")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string birthday { get; set; }

        // <summary>
        **邮件地址*/
        /// </summary>
        [Display(Name = "**城市")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string cityName { get; set; }

        // <summary>
        **地址*/
        /// </summary>
        [Display(Name = "**地址")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string address { get; set; }

        // <summary>
        **vipvipGradeId*/
        /// </summary>
        [Display(Name = "**vipvipGradeId")]
        [MaxLength(20)]
        [Column(TypeName = "nvarchar(20)")]
        public string vipGradeId { get; set; }

        / <summary>
        //**创建日期*/
        / </summary
        [Display(Name = "创建日期")]
        [Column(TypeName = "datetime")]
        public DateTime CreateTime { get; set; }

        / <summary>
        //**更新日期*/
        / </summary
        //[Display(Name = "更新日期")]
        //[Column(TypeName = "datetime")]
        //public DateTime UpdateTime { get; set; }

    }
}
 

3、Controllers

using CoreDB;
using CoreModel;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Threading.Tasks;

namespace WebApi.Controllers
{
    [Route("CrmApi/[action]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="model">客户信息</param>
        /// <returns></returns>
        [HttpPost]
        public async Task<string> addCustomer([FromBody] CustomerAdd model)
        {
            MysqlDapperHelper dapper = new MysqlDapperHelper();
            //model.CreateTime = DateTime.Now; //province_id  city_id
            //id,customer_name,phone,card_no,store_id,customer_no,sales_manId,vip_grade_id,vip_grade_name,vip_no,customer_code,create_time,update_time,email,birthday,provinceName,cityName,address,vipvipGradeId
            try
            {
                string sql = @$" INSERT INTO cus_customer(customer_name,customer_code,sex,store_id,sales_manid,phone,vip_no,card_no,customer_no,create_time,birthday,email,province_id,city_id,address,vip_Grade_Id)
                        VALUES
                        (   '{model.customerName}','{model.customer_code}','{model.sex}','{model.storeId}','{model.salesManid}','{model.phone}','{model.vipNo}','{model.card_no}','{model.customer_No}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}','{model.birthday}','{model.email}','{model.provinceName}','{model.cityName}','{model.address}','{model.vipGradeId}' )";
                if (await dapper.ExecuteAsync(sql) > 0)
                    return WebResponseContent.OK("新增成功", model);
                else
                    return WebResponseContent.Error("新增失败");
            }
            catch (Exception ee)
            {
                return WebResponseContent.Error(ee.ToString());
            }
        }
        /// <summary>
        /// 修改客户
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<string> updateCustomer([FromBody] CustomerUpd model)
        {
            MysqlDapperHelper dapper = new MysqlDapperHelper();
            //model.UpdateTime = DateTime.Now;
            try
            {
                string sql = @$" update cus_customer set  update_time='{DateTime.Now.AddMinutes(3).ToString("yyyy-MM-dd HH:mm:ss")}'";
                if (model.customerName != null)
                    sql += @$",customer_name='{model.customerName}'";
                if (model.customer_No != null)
                    sql += @$",customer_code='{model.customer_No}'";
                sql += @$",sex='{model.sex}'";
                sql += @$",store_id='{model.storeId}'";
                sql += @$",sales_manid='{model.salesManid}'";
                if (model.vipNo != null)
                    sql += @$",vip_no='{model.vipNo}'";
                sql += @$" where id='" + model.Id + "'";
                model = await dapper.ExecuteScalarAsync<CustomerUpd>(sql);
                if (await dapper.ExecuteAsync(sql) > 0)
                    return WebResponseContent.OK("修改成功", model);
                else
                    return WebResponseContent.Error("修改失败");
            }
            catch (Exception ee)
            {
                return WebResponseContent.Error(ee.ToString());
            }
        }
        /// <summary>
        /// 手机号查询用户
        /// </summary>
        /// <param name="phone">电话号码（19909461116）</param>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> getCustomerByPhone(string phone)
        {
            MysqlDapperHelper dapper = new MysqlDapperHelper();
            try
            {
                string sql = "select id,customer_name,phone,card_no,store_id,customer_no,sales_manId,vip_grade_id,vip_grade_name,vip_no,create_time,update_time from cus_customer where phone='" + phone + "'";
                CustomerSlc model = await dapper.QueryFirstOrDefaultAsync<CustomerSlc>(sql);
                return WebResponseContent.OK(model);
            }
            catch (Exception ee)
            {
                return WebResponseContent.Error(ee.ToString());
            }
        }
        /// <summary>
        /// 获取开卡流水号
        /// </summary>
        /// <param name="storeId">门店ID</param>
        /// <returns></returns>
        [HttpGet]
        public async Task<string> getVipNumber([FromBody] int storeId)
        {//返回所有字段列表
            //List<Customer> list = await dapper.QueryAsync<Customer>(sql);
            返回指定字段列表
            //var devdyns = await dapper.QueryAsync<dynamic>(sql);
            //List<string> devs = devdyns.Select(q => (string)q.realname).ToList();
            //返回指定条件的所有字段列表
            MysqlDapperHelper dapper = new MysqlDapperHelper();
            string sql = "select id,customer_name,phone,card_no,store_id,customer_no,sales_manId,vip_grade_id,vip_grade_name,vip_no,create_time,update_time from cus_customer where store_id='" + storeId + "'";
            Customer model = await dapper.QueryFirstOrDefaultAsync<Customer>(sql);
            return WebResponseContent.OK(model);
        }

        [HttpGet]
        public async Task<string> GetCustomer(string CusNo)
        {
            MysqlDapperHelper dapper = new MysqlDapperHelper();
            string sql = "select id,customer_name,phone,card_no,store_id,customer_no,sales_manId,vip_grade_id,vip_grade_name,vip_no,create_time,update_time from cus_customer where customer_no='" + CusNo + "'";
            CustomerSlc model = await dapper.QueryFirstOrDefaultAsync<CustomerSlc>(sql);
            return WebResponseContent.OK(model);
        }
    }
}

4、页面测试