VB.NET - Simple Database Connection

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

Any data access code that doesn't provide the facility to insert values as parameters is fatally flawed. It means that you need to mess around with date formatting and escaping single quotes and it also leaves you open to SQL injection unless you write extra, possibly error-prone sanitation code.