数据库的连接及三层结构中的业务层及数据处理层的编写

最新推荐文章于 2022-02-22 15:14:01 发布

ernest808

最新推荐文章于 2022-02-22 15:14:01 发布

阅读量796

点赞数

文章标签：数据库 string integer function dataset exception

本文链接：https://blog.csdn.net/ernest808/article/details/3352691

版权

连接数据库：

   Private Function getConnection() As OracleClient.OracleConnection
        Dim cnStr As String
        cnStr = AppSettings.Get(NBeFormConst.APPSETTINGS_KEY_NBeForm_CONNECTION_STRING)
        Return New OracleClient.OracleConnection(cnStr)
    End Function

业务层（对业务逻辑进行编写）：

Function findformtype(ByVal formtypeid As String, ByVal formtypename As String) As NBeFormDataset Implements INBeFormService.findformtype    ' BY apple
        Dim cn As OracleClient.OracleConnection
        Dim cd As INBeFormDAO
        Dim ds2 As New NBeFormDataset
        Dim dr2 As NBeFormDataset.EFORM_FORMTYPERow, dr3 As NBeFormDataset.EFORM_FORMTYPERow

        Try
            cn = getConnection()
            cn.Open()
            cd = DefaultDAOFactory.getInstance().getNBeFormDAO(cn)
            dr2 = cd.findformtype(formtypeid, formtypename)
            If Not dr2 Is Nothing Then
                dr3 = ds2.EFORM_FORMTYPE.NewEFORM_FORMTYPERow
                cd.copyDataRow(dr2, dr3)
                ds2.EFORM_FORMTYPE.Rows.Add(dr3)
                cd.fillDetails(ds2)
                'mark as not changed
                ds2.AcceptChanges()
            End If
            Return ds2
        Finally
            If Not cn Is Nothing Then
                cn.Close()
                cn = Nothing
            End If
            cd = Nothing
        End Try
    End Function

