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.

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

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.

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

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.

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, that it is reasonably efficient and that I haven't made some schoolboy error in creating this.

I've recently been asked to make some emergency changes to a legacy application. It was 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 all makes sense / is reasonably efficient / that I haven't made some schoolboy error in creating this. Thanks in advance for any thoughts.

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.