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.
精彩评论