I am new to programming and have written this Data Access Layer
. I am using this DAL
in my projects. Now, I have a feeling that it is pathetic (when I wrote it, I was kind of happy). Do you guys also feel the same for this DAL
. Kindly help me write a better one. There is no Transaction
in my DAL
and I have no idea how to implement it in this DAL
. I am expecting some criticism.
Connections.vb Class File
Option Explicit On
Option Strict On
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Public Class Connection
Public Shared Function GetDbConnection() As SqlConnection
Dim _conString As String = ConfigurationManager.ConnectionStrings("CoachingConnectionString").ConnectionString
Dim _connection As New SqlConnection(_conString)
_connection.Open()
Return _connection
End Function
End Class
DataAccess.vb Class File
Option Explicit On
Option Strict On
Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Public Class DataAccess
Public Overloads Shared Function GetDataTable(ByVal _sql As String, ByVal _parameterNames() As String, ByVal _parameterVals() As String) As DataTable
Dim _connection As SqlConnection = Global.Connection.GetDbConnection()
Dim _command As New SqlDataAdapter(_sql, _connection)
Dim _table As New DataTable
Try
If _parameterNames IsNot Nothing Then
For i = 0 To _parameterNames.Length - 1
_command.SelectCommand.Parameters.AddWithValue(_parameterNames(i), _parameterVals(i))
Next
End If
_command.Fill(_table)
Catch ex As Exception
'MsgBox(ex.Message)
_table = Nothing
Finally
If _connection.State = ConnectionState.Open Then
_connection.Close()
_connection.Dispose()
_command.Dispose()
End If
End Try
Return _table
End Function
Public Overloads Shared Function GetDataTable(ByVal _sql As String) As DataTable
Dim _connection As SqlConnection = Global.Connection.GetDbConnection()
Dim _command As New SqlDataAdapter(_sql, _connection)
Dim _table As New DataTable
Try
_command.Fill(_table)
Catch ex As Exception
'MsgBox(ex.Message)
_table = Nothing
Finally
If _connection.State = ConnectionState.Open Then
_connection.Close()
_connection.Dispose()
_command.Dispose()
End If
End Try
Return _table
End Function
Public Shared Function SelectScalar(ByVal _sql As String, ByVal _parameterNames() As String, ByVal _parameterVals() As String) As String
Dim _returnVal As String
Dim _connection As SqlConnection = Global.Connection.GetDbConnection()
Dim _command As New SqlCommand(_sql, _connection)
Dim _value As String
Try
If _parameterNames IsNot Nothing Then
For i = 0 To _parameterNames.Length - 1
_command.Parameters.AddWithValue(_parameterNames(i), _parameterVals(i))
Next
End If
_value = CStr(_command.ExecuteScalar)
_returnVal = _value
Catch ex As Exception
_returnVal = Nothing
Finally
If _connection.State = ConnectionState.Open Then
_connection.Close()
_connection.Dispose()
_command.Dispose()
End If
End Try
Return _returnVal
End Function
Public Shared Function SelectScalar(ByVal _sql As String) As String
Dim _returnVal As String
Dim _connection As SqlConnection = Global.Connection.GetDbConnection()
Dim _command As New SqlCommand(_sql, _connection)
Dim _value As String
Try
_value = CStr(_command.ExecuteScalar)
_returnVal = _value
Catch ex As Exception
_returnVal = Nothing
Finally
If _connection.State = ConnectionState.Open Then
_connection.Close()
_connection.Dispose()
_command.Dispose()
End If
End Try
Return _returnVal
End Function
Public Shared Function CRUD(ByVal _sql As String, ByVal _parameterNames() As String, ByVal _parameterVals() As String) As Integer
Dim _noOfRowsAffected As Integer
Dim _connection As SqlConnection = Global.Connection.GetDbConnection()
Dim _command As New SqlCommand(_sql, _connection)
Try
If _parameterNames IsNot Nothing Then
For i = 0 To _parameterNames.Length - 1
_command.Parameters.AddWithValue(_parameterNames(i), _parameterVals(i))
Next
End If
_noOfRowsAffected = _command.ExecuteNonQuery()
Catch ex As Exception
'MsgBox(ex.Message)
_noOfRowsAffected = -1
Finally
If _connection.State = ConnectionState.Open Then
_connection.Close()
_connection.Dispose()
_command.Dispose()
End If
End Try
Return _noOfRowsAffected
End Function
End Class
DAL
so that i can learn what are my mistakes. \$\endgroup\$