基于MySql数据库表的记录类型自动生成，供FSharpDAL使用 [英] Automatic generation of record type based on MySql database tables, for use in FSharpDAL

问题描述

我一直在使用 FSharpDAL 来读取Mysql数据库中的数据表.它运作良好，但您必须提供记录类型.这意味着您必须为每个表定义一个记录类型.我想从表模式中自动生成记录类型.同样，当表列为空时，我希望coresponding记录字段为选项类型.

I have been using FSharpDAL to read data tables in Mysql database. It works well, but you have to provide it the record type. Which means for every table you have to define a record type. I would like to get the record type generated automaticly from the table schema. Also when the table column is nullable i would like the coresponding record field to be an option type.

那里有可以做到这一点的东西吗? (直到类型提供者到达)

Is there something out there that can do this ? (until type providers arrive)

非常感谢

请注意，我尝试执行我的版本(该模块使用记录类型编译一个模块)，似乎可以正常工作，但是它很丑陋:(感谢@DannyAsher的编译代码)

Note I have tried to do my version (which compiles a module with the records types), seems to work but its ugly: (thanks to @DannyAsher for the compiling code)

let typeString conString (table:string) = 

    use con = new MySqlConnection(conString)
    con.Open()

    use cmd = new MySqlCommand(("select * from "+table),con)
    let schema = cmd.ExecuteReader().GetSchemaTable()

    let schemaData = 
        [for col in schema.Columns do
               yield!  [for row in schema.Rows -> 
                    //System.Console.WriteLine(col.ColumnName+" "+string row.[col])
                            (col.ColumnName),(string row.[col])]]
        |>Seq.filter(fun (name,_) ->
              ((name="ColumnName")||(name="DataType"))||(name="AllowDBNull"))
        |>Seq.groupBy(fst)
        |>Seq.cache

    let columnsNames = snd(schemaData|>Seq.nth(0))
    let colDataTypes = snd(schemaData|>Seq.nth(1))
    let optionType = snd(schemaData|>Seq.nth(2))


    let toCompileType = 
        (Seq.zip3 columnsNames  colDataTypes optionType
         |> Seq.map(fun ((_,colName),(_,colType),(_,allowNull)) -> 
             if allowNull="True" then 
                 colName+":"+colType+" option;" 
             else
                 colName+":"+colType+";" 
                 )
          |>Seq.fold(fun res elem ->res+elem) ("type "+table+"={"))+"}"

    toCompileType



#r "FSharp.Compiler.dll"
#r "FSharp.Compiler.CodeDom.dll"

open System
open System.IO
open System.CodeDom.Compiler
open Microsoft.FSharp.Compiler.CodeDom

let CompileFSharpString(str, assemblies, output) =
        use pro = new FSharpCodeProvider()
        let opt = CompilerParameters(assemblies, output)
        let res = pro.CompileAssemblyFromSource( opt, [|str|] )
        if res.Errors.Count = 0 then 
             Some(FileInfo(res.PathToAssembly)) 
        else 
             None

let (++) v1 v2   = Path.Combine(v1, v2)    
let defaultAsms  = [||] 
let randomFile() = __SOURCE_DIRECTORY__ ++ Path.GetRandomFileName() + ".dll"   

type System.CodeDom.Compiler.CodeCompiler with 
    static member CompileFSharpString (str, ?assemblies, ?output) =
        let assemblies  = defaultArg assemblies defaultAsms
        let output      = defaultArg output (randomFile())
        CompileFSharpString(str, assemblies, output) 

let tables = [|"users";"toys"|]

let compileTypes conString tables = 
    let m= "namespace Toto
              module Types = 
                       "
    let str = tables|>Seq.fold(fun res elem -> 
                          res+"\n                   "+(typeString conString elem)) m


    // Create the assembly
    CodeCompiler.CompileFSharpString(str)



let conString = "connectionstring"

let file =compileTypes conString  tables

#r "theFileName"
open Toto.Types

推荐答案

我能想到的最好的事情是使用.它可以根据表的XML描述创建一个Assembly或C#源文件.

The best thing I can think of, is to use the "xsd.exe" tool that comes with VS2010. It can create an Assembly or C# source file from an XML description of the table.

可以从 WriteXmlSchema方法.

这仅适用于ADO.Net绑定，但是MySQL

This only works with ADO.Net bindings, but MySQL supports those.

这篇关于基于MySql数据库表的记录类型自动生成，供FSharpDAL使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！