We help IT Professionals succeed at work.
Get Started

Help with threading database routine?

kevp75
kevp75 asked
on
378 Views
Last Modified: 2012-05-10
Can someone help me out here.  I am attempting to implement some threading within a db class, yet I am not getting anything returned.

What can I do to get my returnerd properties back from the class?

Code:
Partial Class test
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.Write("<h1>Threading</h1>")

        Test()
        'Test2()

    End Sub

    Public Sub Test()
        Dim objDb As New TurboKits.DataWork
        With objDb
            .ConnectionString = Common.ConnString
            .CommandType = 1
            .Query = "Select * From TblCustomerRideCategories"
            .ParamNames = New String() {}
            .ParamValues = New String() {}
            .ExecuteDR()
            HttpContext.Current.Response.Write(.TotalRecords)
            'If .TotalRecords > 0 Then
            '    For i As Long = 0 To .TotalRecords - 1
            '        HttpContext.Current.Response.Write(.ReturnValues(1, i)(1) & "<br />")
            '    Next
            'Else
            '    HttpContext.Current.Response.Write("No records returned")
            'End If
        End With
        objDb = Nothing
    End Sub

    Public Sub Test2()
        Dim objDb As New TurboKits.DataWork
        With objDb
            .ConnectionString = Common.ConnString
            .CommandType = 1
            .Query = "Select * From TblParts"
            .ParamNames = New String() {""}
            .ParamValues = New String() {""}
            .ExecuteDR()
            If .TotalRecords > 0 Then
                For i As Long = 0 To .TotalRecords - 1
                    Response.Write(.ReturnValues(4, i)(1) & "<br />")
                Next
            End If
        End With
    End Sub


End Class

Open in new window

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.Serialization
Imports System.Web.Caching
Imports System.Text
Imports System.IO
Imports System.Threading
Imports System.Web

Namespace TurboKits

    Public Class DataWork
#Region "Properties"

#Region "Public Properties"

#Region "Set"

        ''' <summary>
        ''' Sets the connection string
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ConnectionString() As String
            Set(ByVal value As String)
                _ConnString = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the command type: 1 = Inline SQL, 4 = Stored Procedure
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property CommandType() As Integer
            Set(ByVal value As Integer)
                _CmdType = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the Inline SQL or Stored Procedure to use
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property Query() As String
            Set(ByVal value As String)
                _Qry = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the parameter values for the queries performed
        ''' </summary>
        ''' <value></value>
        ''' <remarks>New DataType() {}</remarks>
        Public WriteOnly Property ParamValues() As Object
            Set(ByVal value As Object)
                _ParamValues = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the paramater names for the queries performed
        ''' </summary>
        ''' <value></value>
        ''' <remarks>New DataType() {}</remarks>
        Public WriteOnly Property ParamNames() As Object
            Set(ByVal value As Object)
                _ParamNames = value
            End Set
        End Property

        ''' <summary>
        ''' Does the Stored Procedure return a value?
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property HasReturn() As Boolean
            Set(ByVal value As Boolean)
                _HasReturn = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the return parameter name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ReturnParamName() As String
            Set(ByVal value As String)
                _RetParamName = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the return data type
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ReturnDataType() As Object
            Set(ByVal value As Object)
                _RetDataType = value
            End Set
        End Property

        ''' <summary>
        ''' Should it be paged?
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ShouldPage() As Boolean
            Set(ByVal value As Boolean)
                _ShouldPage = value
            End Set
        End Property

        ''' <summary>
        ''' Sets how many records per page
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property PageSize() As Integer
            Set(ByVal value As Integer)
                _PageSize = value
            End Set
        End Property

        ''' <summary>
        ''' Sets the current page we are on
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property CurrentPage() As Long
            Set(ByVal value As Long)
                _CurrPage = value
            End Set
        End Property

#End Region

        ''' <summary>
        ''' Gets the recordset returned as a 3d Array
        ''' ReturnValues(Column, Row)(0 = Value, 1 = ColumnName)
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Property ReturnValues() As Object
            Get
                Return _RetVals
            End Get
            Set(ByVal value As Object)
                _RetVals = value
            End Set
        End Property

#Region "Get"

        ''' <summary>
        ''' Gets the total number of fields pulled
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property FieldCount() As Long
            Get
                Return _FieldCount
            End Get
        End Property

        ''' <summary>
        ''' Gets the total number of records returned
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property TotalRecords() As Long
            Get
                Return _TotRecords
            End Get
        End Property

        ''' <summary>
        ''' Gets the number of rows affected by the query
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property AffectedRows() As Long
            Get
                Return _AffRows
            End Get
        End Property

        ''' <summary>
        ''' Gets the return value from a stored procedure
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ReturnValue() As Object
            Get
                Return _RetVal
            End Get
        End Property

        ''' <summary>
        ''' Gets the returned XML
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property ReturnXml() As XDocument
            Get
                Return _RetXml
            End Get
        End Property

        ''' <summary>
        ''' Set the Root Node Name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property RootNode() As String
            Set(ByVal value As String)
                _RootNode = value
            End Set
        End Property

        ''' <summary>
        ''' Set the parent node name
        ''' </summary>
        ''' <value></value>
        ''' <remarks></remarks>
        Public WriteOnly Property ParentNode() As String
            Set(ByVal value As String)
                _ParentNode = value
            End Set
        End Property

        ''' <summary>
        ''' Gets the total number of pages returned
        ''' </summary>
        ''' <value></value>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public ReadOnly Property TotalPages() As Long
            Get
                Return _TotPages
            End Get
        End Property

