Solved

Slow running process

Posted on 2010-11-15
24
588 Views
Last Modified: 2013-11-26
Can Somebody suggest me how can I get rid of unmanaged code errror .I am converting excel input file to the output text file and   I need to use lots of function with Byval and now I am getting error of Context switch Deadlock and I do not have any idea how do I overcome it.

Error

ContextSwitchDeadlock was detected
Message: The CLR has been unable to transition from COM context 0x554fe8 to COM context 0x555158 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

 

Can someone help me please


--------------------------------------------------------------------------------

p = Mid(LTrim(Convert.ToString(dt.Rows(x).Item(5))), 3, 6)
                ''         Month          Day            Year
                p = Mid(p, 3, 2) & Mid(p, 5, 2) & Mid(p, 1, 2)
                '#01 Start
                '************************************************************************

                MatterCode = GetMatterCode(LTrim(Convert.ToString(dt.Rows(x).Item(7)).PadRight(15, " ")), dtMatterCode)
                CostCode = GetCostCode(LTrim(Convert.ToString(dt.Rows(x).Item(9)).PadRight(7, " ")), dtCostCode)

                p = "1" _
                    & p _
                    & MatterCode _
                    & "   " _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(8)).PadRight(5, " ")) _
                    & CostCode _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(10)).PadRight(7, " ")) _
                    & "       " _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(11)).PadRight(12, " "))

                ' f.WriteLine(p)
                If (MatterCode = Nothing And CostCode = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid MatterCode and Costcode at Row " & x)
                    ' f.WriteLine("COSTCODE OR MATTERCODE NOT FOUND ")
                Else
                    f.WriteLine(p)
                End If



                '1ST ROW OUTPUT: MATTERCODE(SP) TIMEKEEPER(IP) COSTCODE(SP) QUANTITY(IN) AMOUNT(IN) 
                'Row 2: Voucher Information

                ''***********************************************************

                'v = "3"
                InvoiceDate = ""
                '20080509 becomes 090508
                InvoiceDate = Mid(LTrim(Convert.ToString(dt.Rows(x).Item(3))), 3, 6)
                ''         Month          Day            Year
                InvoiceDate = Mid(InvoiceDate, 3, 2) & Mid(InvoiceDate, 5, 2) & Mid(InvoiceDate, 1, 2)

                InvoiceAmount = LTrim(Convert.ToString(dt.Rows(x).Item(4))).PadRight(12, " ")

                VendorId = GetVendorID(LTrim(Convert.ToString(dt.Rows(x).Item(1)).PadRight(8, " ")), dtVendor)
                v = "3" _
                 & VendorId _
                 & LTrim(Convert.ToString(dt.Rows(x).Item(2))).PadRight(16, " ") _
                 & InvoiceDate _
                 & InvoiceAmount
                'f.WriteLine(v)
                If (VendorId = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid VendorID at Row " & x)
                Else
                    f.WriteLine(v)
                End If


                '2ND ROW OUTPUT: VENDORID(SP) INVOICE_DATE(IP) INVOICE_AMOUNT(SP) 

                'Row 3: Cost Card Narrative record
                '***********************************************************
                '
                S = "4" _
                            & "Vendor: " _
                             & LTrim(Convert.ToString(dt.Rows(x).Item(0))).PadRight(29, " ") _
                             & Mid(LTrim(Convert.ToString(dt.Rows(x).Item(6))).PadRight(18, " "), 1, 18) & vbCrLf _
                             & "4" _
                             & Mid(LTrim(Convert.ToString(dt.Rows(x).Item(6))).PadRight(48, " "), 19, 48)

                f.WriteLine(S)

                '*************************************************************************************************************************************************
                'Row 4: GL Account Information

                Glaccount = GetGLNumber(LTrim(Convert.ToString(dt.Rows(x).Item(7)).PadRight(19, " ")), dtGLAccount)
                G = "5" _
                    & Glaccount
                'f.WriteLine(G)

                If (Glaccount = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid GLAcccount at Row " & x)
                    'Return Nothing
                Else
                    f.WriteLine(G)
                End If

            Next
            ''Memory Release of datatables

            dt.Dispose()
            dtMap.Dispose()
            dtVendor.Dispose()
            dtCostCode.Dispose()
            dtGLAccount.Dispose()
            dtMatterCode.Dispose()

            If sErrMsg = String.Empty Then
                Return "File Successfully created at " & OutFileName & " with " & rows & " row(s) with Date=" & MyDate
            Else
                Return "File created UNSuccessfully at " & OutFileName & " with " & rows & " row(s) with Date=" & MyDate & " " & sErrMsg
            End If
            'f.Flush()


        Catch ex As Exception
            Row = Row
            'ex.Message = ex.Message & " Row=" & Row.ToString()
            Throw ex
        Finally
            f.Close()

        End Try

        Return ""

    End Function

    Dim x As Integer = 0
    Dim InvoiceAmt As Double = 0
    Dim LastInvoiceNumber As String = ""
    Dim LastInvoiceAmt As String = ""

    Private Function fillDataTable(ByVal Path As String) As System.Data.DataTable

        Dim RetVal As New System.Data.DataTable
        Dim ConnectionString As String = String.Format(CONNECT_SKElETON, Path)

        'Dim cOLE As OLEDBConnection
        'cOLE = New OleDbConnection(ConnectionString)

        'sdk did below per upgrade of program machine to office 2007
        'also removed Excel reference, and added Microsoft.Office.InterOp.Excel ref.

        Dim cOLE As System.Data.OleDb.OleDbConnection
        cOLE = New System.Data.OleDb.OleDbConnection(ConnectionString)

        Try
            Dim SQL As String = String.Format(SQL_SKElETON, getWorksheetName(Path))

            Dim daOLE As New OleDbDataAdapter(SQL, cOLE)
            daOLE.Fill(RetVal)

        Catch ex As Exception
            Throw ex
        Finally
            cOLE.Close()
        End Try

        Return RetVal

    End Function

    Private Function getWorksheetName(ByVal filePath As String, Optional ByVal index As Integer = 1) As String
        Dim RetVal As String = "" '"Sheet1"
        'Return RetVal
        Dim workbook As Microsoft.Office.Interop.Excel.Workbook
        Try
            workbook = m_ExcelApp.Workbooks.Open(filePath)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets(index)
            RetVal = worksheet.Name
        Catch ex As Exception
            Throw ex
        Finally
            workbook.Close()
        End Try
        Return RetVal
    End Function

    Dim Valid_VendorID As Integer

    Public Function GetMatterCode(ByVal fnMatterCode As String, ByVal dt As System.Data.DataTable) As String
        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        fnMatterCode = fnMatterCode.Trim()
        dv.Sort = dt.Columns(0).ColumnName
        If (dv.FindRows(fnMatterCode).Length <> Nothing) Then
            dv.Dispose()
            dt.Dispose()

            Return fnMatterCode


        Else
            Return s

        End If


    End Function

    Public Function GetVendorID(ByVal VendorIDfn As String, ByVal dt As System.Data.DataTable) As String

        Dim dv As New DataView(dt)
        Dim incon As Integer = dt.Rows.Count

        Dim s As String = String.Empty
        VendorIDfn = VendorIDfn.Trim
        dv.Sort = dt.Columns(0).ColumnName
        If (dv.FindRows(VendorIDfn).Length <> Nothing) Then
            dv.Dispose()
            dt.Dispose()
            Return VendorIDfn
        Else
            Return s

        End If

    End Function

    Public Function GetCostCode(ByVal CostCodefn As String, ByVal dt As System.Data.DataTable) As String
        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        CostCodefn = CostCodefn.Trim
        dv.Sort = dt.Columns(0).ColumnName

        If (dv.FindRows(CostCodefn).Length <> Nothing) Then
            Return CostCodefn
            dv.Dispose()
            dt.Dispose()
        Else
            Return s

        End If


    End Function


    Public Function GetGLNumber(ByVal ClientMatterNumberfn As String, ByVal dt As System.Data.DataTable) As String
        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        ClientMatterNumberfn = ClientMatterNumberfn.Trim
        dv.Sort = dt.Columns(0).ColumnName

        If (dv.FindRows(ClientMatterNumberfn).Length <> Nothing) Then
            Return ClientMatterNumberfn
            dv.Dispose()
            dt.Dispose()
        Else
            Return s

        End If

    End Function

    Public Function GetVendorDataTable() As System.Data.DataTable
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_VendorNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        'command = "select * FROM OPENQUERY(ELITE,'SELECT apnum from ap where  ap.apst =''A''')"
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt
    End Function


    Public Function MatterDataTable() As System.Data.DataTable
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_MatterNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt

    End Function

    Public Function GLaccountNumberTable() As System.Data.DataTable

        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_ClientMatterNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt
    End Function

    Public Function GetCostCodeTable() As System.Data.DataTable
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_CostCode")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt

    End Function

    Public Function FileExists(ByVal FileToTest As String) As Boolean
        System.IO.File.Delete(FileToTest)
        Return Nothing
    End Function

End Class

Open in new window

0
Comment
Question by:sanija007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 4
  • +1
24 Comments
 
LVL 18

Expert Comment

by:Richard Lee
ID: 34144690
Firstly try updating your getWorksheetName method to use ADO.NET.

http://www.codeproject.com/KB/aspnet/getsheetnames.aspx

In addition you could also feedback to the UI what is happening and where you are in the process. UpdateProgress, etc.

http://www.devnewsgroups.net/dotnetframework/t56730-pump-messages-during-long-operations.aspx

Also use a BackgroundWorker and run it asynchronously.

DaTribe
0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 34144694
BTW 499? What is up with that :)?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34147048
@sanija007

So this is a VB.Net environment and not a VBA environment?
0
Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

 

Author Comment

by:sanija007
ID: 34150946
Hi aikimark its vb.net and its windows application I have got the performance that good as it increased the performance by very less amount if you could can I have a reopen the question.
Thank you
0
 

Author Comment

by:sanija007
ID: 34152188
@aikimark-->do you have any idea why my process is taking a longer time or sometimes its facing deadlocks
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34152341
@sanija007

We only see some of the code.  There might be something relevant to your performance problem(s) before the first posted statement.

Is this a VSTO application?

Just looking at the code, I wondered if the database connections were being dissolved prematurely.

You are also filling data tables in your functions with an unknown amount of data.  If you have a lot of data, this might squeeze your memory resources.
0
 

Author Comment

by:sanija007
ID: 34152492
@alikimark->
This is the standalone application that converts excel format file to text format and it has to validate with the sqldatabasees.For the input file we have around 2000 rows in which some of the fields has to be validated with sql.Basically it giving the correct output the only issue is the performance as it is taking longer .I am not sure if I am doing something wrong.I tried doing background worker but no luck any idea how to implement thread if I have to do so.
I placed progress bar and I know it for the writing the long text file but dont know how to optimize it.
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.IO
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Imports System.Data.DataTable
Imports System.Runtime.InteropServices



Public Class MakeApInvoiceVoucher


    Private CONNECT_SKElETON As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;"
    'sdk changed for xlsx dec. 10/08
    '#End If

    Private SQL_SKElETON As String = "select * from [{0}$]"
    Dim m_ExcelApp As New Excel.Application
    Dim rows As Integer = 0




    Public Function DoIt(ByVal MyDate As String _
                       , ByVal AlixFilePath As String _
                       , ByVal OutFileName As String _
                       , ByVal BillAccountNumberMappingFilePath As String) As String
        Dim Row As Integer = 0
        'Dim progressbar As New ProgressBar
        'progressbar.Minimum = 0
        'progressbar.Maximum = rows
        'progressbar.Increment(+1)
        Dim sErrMsg As String = String.Empty
        Dim Vendor As String = String.Empty
        Dim VendorAddress As String = String.Empty
        Dim dtVendor As System.Data.DataTable = GetVendorDataTable()
        Dim dtMatterCode As System.Data.DataTable = MatterDataTable()
        Dim dtGLAccount As System.Data.DataTable = GLaccountNumberTable()
        Dim dtCostCode As System.Data.DataTable = GetCostCodeTable()



        'All validations are done.
        Dim dt As System.Data.DataTable = fillDataTable(AlixFilePath)
        If dt.Rows.Count = 0 Then
            Throw New Exception("There is no data in " & AlixFilePath)
        End If

        Dim dtMap As System.Data.DataTable = fillDataTable(BillAccountNumberMappingFilePath)
        If dtMap.Rows.Count < 1 Then
            Throw New Exception("There is no data in " & BillAccountNumberMappingFilePath & " Bill to Account Numbers cannot be mapped to a Vendor.")
        End If

        rows = dt.Rows.Count
        'get a handle for the output file, delete it first if it exists
        If System.IO.File.Exists(OutFileName) Then
            System.IO.File.Delete(OutFileName)
        End If
        Dim f As New StreamWriter(OutFileName)
        Try

            f.WriteLine(MyDate.Replace("/", "")) ' first row in file

            '#01 = sdk changes Nov, 25, 08 to add Shipper name to replace Timekeeper

            'Input file columns:
            '0   VendorName
            '1   VendorNumber 
            '2   Invoice(Number)
            '3   Invoice Date 	
            '4   Invoice Amount(Total)	
            '5   Transaction date
            '6   Description
            '7   Matter Number
            '8   TimeKeeper Number
            '9   CostCode
            '10  Quantity
            '11  Amount 




            Dim x As Integer = 0
            Dim p As String = ""
            Dim v As String = ""
            Dim S As String = ""
            Dim S1 As String = ""
            Dim G As String = ""
            'Dim G As String = "5120100000205000000000000000".PadRight(64, " ") 'for row 4 on all rows.
            'Dim GLDepartment As String = "!".PadRight(37, " ")

            Dim InvoiceDate As String
            Dim InvoiceAmount As String
            Dim MatterCode As String
            Dim CostCode As String
            Dim VendorId As String
            Dim Glaccount As String
            For x = 0 To rows - 1
                Row = Row + 1
                'Row 1: Primary record
                'Primary record (record 1 for each row in xls.)
                'Field	            Length	Start Position	Column Name on Source
                'Record Type	    1	        1	        Will always =1
                'Transaction Date	6	        2	        Shipment Date (20080102 is not 6 characters).
                'Matter Number	    15	        8	        Original Customer Reference
                ''''''''''''''''Timekeeper	        5	        23	        Will always = 00000
                'Timekeeper	        5	        23	        Shipper Name ' #01
                'Cost Code	        7	        28	        Will always = POSTAGE
                'Quantity	        1	        35	        Will always = 1
                'Amount	            12	        49	        Net Charge Amount
                '20080429 becomes 042908'


                p = Mid(LTrim(Convert.ToString(dt.Rows(x).Item(5))), 3, 6)
                ''         Month          Day            Year
                p = Mid(p, 3, 2) & Mid(p, 5, 2) & Mid(p, 1, 2)
                '#01 Start
                '************************************************************************

                MatterCode = GetMatterCode(LTrim(Convert.ToString(dt.Rows(x).Item(7)).PadRight(15, " ")), dtMatterCode)
                CostCode = GetCostCode(LTrim(Convert.ToString(dt.Rows(x).Item(9)).PadRight(7, " ")), dtCostCode)
                System.Windows.Forms.Application.DoEvents()
                p = "1" _
                    & p _
                    & MatterCode _
                    & "   " _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(8)).PadRight(5, " ")) _
                    & CostCode _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(10)).PadRight(7, " ")) _
                    & "       " _
                    & LTrim(Convert.ToString(dt.Rows(x).Item(11)).PadRight(12, " "))

                ' f.WriteLine(p)
                If (MatterCode = Nothing And CostCode = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid MatterCode and Costcode at Row " & x)
                    ' f.WriteLine("COSTCODE OR MATTERCODE NOT FOUND ")
                Else
                    f.WriteLine(p)
                End If



                '1ST ROW OUTPUT: MATTERCODE(SP) TIMEKEEPER(IP) COSTCODE(SP) QUANTITY(IN) AMOUNT(IN) 
                'Row 2: Voucher Information

                ''***********************************************************

                'v = "3"
                InvoiceDate = ""
                '20080509 becomes 090508
                InvoiceDate = Mid(LTrim(Convert.ToString(dt.Rows(x).Item(3))), 3, 6)
                ''         Month          Day            Year
                InvoiceDate = Mid(InvoiceDate, 3, 2) & Mid(InvoiceDate, 5, 2) & Mid(InvoiceDate, 1, 2)

                InvoiceAmount = LTrim(Convert.ToString(dt.Rows(x).Item(4))).PadRight(12, " ")

                VendorId = GetVendorID(LTrim(Convert.ToString(dt.Rows(x).Item(1)).PadRight(8, " ")), dtVendor)
                v = "3" _
                 & VendorId _
                 & LTrim(Convert.ToString(dt.Rows(x).Item(2))).PadRight(16, " ") _
                 & InvoiceDate _
                 & InvoiceAmount
                'f.WriteLine(v)
                If (VendorId = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid VendorID at Row " & x)
                Else
                    f.WriteLine(v)
                End If


                '2ND ROW OUTPUT: VENDORID(SP) INVOICE_DATE(IP) INVOICE_AMOUNT(SP) 

                'Row 3: Cost Card Narrative record
                '***********************************************************
                '
                S = "4" _
                            & "Vendor: " _
                             & LTrim(Convert.ToString(dt.Rows(x).Item(0))).PadRight(29, " ") _
                             & Mid(LTrim(Convert.ToString(dt.Rows(x).Item(6))).PadRight(18, " "), 1, 18) & vbCrLf _
                             & "4" _
                             & Mid(LTrim(Convert.ToString(dt.Rows(x).Item(6))).PadRight(48, " "), 19, 48)

                f.WriteLine(S)

                '*************************************************************************************************************************************************
                'Row 4: GL Account Information

                Glaccount = GetGLNumber(LTrim(Convert.ToString(dt.Rows(x).Item(7)).PadRight(19, " ")), dtGLAccount)
                G = "5" _
                    & Glaccount
                'f.WriteLine(G)

                If (Glaccount = Nothing) Then
                    f.Close()
                    FileExists(OutFileName)
                    Throw New Exception("Invalid GLAcccount at Row " & x)
                    'Return Nothing
                Else
                    f.WriteLine(G)
                End If

            Next
            ''Memory Release of datatables
            System.Windows.Forms.Application.DoEvents()
            dt.Dispose()
            dtMap.Dispose()
            GC.Collect()
            GC.WaitForPendingFinalizers()


            If sErrMsg = String.Empty Then
                Return "File Successfully created at " & OutFileName & " with " & rows & " row(s) with Date=" & MyDate
            Else
                Return "File created UNSuccessfully at " & OutFileName & " with " & rows & " row(s) with Date=" & MyDate & " " & sErrMsg
            End If
            'f.Flush()


        Catch ex As Exception
            Row = Row
            'ex.Message = ex.Message & " Row=" & Row.ToString()
            Throw ex
        Finally
            f.Close()

        End Try

        Return ""

    End Function

    Dim x As Integer = 0
    Dim InvoiceAmt As Double = 0
    Dim LastInvoiceNumber As String = ""
    Dim LastInvoiceAmt As String = ""

    Private Function fillDataTable(ByVal Path As String) As System.Data.DataTable

        Dim RetVal As New System.Data.DataTable
        Dim ConnectionString As String = String.Format(CONNECT_SKElETON, Path)

        'Dim cOLE As OLEDBConnection
        'cOLE = New OleDbConnection(ConnectionString)

        'sdk did below per upgrade of program machine to office 2007
        'also removed Excel reference, and added Microsoft.Office.InterOp.Excel ref.

        Dim cOLE As System.Data.OleDb.OleDbConnection
        cOLE = New System.Data.OleDb.OleDbConnection(ConnectionString)

        Try
            Dim SQL As String = String.Format(SQL_SKElETON, getWorksheetName(Path))

            Dim daOLE As New OleDbDataAdapter(SQL, cOLE)
            daOLE.Fill(RetVal)

        Catch ex As Exception
            Throw ex
        Finally
            cOLE.Close()
        End Try

        Return RetVal

    End Function

    Private Function getWorksheetName(ByVal filePath As String, Optional ByVal index As Integer = 1) As String

        Dim RetVal As String = "" '"Sheet1"
        'Return RetVal
        Dim workbook As Microsoft.Office.Interop.Excel.Workbook
        Try
            workbook = m_ExcelApp.Workbooks.Open(filePath)
            Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets(index)
            RetVal = worksheet.Name
            System.Windows.Forms.Application.DoEvents()
        Catch ex As Exception
            Throw ex
        Finally
            workbook.RefreshAll()
            workbook.Close(False)
        End Try
        Return RetVal
    End Function

    Dim Valid_VendorID As Integer

    Public Function GetMatterCode(ByVal fnMatterCode As String, ByVal dt As System.Data.DataTable) As String

        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        fnMatterCode = fnMatterCode.Trim()
        dv.Sort = dt.Columns(0).ColumnName
        If (dv.FindRows(fnMatterCode).Length <> Nothing) Then
            dv.Dispose()
            dt.Dispose()
            System.Windows.Forms.Application.DoEvents()
            Return fnMatterCode
        Else
            Return s

        End If


    End Function

    Public Function GetVendorID(ByVal VendorIDfn As String, ByVal dt As System.Data.DataTable) As String

        Dim dv As New DataView(dt)
        Dim incon As Integer = dt.Rows.Count

        Dim s As String = String.Empty
        VendorIDfn = VendorIDfn.Trim
        dv.Sort = dt.Columns(0).ColumnName
        If (dv.FindRows(VendorIDfn).Length <> Nothing) Then
            System.Windows.Forms.Application.DoEvents()
            dv.Dispose()
            dt.Dispose()
            Return VendorIDfn

        Else
            Return s

        End If

    End Function

    Public Function GetCostCode(ByVal CostCodefn As String, ByVal dt As System.Data.DataTable) As String

        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        CostCodefn = CostCodefn.Trim
        dv.Sort = dt.Columns(0).ColumnName

        If (dv.FindRows(CostCodefn).Length <> Nothing) Then
            System.Windows.Forms.Application.DoEvents()
            dv.Dispose()
            dt.Dispose()
            Return CostCodefn

        Else
            Return s

        End If


    End Function


    Public Function GetGLNumber(ByVal ClientMatterNumberfn As String, ByVal dt As System.Data.DataTable) As String

        Dim dv As New DataView(dt)
        Dim s As String = String.Empty
        ClientMatterNumberfn = ClientMatterNumberfn.Trim
        dv.Sort = dt.Columns(0).ColumnName

        If (dv.FindRows(ClientMatterNumberfn).Length <> Nothing) Then
            dv.Dispose()
            dt.Dispose()
            System.Windows.Forms.Application.DoEvents()
            Return ClientMatterNumberfn

        Else
            Return s

        End If

    End Function

    Public Function GetVendorDataTable() As System.Data.DataTable
        System.Windows.Forms.Application.DoEvents()
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_VendorNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        'command = "select * FROM OPENQUERY(ELITE,'SELECT apnum from ap where  ap.apst =''A''')"
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt
    End Function


    Public Function MatterDataTable() As System.Data.DataTable
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_MatterNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt

    End Function

    Public Function GLaccountNumberTable() As System.Data.DataTable

        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_ClientMatterNumber")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt
    End Function

    Public Function GetCostCodeTable() As System.Data.DataTable
        Dim Dt As New System.Data.DataTable
        Dim connStr As String = ConnectionInfo.ConnectionString
        Dim conn As New SqlConnection(connStr)
        Dim command As New SqlCommand("app_ApInvoiceConverter_Get_CostCode")
        command.CommandType = CommandType.StoredProcedure
        command.Connection = conn
        Dim daVendor As New SqlDataAdapter(command)
        daVendor.Fill(Dt)
        conn.Close()
        conn.Dispose()
        Return Dt

    End Function

    Public Function FileExists(ByVal FileToTest As String) As Boolean
        System.IO.File.Delete(FileToTest)
        Return Nothing
    End Function

End Class

Open in new window

0
 
LVL 18

Expert Comment

by:Richard Lee
ID: 34153484
I am currently working on an application that is using ADO.NET to load, process and write data to excel. My Findings:

1. Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = workbook.Worksheets(index). Opening a workbook causes very poor performance hence why I switch everything to ADO.NET.

2. workbook.Close(False) was not working. When I checked TaskManager there were many instances of Excel loaded.

3. When working with IDisposable object I use the using(var dt = new DataTable()) or something like that if possible. I refrain from forcefully calling the Garbage Collector:

GC.Collect()
GC.WaitForPendingFinalizers()

4. Opening 1 ADO connection and passing that around (since this is a windows app. as oppose to web) rather than opening a connection as needed. Using a property to return the connection may be useful since the property can lazy load or check if the Connection has been closed (inadvertly).

5. Ensure the connection is close on application close if previous approach is used.

6. Use the Stopwatch class and check where the performance issues are.

DaTribe
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34154778
Your data-matching looks very inefficient.  In the GetMatterCode and GetCostCode functions you are instantiating a dataview object, sorting it, and then issuing a FIND method.  The DataTable object has its own a find method.

Perhaps the fastest lookup would be to use a hash table (dictionary) object.  Or you might optimize the lookups by passing in a data table that only has the column you are searching, already sorted (if that is required).
0
 
LVL 34

Expert Comment

by:Norie
ID: 34155784
sanija2007

Is the original/raw data in Excel and you are trying to parse it into an acceptable form/structure so it can be used by some other application/database/whatever?

By the way are you only creating an instance of Excel, opening a workbook etc to get the worksheet name for the SQL?


DaTribe

Just a quick comment regarding 2) in your list.

workbook.Close is unlikely to remove an instance of Excel.

Are you also using xlApp.Quit?

Also are you sure you are referencing everything correctly?

If you are not that's a common reason why you might be ending up with 'ghost' instance(s) floating about.
0
 

Author Comment

by:sanija007
ID: 34161598
@ imnorie:
For the excel input file I need to vailidate some of the field with database so I have created the datatable that reads the the value and then after storing in datatable dt I am closing it do you think I need to use App.Quit
I think I am refrencing everything correct and to me I cannot find out where the error is.Can you please tell me where the error might have come and which could have created ghost instances.
0
 

Author Comment

by:sanija007
ID: 34161610
@aikimirk:
Do you think the problem is due to lookup or its improper disposable of the datatable and dataview is creating an issue?
As it detected the context deadlock I feel its issue with the streamreader.
0
 
LVL 34

Expert Comment

by:Norie
ID: 34163472
If you are creating an instance of Excel you should use App.Quit as soon as you are finished with it.

Mind you if all you are using it for is to get a worksheet name you should probably look at some other method to do that.

What is it exactly you are trying to do anyway?

There seems to be a lot of code for something that sounds as though it might able to be done in with a more straightforward method.



0
 
LVL 34

Expert Comment

by:Norie
ID: 34163529
Just a follow up - if you are using OLEDB then you can return the sheet names from an Excel workbook without opening it.

The only example I've got it is in C#, I'll try and convert it.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 34164180
@sanija007

I'm not sure which is more problematic.  I merely identified these as things to measure and, potentially, performance tune.
0
 

Author Comment

by:sanija007
ID: 34191865
Hi Inmorie ,
If you dont mind can you please send me the workbook exaple you have.I will be very grateful
0
 
LVL 34

Expert Comment

by:Norie
ID: 34192674
It's not a workbook example - it''s C# code.

I can post it if you want but it's only an example of how to get the names of worksheets from a workbook without opening it.

Your question involves a lot more than that.:)
0
 

Author Comment

by:sanija007
ID: 34192700
Ya If you could then it will be lots of help.
Thank you
0
 
LVL 34

Accepted Solution

by:
Norie earned 499 total points
ID: 34192809
Well here it is.

Please remember it is just an example, the code is pretty rough and filenames etc are harcoded.

Also it's meant to be used with a form to populate a listbox.
private void button1_Click(object sender, EventArgs e)
        {

            string strFile = "C:\\NetSampleData.xls";

            string connString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + strFile + ";Extended Properties=\"Excel 12.0;HDR=Yes\"";

            try
            {
                objConn = new System.Data.OleDb.OleDbConnection(connString);
                objConn.Open();

                dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);

                if (dt != null)
                {
                    String[] xlSheets = new String[dt.Rows.Count];

                    int cnt = 0;

                    objAdp = new System.Data.OleDb.OleDbDataAdapter();

                    foreach (DataRow row in dt.Rows)
                    {
                        objConn.ResetState();

                        objComm = null;

                        objComm = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + row["TABLE_NAME"].ToString()+"]", objConn);
                        objAdp = new System.Data.OleDb.OleDbDataAdapter(objComm);

                        objAdp.Fill(ds, row["TABLE_NAME"].ToString());

                        xlSheets[cnt++] = row["TABLE_NAME"].ToString();
                        listBox1.Items.Add(xlSheets[cnt - 1]);
                    }

                }

            }

            catch (Exception exp)
            {
                label1.Text = exp.Message;

            }

            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                }
                if (dt != null) dt.Dispose();
            }
        }

Open in new window

0
 

Author Comment

by:sanija007
ID: 34192888
Thank yuo so much.I try to implement and see how well the performance goes.
0
 
LVL 34

Expert Comment

by:Norie
ID: 34192933
I don't think that code is not going to affect the performance much, it really is just an example of how to  get the names of the worksheets of an Excel workbook without opening it.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question