cdataservice.vb

The Management Information System of Library using .NET

Imports System.Data
Imports System.Data.SqlClient
Public Class CdataService
    Protected Connection As SqlConnection
    Private m_DSN As String
    '构建一个带有参数的sqlcommand
    Private Overloads Function CreateCommand(ByVal sprocName As String, ByVal parameters() As IDataParameter) As SqlCommand
        Dim command As SqlCommand = New SqlCommand(sprocName, Connection)
        command.CommandType = CommandType.StoredProcedure
        Dim para As SqlParameter
        For Each para In parameters
            command.Parameters.Add(para)
        Next
        command.Parameters.Add(New SqlParameter("Return Value", _
        SqlDbType.Int, _
        4, _
        System.Data.ParameterDirection.ReturnValue, _
         False, _
          0, _
          0, _
          String.Empty, _
          DataRowVersion.Default, _
          Nothing))
        Return command
    End Function
    '构建一个没有参数的sqlcommand
    Private Overloads Function CreateCommand(ByVal sprocName As String) As SqlCommand
        Dim command As SqlCommand = New SqlCommand(sprocName, Connection)
        command.CommandType = CommandType.StoredProcedure

        command.Parameters.Add(New SqlParameter("Return Value", _
        SqlDbType.Int, _
        4, _
        System.Data.ParameterDirection.ReturnValue, _
         False, _
          0, _
          0, _
          String.Empty, _
          DataRowVersion.Default, _
          Nothing))
        Return command
    End Function

    Public ReadOnly Property DSN() As String
        Get
            Return m_DSN
        End Get
    End Property
    Public Overloads Function RunSP(ByVal sprocName As String) As Int32
        Dim result As Int32
        Connection.Open()
        Dim command As SqlCommand = Me.CreateCommand(sprocName)
        command.ExecuteNonQuery()
        result = CType(command.Parameters("Return Value").Value, Int32)
        Me.Connection.Close()
        Return result
    End Function
    Public Overloads Function RunSP(ByVal sprocName As String, ByVal parameters() As IDataParameter) As Int32
        Dim result As Int32
        Connection.Open()
        Dim command As SqlCommand = Me.CreateCommand(sprocName, parameters)
        command.ExecuteNonQuery()
        result = CType(command.Parameters("Return Value").Value, Int32)
        Me.Connection.Close()
        Return result
    End Function

    Public Overloads Function RunSP(ByVal sprocName As String, _
   ByVal parameters() As IDataParameter, ByVal DataSet As DataSet) As Int32
        Dim result As Int32
        Dim sqlDA As New SqlDataAdapter
        Connection.Open()
        sqlDA.SelectCommand = Me.CreateCommand(sprocName, parameters)
        sqlDA.Fill(DataSet, "SourceTable")
        result = CType(sqlDA.SelectCommand.Parameters("Return Value").Value, Int32)
        Connection.Close()
        Return result
    End Function
    Public Overloads Function RunSP(ByVal sprocName As String, _
    ByRef DataSet1 As DataSet) As Int32
        Dim result As Int32
        Dim sqlDA As New SqlDataAdapter
        Connection.Open()
        sqlDA.SelectCommand = Me.CreateCommand(sprocName)
        sqlDA.Fill(DataSet1)
        result = CType(sqlDA.SelectCommand.Parameters("Return Value").Value, Int32)
        Connection.Close()
        Return result
    End Function
    Public Overloads Function RunSP(ByVal sprocName As String, _
       ByVal parameters() As IDataParameter, ByRef DataSet As DataSet, ByVal TableName As String) As Int32
        Dim result As Int32
        Dim sqlDA As New SqlDataAdapter
        Connection.Open()

        sqlDA.SelectCommand = Me.CreateCommand(sprocName, parameters)
        sqlDA.Fill(DataSet, TableName)
        result = CType(sqlDA.SelectCommand.Parameters("Return Value").Value, Int32)
        Connection.Close()
        Return result
    End Function
    Public Function Construct(ByVal ConstructString As String)
        m_DSN = ConstructString
    End Function
    Public Function Activate()
        Connection = New SqlConnection(m_DSN)
    End Function
    Public Function Deactivate()
        Connection = Nothing
    End Function

    Public Sub New()

    End Sub
    Public Sub New(ByVal ConstrucString As String)
        Construct(ConstrucString)
        Me.Activate()
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
        Me.Deactivate()
    End Sub
End Class