VB.NET ASP.NET WebAPI及应用(三)使用Mysql数据库简单的用户登录注册取数据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' );

https://dev.mysql.com/downloads/connector/net/

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

''' <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

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

http://localhost:63337/api/Register

{
    "Name":"vbee",
    "password":"123456",
    "Phone":"13832138123",
    "Email":"f@qq.com"
}

''' <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

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

http://localhost:63337/api/login/token?phone=13800138000&pwd=e10adc3949ba59abbe56e057f20f883e

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的分享