vb.net 8.0 Core访问数据库

使用Imports Microsoft.Data.SqlClient进行访问，使用ExecuteReader直接转为DataTable和DataSet

执行sql附带List(Of SqlParameter)数组模式参数

Imports Microsoft.Data.SqlClient

Partial Module BaseModule
  Dim ConnString As String = "Server=127.0.01,1433;Database=ouen;Integrated Security=False;User ID=sa;Password=sqlpwd;Encrypt=True;TrustServerCertificate=True;"
  Public SqlCommd As New SqlCommand("", New SqlConnection(ConnString))
  ''' <summary>
  ''' 生成新的ID值
  ''' </summary>  '''
  Function NewGUID(Optional Len As Integer = 8) As String '生成GUID码
    Dim N As String = Guid.NewGuid.ToString.Replace("-", "")
    Return N.Substring(N.Length - Len, Len).ToLower
  End Function
  ''' <summary>
  ''' 执行SQL语句返回执行成功行数
  ''' </summary>
  Function SQLNonQuery(CmdText As String, Optional Par As List(Of SqlParameter) = Nothing) As Integer  '指行SQL操作语句
    If CmdText = "" Then Return 0
    Try
      SqlCommd.CommandText = CmdText
      SqlCommd.Parameters.Clear()
      If Par IsNot Nothing AndAlso Par.Count > 0 Then SqlCommd.Parameters.AddRange(Par.ToArray)
      If SqlCommd.Connection.State <> Data.ConnectionState.Open Then SqlCommd.Connection.Open()
      Return SqlCommd.ExecuteNonQuery
    Catch ex As Exception
      SoftLog("SqlNonQuery：" & ex.Message & vbCrLf & CmdText, "SQL错误_" & Now.ToString("yyyyMMdd") & ".log")
    End Try
    Return 0
  End Function
  ''' <summary>
  ''' 快速查询并返回第一个列字段数据
  ''' </summary>
  Function SQLReader(CmdText As String) As Object  '指行SQL操作语句
    Try
      SqlCommd.CommandText = CmdText
      SqlCommd.Parameters.Clear()
      If SqlCommd.Connection.State <> Data.ConnectionState.Open Then SqlCommd.Connection.Open()
      Return SqlCommd.ExecuteScalar() '返回首行首列
    Catch ex As Exception
      SoftLog("SQLReader：" & ex.Message & vbCrLf & CmdText, "SQL错误_" & Now.ToString("yyyyMMdd") & ".log")
    End Try
    Return 0
  End Function
  ''' <summary>
  ''' 查询一个表，返回Table表
  ''' </summary>
  Function SQLTable(CmdText As String) As Data.DataTable '查询表
    Dim dt As New Data.DataTable
    Try
      SqlCommd.Parameters.Clear()
      SqlCommd.CommandText = CmdText
      If SqlCommd.Connection.State <> Data.ConnectionState.Open Then SqlCommd.Connection.Open()
      Dim dr As SqlDataReader = SqlCommd.ExecuteReader()
      dt.Load(dr)
      dr.Close()
    Catch ex As Exception
      SoftLog("SQLTable：" & ex.Message & vbCrLf & CmdText, "SQL错误_" & Now.ToString("yyyyMMdd") & ".log")
    End Try
    Return dt
  End Function
  ''' <summary>
  ''' 查询SQL，一次性返回多个表数据
  ''' </summary>
  Function SQLDataSet(CmdText As String) As Data.DataSet '一性次查询多个表
    Dim ds As New Data.DataSet
    Try
      SqlCommd.CommandText = CmdText
      SqlCommd.Parameters.Clear()
      If SqlCommd.Connection.State <> Data.ConnectionState.Open Then SqlCommd.Connection.Open()
      Dim dr As SqlDataReader = SqlCommd.ExecuteReader()
      Do
        Dim dt As New Data.DataTable()
        dt.Load(dr)
        ds.Tables.Add(dt)
      Loop While Not dr.IsClosed
      dr.Close()
    Catch ex As Exception
      SoftLog("SQLDataSet：" & ex.Message & vbCrLf & CmdText, "SQL错误_" & Now.ToString("yyyyMMdd") & ".log")
    End Try
    Return ds
  End Function
End Module