三層式系統架構－資料存取層

2009-11-17

此介紹資料存取層需要的程式碼，

我個人對於資料存取層的定義為一個Tabel對映一個類別，

此類別需定義針對Table的查詢、新增、修改、刪除等資料操作函式。

範例

有一Table如下

欄位名稱	資料型態
CustomerID	Varchar(10)
CompanyName	Varchar(50)

1.建立連線字串變數



2.查詢函式-關於查詢函式,依需求來增加各種不同的查詢函式

      Try
         Using SQLConn As New SqlConnection(_CnStr)
            Dim SQL As String = " SELECT customerid,companyname FROM customers "
            Dim SqlCmd As New SqlCommand(SQL, SQLConn)
            SQLConn.Open()
            Dim DT As New DataTable
            DT.Load(SqlCmd.ExecuteReader)
            Return DT
         End Using
      Catch ex As Exception
         Throw New Exception(ex.Message)
      End Try
   End Function

3.新增、修改函式

關於新增、修改函式中，都會傳入一個商業邏輯類別，

函式裡會依類別的欄位屬性取出所需用到的資料(此部份純個人想法)

      Try
         Dim SQL As String = " INSERT INTO customers (customerid,CompanyName) values ('@customerid','@CompanyName') "
         SQL = SQL.Replace("@customerid", Cols.CustomerID)
         SQL = SQL.Replace("@CompanyName", Cols.CompanyName)

         Using SQLConn As New SqlConnection(_CnStr)
            SQLConn.Open()
            Dim SqlCmd As New SqlCommand(SQL, SQLConn)
            Return SqlCmd.ExecuteNonQuery()
         End Using
      Catch ex As Exception
         Throw New Exception(ex.Message)
      End Try
   End Function

   Public Shared Function Update(ByVal Cols As bllCustomers) As Integer
      Try
         Dim SQL As String = " UPDATE customers SET CompanyName='@CompanyName' WHERE customerid='@customerid' "
         SQL = SQL.Replace("@customerid", Cols.CustomerID)
         SQL = SQL.Replace("@CompanyName", Cols.CompanyName)

         Using SQLConn As New SqlConnection(_CnStr)
            SQLConn.Open()
            Dim SqlCmd As New SqlCommand(SQL, SQLConn)
            Return SqlCmd.ExecuteNonQuery()
         End Using
      Catch ex As Exception
         Throw New Exception(ex.Message)
      End Try
   End Function

4.刪除函式-需傳入Key值,若此Table的Key值有二個,則建立二個傳入參數

      Try
         Dim SQL As String = " DELETE customers WHERE customerid='@customerid' "
         SQL = SQL.Replace("@customerid", Code)

         Using SQLConn As New SqlConnection(_CnStr)
            SQLConn.Open()
            Dim SqlCmd As New SqlCommand(SQL, SQLConn)
            Return SqlCmd.ExecuteNonQuery()
         End Using
      Catch ex As Exception
         Throw New Exception(ex.Message)
      End Try
   End Function