专家坐堂-[求助]代码优化问题，请求指教Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

以文本方式查看主题

-  Foxtable(狐表)  (http://www.foxtable.com/bbs/index.asp)
--  专家坐堂  (http://www.foxtable.com/bbs/list.asp?boardid=2)
----  [求助]代码优化问题，请求指教  (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=93713)

--  作者：lzzhx
--  发布时间：2016/12/5 14:03:00
--  [求助]代码优化问题，请求指教

\'目的：判断tb中是否包含tbltaizhang的记录且tb中字段【春检年度】=cmb年度.Text，关键字段是【台账号】
\'若包含，则用tbltaizhang中的字段值去修改tb中的相同字段值，若不包含，则在tb中增加相应记并赋值。
\'tbltaizhang中有5000条记录，下面代码执行需要耗时500秒左右，请老师们看看该如何优化

Dim time1 As Date = Functions.Execute("SQLTIME")
e.Form.Controls("Label1").Text = "正在增加行,请稍后..."
Application.DoEvents()

Dim tb As Table = e.Form.Controls("Table1").Table
Dim tbltaizhang As Table = e.Form.Controls("Table2").Table
tb.StopRedraw
Dim cmb年度 As WinForm.ComboBox = e.Form.Controls("cmb年度")
Dim mydate As Date = Functions.Execute("SQLTIME")
For Each ydr As Row In tbltaizhang.Rows
Dim wz As Integer = tb.FindRow("[台账号] = \'" & ydr("台账号") & "\' And [春检年度] = \'" & cmb年度.Text & "\'")
If wz >= 0 Then
tb.Position = wz
Else
Dim dr As Row = tb.AddNew()
End If
tb.Current("部门编号")= ydr("部门编号")
tb.Current("部门")= ydr("部门")
tb.Current("春检年度")= e.Form.Controls("cmb年度").text
tb.Current("台账号")= ydr("台账号")
tb.Current("台账号old")= ydr("台账号old")
tb.Current("地点")= ydr("地点")
tb.Current("台账名称")= ydr("台账名称")
tb.Current("录入人")= _UserTag
tb.Current("录入终端")= _UserPCip
tb.Current("录入日期")= mydate
Next
tb.ResumeRedraw
Dim time2 As Date = Functions.Execute("SQLTIME")
e.Form.Controls("Label1").Text = "耗时:" & Format((time2-time1).TotalSeconds,"0.0000") & "秒"

--  作者：lzzhx
--  发布时间：2016/12/5 14:39:00
--
请老师看看代码有什么问题？

--  作者：有点蓝
--  发布时间：2016/12/5 14:57:00
--
效率的问题可以参考：http://www.foxtable.com/webhelp/scr/2225.htm

具体上例子测试

--  作者：有点色
--  发布时间：2016/12/5 15:02:00
--
Dim time1 As Date = Functions.Execute("SQLTIME")
e.Form.Controls("Label1").Text = "正在增加行,请稍后..."
Application.DoEvents()
Dim tb As Table = e.Form.Controls("Table1").Table
Dim tbltaizhang As Table = e.Form.Controls("Table2").Table
tb.StopRedraw
Dim cmb年度 As WinForm.ComboBox = e.Form.Controls("cmb年度")
Dim mydate As Date = Functions.Execute("SQLTIME")
Dim nlist As new List(Of Row)
Dim dic As new Dictionary(Of Row, Integer)
For Each ydr As Row In tbltaizhang.Rows
    Dim wz As Integer = tb.FindRow("[台账号] = \'" & ydr("台账号") & "\' And [春检年度] = \'" & cmb年度.Text & "\'")
    If wz >= 0 Then
        dic.add(ydr, wz)
    Else
        nlist.Add(ydr)
    End If
Next
For Each ydr As Row In nlist
    Dim nr As Row = tb.AddNew
    nr("部门编号")= ydr("部门编号")
    nr("部门")= ydr("部门")
    nr("春检年度")= e.Form.Controls("cmb年度").text
    nr("台账号")= ydr("台账号")
    nr("台账号old")= ydr("台账号old")
    nr("地点")= ydr("地点")
    nr("台账名称")= ydr("台账名称")
    nr("录入人")= _UserTag
    nr("录入终端")= _UserPCip
    nr("录入日期")= mydate
next
For Each ydr As Row In dic.Keys
    Dim nr As Row = tb.rows(dic(ydr))
    nr("部门编号")= ydr("部门编号")
    nr("部门")= ydr("部门")
    nr("春检年度")= e.Form.Controls("cmb年度").text
    nr("台账号")= ydr("台账号")
    nr("台账号old")= ydr("台账号old")
    nr("地点")= ydr("地点")
    nr("台账名称")= ydr("台账名称")
    nr("录入人")= _UserTag
    nr("录入终端")= _UserPCip
    nr("录入日期")= mydate
next
tb.ResumeRedraw
Dim time2 As Date = Functions.Execute("SQLTIME")
e.Form.Controls("Label1").Text = "耗时:" & Format((time2-time1).TotalSeconds,"0.0000") & "秒"

--  作者：有点色
--  发布时间：2016/12/5 15:03:00
--

不要一边插一遍赋值。

http://www.foxtable.com/webhelp/scr/2225.htm

--  作者：lzzhx
--  发布时间：2016/12/5 15:26:00
--
谢谢老师：现在执行比较快了，若全部是新增，需要8秒，全部修改，需要3秒

--  作者：lzzhx
--  发布时间：2016/12/8 9:28:00
--
【有点色】老师：
前面的代码是在table层面上进行操作，要在datatable上进行操作，代码该如何修改？因为Table和DataTable的Find方法不一样，字典操作的一块代码不会写，请老师指教一下。

--  作者：有点蓝
--  发布时间：2016/12/8 9:38:00
--
Dim tb As dataTable = e.Form.Controls("Table1").Table.datatable
......
Dim nlist As new List(Of dataRow)
......
Dim dic As new Dictionary(Of dataRow, Integer)

For Each ydr As Row In tbltaizhang.Rows
    Dim wz As Integer = tb.FindRow("[台账号] = \'" & ydr("台账号") & "\' And [春检年度] = \'" & cmb年度.Text & "\'")
    If wz >= 0 Then
        dic.add(ydr.datarow, wz)
    Else
        nlist.Add(ydr.datarow)
    End If
Next

--  作者：lzzhx
--  发布时间：2016/12/8 9:52:00
--
错误提示：Findrow不是DataTable的成员

--  作者：lzzhx
--  发布时间：2016/12/8 9:54:00
--
tb和tbltaizhang都是Datatable