OpenAccess ORM for Oracle 實戰演練 Day 18

DAY 18

OpenAccess ORM for Oracle 實戰演練系列第 14 篇

oracle vb linq orm openaccess

2013-10-10 23:36:38

2237 瀏覽

取得OpenAccess ORM轉換、執行的Oracle Sql Statements
透過 ORM 框架存取資料庫，雖然目的就是我們可以省去多撰寫一種語言（Sql Script）的麻煩，加快開發速度，但是畢竟資料庫可以接受的語法還是 Sql，所以我們還是要了解 OpenAccess ORM 幫我們轉換、真實執行的 Sql 是什麼。

PS. 小孩報到的第一天，真是無比忙碌，差點鐵人賽就要中斷了，好不容易寫完要貼文，又遇上 ithelp 死站，嚇的我一身冷汗……

要查閱 OpenAccess ORM 所轉換出來的 Sql 語法，要透過 StringWriter 物件處理，我們切換到 Model 專案，在 ProductMgmt 類別中加一些處理：

Public Class ProductsMgmt
    Private cxt As SecondModel
    Private output As IO.StringWriter
    Public Sub New()
        cxt = New SecondModel()
        output = New IO.StringWriter()
        cxt.Log = output
    End Sub

    Public Function TraceSQLStatements() As String
        If output IsNot Nothing Then
            Return output.ToString()
        Else
            Return Nothing
        End If
    End Function

    Public Function GetGiantProducts() As IList(Of Products)
        Dim query = From x In cxt.Products
                    Where x.PRODUCTNAME.Contains("巨人")
                    Select x
        Return query.ToList()
    End Function

    Public Function DeleteProduct(Id As Long) As Boolean
        Dim product = GetProduct(Id)
        If product Is Nothing Then
            Throw New System.NullReferenceException(String.Format("查無產品編號：{0} 的資料。", Id))
        End If
        cxt.Delete(product)
        cxt.SaveChanges()
        Return True
    End Function

    Public Function GetProduct(ByVal Id As Long) As Products
        Dim product = (From x In cxt.Products
                              Where x.PRODUCTID = Id
                              Select x).FirstOrDefault()
        Return product
    End Function
End Class

和先前版本的差異，主要在於建構子裡面，我們在建立物件容器執行個體後，再建立一個 StringWirter 執行個體，並指派給物件容器的 Log 屬性，然後再加入一個新函式 TraceSQLStatements()，會調用剛剛建立的 StringWriter 物件之 ToString() 函式。

照例，我們建立一個單元測試來驗證 TraceSQLStatements() 是否能正確執行：

<TestMethod()> _
Public Sub TraceSQLStatementsTest()
    Dim target As ProductsMgmt = New ProductsMgmt()
    Dim actual As IList(Of Products)
    actual = target.GetGiantProducts
    Assert.IsTrue(actual.Any)
    Assert.IsNotNull(target.TraceSQLStatements())
End Sub

當然，測試一定要通過，接著我們到 Web 專案中，新增 ShowTraceSQLStatements.aspx 頁面，加入以下程式碼：

<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="ShowTraceSQLStatements.aspx.vb" Inherits="OpenAccessWebApp01.ShowTraceSQLStatements" %>


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>


    <form id="form1" runat="server">
        <div>
            <asp:Button ID="btnGetGiant" runat="server" Text="取得資料並顯示Sql" />
            <br />
            輸入要刪除的 ProductId：<asp:TextBox ID="txtId" runat="server"></asp:TextBox>
            <asp:Button ID="btnDelete" runat="server" Text="刪除資料" />
            <hr />
            實際執行的SQL Statements：<br />
            <asp:Label ID="lblSQL" runat="server" Text=""></asp:Label>
            <hr />
            <asp:GridView ID="gvList" runat="server"></asp:GridView>
        </div>
    </form>

ShowTraceSQLStatements.aspx.vb

Imports OpenAccessWebApp01Model

Public Class ShowTraceSQLStatements
    Inherits System.Web.UI.Page

    Protected Sub btnGetGiant_Click(sender As Object, e As EventArgs) Handles btnGetGiant.Click
        DataBinding(New ProductsMgmt())
    End Sub
    Private Sub DataBinding(ByVal bo As ProductsMgmt)
        gvList.DataSource = bo.GetGiantProducts()
        gvList.DataBind()
        lblSQL.Text = bo.TraceSQLStatements()
    End Sub

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
        Dim bo = New ProductsMgmt()
        If  IsNumeric(txtId.Text) Then
            bo.DeleteProduct(txtId.Text)
        End If
        DataBinding(bo)
    End Sub
End Class

執行結果如下：

上面 Gif 圖檔的連結：http://i.minus.com/inZs3VWqqGaNt.Gif