快速取出sap返回的数据

随风忘记 2010-03-22 05:04:12

当sap里返回数据比较多时如果通过value(行,列)返回会很慢(20来万会用一两个小时),
这时可以用returnMessTable_SO.Data获得一个二维数组然后再取二维数组里面的数据,
下面就是例子:
以前一二十万数据需要用一个多小时,而用这种方法只需几分钟就可以了
不知道这样会不会有错误?

 Dim functionCtrl As Object      'sap function

        Dim sapConnection As Object     'sap  conncetion

        Dim theFunc As Object           'function

        Dim result As Boolean   '连接是否成功



        functionCtrl = CreateObject("Sap.Functions")

        sapConnection = functionCtrl.Connection

        sapConnection.Client = ""

        sapConnection.Language = ""

        sapConnection.User = ""

        sapConnection.password = ""

        sapConnection.ApplicationServer = ""

        sapConnection.SystemNumber = ""



        result = sapConnection.Logon(0, True)

        If result = False Then

            ' Response.Write(Cls_common.ShowErrMessage("连接失败，请重试"))

            Exit Sub

        End If



        theFunc = functionCtrl.Add("")

        Dim returnMessTable_SO As Object   '出参表，返回信息     

        theFunc.Call()

        returnMessTable_SO = theFunc.TABLES.Item(1)      '出参



        Dim i As Double

        Dim icount As Integer

        icount = returnMessTable_SO.RowCount

        '取得sap里每一列所在的位置

        Dim NUM As Integer = returnMessTable_SO.Columns("MANDT").Index

        Dim BUKRS As Integer = returnMessTable_SO.Columns("BUKRS").Index

        Dim KONTO As Integer = returnMessTable_SO.Columns("KONTO").Index

        Dim BELNR As Integer = returnMessTable_SO.Columns("BELNR").Index

        Dim GJAHR As Integer = returnMessTable_SO.Columns("GJAHR").Index

        Dim BUZEI As Integer = returnMessTable_SO.Columns("BUZEI").Index

        Dim NAME1 As Integer = returnMessTable_SO.Columns("NAME1").Index

        Dim FAEDT As Integer = returnMessTable_SO.Columns("FAEDT").Index

        Dim DMSHB As Integer = returnMessTable_SO.Columns("DMSHB").Index

        Dim HWAER As Integer = returnMessTable_SO.Columns("HWAER").Index

        Dim PRCTR As Integer = returnMessTable_SO.Columns("PRCTR").Index

        Dim HKONT As Integer = returnMessTable_SO.Columns("HKONT").Index

        Dim VBELN As Integer = returnMessTable_SO.Columns("VBELN").Index

        Dim ZTERM As Integer = returnMessTable_SO.Columns("ZTERM").Index

        Dim REBZG As Integer = returnMessTable_SO.Columns("REBZG").Index

        Dim BWWRT_B As Integer = returnMessTable_SO.Columns("BWWRT_B").Index

        Dim BWWRT_C As Integer = returnMessTable_SO.Columns("BWWRT_C").Index



        Dim connectionString As String

        connectionString = "连接字符串"

        Dim rs As New SqlClient.SqlBulkCopy(connectionString)

        Dim dt As New DataTable

        Using connection As New SqlClient.SqlConnection(connectionString)

            Dim adapter As New SqlClient.SqlDataAdapter()

            '给dt设置每一列

            adapter.SelectCommand = New SqlClient.SqlCommand("select * from 表 where 1=2", connection)

            adapter.Fill(dt)

        End Using



        Dim data As Object

        data = returnMessTable_SO.Data

        For i = 1 To icount Step 1

            Dim row As DataRow = dt.NewRow()

            '将sap里的每一列赋给dt,用这种方法比用returnMessTable_SO.Value(i, BUKRS)快很多

            row("NUM") = data(i, NUM)

            row("BUKRS") = data(i, BUKRS)

            row("KONTO") = data(i, KONTO)

            row("BELNR") = data(i, BELNR)

            row("GJAHR") = data(i, GJAHR)

            row("BUZEI") = data(i, BUZEI)

            row("NAME1") = data(i, NAME1)

            row("FAEDT") = data(i, FAEDT)

            row("DMSHB") = data(i, DMSHB)

            row("HWAER") = data(i, HWAER)

            row("PRCTR") = data(i, PRCTR)

            row("HKONT") = data(i, HKONT)

            row("VBELN") = data(i, VBELN)

            row("ZTERM") = data(i, ZTERM)

            row("REBZG") = data(i, REBZG)

            Dim tobject As Object

            tobject = data(i, BWWRT_B)

            row("BWWRT_B") = tobject

            If IsDBNull(tobject) Then

                row("BWWRT_B") = 0

            End If

            tobject = data(i, BWWRT_C)

            row("BWWRT_C") = tobject

            If IsDBNull(tobject) Then

                row("BWWRT_C") = 0

            End If

            row("cautodate") = Date.Now



            dt.Rows.Add(row)



        Next i

        rs.DestinationTableName = "表"

        '将dt里面的信息更新到表里

        rs.WriteToServer(dt)