I've recently been asked to make some emergency changes to a legacy application written in VB.Net.  I come from a C# background so am not overly familiar with this language, though there are enough similarities for me to get by.

The current code has no separation of concerns (e.g. DAL code is written in the codebehind of each page rather than separated out).  To make it usable without introducing too much risk I'm avoiding libraries but extracting the DAL code to separate classes.  Since my DB code's going to be used from a lot of places in the app, I wanted to check that this all makes sense / is reasonably efficient / that I haven't made some schoolboy error in creating this.


    Imports Microsoft.VisualBasic
    Imports System.Data.SqlClient
    Imports System.Collections.Generic


    Public Class DB

        Private _connectionString As String

        Public Sub New(connectionString As String)
            _connectionString = connectionString
        End Sub

        Public Sub ExecuteNonQuery(cmdTxt As String, params As Dictionary(Of String, Object))
            Using cmd As SqlCommand = BuildCommand(cmdTxt, params)
                cmd.ExecuteNonQuery()
            End Using
        End Sub

        Public Function ExecuteReader(cmdTxt As String, params As Dictionary(Of String, Object)) As SqlDataReader
            Using cmd As SqlCommand = BuildCommand(cmdTxt, params)
                Return cmd.ExecuteReader()
            End Using
        End Function

        Public Function ExecuteScalar(cmdTxt As String, params As Dictionary(Of String, Object)) As Object
            Using cmd As SqlCommand = BuildCommand(cmdTxt, params)
                Return cmd.ExecuteScalar()
            End Using
        End Function

        Private Function BuildCommand(cmdTxt As String, params As Dictionary(Of String, Object)) As SqlCommand
            Using con As New SqlConnection(_connectionString)
                Using cmd As SqlCommand = con.CreateCommand()
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandText = cmdTxt
                    AddParameters(cmd, params)
                    con.Open() 'working on the assumption this command will be run as soon as it's retuned; so this open is left as late as possible but here to avoid duplicate code
                    Return cmd
                End Using
            End Using
        End Function

        Private Sub AddParameters(ByRef cmd As SqlCommand, params As Dictionary(Of String, Object))
            If Not params Is Nothing Then
                For Each kvp As KeyValuePair(Of String, Object) In params
                    cmd.Parameters.AddWithValue(kvp.Key, kvp.Value)
                Next
            End If
        End Sub

    End Class