Creating Table in SQL Server Using F#

Question

Currently, I am attempting to create a SQl Server table using F# in Visual Studio. I have a list where each element is of the form: (column name * column type). Ideally, I would like to be able to create a new table in SQl Server with each column name as the column name having column type. I feel like this would have to be done dynamically somehow, but I am still unsure of how this might be accomplished in F#. If it is not possible to construct a CREATE query directly, is there possibly some object that can be created that is some sort of SQL Server table type, which could later be imported into the database directly? Documentation on this subject seems to be fairly light, so I would appreciate any help.

Answer 1

Unfortunately, even having built a full-fledged ADO DataTable, or Table type T-SQL arguments from table structure information it does not seem possible to programmatically dump the such into a database given a sought data table type does not exist in the schema yet. So you should somehow convert your table structure information to a T-SQL CREATE TABLEstatement and then execute it, the simplest.

To give you a jump start in this direction, here is a small rough snippet that given a F# record of user-defined type Car makes a Sql script for creating the correspondent db table [dbo].[Car] able of persisting such records, and executes it against a localdb myDb database:

open System.Data
open System
open System.Data.SqlClient
open Microsoft.FSharp.Reflection

let createSqlTableFromRecordTSql protoType =
    let net2SqlTypeMapper = function
    | "System.Int32" -> "int"
    | "System.String" -> "nvarchar(max)"
    | "System.DateTime" -> "datetime"
    | _ -> failwith "Unsupported type yet!"

    if FSharpType.IsRecord protoType then
        let fields = FSharpType.GetRecordFields protoType
        new String(
            seq {
                yield protoType.Name |> sprintf "CREATE TABLE %s (\n"
                for field in fields do
                    yield sprintf "[%s] %s,\n" field.Name (net2SqlTypeMapper field.PropertyType.FullName)
                yield ")"
            } |> Seq.concat |> Seq.toArray)
    else
        failwith "Can make table from f# record types only!"

type Car = {make: string; model: string; year: int; purchased: DateTime}

let connectionString = "Data Source=(localdb)\ProjectsV12;Initial Catalog=myDB;Integrated Security=True;Pooling=False;Connect Timeout=30"
let conn = new SqlConnection(connectionString)
conn.Open()
let cmd = new SqlCommand((createSqlTableFromRecordTSql typeof<Car>),conn)
cmd.CommandType <- CommandType.Text
cmd.ExecuteNonQuery()
conn.Close()

The key piece iscreateSqlTableFromRecordTSql function that accepts a record type, finds out names and types of the fields, maps (with a lot of corners cut) .NET types to Sql types and builds a correspondent CREATE TABLE statement. So, given

type Car = {make: string; model: string; year: int; purchased: DateTime}

it returns the derived T-SQL statement

    CREATE TABLE Car (
    [make] nvarchar(max),
    [model] nvarchar(max),
    [year] int,
    [purchased] datetime,
    )