#End Region

#End Region

        Private i As Integer
        Private _ConnString As String, _CmdType As Integer, _Qry As String
        Private _ParamValues As Object, _ParamNames As Object, _RetVals As Object
        Private _HasReturn As Boolean, _RetParamName As String, _RetDataType As Object
        Private _ShouldPage As Boolean, _PageSize As Integer, _FieldCount As Long
        Private _TotRecords As Long
        Private _AffRows As Long, _RetVal As Object
        Private _RetXml As XDocument, _CurrPage As Integer, _TotPages As Long, _RootNode As String, _ParentNode As String

#End Region

        ''' <summary>
        ''' Execute a datareader object against the properties passed
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub ExecuteDataReader()
            Try
                Using objConn As New SqlConnection(_ConnString)
                    Using objCmd As New SqlCommand(_Qry, objConn)
                        Using objDA As New SqlDataAdapter()
                            Using objDS As New DataSet()
                                If IsArray(_ParamValues) And IsArray(_ParamNames) Then
                                    If UBound(_ParamValues) = UBound(_ParamNames) Then
                                        'Prepare our parameters
                                        For Me.i = 0 To UBound(_ParamValues)
                                            objCmd.Parameters.AddWithValue(_ParamNames(i), _ParamValues(i))
                                        Next
                                        Erase _ParamValues : Erase _ParamNames
                                        If _ShouldPage Then
                                            objCmd.Parameters.AddWithValue("@CurrentPage", _CurrPage)
                                            objCmd.Parameters.AddWithValue("@PageSize", _PageSize)
                                            _TotPages = Math.Ceiling((GetTotalRecords() / _PageSize))
                                        End If
                                        objCmd.CommandType = _CmdType
                                        objCmd.Connection.Open()
                                        objDA.SelectCommand = objCmd
                                        objDA.Fill(objDS, "Tbl")
                                        Dim DT As DataTable = objDS.Tables("Tbl")
                                        _FieldCount = DT.Columns.Count
                                        _TotRecords = DT.Rows.Count
                                        Dim c As Integer = 0, r As Integer = 0
                                        Dim tmpRetVals(FieldCount - 1, TotalRecords - 1) As Array
                                        For c = 0 To FieldCount - 1
                                            For r = 0 To TotalRecords - 1
                                                tmpRetVals(c, r) = New String() {DT.Columns.Item(c).ColumnName, DT.Rows(r)(c).ToString()}
                                            Next
                                        Next
                                        DT = Nothing
                                        objCmd.Parameters.Clear()
                                        _RetVals = tmpRetVals
                                        Erase tmpRetVals
                                    End If
                                End If
                            End Using
                        End Using
                    End Using
                End Using
            Catch SqlEx As SqlException
                Dim objErr As New TurboKits.ErrorManager
                objErr.Location = "Database - ExecuteDataReader"
                objErr.Notes = "Query: " & _Qry
                objErr.Stack = SqlEx.StackTrace()
                objErr.Except = SqlEx
                objErr.ParseError()
                objErr = Nothing
            Catch ex As Exception
                Dim objErr As New TurboKits.ErrorManager
                objErr.Location = "Database - ExecuteDataReader"
                objErr.Notes = "Query: " & _Qry
                objErr.Stack = ex.StackTrace()
                objErr.Except = ex
                objErr.ParseError()
                objErr = Nothing
            End Try
        End Sub

#Region "Extras"

        ''' <summary>
        ''' Get the total number of records
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Private Function GetTotalRecords() As Long
            Dim objDB As New Database
            With objDB
                .ConnectionString = Common.ConnString
                .Query = _Qry
                .ParamNames = _ParamNames
                .ParamValues = _ParamValues
                .ExecuteDataReader()
                If .TotalRecords > 0 Then
                    Return .TotalRecords
                Else
                    Return 0
                End If
            End With
            objDB = Nothing
        End Function

#End Region

#Region "Threading"

        Public Sub ExecuteDR()
            Dim objT As New Thread(New ParameterizedThreadStart(AddressOf ExecuteDataReader))
            With objT
                .Name = "DBReader"
                .Priority = ThreadPriority.Normal
                .SetApartmentState(ApartmentState.MTA)
                .Start()
            End With
        End Sub

#End Region

    End Class

End Namespace

Open in new window

Comment
Watch Question
This problem has been solved!
Unlock 4 Answers and 11 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE