Dump data from SQL to CSV (or other format) with F#

Recently I started playing with F#. And when I looked up for a way of accessing data, I found something called TypeProvider. And I have to say it is amazing tool. Then I realized that I was looking for some simple way to dump part of my database to CSV file, so I can use this data in automated testing (I might write something about that in near future). And I could use F# to just do it!
The result of my work was code that could dump selected tables (or part of selected tables) into separate CSV files (or in any other format, if you change function that dumps data). It will automatically get all columns in your database, so you have to only list tables that you want to dump (or write some simple F# query to get only data you want – useful if you have tables with millions of records, and don’t want to dump all of them).

Here is description of how it is done: (or you can just look on full source code).

Firstly, we have to create connection, as with all TypeProviders solutions:

///Let's create some connection
type EntityConnection = SqlEntityConnection<ConnectionString="Server=sqlServer;Initial Catalog=dbName;User=user;password=password;MultipleActiveResultSets=true",
                                                      Pluralize = false>
//now we have to get some context
let context = EntityConnection.GetDataContext()

Then we need few functions, that basically get all properites from database entity and filter out the ones that are not simple types or Nullables (using reflection). I’m assuming here that all such properties contain data from database (so each property correspond to value from SQL table column). We also need to format some of these data (like dates) and escape some characters (like ” in string). This part might require some improvements, but it worked on my database.

//checks if given field is simple type, ie. if it is some of System.Int32, System.Boolean or if it is Nullable type
//later on we will use this to get values from entieties that are database fields
let isSimpleType (typeName:System.Reflection.PropertyInfo) =
    let tn = typeName.PropertyType.ToString()
    tn.StartsWith("System.") && (2 = tn.Split('.').Length || tn.Contains("Nullable")) //types like System.Int32, System.String etc

//function that get entity from database, get all its properties, filter it for simple types, use mapping funcion on all of them and then concatenate them for one line string (as line is CSV file)
let getLine mappingFunc x =
    x.GetType().GetProperties()
    |> Array.filter isSimpleType
    |> Array.map (mappingFunc x)
    |> String.concat ","

//convert value to so it can be used in cvs file (ie add " to string and escape existing ones)
let convertValue value (typeName:System.Reflection.PropertyInfo) =
    match typeName.PropertyType.ToString() with
    | "System.Int32" -> value
    | "System.String" -> sprintf "\"%s\"" (value.Replace("\\", "\\\\").Replace("\"", "\\\"").Replace("\n", "\\n"))
    | _ -> value

//get field name (same as column name in database)
let headerValue x (p:System.Reflection.PropertyInfo) = p.Name

//get value of property and convert it if it is not null
let lineValue x (p:System.Reflection.PropertyInfo) =
    match (p.GetValue(x, null)) with
    | null -> "NULL"
    | a -> convertValue (a.ToString()) p

When we have all above helper functions, we need something to get data and dump them into files:

//main function that dumps data from sequence taken from database into cvs format
let dumpData dumper sequence =
    sequence |> Seq.head |> (getLine headerValue) |> dumper
    sequence |> Seq.map (getLine lineValue) |> Seq.iter dumper

//function that get all records from given table and dumps it using passed function
let dumpDataFromTable dumper table =
    let sequence = query { for sev in table do
                           select sev }
    dumpData dumper sequence

//save data from given table, using dumpData into file
let saveFile (table:System.Data.Objects.ObjectSet<'a>) =
    let tableName = table.EntitySet.ToString()
    use writer = new StreamWriter((sprintf @"C:\temp\%s.csv" tableName), false)
    dumpDataFromTable writer.WriteLine table
    printfn "table %s done" tableName

//save data from given collection to file
let saveFileWithData tableName data =
    use writer = new StreamWriter((sprintf @"C:\temp\%s.csv" tableName), false)
    dumpData writer.WriteLine data
    printfn "table %s done" tableName

If we have that, we can now write easiest part – dumping data. We can either dump full table:

saveFile context.List_Status

or dump only selected part of it, by writing query that gets desired data:

//dump selected data from big table
let items = query { for el in context.Table_With_Contents do
                    take 20
                    sortByDescending el.Date
                    where (el.ID < 200)
                    select el }

saveFileWithData "Table_With_Contents" items

You could find whole source on my GitHub.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *