1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194
| Imports System.Configuration
Imports System.Reflection
Imports ANTI_MALI_DTO
Imports System.Data.SqlClient
Public Class Base
Private Shared Function CreateConnection() As SqlConnection
Dim cnx As New SqlConnection
'Open the configuration file using the dll location
Dim myDllConfig As Configuration = ConfigurationManager.OpenExeConfiguration(Assembly.GetExecutingAssembly.Location)
' Get the appSettings section
Dim myDllConfigAppSettings As AppSettingsSection = DirectCast(myDllConfig.GetSection("appSettings"), AppSettingsSection)
' return the desired field
'myDllConfigAppSettings.Settings("MyConnectionString").Value
cnx.ConnectionString = myDllConfigAppSettings.Settings("MyConnectionString").Value
Return cnx
End Function
Protected Shared Function CreateSpCommand(spName As String) As SqlCommand
Dim cmd As New SqlCommand
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = spName
cmd.Connection = CreateConnection()
Return cmd
End Function
Protected Shared Function CreateParameter(name As String, value As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Int
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Short) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.SmallInt
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Byte) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.TinyInt
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Byte(), size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.VarBinary
param.Size = size
param.Value = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As String, size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.VarChar
param.Size = size
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As DateTime) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.DateTime2
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Boolean) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Bit
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateParameter(name As String, value As Decimal) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Input
param.ParameterName = name
param.SqlDbType = SqlDbType.Decimal
param.SqlValue = value
Return param
End Function
Protected Shared Function CreateOutputParameter(name As String, type As SqlDbType) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Output
param.SqlDbType = type
param.ParameterName = name
Return param
End Function
Protected Shared Function CreateOutputParameter(name As String, type As SqlDbType, size As Integer) As SqlParameter
Dim param As New SqlParameter
param.Direction = ParameterDirection.Output
param.ParameterName = name
param.SqlDbType = type
param.Size = size
Return param
End Function
Protected Shared Function GetSingleDTO(Of T As DTOBase)(ByRef command As SqlCommand) As T
Dim dto As T = Nothing
Try
command.Connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
reader.Read()
Dim parser As Parser = DTOParserFactory.GetParser(GetType(T))
parser.PopulateOrdinals(reader)
dto = DirectCast(parser.PopulateDTO(reader), T)
reader.Close()
'Else
' ' Whever there's no data, we return null.
' dto = Nothing
End If
Catch e As Exception
Throw New Exception("Error populating data", e)
Finally
command.Connection.Close()
command.Connection.Dispose()
End Try
' return the DTO, it's either populated with data or null.
Return dto
End Function
Protected Shared Function GetDTOList(Of T As DTOBase)(ByRef command As SqlCommand) As List(Of T)
Dim dtoList As New List(Of T)()
Try
command.Connection.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
If reader.HasRows Then
' Get a parser for this DTO type and populate
' the ordinals.
Dim parser As Parser = DTOParserFactory.GetParser(GetType(T))
parser.PopulateOrdinals(reader)
' Use the parser to build our list of DTOs.
While reader.Read()
Dim dto As T = Nothing
dto = DirectCast(parser.PopulateDTO(reader), T)
dtoList.Add(dto)
End While
reader.Close()
Else
' Whenever there's no data, we return null.
dtoList = New List(Of T)
End If
Catch e As Exception
Throw e
Finally
command.Connection.Close()
command.Connection.Dispose()
End Try
Return dtoList
End Function
Protected Shared Sub ExecuteNonQuery(cmd As SqlCommand)
cmd.Connection.Open()
cmd.ExecuteNonQuery()
cmd.Connection.Close()
End Sub
Protected Shared Sub ExecuteQuery(cmd As SqlCommand)
cmd.Connection.Open()
cmd.ExecuteReader()
cmd.Connection.Close()
End Sub
End Class |
Partager