VSCode创建.NetCore 2.0 WebAPI+EF+SqlServer，vscodewebapi

VSCode创建.NetCore 2.0 WebAPI+EF+SqlServer，vscodewebapi

一、准备工作

1、使用VSCode创建一个WebAPI项目，项目的架构如下

2、项目中添加Package引用

• DotNet.Model  从Nuget 中添加 Microsoft.EntityFrameworkCore
• NotNet.DAL 从Nuget 中添加Microsoft.EntityFrameworkCore、Microsoft.EntityFrameworkCore.Tools、Microsoft.EntityFrameworkCore.SqlServer、Microsoft.EntityFrameworkCore.Design、System.Linq.Dynamic.Core、Microsoft.AspNetCore，同时添加对Model和Utilities的引用
• DotNet.BLL 添加对 Model、Utilities、DAL的引用
• DotNet.Utilities 从Nuget中添加Microsoft.AspNetCore
• WebAPI添加对Model、BLL、Utilities的引用

二、数据配置

1、在WebAPI项目中的appsettings.json中添加数据库连接配置

"ConnectionStrings": {
    "SqlServerConnection": "Server=.;Database=AngularDemo;User ID=sa;Password=123456;"
  }

 2、在DotNet.Model中创建一个AcountInfo的实体类

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace DotNet.Model
{
    /// <summary>
    /// 用户登录信息
    /// </summary>
    public class AccountInfo
    {
        /// <summary>
        /// 账户ID
        /// </summary>
        /// <returns></returns>
        [Key]
        public string AccountID { get; set; }

        /// <summary>
        /// 登陆账号
        /// </summary>
        /// <returns></returns>
        public string LoginID { get; set; }
        /// <summary>
        /// 密码
        /// </summary>
        /// <returns></returns>
        public string PassWord { get; set; }

        /// <summary>
        /// 用户姓名
        /// </summary>
        /// <returns></returns>
        public string UserName { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        /// <returns></returns>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 修改时间
        /// </summary>
        /// <returns></returns>
        public DateTime? ModifyTime { get; set; }
    }
}

3、DotNet.DAL创建AngularDbContent类文件，设置数据的相关连接以及处理

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using DotNet.Model;

namespace DotNet.DAL
{
    public class AngularDbContent : DbContext
    {
        public AngularDbContent() : base() { }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var config = new ConfigurationBuilder()
                            .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                            .AddJsonFile("appsettings.json")
                            .Build();

            optionsBuilder.UseSqlServer(config.GetConnectionString("SqlServerConnection"));
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AccountInfo>().HasKey(c => new { c.AccountID });
        }

        public DbSet<AccountInfo> accountInfo { get; set; }
    }
}

4、DAL中创建一个AcountInfoDAL类文件，用于数据的增删改查等操作

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using System.Linq.Dynamic.Core;
using DotNet.Model;

namespace DotNet.DAL
{
    public class AccountInfoDAL
    {
        AngularDbContent dbContent;

