WebAPI应用集合列表
一,首先我们要在数据库里面创建一个简单用户表(角色表等其他表需要的自行创建,这里只做演示,就创建一个简单的用户表)
/*建表SQL语句*/
CREATE TABLE `t_user` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',
`u_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
`u_password` varchar(100) NOT NULL DEFAULT '' COMMENT '用户密码',
`u_phone` varchar(11) NOT NULL DEFAULT '' COMMENT '手机号',
`u_email` varchar(100) NOT NULL DEFAULT '' COMMENT '邮箱',
`u_department` varchar(11) NOT NULL COMMENT '部门',
PRIMARY KEY (`id`),
UNIQUE INDEX `手机号索引`(`u_phone`) USING HASH
);
再创建几个默认用户
INSERT INTO t_user ( u_name, u_password, u_phone, u_email, u_department )
VALUES
( '张三', MD5( '123456' ), '13800138000', 'a@qq.com', 'vbxym' ),
( '李四', MD5( '123456' ), '13801138001', 'b@qq.com', 'vbxym' ),
( '王五', MD5( '123456' ), '13802138002', 'c@qq.com', 'vbxym' ),
( '马六', MD5( '123456' ), '13803138003', 'd@qq.com', 'vbxym' ),
( '陈七', MD5( '123456' ), '13804138004', 'e@qq.com', 'vbxym' );
二,写注册登录WebAPI(沿用第一篇文章的精简模板,引用MySql.Data.dll数据库链接库即可)
2.1 开干之前我们要先创建一个Mysql帮助类,用于与Mysql的数据交换,代码如下直接复制引用即可,请在代码注明出处哦! 2.1.1 在文章一项目里面创建一个ClassModel文件夹,用来存放类文件
2.1.2 在文件夹里面添加一个MysqlHelper.vb帮助类文件
2.1.3 添加MySql.Data.dll引用,先下载
https://dev.mysql.com/downloads/connector/net/
2.1.3.1 打开压缩包找到V4.8文件夹,我用的框架是4.8,根据实际即可,点击打开把MySql.Data.dll和MySql.Data.xml两个文件复制出来放到自己能找到的地方即可,其他的可以删除掉了
2.1.3.2 然后再项目里引用刚刚复制出来的dll即可
接下来就是撸代码了(转载或使用,请标明出处"微信公众号:VB小源码")
Imports MySql.Data.MySqlClient
Public Class MysqlHelper
Implements IDisposable
''' <summary>
''' 默认IP地址
''' </summary>
Private PDBHost As String = "127.0.0.1"
''' <summary>
''' IP地址属性
''' </summary>
Public WriteOnly Property DBHost As String
Set(value As String)
PDBHost = value
End Set
End Property
''' <summary>
''' 默认端口
''' </summary>
Private PDBPort As String = 3306
''' <summary>
''' 端口属性
''' </summary>
Public WriteOnly Property DBPort As String
Set(value As String)
PDBPort = value
End Set
End Property
''' <summary>
''' 默认用户名
''' </summary>
Private PDBUser As String = "***"
''' <summary>
''' 用户名属性
''' </summary>
Public WriteOnly Property DBUser As String
Set(value As String)
PDBUser = value
End Set
End Property
''' <summary>
''' 默认密码
''' </summary>
Private PDBPwd As String = "***"
''' <summary>
''' 密码属性
''' </summary>
Public WriteOnly Property DBPwd As String
Set(value As String)
PDBPwd = value
End Set
End Property
''' <summary>
''' 默认数据库名称
''' </summary>
Private PDBName As String = "db_user"
''' <summary>
''' 数据库名称属性
''' </summary>
Public WriteOnly Property DBName As String
Set(value As String)
PDBName = value
End Set
End Property
''' <summary>
''' 默认数据库编码
''' </summary>
Private PDBCharSet As String = "UTF8"
''' <summary>
''' 数据库编码属性
''' </summary>
Public WriteOnly Property DBCharSet As String
Set(value As String)
PDBCharSet = value
End Set
End Property
''' <summary>
''' 数据库链接对象
''' </summary>
Private ConnectionObject As MySqlConnection
Private disposedValue As Boolean
''' <summary>
''' 初始化类
''' </summary>
Public Sub New()
If Not disposedValue Then
CreateMySqlConnection()
End If
End Sub
''' <summary>
''' 创建数据库链接对象
''' </summary>
Private Sub CreateMySqlConnection()
Try
'数据库连接字符串对象
Dim ConStr As New MySqlConnectionStringBuilder With {
.PersistSecurityInfo = False,
.Server = PDBHost,
.Port = PDBPort,
.UserID = PDBUser,
.Password = PDBPwd,
.Database = PDBName,
.CharacterSet = PDBCharSet,
.DefaultCommandTimeout = 0,
.ConnectionTimeout = 30,
.AllowUserVariables = True
}
'数据库连接字符串
Dim ConnString As String = ConStr.ConnectionString
ConnectionObject = New MySqlConnection(ConnString)
If ConnectionObject.State = ConnectionState.Closed Then
ConnectionObject.Open()
'ConnectionObject.ChangeDatabase("test2") ''使用新的数据库
'ConnectionObject.ServerVersion ''数据库版本号
'ConnectionObject.Database ''数据库名称
'ConnectionObject.Close()
End If
Catch ex As Exception
ConnectionObject = Nothing
End Try
End Sub
''' <summary>
''' 返回数据库数据表
''' </summary>
''' <param name="SqlStr"></param>
''' <returns></returns>
Public Function ExecuteDataTable(SqlStr As String) As DataTable
Try
If Not disposedValue Then
CreateMySqlConnection()
End If
If ConnectionObject Is Nothing Then
CreateMySqlConnection()
End If
Using CmdObject As New MySqlCommand With {
.CommandText = SqlStr,
.CommandType = CommandType.Text,
.Connection = ConnectionObject,
.CommandTimeout = 0
}
Using AdpObject As New MySqlDataAdapter With {.SelectCommand = CmdObject}
Using Dt As New DataTable()
AdpObject.Fill(Dt)
CmdObject.Parameters.Clear()
Return Dt
End Using
End Using
End Using
Catch ex As MySqlException
Return Nothing
End Try
End Function
''' <summary>
''' 执行SQL指令语句
''' </summary>
''' <param name="SqlStr">sql语句</param>
''' <returns></returns>
Public Function ExecuteNonQuery(SqlStr As String) As Integer
Try
If Not disposedValue Then
CreateMySqlConnection()
End If
If ConnectionObject Is Nothing Then
CreateMySqlConnection()
End If
Using CmdObject As New MySqlCommand With {
.CommandText = SqlStr,
.CommandType = CommandType.Text,
.Connection = ConnectionObject,
.CommandTimeout = 0
}
Dim rows As Integer = CmdObject.ExecuteNonQuery()
CmdObject.Parameters.Clear()
Return rows
End Using
Catch ex As MySqlException
Return 0
End Try
End Function
''' <summary>
''' 释放对象
''' </summary>
''' <param name="disposing"></param>
Protected Overridable Sub Dispose(disposing As Boolean)
If Not disposedValue Then
If disposing Then
Try
If ConnectionObject.State <> ConnectionState.Closed Then
ConnectionObject.Close()
disposedValue = False
End If
Catch ex As Exception
Return
End Try
End If
disposedValue = True
End If
End Sub
''' <summary>
''' 回收垃圾
''' </summary>
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(disposing:=True)
GC.SuppressFinalize(Me)
End Sub
End Class
2.2 书写用户注册WebAPI
创建一个以Post形式注册的API
2.2.1 先创建一个,接收来自客户端的数据结构体
''' <summary>
''' 注册webAPI收体
''' </summary>
Public Class RegisterPostType
Public Property Name As String '用户名
Public Property password As String '用户密码
Public Property Phone As String '手机号
Public Property Email As String '邮箱
End Class
2.2.2 然后就是注册控制器的代码了
Imports System.Net
Imports System.Web.Http
''注册API控制器
Public Class RegisterController
Inherits ApiController
''' <summary>
''' 用户注册API
''' </summary>
''' <param name="getpostdata"></param>
<HttpPost>
<AcceptVerbs("POST")>
<Route("api/register")>
Public Function RegisterUser(<FromBody()> getpostdata As RegisterPostType) As Object
Dim postdata As RegisterPostType = getpostdata
''验证数据
If String.IsNullOrWhiteSpace(postdata.Name) OrElse
String.IsNullOrWhiteSpace(postdata.password) OrElse
String.IsNullOrWhiteSpace(postdata.Phone) OrElse
String.IsNullOrWhiteSpace(postdata.Email) Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"缺少必要参数,请完整填写",
.Datas = Nothing
}
End If
''验证邮箱地址
If Not IsEmail(postdata.Email) Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"非法的邮箱地址[{postdata.Email}],请审核后继续提交",
.Datas = Nothing
}
End If
''验证手机号
If Not IsPhoneNumber(postdata.Phone) Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"非法的手机号码[{postdata.Phone}],请审核后继续提交",
.Datas = Nothing
}
End If
''验证数据库信息并把新注册用户数据写入数据库
Using dbmysql As New MysqlHelper
''先验证数据库数据(注意:这里不涉及什么防注入之类,只说功能的实现,安全方面自行考虑)
Dim udt As DataTable = dbmysql.ExecuteDataTable($"SELECT COUNT(*) AS ct FROM t_user WHERE u_phone='{postdata.Phone}' OR u_email='{postdata.Email}'")
If udt IsNot Nothing Then
If udt.Rows.Count > 0 Then
If Val(udt.Rows(0).Item("ct")) > 0 Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"手机号码或邮箱地址已被注册,请审核后继续提交",
.Datas = Nothing
}
End If
End If
End If
''通过验证没问题后才往数据库里面写(在这里,可以做一个,手机验证码或者邮箱验证码的验证,自行搞哈)
Dim issucc As Integer = dbmysql.ExecuteNonQuery($"INSERT INTO t_user ( u_name, u_password, u_phone, u_email, u_department ) VALUES ( '{postdata.Name}', MD5( '{postdata.password}' ), '{postdata.Phone}', '{postdata.Email}', 'vbxym' );")
If issucc > 0 Then
''返回成功信息
Return New Responobj With {
.Code = 200,
.Msg = "成功",
.Info = $"{postdata.Name}注册成功,请返回登录!",
.Datas = Nothing
}
End If
End Using
''返回成功信息
Return New Responobj With {
.Code = 200,
.Msg = "失败",
.Info = $"{postdata.Name}注册失败,发生未知错误,请重新提交或者联系管理员!",
.Datas = Nothing
}
End Function
End Class
2.2.3 访问地址
地址:
http://localhost:63337/api/Register
POST数据结构;方式{application/json}
{
"Name":"vbee",
"password":"123456",
"Phone":"13832138123",
"Email":"f@qq.com"
}
各种访问错误效果
注册成功效果
2.3 书写用户登录WebAPI
创建一个以GET形式登录的API
2.3.1 先创建一个,统一返回数据结构体
''' <summary>
''' 定义一个返回体
''' </summary>
Public Class Responobj
Public Property Code As Integer ''状态码
Public Property Msg As String ''信息
Public Property Info As String ''描述
Public Property Datas As Object ''数据
End Class
2.3.2 然后就是登录控制器的代码了,直接使用第一章节的内容
Imports System.Net
Imports System.Web.Http
Public Class LoginController
Inherits ApiController
' GET api/login?
' <HttpGet> ''请求方式
' <ActionName("Token")> ''方法名称
<AcceptVerbs("GET", "HEAD")> ''允许的请求方式
<Route("api/login/token")> ''自定义路由
Public Function GetToken(<FromUri()> phone As String, <FromUri()> pwd As String) As Object
''验证手机号
If Not IsPhoneNumber(phone) Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"非法的手机号码[{phone}],请审核后继续提交",
.Datas = Nothing
}
End If
''----------------
Using dbmysql As New MysqlHelper
''首先要验证用户是否存在(这里只写用手机号码登录的情况)
Dim udt As DataTable = dbmysql.ExecuteDataTable($"SELECT COUNT(*) AS ct FROM t_user WHERE u_phone='{phone}';")
If udt IsNot Nothing Then
If udt.Rows.Count > 0 Then
If Val(udt.Rows(0).Item("ct")) = 0 Then
Return New Responobj With {
.Code = 400,
.Msg = "登录失败",
.Info = $"手机号码或邮箱地址尚未注册,请注册后登录",
.Datas = Nothing
}
End If
End If
End If
''已注册则取出用户信息进行比对,比对成功则返回 token或者其他信息给客户端表示该账号已登录
Dim ldt As DataTable = dbmysql.ExecuteDataTable($"SELECT u_name as n, u_password as p FROM t_user WHERE u_phone='{phone}';")
If ldt IsNot Nothing Then
If ldt.Rows.Count > 0 Then
Dim dbn As String = ldt.Rows(0).Item("n")
Dim dbp As String = ldt.Rows(0).Item("p")
''------
If pwd = dbp Then
Return New Responobj With {
.Code = 200,
.Msg = "成功",
.Info = "登录系统成功",
.Datas = New TokenType With { ''这里假装有一个token
.exp = Now().Ticks + 60, ''假装有效时间
.iat = Now.Ticks,
.token = GET_MD5(dbn & dbp) ''假装的token
}
}
End If
End If
End If
''登录失败
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = "账户或密码错误",
.Datas = Nothing
}
End Using
End Function
End Class
2.3.3 访问地址
地址(GET):
http://localhost:63337/api/login/token?phone=13800138000&pwd=e10adc3949ba59abbe56e057f20f883e
各种登录错误效果
登录成功效果
三,数据库取数WebAPI书写
新建一个控制器DataController.vb
控制器代码
Imports System.Net
Imports System.Web.Http
Imports Newtonsoft.Json
Public Class DataController
Inherits ApiController
''' <summary>
''' 简单的数据库取数据
''' </summary>
''' <returns></returns>
<HttpGet>
<AcceptVerbs("GET")>
<Route("api/data/userinfo")>
Public Function GetUserDatas(<FromUri> token As String, <FromUri> Optional uname As String = "") As Object
If String.IsNullOrWhiteSpace(token) Then
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"缺少必要参数",
.Datas = Nothing
}
End If
''验证TOKEN(注意,如果想要没一条api都要登录后访问,都要验证登录标志或者token,用什么标记自行考虑,这里不在描述)
If token <> "19457E886188871DF30BB2D4571E6C1C" Then ''这里就假装验证一下,一般不固定,这里只是假装有这回事
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"非法的访问",
.Datas = Nothing
}
End If
''---------------------------
Using dbmysql As New MysqlHelper
Dim tj As String = ""
If String.IsNullOrWhiteSpace(uname) = False Then
tj = $" WHERE u_name ='{uname}'"
End If
''---------------------
Dim udt As DataTable = dbmysql.ExecuteDataTable($"SELECT * FROM t_user {tj};")
If udt IsNot Nothing Then
If udt.Rows.Count > 0 Then
Return New Responobj With {
.Code = 200,
.Msg = "成功",
.Info = $"获取用户表数据成功",
.Datas = udt
}
End If
End If
''取数失败
Return New Responobj With {
.Code = 400,
.Msg = "失败",
.Info = $"获取用户表数据失败",
.Datas = Nothing
}
End Using
End Function
End Class
访问地址
全部用户信息
http://localhost:63337/api/data/userinfo?token=19457E886188871DF30BB2D4571E6C1C
单一用户信息
http://localhost:63337/api/data/userinfo?token=19457E886188871DF30BB2D4571E6C1C&uname=vbee
访问全部用户信息结果
{
"code": 200,
"msg": "成功",
"info": "获取用户表数据成功",
"datas": [
{
"id": 1,
"u_name": "张三",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13800138000",
"u_email": "a@qq.com",
"u_department": "vbxym"
},
{
"id": 2,
"u_name": "李四",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13801138001",
"u_email": "b@qq.com",
"u_department": "vbxym"
},
{
"id": 3,
"u_name": "王五",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13802138002",
"u_email": "c@qq.com",
"u_department": "vbxym"
},
{
"id": 4,
"u_name": "马六",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13803138003",
"u_email": "d@qq.com",
"u_department": "vbxym"
},
{
"id": 5,
"u_name": "陈七",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13804138004",
"u_email": "e@qq.com",
"u_department": "vbxym"
},
{
"id": 6,
"u_name": "vbee",
"u_password": "e10adc3949ba59abbe56e057f20f883e",
"u_phone": "13832138123",
"u_email": "f@qq.com",
"u_department": "vbxym"
}
]
}
访问异常效果
获取数据成功效果
本章节项目下载
链接:https://pan.baidu.com/s/1c6z2yMvR7I3LQ1s2KfQSMQ
提取码:vbee
--来自百度网盘超级会员V5的分享