''''查找已设定的会签群组，写入会签人员。

   Function SetPreCosignMember(ByVal DS As NBeFormDataset, ByVal TableName As String, ByVal FormTypeId As String, ByVal GROUPID As String) As NBeFormDataset Implements INBeFormService.SetPreCosignMember   ''糶箇砞帽竤舱
        Dim CS As INBeFormService
        Dim DV As DataView
        Dim i As Integer
        Dim num As String
        Dim sn As String
        Dim DRSYSH As NBeFormDataset.EFORM_RSYS_HEADRow
        Dim arr As String = ""
        Dim sb0 As New StringBuilder
        Dim end_role As String
        Dim CS1 As INBeFormService
        Dim DS1 As New NBeFormDataset
        Dim DV1 As DataView

Dim COMID As String = WebUtil.GetCookieValue("NBeFormCompanyCookie", "COMPANY_ID")

        Dim CS2 As INBeFormService
        Dim DS2 As New NBeFormDataset
        Dim DV2 As DataView
        Dim j As Integer
        Try
            sb0.Append("SELECT * FROM EFORM_DECISION_COLUMN where FORMTYPE_ID= '" + FormTypeId + "' ")
            DS1 = DefaultServiceFactory.getInstance.getNBeFormService.finddecisioncolumnBySql(sb0.ToString)
            DV1 = New DataView(DS1.EFORM_DECISION_COLUMN)
            If Not DV1 Is Nothing Then
                For i = 0 To DV1.Count - 1
                    Dim decisionCol As String = DV1.Item(i).Row.Item("DECISION_COLUMN_ID").ToString
                    Dim decisionSn As String = DV1.Item(i).Row.Item("DECISION_SN").ToString

                    Dim DRFC As NBeFormDataset.EFORM_COLUMNRow
                    Dim sb As New StringBuilder
                    CS1 = New NBeFormServiceImpl
                    DRFC = CS1.findformcolumnBycolumnid(decisionCol)
                    If Not DRFC Is Nothing Then
                        'sb.Append("SELECT *   FROM EFORM_MAF   where ID= '" + id + "' ")
                        'DS1 = DefaultServiceFactory.getInstance.getNBeFormService.getQKFHbysql(sb.ToString)
                        DV = New DataView(DS.Tables(TableName))
                        num = DV.Item(0).Row.Item(decisionCol).ToString
                    End If
                    Dim sb2 As New StringBuilder
                    sb2.Append("SELECT *   FROM EFORM_DECISION_NUMERICAL A where formtype_id='" + FormTypeId + "' and decision_sn='" + decisionSn + "' ")
                    DS2 = DefaultServiceFactory.getInstance.getNBeFormService.findFDNBySql(sb2.ToString)
                    DV2 = New DataView(DS2.EFORM_DECISION_NUMERICAL)
                    If Not DV2 Is Nothing Then
                        For j = 0 To DV2.Count - 1
                            If DV2.Item(j).Row.Item("DECISION_FROM").ToString = DV2.Item(j).Row.Item("DECISION_TO").ToString Then
                                If num = DV2.Item(j).Row.Item("DECISION_FROM").ToString Then
                                    sn = DV2.Item(j).Row.Item("STANDARD_DECISION_SN").ToString
                                End If
                            ElseIf Convert.ToInt32(num) > Convert.ToInt32(DV2.Item(j).Row.Item("DECISION_FROM").ToString) And Convert.ToInt32(num) <= Convert.ToInt32(DV2.Item(j).Row.Item("DECISION_TO").ToString) Then
                                sn = DV2.Item(j).Row.Item("STANDARD_DECISION_SN").ToString
                            End If
                            If arr = "" Then
                                arr = sn
                            ElseIf Not InStr(arr, sn) > -1 Then
                                arr = arr & "_" & sn
                            End If
                        Next

End If
Next

            End If
            Dim A() As String
            Dim x As String
            Dim combsn As String
            Dim m As Integer, n As Integer
            Dim s As Integer
            A = Split(arr, "_")
            For m = 0 To UBound(A)
                For n = 1 To UBound(A) - m
                    Do While A(m) > A(m + n) 'р程?程
                        x = A(m)
                        A(m) = A(m + n)
                        A(m + n) = x
                    Loop
                Next n
            Next m
            'For s = 0 To UBound(A)
            '    If s = 0 Then
            '        combsn = A(s)
            '    Else
            '        combsn = combsn & "_" & A(s)
            '    End If
            'Next

            Dim CSS As INBeFormService
            Dim DSS As New NBeFormDataset
            Dim DSS1 As New NBeFormDataset
            Dim HR_EMP As HRDataSet.HR_EMP_EFORMRow
            Dim HR As New HrServiceImpl
            Dim DR As NBeFormDataset.EFORM_GROUPMEMBERRow
            Dim DRDEPT As HRDataSet.HR_DEPT_EFORMRow

            Dim DRD As NBeFormDataset.EFORM_CATEGORY_COSIGN_GROUPSRow
            Dim DMD As NBeFormDataset.EFORM_COSIGN_GROUP_MEMBERSRow
            Dim DRMD As NBeFormDataset.EFORM_COSIGN_GROUPSRow
            For s = 0 To UBound(A)
                combsn = A(s)
                CSS = New NBeFormServiceImpl
                DSS = CSS.findCOSIGNDECISIONBysn(combsn, COMID)
                For i = 0 To DSS.EFORM_CATEGORY_COSIGN_GROUPS.Count - 1
                    DRD = DSS.EFORM_CATEGORY_COSIGN_GROUPS.Rows(i)
                    DSS = CSS.findCosignGroupByKey(DRD.COUNTERSIGN_GROUP_ID, "", "")
                    For m = 0 To DSS.EFORM_COSIGN_GROUP_MEMBERS.Rows.Count - 1
                        DMD = DSS.EFORM_COSIGN_GROUP_MEMBERS.Rows(m)
                        DRMD = DSS.EFORM_COSIGN_GROUPS.Rows(0)
                        If DMD.IsFLAGNull Then
                            DR = DSS1.EFORM_GROUPMEMBER.NewEFORM_GROUPMEMBERRow
                            DR.ID = WebUtil.generateSequenceNumber(1)
                            HR_EMP = HR.findEmployeeByEmpNoComid(DMD.EMP_NO, DRMD.COMPANY_ID)
                            DR.GROUPID = GROUPID
                            DR.EMPNO = HR_EMP.EMP_NO
                            DR.UPDATEEMPNO = HR_EMP.NAME
                            DR.DEPT_NO = HR_EMP.DEPT_NO
                            DR.SIGNTYPE = HR_EMP.COMPANY_ID
                            DR.ACTION_SEQ = DMD.ACTION_SEQ
                            DR.APPROVAL_TYPE = DMD.APPROVAL_TYPE
                            DRDEPT = HrServiceFactory.getInstance().findDepartmentByDeptNo(HR_EMP.DEPT_NO, DR.SIGNTYPE)
                            DR.DEPT_NAME = DRDEPT.FULL_NAME
                            DSS1.EFORM_GROUPMEMBER.Rows.Add(DR)
                        End If
                    Next
                    CSS.OnlySave(DSS1)
                Next
            Next

        Catch ex As Exception
            Throw ex
        Finally
            DS1 = Nothing
            CS1 = Nothing
            DV1 = Nothing
            CS2 = Nothing
            DS2 = Nothing
        End Try
    End Function

数据操纵层的编写：

Public Class NBeFormDAOImpl : Inherits DAOBaseOracleSqlImpl : Implements INBeFormDAO

    Public Sub New(ByVal cn As OracleClient.OracleConnection)
        MyBase.New(cn)
    End Sub

''对数据库的操作

       Protected Friend Function fillQueryResult(ByRef ds As DataSet, ByVal tableName As String, ByVal sql As String) As Integer Implements IDAOBase.fillQueryResult
            Dim scmd As OracleClient.OracleCommand
            Dim param As OracleClient.OracleParameter
            Dim adapter As OracleClient.OracleDataAdapter
            Try
                scmd = New OracleClient.OracleCommand(sql)
                scmd.Connection = cn
                If Not tn Is Nothing Then
                    scmd.Transaction = tn
                End If
                adapter = New OracleClient.OracleDataAdapter(scmd)
                adapter.Fill(ds, tableName)
                Return ds.Tables(tableName).Rows.Count
            Finally
                param = Nothing
                scmd = Nothing
                adapter = Nothing
            End Try
        End Function