通过fsharp探索Enterprise Library 6 DataBase 1.2

易焱

2023-12-01

上一次讲到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

通过fsharp探索Enterprise Library 6 DataBase 1.2

相关阅读

相关文章

相关问答

相关文档