通过fsharp探索Enterprise Library DataBase 1.2

浏览数：30 / 时间：2015年06月12日
    上一次讲到Enterprise Library中Data Access 模块的配置以及简单SQL语句和存储过程的执行。在探索的过程中应用Fsharp语言和交互环境能够马上看到结果，这感觉真的是非常通透。
1.提高数据库操作的复杂性，加入参数的变化，这一点和ADO的操作没有太多的不同。
SQL语句带参数
    let sqlStatement = "select top 1 * from OrderList where State like @state"
    using(defaultDB.GetSqlStringCommand(sqlStatement))(fun sqlCmd ->
        defaultDB.AddInParameter(sqlCmd, "state", DbType.String, "New York")
        using(defaultDB.ExecuteReader(sqlCmd))(fun sqlReader ->
            DisplayRowValue sqlReader))

存储过程带参数

    using(defaultDB.GetStoredProcCommand("ListOrdersByState"))(fun sprocCmd ->
        defaultDB.AddInParameter(sprocCmd, "state", DbType.String, "New York")
        using(defaultDB.ExecuteReader(sprocCmd))(fun sprocReader ->
            DisplayRowValue sprocReader))
2.对象映射
    Enterprise Library 还带有一种简单的对象映射机制，这种映射并不对称，只用于获取结果。其并不等同与数据库ORM。在取得数据结果并将其映射到对象时，这一最常见的实用场景中，用来非常的顺手。对于那种较复杂的带有层次关系的多对象实体，则需要加入代码进行定制，暂未深究。
结果对象类
type Product() = 
    member val ID:int=0 with get, set
    member val Name:string="" with get, set
    member val Description:string="" with get, set

通过存储过程获得对象列表
    let productData = defaultDB.ExecuteSprocAccessor<Product>("GetProductList", [|box "%bike%"|])
    let result = query{
                        for productItem in productData do
                        where (productItem.Description <> null)
                        sortBy productItem.Name
                        select productItem
    }
    result |> Seq.iter (fun i -> printfn "Product Name:%A\nDescription:%A\n" i.Name i.Description)

除了这些语句不需要其他任何配置，相对与ORM轻便了很多。
我记得例子程序里只有存储过程的例子，我加了一个sql语句，也是一样的结果。
    let sqlproduct = defaultDB.ExecuteSqlStringAccessor<Product>("select * from Products")
    query{
        for productItem in sqlproduct do
        where (productItem.Description <> null)
        sortBy productItem.Name
        select productItem
    } |> Seq.iter (fun i -> printfn "id:%A\nProduct Name:%A\nDescription:%A\n" i.ID i.Name i.Description)
3.获得XML结果
仅针对sql server 该模块还提供了获得一个非正则XML文件的方法。
    let sqlserverDB = factory.Create("ExampleDatabase") :?> SqlDatabase 
    using(sqlserverDB.GetSqlStringCommand("select * from OrderList where State = @state for xml auto"))(fun xmlCmd->
        xmlCmd.Parameters.Add(new SqlParameter("state", "Colorado")) |> ignore
        using(sqlserverDB.ExecuteXmlReader(xmlCmd))(fun reader ->
            while not reader.EOF do
                if reader.IsStartElement() then
                    Console.WriteLine(reader.ReadOuterXml())))

结果
<OrderList Id="1" Status="DRAFT" CreatedOn="2009-02-01T11:12:06" Name="Adjustable Race" LastName="Abbas" FirstName="Syed" ShipStreet="123 Elm Street" ShipCity="Denver" ShipZipCode="12345" ShippingOption="Two-day shipping" State="Colorado" />
<OrderList Id="2" Status="DRAFT" CreatedOn="2009-02-03T01:12:06" Name="All-Purpose Bike Stand" LastName="Abel" FirstName="Catherine" ShipStreet="321 Cedar Court" ShipCity="Denver" ShipZipCode="12345" ShippingOption="One-day shipping" State="Colorado" />

如果要以XML文件的形式使用的话要再加一个根节点。

4.获得单个结果
仅获得单个结果的辅助方法
    using(defaultDB.GetSqlStringCommand("select [Name] from States"))(fun sqlCmd ->
        printfn "%A" (defaultDB.ExecuteScalar(sqlCmd).ToString()))

