取得OpenAccess ORM轉換、執行的Oracle Sql Statements

2013-10-10

1478
0
2013-10-13

DotBlogs Tags: LINQ, Orm, OpenAccess, Oracle, VB.NET, 隨手記

透過 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" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <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>
</body>
</html>

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

執行結果如下：

CC10-10-2013-4

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

--------
沒什麼特別的~
不過是一些筆記而已

回首頁