7.5k
views3
comments

0 score	+
	-

Using F# to work with C# DataSets

f#parsing c#

Hey all,

I am playing around with using F# to perform complex calculations on a C# DataSet.

Right now, I am able to sum up the decimal values in a DataSet DataColumn using F#, just the most basic example I could think of to get me started . . .

I want to get feedback from this forum on whether I am doing this efficiently here. It feels clunky to me having to cycle through all of the rows in the DataTable in the DataSet, pulling the column data out as strings, parsing to decimals, filling a decimal array, passing that to F#, where I convert to an F# List because I prefere F# lists over arrays.

It just feels clunky. Am I missing a smoother way? Maybe I am just spoiled by F# . . .

public Decimal SumDataColumn(string columnName)
{
    DataColumn dc = this.GetDataColumn(columnName);
    decimal[] decimalArray= new decimal[this.Count];

    int i = 0;
    foreach (DataRow dr in this.Tables[innerTableName].Rows)
    {
        decimalList[i]= (System.Decimal.Parse(dr[dc].ToString()));
        i++;
    }

    FSharpDataColumn fsDc = new FSharpDataColumn(decimalArray);
    return fsDc.Sum;
}

#light
namespace IB.Framework.Math.FSharp

open System
open Microsoft.FSharp.Math

type FSharpDataColumn(inList: decimal array) = 
    let s =Array.to_list inList
    member this.Sum = List.sum (s)

I believe that my next step is to expand out of just using a DataColumn and instead, move my whole DataSet into an F# Matrix or dataset (since my test DataSet is all just rows and columns of decimal values) . . . I guess to do this, I'll have to pass in DataSet rows as decimal arrays and build up an F# matrix that way or else pass in an array of decimal arrays? It's an intriguing exercise to me . . .

If I can get this working well, then I could use ADO.NET Sync Services to sync SQL Server with a local SQL CE file in my app, so users could theoretically run local queries to pull data into C# datasets and then perform complex statistical calculations on the datasets using F# functions for speed and efficiency . . .

Of course, I should be upgrading to LINQ soon and I will likely have a business entities version rather than just ADO.NET DataSets, but right now my team at work still uses C# 2.0 DataSets from time to time . . .

Is anyone else looking at something like this? I am thinking this would work for risk modeling and running through pricing scenarios, etc. . . .

Cheers,
Bryan

.Reply

I took a look at more than just running an F# function against a single DataColumn . . . at first, I tried to pass a decimal array array into F# and cast to a matrix, but I had trouble doing that, and it looked like I'd have to cast my decimals to floats. Since I work in the financial arena, I prefer to use decimals.

So instead, I ended up just keeping the array of decimal arrays and that worked fine. I allow the developer to pass in a List<int> of column indexes, basically choosing which columns from the DataSet to calculate against. Then, they can choose from an enum to SumEachColumn or SumEachRow. Eventually, as I add more real-world complex F# Functions, I can just add on to this enum.

Now, I do wonder a little bit at the efficiency of cycling through all the values in the desgnated columns to build the array of decimal arrays . . . it would be a bit better in the F# team, perhaps, supplied a more efficient way to pass DataSet data directly to an F# function . . . but I'll give this a try against a large dataset when I get the chance and see how slow or fast it is. At the very least, I gain the advantage of using F# in my actual calculations.

I really do like using F# and I think it's a great tool to add to one's arsenal.

My C# Code

public enum ListExpressions
    {
        SumEachColumn,
        SumEachRow
    }

public Decimal[] GetColumnResults(List<int> columnIndexes, ListExpressions mathExpression)
{
    decimal[][] decimalArray = new decimal[this.Count][];

    decimal[] decimal_result = { 0.00M, 0.00M, 0.00M };

    int i = 0;

    foreach (DataRow dr in this.Tables[innerTableName].Rows)
    {
        decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
        i++;
    }

    FSharpDataSet fsDs = new FSharpDataSet(decimalArray);

    if (mathExpression == ListExpressions.SumEachColumn)
    {
    decimal_result = fsDs.SumEachColumn;
    }
    else if (mathExpression == ListExpressions.SumEachRow)
    {
    decimal_result = fsDs.SumEachRow;
    }

    return decimal_result;
}

 

private decimal[] GatherColumnValues(List<int> columnIndexes, DataRow dr)
{
    decimal[] decimalArray = new decimal[columnIndexes.Count];
    int j = 0;

    for(int i=0; i<dr.ItemArray.Length;i++)
    {
        if (columnIndexes.Contains(i))
        {
            decimalArray[j] = System.Decimal.Parse(dr.ItemArray[i].ToString());
            j++;
        }
    }

    return decimalArray;
}

My F# Code

type FSharpDataSet(inList: decimal array array) =
    let m =inList|>Array.to_list |>List.map (fun x-> (x|>List.of_array))
    member this.GetColumn (z: decimal list list) (i:int)= z|>List.map(fun x-> ( (x.[i])))
    member this.SumEachColumn: decimal array= List.to_array([for j in [0..(m.[0].Length - 1)] -> (List.sum (this.GetColumn m j)) ])
    member this.SumEachRow: decimal array = List.to_array([for k in [0..(m.Length - 1)] -> (List.sum (m.[k])) ])

Please let me know what you think or any pointers. Obviously, this isn't production-ready really. I don't have error handling or anything. However, this looks like a good starting point for me, and perhaps, other newbies to F# who wish to leverage C# DataSet data passed in to F# code.

Cheers,
Bryan

PS I know you could do these basic sums with DataColumn Expressions native to C# DataSets, so please don't point that out. This isn't about summing columns or summing rows, it's about opening the door to performing much more complex F# calculations against columns or rows sourced from a C# DataSet. This is just the most bare-bones example to get started . . .

