c# WebApi 数据库连接(EF框架)_c#数据库框架

1.EF框架包：

安装EF框架包:
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlSeerver
Microsoft.EntityFrameworkCore.Tools
Microsoft.EntityFrameworkCore.Design

2.创建数据库

数据表

CREATE table base_user (
id int IDENTITY(1,1) PRIMARY KEY not null
,t_name VARCHAR(50) not null
,t_city VARCHAR(50) 
,t_money int
)
1
2
3
4
5
6

3.利用框架包连接数据库

在VS中视图选择程序包管理器控制台,输入命令:(Scaffold-DbContext -Force “Server=.;Database=…;uid=…;Password=…;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Context WebApiContext)(将等号后面的值换作自己的数据库信息值)
命令执行完成后,EF框架会自动生成与数据表相对应的实体类.

4.数据处理

[ApiController]
[Route("user")]
puliic class BaseUserController:ControllerBase{
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="account">包括：账号/手机号/邮箱</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        [HttpGet("login")]
        public WebApiResponse Login(string account, string pwd)
        {
            WebApiContext context = null;
            try
            {
                context = new WebApiContext();
                BaseUser user = context.BaseUsers.FirstOrDefault(x => (x.TCode == account || x.TPhone == account || x.TEmail == account) && x.TPwd == pwd);
                if(user == null)
                {
                    return WebApiResponse.Create(2, "账号或密码错误");
                }
                user.TPwd = null;
                string token = Guid.NewGuid().ToString().Replace("-", "");
                UserData.AddToken(token, user.Id);
                Dictionary<string, object> data = new Dictionary<string, object>()
                {
                    ["user"] = user,
                    ["token"] = token
                };
                return WebApiResponse.OK(data);
            }
            catch (Exception ex)
            {
                return WebApiResponse.Create(1, "服务器异常");
            }
        }
        [HttpGet("selectById")]
        public WebApiResponse SeleteById(string token, int id)
        {
            BaseUser user = UserData.GetUserById(id);
            if (user == null)
            {
                return WebApiResponse.Create(1, "未查询到数据");
            }
            return WebApiResponse.OK(user);
        }

        /// <summary>
        /// 修改（会有问题，容易产生数据库和缓存不一致的情况）
        /// </summary>
        /// <returns></returns>
        [HttpPost("edit1")]
        public WebApiResponse Edit1(string token, BaseUserEditDto dto)
        {
            //当修改的时候，先删除缓存数据，再修改数据库，再次删除缓存数据
            BaseUser user = UserData.GetUserById(dto.Id);
            if (user == null)
            {
                return WebApiResponse.Create(2, "数据不存在");
            }
            WebApiContext context = null;

            try
            {
                context = new WebApiContext();
                user.TName = dto.TName;
                user.TCity = dto.TCity;
                user.TStatus = dto.TStatus;
                user.TEmail = dto.TEmail;
                user.TPhone = dto.TPhone;
                context.BaseUsers.Update(user);
                context.SaveChanges();
            }
            catch (Exception)
            {
                return WebApiResponse.Create(1, "服务器异常");
            }
            finally
            {
                context?.Dispose();
            }
            return WebApiResponse.OK("修改成功");
        }

 /// <summary>
        /// 修改
        /// </summary>
        /// <returns></returns>
        [HttpPost("edit")]
        public WebApiResponse Edit(string token, BaseUserEditDto dto)
        {
            //当修改的时候，先删除缓存数据，再修改数据库，再次删除缓存数据
            WebApiContext context = null;
            try
            {
                //先删除缓存数据
                UserData.RemoveUserById(dto.Id);
                context = new WebApiContext();
                //再修改数据库
                BaseUser user = context.BaseUsers.FirstOrDefault(x => x.Id == dto.Id);
                if (user == null)
                {
                    return WebApiResponse.Create(2, "数据不存在");
                }
                user.TName = dto.TName;
                user.TCity = dto.TCity;
                user.TStatus = dto.TStatus;
                user.TEmail = dto.TEmail;
                user.TPhone = dto.TPhone;
                context.BaseUsers.Update(user);
                context.SaveChanges();
                //再次删除缓存数据
                UserData.RemoveUserById(dto.Id);
            }
            catch (Exception)
            {
                return WebApiResponse.Create(1, "服务器异常");
            }
            finally
            {
                context?.Dispose();
            }
            return WebApiResponse.OK("修改成功");
        }

        [HttpDelete("delete")]
        public WebApiResponse Delete(string token, int id)
        {
            WebApiContext context = null;
            try
            {
                context = new WebApiContext();
                BaseUser user = context.BaseUsers.FirstOrDefault(x => x.Id == id);
                if (user == null)
                {
                    return WebApiResponse.Create(2, "数据不存在");
                }
                context.BaseUsers.Remove(user);
                context.SaveChanges();
                //从缓存中删除
                UserData.RemoveUserById(user.Id);
                return WebApiResponse.OK();
            }
            catch (Exception)
            {
                return WebApiResponse.Create(1, "服务器异常");
            }
            finally
            {
                context?.Dispose();
            }
        }
}
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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153