« Back
in SQL FSharp.Data F# Fsharp C# read.

Calling An F# Library From C# to Import A CSV File Into SQL Server.

Parsing and dealing with CSV files in F# is dead easy when using the amazing FSharp.Data Type Provider. I've been on vacation for 6 weeks and in that time I've been reading up on F# and data mining. It's absolutely amazing how succinct F# is and it suits perfectly with the direction I want to head in. Along the way I hope that some of this helps someone out there who's looking to get into the action as well.

A Small Task - Import CSV Into SQL Server

Let's start out with something small. The task is to read in a CSV file, build a DataTable out of it so that we can import it into SQL server using SqlBulkCopy. We want to build the library in F# but call it from some C# code.

At this time, the development team just agreed to start introducing F# code into production which is a big win! However, our team is still predominantly C# and MVC based so I didn't want to introduce too much change. Instead we'll build a small library and maybe build a nuget package out of it. I have an article here about publishing a nuget package. The same principles would apply for this package.

Preparing The Database for CSV Import

First we should setup our tables. I did so in SSMS and the table looks like this:
test table for csv We've got 3 types of fields in there: int, nvarchar, and datetime.

My CSV file looks like this:

"ReceiptNumber","FirstName","LastName","Comments","Timestamp","WCC","IPAddress"
"123456","Joe","MacDonald","Hi, It's a fantastic survey!","2014-03-18","112233","24.129.19.1"
"654321","Dorothy","White","Cool","2014-03-19","112233","24.129.19.1"

F# Class For Creating A <DataTable> & Importing to SQL Server

In my Visual Studio solution, we create an F# library and have the following class:

namespace DataTools

module Tools =  
    open FSharp.Data
    open System.Data.SqlClient
    open System.Data
    open System.Xml

    type CSV(connectionString:string, csvFile:string, table:string) = 
        let mutable _connectionString=connectionString
        let mutable _table=table

        member this.Table with get() = _table and set(v) = _table <- v
        member this.ConnectionString with get() = _connectionString and set(v) = _connectionString <- v

        member this.CSVToDataTable = 
            let dataTable = new DataTable()                
            let file=CsvFile.Load(csvFile)
            let headers=[| file.Headers.Value |]
            headers.[0]
                |>Seq.iter(fun h-> dataTable.Columns.Add(new DataColumn(h)))
            file.Rows
                |>Seq.iter(fun r-> 
                    let dr = dataTable.NewRow()
                    r.Columns |> Seq.iteri(fun i c ->  dr.Item(i) <- c )
                    dataTable.Rows.Add(dr))                           
            dataTable


        member this.ImportToMSSQL(dt:DataTable) =
            use con = new SqlConnection(_connectionString)
            con.Open()
            use bulkCopy = new SqlBulkCopy(con, DestinationTableName=_table)        
            bulkCopy.WriteToServer(dt)
            ignore

So in here I have a class which has two members on it. These will be the methods called by the C# code. One reads the CSV file and returns a DataTable in case the C# code wants to do something else with it and the other takes a DataTable and imports it into SQL Server based on the connection string.
I pulled in FSharp.Data from nuget with Install-Package FSharp.Data.

Ony one line of code to build our DataColumns based on the headers and a couple to build our DataRows. Remember how we built the table with multiple datatypes earlier? How do we deal with the datetime and ints and strings? The beautiful thing is that we don't have to! It automatically infers that for us and you can verify it by issuing an T-SQL query.

Calling The Library From C# WPF Program

In the solution just add a C# WPF Program. Drag into it a button and attach this code to the click handler:

private void Button_Click(object sender, RoutedEventArgs e)  
{
    var csv = new Tools.CSV(@"Data Source=(LocalDb)\v11.0;Initial Catalog=test;Integrated Security=True;persist security info=True;MultipleActiveResultSets=True;", "C:\\temp\\test.csv", "test");
    var dt = csv.CSVToDataTable;
    csv.ImportToMSSQL<DataTable>(dt);

}

Make sure you add a reference in this project to the F# library. You may also need FSharp.Core.

It's just as simple as that. We instantiated a class and called its methods. The nice thing about the library is that it returned a DataTable which is a native .NET type. So C# knows how to deal with that. There are certain types in F# that won't play well with C# and one of them is a List. Just something to look out for.

Just as a point of interest, I made a 5.2MB CSV file with about 63,000 records and it imported the data in less than 1 second in debug mode.

It's been quite fun playing with F# and I look forward to doing much more with it especially in the realm of BI and Data Mining.

Stay tuned for more and thanks for reading!

comments powered by Disqus