Solved

Slow running process

Posted on 2010-11-15
24
550 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
  • 8
  • 6
  • 4
  • +1
24 Comments
 
LVL 18

Expert Comment

by:Richard Lee
Comment Utility
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
Comment Utility
BTW 499? What is up with that :)?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@sanija007

So this is a VB.Net environment and not a VBA environment?
0
 

Author Comment

by:sanija007
Comment Utility
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
Comment Utility
@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
Comment Utility
@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
Comment Utility
@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
Comment Utility
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
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:sanija007
Comment Utility
@ 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
Comment Utility
@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 33

Expert Comment

by:Norie
Comment Utility
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 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
@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
Comment Utility
Hi Inmorie ,
If you dont mind can you please send me the workbook exaple you have.I will be very grateful
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
Ya If you could then it will be lots of help.
Thank you
0
 
LVL 33

Accepted Solution

by:
Norie earned 499 total points
Comment Utility
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
Comment Utility
Thank yuo so much.I try to implement and see how well the performance goes.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now