第1章 1.2 API 服务 (webapi) 访问数据库

• 基础环境
  第三方引用：dapper

• 参考
  采用dapper连接mssql
  https://github.com/renatogroffe/Dapper-DotNetCore2/tree/master/Dapper_DotNetCore2/ExemploJsonSqlServer

• 数据库管理工具
  dbeaver
  https://dbeaver.io/download/

数据库脚本 db.sql

CREATE TABLE dbo.userdata (
    id int IDENTITY(1,1),
    name varchar(100),
    sex varchar(100)
) go

安装dapper

dotnet add package dapper

appsettings.json文件中添加连接字符串

{
  "ConnectionStrings": {
    "Dapper_SQLServer": "Data Source=47.92.110.155,1433;Integrated Security=false;Initial Catalog=GoldReport;User Id = sa;Password = abc;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

注意：

.Net Core 在 Linux 下连接 SqlServer 需要 SqlServer2008 SP3或以上版本，或SqlServer2012，或SqlServer2014.

如果SqlServer2008低于SP3版本，会出现连接超时的问题。

修改之前默认的控制器中的方法

    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.IO;
    using System.Linq;
    using System.Threading.Tasks;
    using Dapper;
    using Microsoft.AspNetCore.Mvc;
    using Microsoft.Extensions.Configuration;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Linq;

    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        
        // GET api/values
        [HttpGet]
        public ContentResult Get(
            [FromServices]IConfiguration config)
        {

            using (SqlConnection connection = new SqlConnection(
                config.GetConnectionString("Dapper_SQLServer")))
            {

                var data = connection.Query<userdata>(
                    "SELECT id " +
                          ",name " +
                          ",sex " +
                    "FROM dbo.userdata " +
                    "ORDER BY id "
                    );


                return Content(JsonHelper.ToJson(data), "application/json");
            }
        }
    }

使用POSTMAN测试

[
    {
        "id": 1,
        "name": "张三",
        "sex": "男"
    },
    {
        "id": 2,
        "name": "李四 ",
        "sex": "男"
    },
    {
        "id": 3,
        "name": "王五 ",
        "sex": "男"
    },
    {
        "id": 4,
        "name": "赵六",
        "sex": "男"
    }
]