代码优化
'检查该商品是否已经销售Private Function checkSaleCode() As Boolean
'声明Connection,DataAdapter和DataSet对象
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim Conn As SqlConnection = New SqlConnection
Dim Comm As SqlCommand = New SqlCommand
Dim ds As DataSet
'退货单号
Dim strReturnNo As String = ""
Dim sSql As String = ""
'销售次数
Dim iSaleNumber As Integer = 0
'Dim j As Integer
'查询结果记录数
Dim lResultCount As Long = 0
'文件记录数
Dim lFileRecordCount As Long = 0
Try
'连接到本地SQL Server数据库
Conn = New SqlConnection(strDBConnection)
Dim i As Long
Dim strCode As String
lFileRecordCount = strArrayListCurCode.Count - 1
For i = 0 To lFileRecordCount
strCode = strArrayListCurCode(i)
'执行select语句查询数据库
sSql = "SELECT SaleCount,RETURNNO FROM SALEJMT WHERE CODE = '" & strCode & "' AND DELFLG <> '1' ORDER BY SALECOUNT DESC"
da = New SqlDataAdapter(sSql, Conn)
'实例化DataSet对象
ds = New DataSet()
'将da中的数据放到DataSet中,并起一个别名SALEJMT,这个别名任意起
da.Fill(ds, "SALEJMT")
lResultCount = ds.Tables(0).Rows.Count
If lResultCount > 0 Then
'多次销售 取得最后一次销售记录
'销售次数
iSaleNumber = ds.Tables("SALEJMT").Rows.Item(0).ItemArray(0)
'退货单号
strReturnNo = ds.Tables("SALEJMT").Rows.Item(0).ItemArray(1).ToString
If checkReturnOKCode(strCode, iSaleNumber, strReturnNo) = False Then
Return False
End If
Else
'第一次销售
strArrayListSaleNumber.Add(1)
strArrayListReturnNo.Add("")
End If
Next
Return True
Catch ex As System.Exception
MessageBox.Show("Exception:" + ex.ToString)
Return False
Finally
If (Conn.State = ConnectionState.Open) Then
Comm.Connection.Close()
End If
End Try
'检查该商品是否合格退货再次销售
Private Function checkReturnOKCode(ByVal strCode As String, ByVal iSaleNum As Integer, ByVal strReturnNoFrom As String) As Boolean
'声明Connection,DataAdapter和DataSet对象
Dim da As SqlDataAdapter = New SqlDataAdapter
Dim Conn As SqlConnection = New SqlConnection
Dim Comm As SqlCommand = New SqlCommand
Dim ds As DataSet
Dim sSql As String = ""
Dim strReturnNo As String = ""
Dim iReturnCount As Integer = 0
Dim lResultCount As Long = 0
Try
'连接到本地SQL Server数据库
Conn = New SqlConnection(strDBConnection)
'多次退货销售
'执行select语句查询数据库
sSql = "SELECT ReturnNo,RETURNCOUNT FROM ReturnGoodsJMT WHERE CODE = '" & strCode & "' AND Qualified = '1' ORDER BY RETURNCOUNT DESC"
da = New SqlDataAdapter(sSql, Conn)
'实例化DataSet对象
ds = New DataSet()
'将da中的数据放到DataSet中,并起一个别名ReturnGoodsJMT,这个别名任意起
da.Fill(ds, "ReturnGoodsJMT")
lResultCount = ds.Tables(0).Rows.Count
If lResultCount < 1 Then
MsgBox("产品条码[" & strCode & "]已经销售,并且不符合再次销售条件,请核实!")
Return False
Else
'退货次数
iReturnCount = ds.Tables("ReturnGoodsJMT").Rows.Item(0).ItemArray(1)
'判断是否符合再次销售
If iSaleNum = iReturnCount Then
'再次销售记录数
lAgainSaleCount = lAgainSaleCount + 1
'销售次数
strArrayListSaleNumber.Add(iSaleNum + 1)
'退货单号
strArrayListReturnNo.Add(ds.Tables("ReturnGoodsJMT").Rows.Item(0).ItemArray(0))
Else
MsgBox("产品条码[" & strCode & "]已经销售,并且不符合再次销售条件,请核实!")
Return False
End If
End If
Return True
Catch ex As System.Exception
MessageBox.Show("Exception:" + ex.ToString)
Return False
Finally
If (Conn.State = ConnectionState.Open) Then
Comm.Connection.Close()
End If
End Try
End Function
End Function
========================================================
以上代码当数据量大的话,执行起来很慢,望高手指点 --------------------编程问答-------------------- 建议在数据库写成存储过程或函数
补充:.NET技术 , VB.NET