Automatic generation of record type based on MySql database tables, for use in FSharpDAL

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 t开发者_C百科he 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)

Thanks a lot

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

---

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.

The XML description itself can be exported from the WriteXmlSchema method of the DataSet class.

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