-
Notifications
You must be signed in to change notification settings - Fork 0
/
UserInfo.cs
97 lines (91 loc) · 3.33 KB
/
UserInfo.cs
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
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using Dapper;
using Microsoft.Extensions.Configuration;
using Model;
using Microsoft.EntityFrameworkCore.Scaffolding.Internal;
using Remotion.Linq.Utilities;
namespace DAL
{
public class UserInfo
{
private UserInfo() { }
private static UserInfo _instance = new UserInfo();
public static UserInfo instance
{
get
{
return _instance;
}
}
string cns =AppConfigurtaionServices.Configuration.GetConnectionString("cns");
public string UserCheck(String UserName)
{
using(IDbConnection cn=new MySqlConnection(cns))
{
string sql = "select password from userinfo where username=@username;";
return cn.ExecuteScalar<string>(sql, new { username = UserName });
}
}
public Model.UserInfo GetModel(String UserName)
{
using (IDbConnection cn =new MySqlConnection(cns))
{
string sql = "select * from userinfo where username=@username";
return cn.QueryFirstOrDefault<Model.UserInfo>(sql, new { username = UserName });
}
}
public IEnumerable<Model.UserInfo> GetAll()
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "select * from userinfo";
return cn.Query<Model.UserInfo>(sql);
}
}
public int GetCount()
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "select count(1) from userinfo";
return cn.ExecuteScalar<int>(sql);
}
}
public IEnumerable<Model.UserInfoNo> GetPage(Model.Page page)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "with a as(select userinfo.*, row_number() over(order by username) as num from userinfo) for " ;
sql += "select * from a where num between(@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize;";
return cn.Query<Model.UserInfoNo>(sql, page);
}
}
public int Add(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "insert into userinfo values(@userName,@passWord,@qq,@email,@type,@userImg);";
return cn.Execute(sql, user);
}
}
public int Update(Model.UserInfo user)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "update userinfo set password=@passWord,qq=@qq,email=@email,type=@type,userimg=@userImg where username=@userName";
return cn.Execute(sql, user);
}
}
public int Delete(string username)
{
using (IDbConnection cn = new MySqlConnection(cns))
{
string sql = "delete from userinfo where username=@userName";
return cn.Execute(sql, new { username = username });
}
}
}
}