clssinglerow.vb

vb2005电子书籍——vb.net项目开发宝典源码

Imports System.Data.SqlClient


Namespace wsbgxt


    Public Class clsSingleRow
        Shared conn As SqlConnection
        Shared cmd As SqlCommand
        Public Shared errmsg As String
        '***********************************************************************
        'Class : 单行数据的简单操纵，包括增、删、改、查单行数据
        '***********************************************************************


        '----------------------------------------------
        '获取单行数据
        '参数：
        '   strTbName, 表名
        '   strCol(), 字段名数组
        '   strVal(), 字段值数组(字段值已经过格式化，如字符串有''限定符、日期有''限定符)
        '   strCondi, 可选的查询条件
        '返回值：
        '   Boolean


        '----------------------------------------------
        '添加单行数据
        '参数：
        '   strTbName, 表名
        '   strCol(), 字段名数组
        '   strVal(), 字段值数组(字段值已经过格式化，如字符串有''限定符、日期有''限定符)
        '返回值：
        '   Boolean
        Shared Function AddRow(ByVal strTbName As String, _
                               ByRef strCol() As String, _
                               ByRef strVal() As String) As Boolean
            Dim i As Integer, lbReturn As Boolean

            Dim cmdstr As String

            '参数检查
            If strTbName = "" Then
                errmsg = "表名为空."
                Return False
            End If
            If strCol.Length < 2 Then
                errmsg = "字段名数组为空(除id外)."
                Return False
            End If
            If strCol.Length <> strVal.Length Then
                errmsg = "字段名数组与字段值数组长度不一致."
                Return False
            End If

            '构造SQL Select语句
            cmdstr = "INSERT INTO " + strTbName + " (" + strCol(1)
            For i = 2 To strCol.Length - 1
                cmdstr += (", " + strCol(i))
            Next
            cmdstr += ") VALUES (" + strVal(1)
            For i = 2 To strVal.Length - 1
                cmdstr += (", " + strVal(i))
            Next
            cmdstr += ")"

            PublicMod.ConnectDB(conn)
            If conn Is Nothing Then Return False

            Try
                cmd = New SqlCommand(cmdstr, conn)
                Dim n As Integer = cmd.ExecuteNonQuery()
                If n = 1 Then
                    lbReturn = True
                Else
                    errmsg = "添加不成功."
                    lbReturn = False
                End If
            Catch sqlex As SqlException
                errmsg = sqlex.Message
                lbReturn = False
            Finally
                PublicMod.DisConnect(conn)
            End Try

            Return lbReturn
        End Function

        '----------------------------------------------
        '删除单行数据
        '参数：
        '   strTbName, 表名
        '   strCol(), 字段名数组
        '   strVal(), 字段值数组(字段值已经过格式化，如字符串有''限定符、日期有''限定符)
        '   strCondi, 可选的查询条件
        '返回值：
        '   Boolean
        Shared Function DeleteRow(ByVal strTbName As String, _
                           ByRef strCol() As String, _
                           ByRef strVal() As String, _
                           Optional ByVal strCondi As String = "") As Boolean
            Dim cmdstr As String
            Dim lbReturn As Boolean
            '参数检查
            If strTbName = "" Then
                errmsg = "表名为空."
                Return False
            End If
            If strCol.Length < 2 Then
                errmsg = "字段名数组为空(除id外)."
                Return False
            End If
            If strCol.Length <> strVal.Length Then
                errmsg = "字段名数组与字段值数组长度不一致."
                Return False
            End If

            '构造SQL Select语句
            If strCondi = "" Then
                strCondi = "id = " + strVal(0)  '默认检索条件：按id检索
            End If
            cmdstr = "DELETE FROM " + strTbName + " WHERE " + strCondi

            PublicMod.ConnectDB(conn)
            If conn Is Nothing Then Return False

            Try
                cmd = New SqlCommand(cmdstr, conn)
                Dim n As Integer = cmd.ExecuteNonQuery()
                If n = 1 Then
                    lbReturn = True
                Else
                    errmsg = "删除不成功."
                    lbReturn = False
                End If
            Catch sqlex As SqlException
                errmsg = sqlex.Message
                lbReturn = False
            Finally
                PublicMod.DisConnect(conn)
            End Try
            Return lbReturn
        End Function

        '----------------------------------------------
        '更新单行数据
        '参数：
        '   strTbName, 表名
        '   strCol(), 字段名数组
        '   strVal(), 字段值数组(字段值已经过格式化，如字符串有''限定符、日期有''限定符)
        '   strCondi, 可选的查询条件
        '返回值：
        '   Boolean
        Shared Function UpdateRow(ByVal strTbName As String, _
                               ByRef strCol() As String, _
                               ByRef strVal() As String, _
                               Optional ByVal strCondi As String = "") As Boolean
            Dim i As Integer, lbReturn As Boolean
            Dim cmdstr As String

            '参数检查
            If strTbName = "" Then
                errmsg = "表名为空."
                Return False
            End If
            If strCol.Length < 2 Then
                errmsg = "字段名数组为空(除id外)."
                Return False
            End If
            If strCol.Length <> strVal.Length Then
                errmsg = "字段名数组与字段值数组长度不一致."
                Return False
            End If

            '构造SQL Select语句
            If strCondi = "" Then
                strCondi = "id = " + strVal(0)  '默认检索条件：按id检索
            End If
            cmdstr = "UPDATE " + strTbName + " SET " + strCol(1) + "=" + strVal(1)
            For i = 2 To strCol.Length - 1
                cmdstr += ", " + (strCol(i) + "=" + strVal(i))
            Next
            cmdstr += " WHERE " + strCondi

            PublicMod.ConnectDB(conn)
            If conn Is Nothing Then Return False

            Try
                cmd = New SqlCommand(cmdstr, conn)
                Dim n As Integer = cmd.ExecuteNonQuery()
                If n = 1 Then
                    lbReturn = True
                Else
                    errmsg = "更新不成功."
                    lbReturn = False
                End If
            Catch sqlex As SqlException
                errmsg = sqlex.Message
                lbReturn = False
            Finally
                PublicMod.DisConnect(conn)
            End Try

            Return lbReturn
        End Function

    End Class

End Namespace