Microbubu的迷你实验室

使用.NetCore搭建一个简单的WebApi

.NetCore WebApi PetaPoco
字数统计: 966阅读时长: 5 min
2018/11/01 Share

创建工程和数据库

  • 首先在Visual Studio中新建.NetCore Api工程CoreWebApiWithPetaPoco.
  • 在Nuget中引入第三方包PetaPoco.NetCore.
  • 本地创建数据库并创建一个名为AuthUser的表:
1
2
3
4
5
CREATE TABLE [dbo].[AuthUser] (
    [Id] BIGINT IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

创建模型

  • 在本地项目中新建Models文件夹、DataAccess文件夹.
  • Models文件夹下新建类AuthUser:
1
2
3
4
5
6
7
8
9
10
11
12
using PetaPoco.NetCore;

namespace CoreWebApiWithPetaPoco.Models
{
    [PrimaryKey(nameof(Id))]
    [TableName(nameof(AuthUser))]
    public class AuthUser
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
}
  • 在DataAccess文件夹下新建数据库访问类DataAccess:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
using PetaPoco.NetCore;
using System.Data.SqlClient;

namespace CoreWebApiWithPetaPoco.DataAccess
{
    public class DataAccess
    {
        private Database db;
        public static string ConnectionString;
        public Database DB { get => db; }
        public DataAccess()
        {
            db = new Database(new SqlConnection(ConnectionString));
        }
    }
}

配置数据库连接

  • 在项目文件appsettings.json中添加数据库连接字符串ConnectionString, 名字随便起，只是后面在获取的时候对应上就OK:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
{
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  },
  "ConnectionString": "Data Source=(localdb)\\ProjectsV13;Initial Catalog=CoreWebApiDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}
  • 在项目文件StartUp.cs文件中的StartUp方法中获取连接字符串的值，并将其赋值给DataAccess类中的静态字段ConnectionString:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using CoreWebApiWithPetaPoco.DataAccess;

namespace CoreWebApiWithPetaPoco
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;

            // 获取连接字符串的值，参数对应上appsettings.json中连接字符串的名称
            DataAccess.DataAccess.ConnectionString = Configuration.GetValue<string>("ConnectionString");
        }
        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)
        {
            services.AddMvc();
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseMvc();
        }
    }
}

新建控制器

  • 在Controller中添加UserController，这里只测试Get和Post:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using CoreWebApiWithPetaPoco.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using PetaPoco.NetCore;

namespace CoreWebApiWithPetaPoco.Controllers
{
    [Produces("application/json")]
    [Route("api/User")]
    public class UserController : Controller
    {
        private DataAccess.DataAccess access = new DataAccess.DataAccess();

        // GET: api/User
        [HttpGet]
        public IEnumerable<AuthUser> Get()
        {
            return access.DB.Query<AuthUser>("select * from AuthUser");
        }
        // GET: api/User/5

        [HttpGet("{id}", Name = "Get")]
        public AuthUser Get(int id)
        {
            return access.DB.SingleOrDefault<AuthUser>("where Id=@0", id);
        }

        // 添加Pager路由，这样就不会因为和已经存在的Get方法重复而报错
        [Route("Pager"),HttpGet]
        public Page<AuthUser> Get(int pageSize,int pageNumber)
        {
            return access.DB.Page<AuthUser>(pageNumber, pageSize, "select * from AuthUser");
        }
       
        // POST: api/User
        [HttpPost]
        public dynamic Post(string name)
        {
            try
            {
                var user = new AuthUser() { Name = name };
                access.DB.Insert(user);
                return new { Message = "Success" };
            }
            catch(Exception e)
            {
                return new { Message = $"Error:{e.Message}" };
            }           
        }
       
        // PUT: api/User/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody]string value)
        {
        }
       
        // DELETE: api/ApiWithActions/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
        }
    }
}

结果测试

  • 最后运行项目并编写python脚本进行api测试:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import requests

if __name__ == '__main__':
    url = "http://localhost:8211/api/user"
    print(requests.get(url).text)

    # 获取分页数据
    url = "http://localhost:8211/api/user/pager"
    params = {"pageSize": 10, "pageNumber": 1}
    print(requests.get(url, params).text)

    # post请求
    url = "http://localhost:8211/api/user"
    params = {"name": "User6"}
    print(requests.post(url, data=params).text)
  • 下面是测试返回的数据:
1
2
3
4
5
6
7
8
// 获取所有AuthUser:
[{"id":1,"name":"User1"},{"id":2,"name":"User2"},{"id":3,"name":"User3"},{"id":4,"name":"User4"},{"id":5,"name":"User5"},{"id":6,"name":"User5"},{"id":7,"name":"User5"},{"id":8,"name":"User5"},{"id":9,"name":"User5"},{"id":10,"name":"User6"}]

// get获取的分页数据:
{"currentPage":1,"totalPages":1,"totalItems":10,"itemsPerPage":10,"items":[{"id":1,"name":"User1"},{"id":2,"name":"User2"},{"id":3,"name":"User3"},{"id":4,"name":"User4"},{"id":5,"name":"User5"},{"id":6,"name":"User5"},{"id":7,"name":"User5"},{"id":8,"name":"User5"},{"id":9,"name":"User5"},{"id":10,"name":"User6"}],"context":null}

// post结果:
{"message":"Success"}

原文作者：Microbubu Groner

原文链接：https://microbubu.github.io/2018/11/01/使用-NetCore搭建一个简单的WebApi/

发表日期：November 1st 2018, 2:27:46 pm

更新日期：November 6th 2018, 4:22:53 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. 创建工程和数据库
  2. 2. 创建模型
  3. 3. 配置数据库连接
  4. 4. 新建控制器
  5. 5. 结果测试
Total : 11
2019
2018
PLC Markdown C# 数据结构与算法 OneDrive 图床 .NetCore WebApi PetaPoco 面试题 Python Map Reduce Kotlin 集合 Linux