Avatar of kevp75
kevp75Flag for United States of America asked on

Help with threading database routine?

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

Visual Basic.NET

Avatar of undefined
Last Comment
kevp75

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
AngryBinary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kevp75

german what?  It would be sort of pointless to do that wouldn't it?  Isn't the whole point of threading to make the page faster?

sorry new to the whole threading thing...

What about delegates?   Is that the same as threading?
ASKER
kevp75

Nah, I guess delegates are not the same thing.

I tried using it, but there was no threading results.
SOLUTION
AngryBinary

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kevp75

well...the thing is that I make multiple calls using this class per page, so I figured that threading the calls would do the trick I was after.

Another situation I would this would be to fire off multiple update/insert/delete storedprocedures at the same time  For which, I have another routine in that class strictly for nonreturning data.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
kevp75

so basically...the question really is, how can I fire off this twice at the same time...
ASKER
kevp75

p.s.  officially I am trying to ustilize threading in a class assembly, that is used in an asp.net web site ;)
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
kevp75

Agreed.  (you've got the points and the grade ;-))  But can you explain what I lay out below?  I can;t  LOL

I did some testing, and hopefully this will come in handy for someone else as well.  And this would be a great discussion...

My test scenario consists of SQL 2008, .NET 3.5, IIS 6 on the same machine.  (P4 HT, 2GB RAM, 10kRPM 150G Drive)

I will post the code I used below...but here are the basics of it, and my findings.

I execute a stored procedure against a test table in my db that simply does a single field insert.  I setup a test page with System.Diagnostics.Stopwatch to time the execution of each scenario.

Scenario 1 Uses threading
Scenario 2 Uses delegates
Scenario 3 Uses nothing but the code :)

I use 2 loops in each procedure as you will see below to produce 1,000 records from each. (and to create multiple threads in Scenario 1)

What I found was Scenario 2 performed better than Scenario 1, and Scenario 2 performed slightly better than Scenario 3.

Results in the screen shot, code to follow.

 Screenshot
Part of my db class (ExecuteNDQuery does the actual work, and simply runs stored procedures):

        Public Sub ExeNDQueryThreaded(ByVal intThr As Long)
            Dim objT As New Threading.Thread(AddressOf ExecuteNDQuery)
            With objT
                .Priority = Threading.ThreadPriority.Normal
                .SetApartmentState(Threading.ApartmentState.MTA)
                .Name = "DBThread_" & intThr
                If Not (.ThreadState = Threading.ThreadState.Running) Then
                    .Start()
                End If
            End With
            objT = Nothing
        End Sub

        Private Delegate Sub MyDel(ByVal obj As Object)
        Public Sub ExeNDQueryDelegate()
            Dim objDel As New MyDel(AddressOf ExecuteNDQuery)
            With objDel
                .Invoke(Nothing)
            End With
            objDel = Nothing
        End Sub

Open in new window

test.aspx.vb


Partial Class test
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim tmpXml = <table width="100%" cellpadding="0" cellspacing="0" style="border:1px solid #000;">
                         <tr>
                             <td width="33%"><h2>Threaded</h2></td>
                             <td width="33%"><h2>Delegated</h2></td>
                             <td width="33%"><h2>Non-Threaded</h2></td>
                         </tr>
                         <tr>
                             <td><%= Test1() %></td>
                             <td><%= Test2() %></td>
                             <td><%= Test3() %></td>
                         </tr>
                     </table>
        Response.Write(HttpUtility.HtmlDecode(tmpXml.ToString()))
    End Sub

    Private Function Test1() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x As Long = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 1." & x & ": " & i}
                    .ExeNDQueryThreaded(x)
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function

    Private Function Test2() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 2." & x & ": " & i}
                    .ExeNDQueryDelegate()
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function


    Private Function Test3() As String
        Dim tmpString As New StringBuilder
        Dim sw As New System.Diagnostics.Stopwatch
        sw.Start()
        For x = 1 To 10
            For i As Long = 1 To 100
                Dim objDb As New ZipCM.Database
                With objDb
                    .ConnectionString = Common.ConnString
                    .CommandType = 4
                    .Query = "TestProc"
                    .ParamNames = New String() {"@Str"}
                    .ParamValues = New String() {"Test 3." & x & ": " & i}
                    .ExecuteNDQuery()
                End With
                objDb = Nothing
            Next
        Next
        sw.Stop()
        tmpString.Append("<strong>Ran:</strong> " & sw.Elapsed.ToString())
        Return tmpString.ToString()
        tmpString.Length = 0 : tmpString = Nothing
    End Function


End Class

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
kevp75

You have been a saint, and I appreciate the descriptive responses.  It gave me some serious insight to what I am trying to achieve here.   Actually, it made me realize that I am going about what I am trying to do the wrong way, however it made me realize what the right way would be, so I am now on the right track.

Thanks again!