Imports System.Data
Imports System.Diagnostics
Imports Microsoft.Data.SqlClient
Public Class sqlData
Private DBCon As New SqlConnection(ConString)
Private DBCmd As SqlCommand
' DB DATA
Public DBDA As SqlDataAdapter
Public DBDT As DataTable
' QUERY PARAMETERS
Public Params As New List(Of SqlParameter)
' QUERY STATISTICS
Public RecordCount As Integer
Public Exception As String
Public Sub New()
End Sub
Public Sub Query(theQuery As String)
RecordCount = 0
Exception = ""
Try
DBCon.Open()
' CREATE DB COMMAND
DBCmd = New SqlCommand(theQuery, DBCon)
' LOAD PARAMS INTO DB COMMAND
Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
' CLEAR PARAM LIST
Params.Clear()
' EXECUTE COMMAND & FILL DATASET
DBDT = New DataTable
DBDA = New SqlDataAdapter(DBCmd)
RecordCount = DBDA.Fill(DBDT)
Catch ex As Exception
' CAPTURE ERROR
Exception = "ExecQuery Error: " & vbCrLf & ex.Message
Finally
' CLOSE CONNECTION
If DBCon.State = ConnectionState.Open Then DBCon.Close()
End Try
End Sub
Public Sub AddParam(theName As String, Value As Object)
Dim NewParam As New SqlParameter("@" & theName, Value)
Params.Add(NewParam)
End Sub
Public Function HasException(Optional Report As Boolean = False) As Boolean
If String.IsNullOrEmpty(Exception) Then Return False
If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
Return True
End Function
Public Function FieldsFromParams(Optional asParamName As Boolean = False) As String
Dim theNames As String
theNames = ""
For Each param In Params
If theNames = "" Then
If asParamName = True Then
theNames = param.ParameterName
Else
theNames = Replace(param.ParameterName, "@", "")
End If
Else
If asParamName = True Then
theNames = theNames & "," & param.ParameterName
Else
theNames = theNames & "," & Replace(param.ParameterName, "@", "")
End If
End If
Next
FieldsFromParams = theNames
End Function
Public Sub InsertRow(theTable As String)
Query("INSERT INTO " & theTable & "(" & FieldsFromParams() & ") VALUES(" & FieldsFromParams(True) & ")")
End Sub
End Class