自動化程式@點部落

 

建立資料庫連線類別

Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data

Public Class DataHelper
    Private _connString As String = Nothing

#Region "Constructors"
    Sub New()
        ConnectionString = "ConnectionString"
    End Sub

    Sub New(ByVal ConnStrName As String)
        ConnectionString = ConnStrName
    End Sub
#End Region

#Region "Properties"
    Public Property ConnectionString() As String
        Get
            If _connString = "" Then
                _connString = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
            End If
            Return _connString
        End Get
        Set(ByVal value As String)
            _connString = ConfigurationManager.ConnectionStrings(value).ConnectionString
        End Set
    End Property
#End Region

#Region "Common Methods"

    ' 判斷輸入值是否為空
   Public Function GetNull(ByVal Field As Object) As Object
        If Not Field Is Nothing Then
            If Field.GetType().ToString = "System.String" Then
                If Field.ToString = "" Then Return DBNull.Value
            End If
            Return Field
        Else
            Return DBNull.Value
        End If
    End Function

    Public Function Checkqstr(ByVal str As String) As String
        str = str.Replace("'", "''")
        str = str.Replace("%", "")
        Return str
    End Function

    Public Function ExecuteReader(ByVal sqlCmd As String) As IDataReader
        Return CType(SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, sqlCmd), IDataReader)
    End Function
#End Region

End Class

 

給資料庫去計算公式，CalculateStr可放入一串計算式，db要放入宣告的上例的資料庫連線類別。

 Public Function SelectDbCalculate(ByVal db As DataHelper, ByVal CalculateStr As String) As Single
            Try
                Dim dr As SqlClient.SqlDataReader = db.ExecuteReader("select " & CalculateStr & "as ANS")
                Do While dr.Read
                    Return dr("ANS")
                Loop
            Catch ex As Exception
                Return 0
            End Try
  End Function