1  
   2  Imports System.Data.SqlClient
   3  Imports System.Data
   4  Imports System.Configuration
   5  
   6  Public Class DataAccess
   7  
   8      ''#If DEBUG Then
   9      ''    Private Shared _strConnectionString As String = Configuration.ConfigurationSettings.AppSettings.Get("ConnStringDebug") 'My.Settings.Item("ConnStringDebug")
  10      ''         '"Server =.;Initial Catalog=IPDB;Integrated security = true"
  11      ''#Else
  12      ''    Private Shared _strConnectionString As String = My.Settings.Item("ConnString") 'Configuration.ConfigurationSettings.AppSettings.Get("ConnString")
  13      ''#End If
  14  
  15  #If DEBUG Then
  16      Private Shared _strConnectionString As String = ConfigurationManager.AppSettings("ConnStringDebug")  '"Server =.;Initial Catalog=IPDB;Integrated security = true"
  17  #Else
  18      Private Shared _strConnectionString As String = ConfigurationManager.AppSettings("ConnString")
  19  #End If
  20  
  21      Public Property ConnectionString() As String
  22          Get
  23              ConnectionString = _strConnectionString
  24          End Get
  25          Set(ByVal Value As String)
  26              _strConnectionString = Value
  27          End Set
  28      End Property
  29  
  30      Public Function TestDBConnection() As Boolean
  31          Dim objConnection As New SqlConnection(_strConnectionString)
  32          Try
  33              objConnection.Open()
  34              objConnection.Close()
  35              Return True
  36          Catch ex As SqlException
  37              Throw ex
  38              Return False
  39          Catch ex As Exception
  40              Throw ex
  41              Return False
  42          Finally
  43              objConnection.Dispose()
  44          End Try
  45      End Function
  46  
  47      Public Function QueryIn_DataSet(ByVal sSPName As String, _
  48                                   ByVal arrInputValues() As String) As DataSet
  49  
  50          Dim objConnection As SqlConnection = Nothing
  51          Dim objAdapter As SqlDataAdapter
  52          Dim objDataSet As New DataSet
  53          Dim objCommand As New SqlCommand
  54          Dim iCount As Integer
  55          Dim strConnectionString As String
  56  
  57          Try
  58              strConnectionString = _strConnectionString
  59              objAdapter = New SqlDataAdapter
  60              objConnection = New SqlConnection(strConnectionString)
  61              objConnection.Open()
  62              objCommand.CommandTimeout = 900
  63              objAdapter.SelectCommand = objCommand
  64              objAdapter.SelectCommand.Connection = objConnection
  65              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
  66              objAdapter.SelectCommand.CommandText = sSPName
  67  
  68              Try
  69                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
  70                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
  71                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
  72                          ParameterDirection.Input) Or _
  73                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
  74                          ParameterDirection.InputOutput) Then
  75  
  76                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
  77                      End If
  78                      If iCount - 1 = UBound(arrInputValues) Then Exit For
  79                  Next
  80  
  81                  objAdapter.Fill(objDataSet)
  82                  objConnection.Close()
  83                  Return objDataSet
  84  
  85              Catch ex As SqlException
  86                  objConnection.Close()
  87                  Throw ex
  88              End Try
  89          Catch ex As Exception
  90              objConnection.Close()
  91              Throw ex
  92          Finally
  93              objConnection.Dispose()
  94          End Try
  95  
  96      End Function
  97  
  98      Public Function QueryIn_DataTable(ByVal sSPName As String, _
  99              ByVal arrInputValues() As String) As DataTable
 100  
 101          Dim objConnection As SqlConnection
 102          Dim objAdapter As SqlDataAdapter
 103          Dim objCommand As New SqlCommand
 104          Dim objDataTable As New DataTable
 105          Dim iCount As Integer
 106          Dim strConnectionString As String
 107  
 108          Try
 109              strConnectionString = _strConnectionString
 110              objAdapter = New SqlDataAdapter
 111              objConnection = New SqlConnection(strConnectionString)
 112              objConnection.Open()
 113              objCommand.CommandTimeout = 900
 114              objAdapter.SelectCommand = objCommand
 115              objAdapter.SelectCommand.Connection = objConnection
 116              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 117              objAdapter.SelectCommand.CommandText = sSPName
 118  
 119              Try
 120                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
 121                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
 122                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 123                          ParameterDirection.Input) Or _
 124                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 125                          ParameterDirection.InputOutput) Then
 126  
 127                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
 128                      End If
 129                      If iCount - 1 = UBound(arrInputValues) Then Exit For
 130                  Next
 131  
 132                  objAdapter.Fill(objDataTable)
 133                  objConnection.Close()
 134                  Return objDataTable
 135  
 136              Catch ex As SqlException
 137                  objConnection.Close()
 138                  Throw ex
 139              End Try
 140          Catch ex As Exception
 141              objConnection.Close()
 142              Throw ex
 143          Finally
 144              objConnection.Dispose()
 145          End Try
 146  
 147      End Function
 148  
 149      Public Function QueryIn_DataView(ByVal sSPName As String, _
 150              ByVal arrInputValues() As String) As DataView
 151  
 152          Dim objConnection As SqlConnection
 153          Dim objAdapter As SqlDataAdapter
 154          Dim objCommand As New SqlCommand
 155          Dim objDataSet As New DataSet
 156          Dim iCount As Integer
 157          Dim strConnectionString As String
 158  
 159          Try
 160              strConnectionString = _strConnectionString
 161              objAdapter = New SqlDataAdapter
 162              objConnection = New SqlConnection(strConnectionString)
 163              objConnection.Open()
 164              objCommand.CommandTimeout = 900
 165              objAdapter.SelectCommand = objCommand
 166              objAdapter.SelectCommand.Connection = objConnection
 167              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 168              objAdapter.SelectCommand.CommandText = sSPName
 169  
 170              Try
 171                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
 172                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
 173                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 174                          ParameterDirection.Input) Or _
 175                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 176                          ParameterDirection.InputOutput) Then
 177  
 178                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
 179                      End If
 180                      If iCount - 1 = UBound(arrInputValues) Then Exit For
 181                  Next
 182  
 183                  objAdapter.Fill(objDataSet, "Default")
 184                  objConnection.Close()
 185                  Return objDataSet.Tables("Default").DefaultView
 186  
 187              Catch ex As SqlException
 188                  objConnection.Close()
 189                  Throw ex
 190              End Try
 191          Catch ex As Exception
 192              objConnection.Close()
 193              Throw ex
 194          Finally
 195              objConnection.Dispose()
 196          End Try
 197  
 198      End Function
 199  
 200      Public Function QueryIn_SqlDataReader(ByVal sSPName As String, _
 201            ByVal arrInputValues() As String) As SqlClient.SqlDataReader
 202  
 203          Dim objConnection As SqlConnection = Nothing
 204          Dim objAdapter As SqlDataAdapter
 205          Dim objCommand As New SqlCommand
 206          Dim objDataReader As SqlClient.SqlDataReader
 207          'Dim strScalar As String
 208          Dim iCount As Integer
 209          Dim strConnectionString As String
 210  
 211          Try
 212              strConnectionString = _strConnectionString
 213              objAdapter = New SqlDataAdapter
 214              objConnection = New SqlConnection(strConnectionString)
 215              objConnection.Open()
 216              objCommand.CommandTimeout = 900
 217              objAdapter.SelectCommand = objCommand
 218              objAdapter.SelectCommand.Connection = objConnection
 219              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 220              objAdapter.SelectCommand.CommandText = sSPName
 221  
 222              Try
 223                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
 224                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
 225                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 226                          ParameterDirection.Input) Or _
 227                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 228                          ParameterDirection.InputOutput) Then
 229  
 230                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
 231                      End If
 232                      If iCount - 1 = UBound(arrInputValues) Then Exit For
 233                  Next
 234  
 235                  objDataReader = objAdapter.SelectCommand.ExecuteReader
 236                  objConnection.Close()
 237                  Return objDataReader
 238  
 239              Catch ex As SqlException
 240                  objConnection.Close()
 241                  Throw ex
 242              End Try
 243          Catch ex As Exception
 244              objConnection.Close()
 245              Throw ex
 246          Finally
 247              objConnection.Dispose()
 248          End Try
 249  
 250      End Function
 251  
 252      Public Function NonQuery_ExecuteScalar(ByVal sSPName As String, _
 253              ByVal arrInputValues() As String) As String
 254  
 255          Dim objConnection As SqlConnection = Nothing
 256          Dim objAdapter As SqlDataAdapter
 257          Dim objCommand As New SqlCommand
 258          Dim strScalar As String
 259          Dim iCount As Integer
 260          Dim strConnectionString As String
 261  
 262          Try
 263              strConnectionString = _strConnectionString
 264              objAdapter = New SqlDataAdapter
 265              objConnection = New SqlConnection(strConnectionString)
 266              objConnection.Open()
 267              objCommand.CommandTimeout = 900
 268              objAdapter.SelectCommand = objCommand
 269              objAdapter.SelectCommand.Connection = objConnection
 270              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 271              objAdapter.SelectCommand.CommandText = sSPName
 272  
 273              Try
 274                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
 275                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
 276                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 277                          ParameterDirection.Input) Or _
 278                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 279                          ParameterDirection.InputOutput) Then
 280  
 281                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
 282                      End If
 283                      If iCount - 1 = UBound(arrInputValues) Then Exit For
 284                  Next
 285  
 286                  strScalar = objAdapter.SelectCommand.ExecuteScalar()
 287                  objConnection.Close()
 288                  Return strScalar
 289  
 290              Catch ex As SqlException
 291                  objConnection.Close()
 292                  Throw ex
 293              End Try
 294          Catch ex As Exception
 295              objConnection.Close()
 296              Throw ex
 297          Finally
 298              objConnection.Dispose()
 299          End Try
 300  
 301      End Function
 302  
 303      Public Function NonQuery_ExecuteNonQuery(ByVal sSPName As String, _
 304             ByVal arrInputValues() As String) As Integer
 305  
 306          Dim objConnection As SqlConnection = Nothing
 307          Dim objAdapter As SqlDataAdapter
 308          Dim objCommand As New SqlCommand
 309          Dim intRowsEffected As String
 310          Dim iCount As Integer
 311          Dim strConnectionString As String
 312  
 313          Try
 314              strConnectionString = _strConnectionString
 315              objAdapter = New SqlDataAdapter
 316              objConnection = New SqlConnection(strConnectionString)
 317              objConnection.Open()
 318              objCommand.CommandTimeout = 900
 319              objAdapter.SelectCommand = objCommand
 320              objAdapter.SelectCommand.Connection = objConnection
 321              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
 322              objAdapter.SelectCommand.CommandText = sSPName
 323  
 324              Try
 325                  SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
 326                  For iCount = 1 To objAdapter.SelectCommand.Parameters.Count - 1
 327                      If (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 328                          ParameterDirection.Input) Or _
 329                          (objAdapter.SelectCommand.Parameters(iCount).Direction = _
 330                          ParameterDirection.InputOutput) Then
 331  
 332                          objAdapter.SelectCommand.Parameters(iCount).Value = IIf(UCase(arrInputValues(iCount - 1).Trim) = "NULL", System.DBNull.Value, arrInputValues(iCount - 1).Trim)
 333                      End If
 334                      If iCount - 1 = UBound(arrInputValues) Then Exit For
 335                  Next
 336  
 337                  intRowsEffected = objAdapter.SelectCommand.ExecuteNonQuery
 338                  objConnection.Close()
 339                  Return intRowsEffected
 340  
 341              Catch ex As SqlException
 342                  objConnection.Close()
 343                  Throw ex
 344              End Try
 345          Catch ex As Exception
 346              objConnection.Close()
 347              Throw ex
 348          Finally
 349              objConnection.Dispose()
 350          End Try
 351  
 352      End Function
 353  End Class
 354