mysql - 通过VB2010将查询结果从MS SQL 2008插入到MYSQL

Question

我是 VB2010 和 MYSQL 数据库的新手。我有 2 个数据库，一个在 MS SQL 2008 (BigData) 上，另一个在 Mysql 上。我在VB2010中编写了一些代码来从SQL2008中获取数据并插入到MySQL中。我的目标是尽快将所有数据从 MS SQL 传输到 MySQL，这就是为什么我创建了一个简单的 vb 脚本，它将充当中间件将数据从 MS SQL 传输到 MySQL。

令我头疼的是，将 28,000 条记录从 MS SQL 传输到 MySQL 数据库需要将近 1 个小时。有没有最简单的方法来传输数据或者我需要增强我的 VBScript 程序。请帮助改进我的下面的 VBScript。

提前谢谢您。

导入MySql.Data.MySqlClient

导入系统数据

导入System.Data.SqlClient

公开课表格1

Dim SQLConnectionSQL As MySqlConnection = New MySqlConnection
Dim connectionStringSQL As String = "Data Source=solomon;Initial Catalog=testapp;Persist Security Info=True;User ID=sa;Password=Passw0rd"


Dim connectionString As String = "Server=192.168.1.199; User Id=gil; Password=Passw0rd; Database=testapp"
Dim SQLConnection As MySqlConnection = New MySqlConnection
Dim oDt_sched As New DataTable()


Private Sub btnRetrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click
    Dim con_Solomon As String
    Dim connection As SqlConnection
    Dim command As SqlCommand
    Dim adapter As New SqlDataAdapter
    Dim ds As New DataSet
    Dim sql As String

    Dim str_carSql As String

    lblCounter.Text = 0

    con_Solomon = "Data Source=solomon;Initial Catalog=MARYLANDAPP;Persist Security Info=True;User ID=sa;Password=Passw0rd"

    sql = "SELECT LTRIM(RTRIM(DocType)) as DocType, LTRIM(RTRIM(cust_classID)) as cust_classID, LTRIM(RTRIM(salesman)) as salesman, LTRIM(RTRIM(CustId)) as CustId, LTRIM(RTRIM(name)) as name, LTRIM(RTRIM(ClassID)) as ClassID, LTRIM(RTRIM(invoice_no)) AS refnbr, invoice_delv_date AS Delv_DocDate, Age, AR_Amount, LTRIM(RTRIM(STATUS)) as STATUS, LTRIM(RTRIM(AGE_GROUP)) as AGE_GROUP, LTRIM(RTRIM(company)) AS comp, '' AS Deposit_Date, Credit_Limit, LTRIM(RTRIM(Terms)) as Terms, LTRIM(RTRIM(customer_name)) AS ShipName, LTRIM(RTRIM(PO_Number)) as PO_Number, LTRIM(RTRIM(Kob)) as Kob, LTRIM(RTRIM(check_date)) as check_date FROM a_aging_ardoc_report"

    connection = New SqlConnection(con_Solomon)

    Try
        connection.Open()
        command = New SqlCommand(sql, connection)
        command.CommandTimeout = 420
        adapter.SelectCommand = command
        adapter.Fill(ds, "PO_Monitoring")
        adapter.Dispose()
        command.Dispose()
        connection.Close()


        ''****** MYSQL CONNECTION *****
        SQLConnection = New MySqlConnection()
        SQLConnection.ConnectionString = connectionString
        SQLConnection.Open()
        Dim sqlCommand As New MySqlCommand

        Dim delv_docdate, check_date

        For a = 0 To ds.Tables(0).Rows.Count - 1

            With ds.Tables(0).Rows(a)


                If Not IsDBNull(.Item(7)) Then
                    delv_docdate = .Item(7)
                Else
                    delv_docdate = ""
                End If


                If Not IsDBNull(.Item(19)) Then
                    check_date = .Item(19)
                Else
                    check_date = ""
                End If

                str_carSql = "insert into agingreportsummary(doctype,cust_classid,salesman,custId,name,classid,refnbr,delv_docdate,age,ar_amount,status,age_group,comp,credit_limit,terms,shipname,po_number,kob,check_date) " & _
                "VALUES('" & .Item(0) & "','" & .Item(1) & "','" & Replace(.Item(2), "'", "") & "','" & .Item(3) & "','" & Replace(.Item(4), "'", "") & "','" & Replace(.Item(5), "'", "") & "','" & .Item(6) & "','" & delv_docdate & "'," & Replace(.Item(8), ",", "") & "," & Replace(.Item(9), ",", "") & ",'" & Replace(.Item(10), "'", "") & "','" & .Item(11) & "','" & .Item(12) & "','" & .Item(14) & "','" & .Item(15) & "','" & Replace(.Item(16), "'", "") & "','" & Replace(.Item(17), "'", "") & "','" & .Item(18) & "','" & check_date & "');"


            End With


            sqlCommand.Connection = SQLConnection
            sqlCommand.CommandText = str_carSql
            sqlCommand.ExecuteNonQuery()
        Next a

        SQLConnection.Close()

        MsgBox("Finish")
    Catch ex As Exception
        MsgBox(str_carSql)
        MsgBox(ex.Message)
    End Try
End Sub

下课

Answer 1

您可以尝试使用参数化查询，而不是为每一行构建查询。这应该会稍微改善情况，因为不需要每次都准备声明。

将所有必需的参数添加到命令中。

设置一次命令文本，然后将其更改为使用参数。

在循环内，您只需设置参数值并调用executenonquery方法

这还有一个额外的好处，那就是不易受到 SQL 注入(inject)的攻击。

希望有帮助