        public AccountInfoDAL()
        {
            dbContent = new AngularDbContent();
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public List<AccountInfo> GetList()
        {
            try
            {
                var accountList = dbContent.accountInfo.OrderByDescending(s => s.CreateTime);
                return (accountList.Any()) ? accountList.ToList() : null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 根据查询条件获取数据
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public List<AccountInfo> GetListByWhere(string where)
        {
            try
            {
                var accountList = dbContent.accountInfo.Where(where).OrderByDescending(s => s.CreateTime);
                return (accountList.Any()) ? accountList.ToList() : null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="currentPage">当前页码</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总数据条数</param>
        /// <param name="pageCount">页码数量</param>
        /// <returns></returns>
        public List<AccountInfo> GetPageList(int currentPage, int pageSize, string where, out int totalCount, out int pageCount)
        {
            int skipCount = (currentPage - 1) * pageSize;
            pageCount = 0;
            totalCount = 0;
            try
            {
                totalCount = dbContent.accountInfo.Where(where).Count();
                if ((totalCount % pageSize) > 0)
                {
                    pageCount = (totalCount / pageSize) + 1;
                }
                else
                {
                    pageCount = totalCount / pageSize;
                }
                var accountList = dbContent.accountInfo.Where(where).OrderByDescending(s => s.CreateTime)
                .Skip(skipCount).Take(pageSize);
                return (accountList.Any()) ? accountList.ToList() : null;
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public bool Save(AccountInfo item)
        {
            if (item == null)
            {
                return false;
            }
            int rowsAffected = 0;//受影响行数
            string accountID = (!string.IsNullOrWhiteSpace(item.AccountID)) ? item.AccountID : Guid.NewGuid().ToString();
            try
            {
                var accountModel = dbContent.accountInfo.Where(s => s.AccountID.Equals(accountID)).FirstOrDefault();
                if (accountModel != null)
                {
                    accountModel.LoginID = item.LoginID;
                    accountModel.PassWord = item.PassWord;
                    accountModel.UserName = item.UserName;
                    accountModel.ModifyTime = System.DateTime.Now;
                    rowsAffected = dbContent.SaveChanges();
                }
                else
                {
                    item.AccountID = accountID;
                    item.CreateTime = System.DateTime.Now;
                    dbContent.accountInfo.Add(item);
                    rowsAffected = dbContent.SaveChanges();
                }
                return (rowsAffected > 0) ? true : false;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="accountID"></param>
        /// <returns></returns>
        public bool Delete(string accountID)
        {
            if (string.IsNullOrWhiteSpace(accountID))
            {
                return false;
            }
            try
            {
                var accountModel = dbContent.accountInfo.Where(s => s.AccountID.Equals(accountID)).FirstOrDefault();
                dbContent.Remove(accountModel);
                int rowsAffected = dbContent.SaveChanges();
                return (rowsAffected > 0) ? true : false;
            }
            catch
            {
                return false;
            }
        }
    }
}

5、BLL中创建AcountInfoBLL类文件，调用DAL

using System;
using DotNet.Utilities;
using DotNet.Model;
using DotNet.DAL;

namespace DotNet.BLL
{
    public class AccountInfoBLL
    {
        private static readonly AccountInfoDAL accountInfoDAL = new AccountInfoDAL();

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <returns></returns>
        public static string GetList()
        {
            var accountList = accountInfoDAL.GetList();
            return (accountList != null) ? JsonHelperT.ToJson(accountList) : "";
        }

        /// <summary>
        /// 根据查询条件获取数据
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public static string GetListByWhere(string where)
        {
            var accountList = accountInfoDAL.GetListByWhere(where);
            return (accountList != null) ? JsonHelperT.ToJson(accountList) : "";
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="currentPage">当前页码</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="where">查询条件</param>
        /// <param name="totalCount">总数据条数</param>
        /// <param name="pageCount">页码数量</param>
        /// <returns></returns>
        public static string GetPageList(int currentPage, int pageSize, string where)
        {
            int totalCount = 0;
            int pageCount = 0;
            var accountList = accountInfoDAL.GetPageList(currentPage, pageSize, where, out totalCount, out pageCount);
            if (accountList != null)
            {
                var accountinfo = new
                {
                    totalcount = totalCount,
                    pagecount = pageCount,
                    mainList = accountList
                };
                return JsonHelperT.ToJson(accountinfo);
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public static string Save(AccountInfo item)
        {
            string isOK = "success";
            if (!accountInfoDAL.Save(item))
            {
                isOK = "fail";
            }
            var mesg = new
            {
                message = isOK
            };
            return JsonHelperT.ToJson(mesg);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="accountID"></param>
        /// <returns></returns>
        public static string Delete(string accountID)
        {
            string isOK = "success";
            if (!accountInfoDAL.Delete(accountID))
            {
                isOK = "fail";
            }
            var mesg = new
            {
                message = isOK
            };
            return JsonHelperT.ToJson(mesg);
        }
    }
}

6、WebAPI中创建AccountInfoController控制器

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Cors;
using Microsoft.AspNetCore.Mvc;
using System.Net.Http;
using DotNet.Utilities;
using DotNet.Model;
using DotNet.BLL;

namespace WebAPI.Controllers
{
    [EnableCors("CorsPolicy")]
    [Route("api/[controller]")]
    [ApiController]
    public class AccountInfoController : ControllerBase
    {
        private HttpResponseMessage WriteMsg(string Msg)
        {
            return new HttpResponseMessage { Content = new StringContent(Msg, System.Text.Encoding.UTF8, "application/json") };
        }
        /// <summary>
        /// 分页数据参数
        /// </summary>
        public class InputModel
        {
            public int CurretPage { get; set; }
            public int PageSize { get; set; }
            public List<SearchModel> SearchList { get; set; }
        }

        /// <summary>
        /// 登录参数
        /// </summary>
        public class LoginModel
        {
            public string LoginID { get; set; }
            public string PassWord { get; set; }
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="login"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("Login")]
        public string Login([FromBody]LoginModel login)
        {
            return AccountInfoBLL.Login(login.LoginID, login.PassWord);
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [Route("GetList")]
        public string GetList()
        {
            return AccountInfoBLL.GetList();
        }

        /// <summary>
        /// 根据条件获取数据
        /// </summary>
        /// <param name="searchList"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("GetListByWhere")]
        public string GetListByWhere([FromBody]List<SearchModel> searchList)
        {
            return AccountInfoBLL.GetListByWhere(SearchHelper.GetSarchCondition(searchList));
        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="inputValue"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("GetPageList")]
        public string GetPageList([FromBody]InputModel inputValue)
        {
            string where = SearchHelper.GetSarchCondition(inputValue.SearchList);
            return AccountInfoBLL.GetPageList(inputValue.CurretPage, inputValue.PageSize, where);
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("Save")]
        public string Save([FromBody]AccountInfo item)
        {
            return AccountInfoBLL.Save(item);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="accountID"></param>
        /// <returns></returns>
        [HttpDelete]
        [Route("Delete")]
        public string Delete([FromBody]string accountID)
        {
            return AccountInfoBLL.Delete(accountID);
        }
    }
}

启动WebAPI接口，使用PostMan 调用接口测试