一个数据库操作的类.0.1 (vb.net)

Public Class Daxecutor

    Private Connstr As String

    Private Conn As SqlClient.SqlConnection

    Private Rs As SqlClient.SqlDataReader

    Private Sqlstr As String

    Private Rcmd As SqlClient.SqlCommand

    Private Sb As New System.Text.StringBuilder

    Private Sqlpar As New SqlClient.SqlParameter

 

    Friend Sub New()

 

    End Sub

 

    '*******************************************************************

    '执行不返回sql

    Friend Sub Execute()

        Rcmd.Connection.Open()

        Rcmd.CommandText = Sqlstr

        Rcmd.ExecuteNonQuery()

    End Sub

 

    '*******************************************************************

    '执行单个返回值sql

    Friend Function GetSingleValue() As Object

        Rcmd.Connection.Open()

        Rcmd.CommandText = Sqlstr

        Return Rcmd.ExecuteScalar()

    End Function

 

    '*******************************************************************

    '执行结果集sql

    Friend Function GetQueryds() As SqlClient.SqlDataReader

        Rcmd.Connection.Open()

        Rcmd.CommandText = Sqlstr

        Rs = Rcmd.ExecuteReader(CommandBehavior.SingleResult)

        Return Rs

    End Function

    '*******************************************************************

    '构造参数

    Friend Overloads Sub Addpar(ByVal SqlPar As String, ByVal Valpar As String)

        Rcmd.Parameters.Add(SqlPar, Valpar)

    End Sub

 

    '*******************************************************************

    'sql语句属性

    Public WriteOnly Property Istext() As Byte

        Set(ByVal Value As Byte)

            If Value = 1 Then

                Rcmd.CommandType = CommandType.Text

            Else

                Rcmd.CommandType = CommandType.StoredProcedure

            End If

        End Set

    End Property

    '*******************************************************************

    'sql语句

    Public Property Sql() As String

        Get

            Return Sqlstr

        End Get

        Set(ByVal Value As String)

            Sqlstr = Value

        End Set

    End Property

 

    '*******************************************************************

    '启动数据库对象

    Friend Sub Open()

        Connstr = ConfigurationSettings.AppSettings("SQLConnString")

        Conn = New SqlClient.SqlConnection(Connstr)

        Rcmd = New SqlClient.SqlCommand(Sqlstr, Conn)

    End Sub

 

    '*******************************************************************

    '终止数据库对象

    Friend Sub Terminate()

        Conn.Close()

        Rcmd.Connection.Close()

        Conn = Nothing

        Rcmd = Nothing

    End Sub

End Class

 

用法一：返回单个值

        Dim Sql As New Daxecutor

        Dim User_t, Pass_t As String

        Dim ck As Object

        User_t = Username.Text

        Pass_t = Password.Text

        'Sqlstr = "select top 1 id from Datadmin_user where Auser='" & User_t & "' and Apass='" & Pass_t & "'"

        With Sql

            .Open()

            .Sql = "select top 1 id from Datadmin_user where Auser=@User_t and Apass=@Pass_t"

            .Istext = 1

            .Addpar("@User_t", User_t)

            .Addpar("@Pass_t", Pass_t)

            ck = .GetSingleValue()

            .Terminate()

        End With

        Sql = Nothing

 

用法二：插入操作

        Dim Sql As New Daxecutor

 

        With Sql

            .Open()

            .Sql = "insert into Npaper_articles (classtype,titles,content,Istop,Keywords,Keyperson) values (" _

                    & "@Classtypes," _

                    & "@Title," _

                    & "@contents," _

                    & "@Istops," _

                    & "@keyw," _

                    & "@keyp)"

            .Istext = 1

            .Addpar("@Classtypes", Classtypes)

            .Addpar("@Title", Title)

            .Addpar("@contents", contents)

            .Addpar("@Istops", Istops)

            .Addpar("@keyw", Keyw)

            .Addpar("@keyp", Keyp)

            .Execute()

            .Terminate()

        End With

        Sql = Nothing

 

用法三：数据集操作

        Dim Rs As SqlClient.SqlDataReader

        Dim Sql As New Daxecutor

        With Sql

            .Open()

            .Sql = "select id,Mname,classtype from Npaper_submenu"

            .Istext = 1

            Rs = .GetQueryds()

        End With

        'Sqlstr = "select id,Mname,classtype from Npaper_submenu"

        'Rcmd = New SqlClient.SqlCommand(Sqlstr, Conn)

        'Rcmd.Connection.Open()

        'Rs = Rcmd.ExecuteReader(CommandBehavior.CloseConnection)

 

        Do While Rs.Read = True

            Classtype.Items.Add(New ListItem(Rs.GetString(1), Rs.GetByte(0).ToString))

        Loop

        Rs.Close()

        Sql.Terminate()

        Sql = Nothing