Imports SystemImports System.DataImports System.Data.SqlClientImports System.DiagnosticsNamespace DAL Public Class DataAccess Implements IDisposable Private strConnection As String = ConfigurationSettings.AppSettings("conString") Private command As SqlCommand Public Sub New(ByVal sprocName As String, ByVal commandType As CommandType, Optional ByVal parameters As SqlParameter() = Nothing) command = New SqlCommand(sprocName, New SqlConnection(strConnection)) command.CommandType = commandType If Not IsNothing(parameters) Then Dim parameter As SqlParameter For Each parameter In parameters command.Parameters.Add(parameter) Next End If command.Connection.Open() End Sub Public Sub New(ByVal sprocName As String, ByVal parameters As ArrayList) command = New SqlCommand(sprocName, New SqlConnection(strConnection)) command.CommandType = CommandType.StoredProcedure Dim parameter As SqlParameter For Each parameter In parameters command.Parameters.Add(parameter) Next command.Connection.Open() End Sub Public Function runNonQuery_Int() As Int32 If IsDBNull(command) Then Throw New ObjectDisposedException("Object is disposed") End If Dim retVal As Integer = 0 retVal = command.ExecuteNonQuery() Return retVal End Function Public Function runUpadateQuery_Int(Optional ByVal strRetParamName As String = "") As Int32 If IsDBNull(command) Then Throw New ObjectDisposedException("Object is disposed") End If Dim i As Integer i = command.ExecuteNonQuery() If i = 0 Then Return 0 Else If strRetParamName = "" Then Return i Else Return command.Parameters("@" & strRetParamName).Value End If End If End Function Public Function runScalar_Obj() As Object If IsDBNull(command) Then Throw New ObjectDisposedException("Object is disposed") End If Return (command.ExecuteScalar()) End Function Public Function runReader_Rd() As SqlDataReader Dim rdr As SqlDataReader If IsDBNull(command) Then Throw New ObjectDisposedException("Object is disposed") End If rdr = command.ExecuteReader() Return rdr End Function Public Function runAdapter_Dt() As DataTable 'hh If IsDBNull(command) Then Throw New ObjectDisposedException("Object is disposed") End If Dim dataAdapter As SqlDataAdapter Dim dataTable As DataTable dataAdapter = New SqlDataAdapter dataTable = New DataTable dataAdapter.SelectCommand = command dataAdapter.Fill(dataTable) dataAdapter.Dispose() Return dataTable End Function Public Sub Dispose() Implements System.IDisposable.Dispose If Not IsDBNull(command) Then Dim tmpsqlcon As SqlConnection tmpsqlcon = command.Connection Debug.Assert(Not IsDBNull(tmpsqlcon)) command.Dispose() tmpsqlcon.Dispose() End If End Sub End ClassEnd Namespace