By the same token, I wouldn't need to transform all my arrays to Lists in F#, especially in this simple example. I could have used Array.sum . . . this is just a matter of personal preference. Arrays are mutable but Lists are not, and sequences allow lazy loading, so you may wish to use one or the other of these three in any given situation.

By prendebp on 1/19/2009 1:21 AM (permalink)Reply

I have been expanding my MathDataSet class . . . hopefully I will get a chance to post this on CodeProject, perhaps, at some point in the future.

I just added support for passing in columns of data from a DataSet and then multiplying all cells by a value or adding a value to all cells, then returning a new decimal array array (which I then convert to a new DataSet, thus returning a new DataSet.)

In summary, I have the basic foundation for mapping an F# function to all cells in the columns of data passed in to my GetResultsAsDataSet method and returning a new C# Dataset . . .

You may notice that I added a new DecimalAdapter to MathDataSet, as I quickly realized that this would be more efficient, using a DecimalAdapter, DoubleAdapter, Int32Adapter, and eventually a catch-all StringAdapter . . . to let you process functions by datatype . . .

C# Code snippets

public enum DataSetExpressions
{
    MultiplyAllCellsByValue,
    AddValueToAllCells
}

...

DataSet newDataSet=myDataSet.DecimalAdapter.GetResultsAsDataSet("testDataSet", columnNames7, columnIndexes7, CalcEnums.DataSetExpressions.MultiplyAllCellsByValue, null, parameterList);

...

public Decimal[][] GetDataSetResults(List<int> columnIndexes, CalcEnums.DataSetExpressions mathExpression, Predicate<DataRow> filter, List<decimal> parms)
{
    decimal[][] decimalArray = new decimal[_source.Count][];
    decimal[][] decimal_result = null;

    int i = 0;

    if (filter != null)
    {
        foreach (DataRow dr in _source.Tables[_source.InnerTableName].Rows)
        {
            if (filter(dr) == true)
            {
                decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
                i++;
            }
        }

    //flatten array

    decimal[][] decimalArray2 = new decimal[i][];

    for (int j = 0; j < i; j++)
    {
        decimalArray2[j] = decimalArray[j];
    }

    decimalArray = decimalArray2;
    }
    else
    {
        foreach (DataRow dr in _source.Tables[_source.InnerTableName].Rows)
        {
            decimalArray[i] = (GatherColumnValues(columnIndexes, dr));
            i++;
        }

    }

    if (i > 0)
    {
        FSharpMatrix fsMatrix = new FSharpMatrix(decimalArray);

        if (mathExpression == CalcEnums.DataSetExpressions.MultiplyAllCellsByValue)
        {
            decimal_result = fsMatrix.IterateMatrix(fsMatrix.MultiplyAllCellsByValue(parms[0]));
        }
        else if (mathExpression == CalcEnums.DataSetExpressions.AddValueToAllCells)
        {
            decimal_result = fsMatrix.IterateMatrix(fsMatrix.AddValueToAllCells(parms[0]));
        }
    }

    return decimal_result;
}

F# Code snippets

type FSharpMatrix(inList: decimal array array) =
member this.MultiplyAllCellsByValue (parm1:decimal) (i:int) =inList|>Array.map(fun x->((x.[i]*parm1)))
member this.AddValueToAllCells (parm1:decimal) (i:int) =inList|>Array.map(fun x->((x.[i]+parm1)))
member this.FirstColumn= this.GetColumn inList 0
member private this.GetColumn (source: decimal array array) (i:int)= source|>Array.map(fun x->((x.[i])))
member this.IterateMatrix f = let mutable x: decimal array array=Array.create inList.Length [||]

for j = 0 to (inList.Length - 1) do //cycle thru rows
    let mutable y: decimal array=Array.create inList.[j].Length 0.00M
        for h = 0 to (inList.[j].Length - 1) do //cycle thru cells in row
            y.[h]<-(((f h): decimal []).[j]) 
        x.[j]<-y
    x

However, I get the feeling that as a newbie to F#, the code above could likely be optimized or improved quite a bit. Feel free to chime in with ideas . . .

It is also possible that I should just be passing in a DataSet directly to F# rather than pulling out values as Decimal arrays and passing them in this way . . . but so far so good.

By prendebp on 2/1/2009 11:43 PM (permalink)Reply

Wind Farm	P50	P80
Chanarambie	19	3.11
	19	3.11
Blue Canyon	17	12.75
	7	4.25
	7	5.25
	3	3.25
Bear Creek	3	3.25
	2	2
	1	1.25
(Not Categorized)	2	2
	2	2

I finally had a free moment to tinker a bit more with this MathDataSet concept. I added support for Sorting and Grouping, but via C#; although, when grouping, you can use one of my ListExpressions which runs against a column (or in this case against a column subset for a particular group), which can be an F# calculation. For instance, in the example above, I sum a couple of columns when grouping . . .

Dictionary<string, CalcEnums.DataSetSortType> columnsToSort = new Dictionary<string,CalcEnums.DataSetSortType>();
columnsToSort.Add(Constants.WIND_FARM, CalcEnums.DataSetSortType.DESC);
columnsToSort.Add(Constants.P50, CalcEnums.DataSetSortType.DESC);
myDataSet.SortInnerDataTable(columnsToSort);

//Test grouping
List<int> li = new List<int>();
li.Add(2);
li.Add(3);
myDataSet.GroupInnerDataTable(Constants.WIND_FARM, li, CalcEnums.AdapterType.Decimal,CalcEnums.ListExpressions.SumEachColumn);

By prendebp on 2/21/2009 2:18 AM (permalink)Reply

Topic tags

Built with WebSharper

Home

Answers

Events

Courses

Groups and Conferences

Blogs

Jobs

Developers

Topic tags