主题：mysql怎么批量添加数据

'''打开mysql数据链接

Dim conn As MySql.Data.MySqlClient.MySqlConnection

Dim cmd As MySql.Data.MySqlClient.MySqlCommand

Dim connStr As String = "server=localhost; port=3307; user id=root; password=root; database=bb2_dt;CharSet=utf8"

conn = New MySql.Data.MySqlClient.MySqlConnection(connStr)

conn.Open

 

Dim st1 As Date = Date.Now

Dim strlist As  List(Of String) 

'''合成变更数据语句

strlist = Functions.Execute("sql提取函数",Tables("A11"),"wtu","_Identify","wtu","wtu|kg|lbs","wtu|kg|lbs",2)

'''更新mysql中的数据

For Each s As String In strlist

cmd = new MySql.Data.MySqlClient.MySqlCommand(s, conn)   '更改col1=5 的行记录,col2的值为fox

cmd.ExecuteNonQuery()   '执行sql数据操作

'MessageBox.show(s)

Next

conn.close

MessageBox.show("成功")

'''Functions.Execute("sql提取函数",Tables("A11"),"wtu","_Identify","wtu","wtu|kg|lbs","wtu|kg|lbs",2)

Dim tb As Table = Args(0)        '要提取的表格

Dim kName As String = Args(1)    '后台表格的名称

Dim tbid As String = Args(2)     '表格中的主键字段名

Dim kid As String = Args(3)      '后台表格的主键字段名

Dim bgzd As String = Args(4)     '表格中的字段,用|分割

Dim kbgzd As String = Args(5)    '对应的后台表中的字段,用|分割

Dim lx As Integer = Args(6)      '数据源的类型,Access=1,sql=2

'************  生成字段对应表 *****************************************************************

Dim bgzds() As String = bgzd.Split("|")

Dim kbgzds() As String = kbgzd.Split("|")

If bgzds.Length <> kbgzds.Length Then Return Nothing   '长度不等,说明设置错误,退出

Dim _表格_字段对应表 As New Dictionary(Of String, String)  '新建一个用于存放字段对应的字典

For Index As Integer = 0 To bgzds.Length - 1

    _表格_字段对应表.Add(bgzds(Index),kbgzds(Index))

Next

'********************  生成字段类型表 ***************************************************

Dim _表格_字段类型表 As New Dictionary(Of String, String)  '新建一个用于存放字段类型

'*************遍历获取列的类型

Dim s1 As String

For Each s As String In _表格_字段对应表.Keys

    s1 = "未知"

    With tb.DataTable.DataCols(s)

        If .IsString Then s1 = "文本"

        If .IsNumeric Then s1 = "数值"

        If .IsDate Then s1 = "日期"

        If .IsBoolean Then s1 = "逻辑"

    End With

    _表格_字段类型表.Add(s, s1)

Next

'

'获取主键列的类型

'

If tbid = "_Identify" Then

    s1 = "_Identify"

Else

    With tb.DataTable.DataCols(tbid)

        s1 = "未知"

        If .IsString Then s1 = "文本"

        If .IsNumeric Then s1 = "数值"

        If .IsDate Then s1 = "日期"

    End With

End If

_表格_字段类型表.Add(tbid, s1)

Dim strlist As New List(Of String)   '定义一个集合,用于返回

'

'获取对象

'

Dim systable As System.Data.DataTable = tb.DataTable.BaseTable

'

Dim sql As String

Dim str As String

Dim ChangeTable As System.Data.DataTable = systable.GetChanges(System.Data.DataRowState.Added)  '获取新增记录

If ChangeTable IsNot Nothing Then '如果有新增记录

    '

    '循环获取新增语句

    

    Dim sql11 As String = "INSERT INTO " & kName & " ("

    

    '生成语句前导

    For Each str1 As String In _表格_字段对应表.Values

        sql11 += str1 & ","

    Next

    sql11 = sql11.Trim(",") & ") VALUES ("

    

    For Each dr As System.Data.DataRow In ChangeTable.Rows

        '遍历设置列值

        sql = sql11

        For Each str1 As String In _表格_字段对应表.Keys

            str = "'" & dr(str1) & "',"

            Select Case _表格_字段类型表(str1)

                Case "数值"

                    str = dr(str1) & ","

                Case "逻辑"

                    If dr(str1) Then

                        str = "1,"

                    Else

                        str = "0,"

                    End If

                Case "日期"

                    If lx = 1 Then   'Access

                        str = "#" & dr(str1) & "#,"

                    End If

            End Select

            If str.Trim(","," ").Trim = "" OrElse str.Trim(",","#"," ") = "" OrElse str.Trim(",","'"," ")= "" Then

                sql += "null,"

            Else

                sql += str

            End If

        Next

        sql = sql.Trim(",") & ")"

        strlist.Add(sql)

    Next

End If

Dim strkey As String