dbtable.vb

对现代企业来说

            REM Return successfull
            r.Close()
            Return True

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function
    Public Function FindRecord(ByRef Row As ValueType) As Boolean
        Dim ErrLoc As String = ClassName + ".FindRecord"
        Dim SQL As String


        SQL = "select * "
        SQL &= "FROM "
        SQL &= ClassName + " "
        SQL &= "WHERE "
        'SQL &= TableIndex() + "= " + RowIndex.ToString + " "

        Try

            Dim i As Integer
            Dim Seperator As String = ""

            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            For i = 0 To fields.Length - 1
                If fields(i).Name <> TableIndex() Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row) Is Nothing Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= SQLStringValue(fields(i).GetValue(Row).ToString)
                                Seperator = "AND "
                            End If
                        Case "System.DateTime"
                            If Not DATE_To_DBSTR(fields(i).GetValue(Row)) = NullDate Then
                                SQL &= Seperator & fields(i).Name & " = "
                                'SQL &= " #" & DATE_To_DBSTR(fields(i).GetValue(Row)) & "# "
                                SQL &= SQLDateValue(fields(i).GetValue(Row))
                                Seperator = "AND "
                            End If
                        Case "System.Boolean"
                            If Not fields(i).GetValue(Row) Is Nothing Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= SQLBooleanValue(fields(i).GetValue(Row))
                                Seperator = "AND "
                            End If
                        Case "System.Int32"
                            If Not fields(i).GetValue(Row) = -1 Then
                                SQL &= Seperator & " " & CStr(fields(i).GetValue(Row)) & " "
                                Seperator = "AND "
                            End If
                        Case Else
                            SQL &= fields(i).GetValue(Row).ToString & " "
                    End Select
                End If
            Next

            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)

            For i = 0 To fields.Length - 1
                If Not ds.Tables(ClassName).Rows(0)(fields(i).Name) Is DBNull.Value Then
                    fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                Else
                    fields(i).SetValue(Row, Nothing)
                End If
            Next

            Return True

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function


    Public Function GetRecord(ByRef Row As ValueType, ByVal RowIndex As Integer) As Boolean
        Dim ErrLoc As String = ClassName + ".GetRecord"
        Dim SQL As String

        SQL = "select * "
        SQL &= "FROM "
        SQL &= ClassName + " "
        SQL &= "WHERE "
        SQL &= TableIndex() + "= " + RowIndex.ToString + " "

        Try
            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)

            Dim i As Integer

            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields
            For i = 0 To fields.Length - 1
                If Not ds.Tables(ClassName).Rows(0)(fields(i).Name) Is DBNull.Value Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                        Case "System.DateTime"
                            fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                        Case "System.Boolean"
                            If ds.Tables(ClassName).Rows(0)(fields(i).Name) = BOOLEAN_To_DBSTR(True) Then
                                fields(i).SetValue(Row, True)
                            Else
                                fields(i).SetValue(Row, False)
                            End If
                        Case "System.Int32"
                            fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                        Case Else
                            fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                    End Select
                Else
                    fields(i).SetValue(Row, Nothing)
                End If
            Next

            Return True

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function
    Public Function GetLastRecord(ByRef Row As ValueType) As Boolean
        Dim ErrLoc As String = ClassName + ".GetRecord"
        Dim SQL As String

        SQL = "select * "
        SQL &= "FROM "
        SQL &= ClassName + " "
        SQL &= "ORDER BY "
        SQL &= TableIndex() + " DESC "

        Try
            Dim ds As DataSet = New DataSet
            ds = SQLDS(SQL)
            If ds.Tables(ClassName).Rows.Count = 0 Then Return False

            Dim i As Integer

            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields
            For i = 0 To fields.Length - 1
                If Not ds.Tables(ClassName).Rows(0)(fields(i).Name) Is DBNull.Value Then
                    fields(i).SetValue(Row, ds.Tables(ClassName).Rows(0)(fields(i).Name))
                Else
                    fields(i).SetValue(Row, Nothing)
                End If
            Next

            Return True

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function
    Public Function PutRecord(ByRef Row As ValueType, ByVal RowIndex As Integer) As Boolean
        Dim ErrLoc As String = ClassName + ".PutRecord"
        Dim SQL As String

        Try
            Dim i As Integer
            Dim Seperator As String = ""
            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            SQL = "UPDATE " + ClassName + " SET "
            For i = 0 To fields.Length - 1
                If fields(i).Name <> TableIndex() Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row) Is Nothing Then
                                'If Not fields(i).GetValue(Row).ToString = "" Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= SQLStringValue(fields(i).GetValue(Row).ToString)
                                Seperator = ","
                            End If
                        Case "System.DateTime"
                            SQL &= Seperator & fields(i).Name & " = "
                            SQL &= SQLDateValue(fields(i).GetValue(Row))
                            Seperator = ","
                        Case "System.Boolean"
                            SQL &= Seperator & fields(i).Name & " = "
                            SQL &= SQLBooleanValue(fields(i).GetValue(Row))
                            Seperator = ","
                        Case "System.Int32"
                            If fields(i).GetValue(Row) <> -1 Then
                                SQL &= Seperator & fields(i).Name & " = "
                                SQL &= " " & CStr(fields(i).GetValue(Row)) & " "
                                Seperator = ","
                            End If
                        Case Else
                            'SQL &= fields(i).GetValue(Row).ToString & " "
                    End Select
                End If
            Next
            SQL &= "WHERE "
            SQL &= TableIndex() + "= " + RowIndex.ToString + " "

            If ExecuteSQL(SQL) Then
                Return True
            Else
                Return False
            End If


        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try

    End Function
    Public Function InsertRecord(ByRef Row As ValueType) As Boolean
        Dim ErrLoc As String = ClassName + ".InsertRecord"
        Dim SQL As String

        Try
            Dim i As Integer
            Dim Seperator As String = ""
            Dim fields() As FieldInfo
            fields = Row.GetType.GetFields

            SQL = "INSERT INTO " + ClassName + " ( "
            For i = 0 To fields.Length - 1
                If fields(i).Name.ToUpper <> TableIndex().ToUpper Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row) Is Nothing Then
                                SQL &= Seperator & fields(i).Name & " "
                                Seperator = ","
                            End If
                        Case "System.DateTime"
                            SQL &= Seperator & fields(i).Name & " "
                            Seperator = ","
                        Case "System.Boolean"
                            SQL &= Seperator & fields(i).Name & " "
                            Seperator = ","
                        Case "System.Int32"
                            If Not fields(i).GetValue(Row) = -1 Then
                                SQL &= Seperator & fields(i).Name & " "
                                Seperator = ","
                            End If
                        Case Else
                            SQL &= Seperator & fields(i).Name & " "
                            Seperator = ","
                    End Select


                End If
            Next
            SQL &= ") VALUES ( "
            Seperator = ""
            For i = 0 To fields.Length - 1
                If fields(i).Name.ToUpper <> TableIndex().ToUpper Then
                    Select Case fields(i).FieldType.ToString
                        Case "System.String"
                            If Not fields(i).GetValue(Row) Is Nothing Then
                                SQL &= Seperator & SQLStringValue(fields(i).GetValue(Row).ToString)
                                Seperator = ","
                            End If
                        Case "System.DateTime"
                            SQL &= Seperator & SQLDateValue(fields(i).GetValue(Row))
                            Seperator = ","
                        Case "System.Boolean"
                            SQL &= Seperator & SQLBooleanValue(fields(i).GetValue(Row))
                            Seperator = ","
                        Case "System.Int32"
                            If Not fields(i).GetValue(Row) = -1 Then
                                SQL &= Seperator & " " & CStr(fields(i).GetValue(Row)) & " "
                                Seperator = ","
                            End If
                        Case Else
                            SQL &= Seperator & fields(i).GetValue(Row).ToString & " "
                            Seperator = ","
                    End Select
                End If
            Next
            SQL += ") "

            If ExecuteSQL(SQL) Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            ErrMsg = UnHandledError(ex.ToString(), ErrLoc, SQL)
            Return False
        End Try
    End Function

End Class