Results 1 to 2 of 2

Thread: VB.NET - Simple Database Connection

  1. #1

    Thread Starter
    Addicted Member NinjaNic's Avatar
    Join Date
    Dec 2013
    Location
    Earth
    Posts
    230

    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

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,301

    Re: VB.NET - Simple Database Connection

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width