This simple class helps in running commands in a MS SQL database. It requires a connection string, and RunCommand and RunCommandAsync require a string parameter to use as the command.
What's my connection string?
Code:
''' <summary>
''' Allows easy access to a database.
''' </summary>
Public Class DatabaseManager
Private SqlConnection As SqlClient.SqlConnection
Private SqlCommand As SqlClient.SqlCommand
Private SqlReader As SqlClient.SqlDataReader
''' <summary>
''' Creates a new connection to a database using the provided connection string.
''' </summary>
Public Sub New(ConnectionString As String)
SqlConnection = New SqlClient.SqlConnection(ConnectionString)
End Sub
''' <summary>
''' Synchronously runs an SQL command.
''' </summary>
Public Function RunCommand(CommandString As String) As List(Of List(Of Object))
SqlConnection.Open()
SqlCommand = New SqlClient.SqlCommand(CommandString, SqlConnection)
SqlReader = SqlCommand.ExecuteReader()
Dim Returns As New List(Of List(Of Object))
While SqlReader.Read()
Returns.Add(New List(Of Object))
For i As Integer = 0 To SqlReader.FieldCount - 1 Step 1
Returns(Returns.Count - 1).Add(SqlReader.Item(i))
Next
End While
SqlConnection.Close()
Return Returns
End Function
''' <summary>
''' Asynchronously runs an SQL command.
''' </summary>
Public Async Function RunCommandAsync(CommandString As String) As Task(Of List(Of List(Of Object)))
Await SqlConnection.OpenAsync()
SqlCommand = New SqlClient.SqlCommand(CommandString, SqlConnection)
SqlReader = Await SqlCommand.ExecuteReaderAsync()
Dim Returns As New List(Of List(Of Object))
While SqlReader.Read()
Returns.Add(New List(Of Object))
For i As Integer = 0 To SqlReader.FieldCount - 1 Step 1
Returns(Returns.Count - 1).Add(SqlReader.Item(i))
Next
End While
SqlConnection.Close()
Return Returns
End Function
End Class
Usage:
Code:
Private Sub SomeSqlFunction()
DBM = New DatabaseManager("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;")
DBM.RunCommand("CREATE TABLE People (LName varchar(255), FName varchar(255), MInitial varchar(255));")
DBM.RunCommand("INSERT INTO People (LName, FName, MInitial) VALUES ('Johnson', 'Bill', 'H');")
DBM.RunCommand("INSERT INTO People (LName, FName) VALUES ('Miller', 'Alex');")
Dim Result As List(Of List(Of Object)) = DBM.RunCommand("SELECT * FROM People")
' Displaying the contents.
Dim MessageString As String = ""
For Each R In Result
For Each C In R
MessageString &= $"{C.ToString()} "
Next
MessageString &= Environment.NewLine
Next
MessageBox.Show(MessageString)
End Sub
Best of luck,
~Nic