net core webapi 数据库连接

引入NuGet包  EntityFrameworkCore 

 

 

修改appsettings.json，添加数据库连接字符串

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*", // 允许所有域名访问  
  "ConnectionStrings": {
    //                 数据库地址       数据库名       帐号          密码
    "SqlConn": "Server=localhost;Database=KBDB;User ID=sjz2018dbuser;Password=1ECD9E*#E9DCE1;"
  }
}

 

创建实体（日期类型注意设置为可为空）

public class kb_Tasks
    {
        /// <summary>
        /// 任务ID
        /// </summary>
        public string ID { get; set; } 
        /// <summary>
        /// 任务内容
        /// </summary>
        public string TaskInfo { get; set; }
        /// <summary>
        /// 作者
        /// </summary>
        public string Author { get; set; }
        /// <summary>
        /// 任务创建时间
        /// </summary>
        public DateTime? InsertTime { get; set; }
        /// <summary>
        /// 任务完成时间
        /// </summary>
        public DateTime? DoneTime { get; set; }
        /// <summary>
        /// 任务状态  0 未完成   1 正在进行   2 已完成
        /// </summary>
        public int State { get; set; }
        /// <summary>
        /// 是否看板显示  0 否   1 是
        /// </summary>
        public int IsShow { get; set; }
    }

 

编写DBHelper

public class DBHelper : DbContext
    {
        public DBHelper() : base() { }
        public DBHelper(DbContextOptions<DBHelper> options)
          : base(options)
        {

        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            #region 方法一 数据库连接配置放在了appsettings.json文件中
            // 获取appsettings.json配置信息
            var config = new ConfigurationBuilder()
                            .SetBasePath(System.IO.Directory.GetCurrentDirectory())
                            .AddJsonFile("appsettings.json")
                            .Build();
            // 获取数据库连接字符串
            string conn = config.GetConnectionString("SqlConn");
            //连接数据库
            optionsBuilder.UseSqlServer(conn);
            #endregion


            #region 方法二 直接连接
            ////连接数据库
            //optionsBuilder.UseSqlServer("server=.;uid=sjz2018dbuser;pwd=1ECD9E*#E9DCE1;database=KBDB;");
            #endregion

        }

        public DbSet<kb_Tasks> kb_Tasks { get; set; } // 不区分大小写，名称必须与数据库表名相同
    }

 

 

备注：在使用 UseSqlServer() 的时候，需要添加依赖包：Microsoft.EntityFrameworkCore.SqlServer

 

Controller

举例：查询集合

public class TaskController : Controller
    {

        [HttpGet]
        public OkObjectResult GetTaskList()
        {
            DBHelper db = new DBHelper();

            //var taskList = db.kb_Tasks;
            //foreach (var item in taskList)
            //{
            //    Console.WriteLine("{0}\t{1}", item.ID, item.TaskInfo);
            //}

            List<kb_Tasks> TaskList = db.kb_Tasks.ToList();
            return Ok(from t in TaskList
                      select new
                      {
                          ID = t.ID,
                          TaskInfo = t.TaskInfo,
                          Author = t.Author,
                          InsertTime = t.InsertTime,
                          DoneTime = t.DoneTime,
                          State = t.State,
                          IsShow = t.IsShow
                      });

        }



    }

 

效果：

 

举例：新增

[HttpPost]
        public OkObjectResult AddTaskInfo(kb_Tasks tModel)
        {
            DBHelper db = new DBHelper();
            try
            {
                if (tModel != null)
                {
                    db.kb_Tasks.Add(tModel);
                    db.SaveChanges();
                    return Ok("入库成功！");
                }
                else
                {
                    return Ok("入库失败，所填信息不得为空！");
                }
            }
            catch(Exception ex)
            {
                return Ok(ex.Message);
            }

        }

效果：

 

 

 

举例：删除

[HttpGet]
        public OkObjectResult RemoveTaskInfoByID(string ID)
        {
            DBHelper db = new DBHelper();
            try
            {
                if (ID!="")
                {
                    kb_Tasks tModel = db.kb_Tasks.Where(t => t.ID == ID).FirstOrDefault();
                    db.kb_Tasks.Remove(tModel);
                    db.SaveChanges();
                    return Ok("移除成功！");
                }
                else
                {
                    return Ok("移除失败，所选任务ID不得为空！");
                }
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }

        }

效果：