'''打开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
加好友 
发短信
Post By：2017/12/15 20:00:00 [只看该作者]
此主题相关图片如下：qq截图20171215204414.png