5.数据库的异步操作
首先在数据连接字符串里要加入异步支持
   <add name="AsyncExampleDatabase" connectionString="Data Source=(localdb)\v11.0;<span style="color:#000099;">Asynchronous Processing=true;</span>AttachDbFilename=E:\WorkHell\fsharp-practise\EnterpriseLibraryPractise\DataAccessExamples.mdf;Integrated Security=True"
      providerName="System.Data.SqlClient" />
注意Asynchronous Processing = true

首先是基本模式
    let asyncDB = factory.Create("AsyncExampleDatabase")

    let AsyncGetDBInfo (db:Database) = 
        using(new ManualResetEvent(false))(fun doneWaitingEvent -> 
            using (new ManualResetEvent(false))(fun readCompleteEvent->
                try
                    let cmd = db.GetStoredProcCommand("ListOrdersSlowly")
                    db.AddInParameter(cmd, "state", DbType.String, "Colorado")
                    db.AddInParameter(cmd, "status", DbType.String, "DRAFT")
                    db.BeginExecuteReader(cmd, (fun asyncResult ->
                        doneWaitingEvent.Set() |> ignore 
                        try
                            try
                                using(db.EndExecuteReader(asyncResult))(fun reader ->
                                    DisplayRowValue(reader)
                                )
                            with
                            | ex -> printfn "Error afer data access completed: %A" ex.Message
                        finally
                            readCompleteEvent.Set() |> ignore
                    
                    ), null) |> ignore
                    while not (doneWaitingEvent.WaitOne(1000)) do
                        Console.Write "Waiting ..."


                    readCompleteEvent.WaitOne() |> ignore
                with
                | ex -> printfn "Error while starting data access :%A" ex.Message
        ))

    AsyncGetDBInfo asyncDB

异步的begin end被分开执行，还要注意设置一个ManualResetEvent。看着就头大。


接下来是使用Task模式
    let DoReadDataAsyncronouslyTask (db:Database) = async{
        try
            let cmd = db.GetStoredProcCommand("ListOrdersSlowly")
            db.AddInParameter(cmd, "state", DbType.String, "Colorado")
            db.AddInParameter(cmd, "status", DbType.String, "DRAFT")
            use timer = new Timer(fun _ -> printf "Waiting...")
            timer.Change(0, 1000) |> ignore
            use! reader = Async.FromBeginEnd(cmd,
                                             (fun (cmd:DbCommand,callback,state) -> db.BeginExecuteReader(cmd, callback, state)),
                                             db.EndExecuteReader) 
            timer.Change(Timeout.Infinite, Timeout.Infinite)|>ignore
            printf "\n\n"
            DisplayRowValue reader
        with
        | ex -> printfn "Error while starting data access: %A" ex.Message
    }

代码少了很多，也清晰了很多。使用fsharp相对来说简洁了那么一点点，用！替代了await，稍微直观一点。
最后是通过异步模式获取对象
    let DoReadAccessorDataAsyncronouslyTask (db:Database) = async{
        try
            let accessor = db.CreateSprocAccessor<Product>("GetProductsSlowly")
            use timer = new Timer(fun _ -> printf "Waiting...")
            timer.Change(0, 1000) |> ignore
            let! productData = Async.FromBeginEnd((fun (callback,state) -> accessor.BeginExecute(callback, state, [|(box "%bike%");(box 20)|])),
                                             accessor.EndExecute) 
            timer.Change(Timeout.Infinite, Timeout.Infinite)|>ignore
            printfn ""
            let result = query{
                                for productItem in productData do
                                where (productItem.Description <> null)
                                sortBy productItem.Name                   
                                select productItem
            }
            productData |> Seq.iter (fun i -> printfn "Product Name:%A\nDescription:%A\n" i.Name i.Description)
            printf "\n\n"
        with
        | ex -> printfn "Error while starting data access: %A" ex.Message
    }
    DoReadAccessorDataAsyncronouslyTask(asyncDB) |> Async.RunSynchronously

6.执行更新删除等的操作
defaultDB.ExecuteNonQuery(cmd) |> ignore

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： enterprise library f# fsharp enterprise library f# fsharp

相关文章

随机文章

您可能还喜欢

您可能还喜欢