Use SqlDataReader in F#

Question

In C# I use sql scripts to add data into a List where T would be a class with fields/properties mapped to the sql script.

How could I do this in F#? This piece uses a stored procedure in the standard way.

using (conn)
{
 conn.Open();
 using (SqlCommand cmd = new SqlCommand("dbo.query_here", conn))
 {
 cmd.CommandText = "dbo.query_here";
 cmd.CommandType = System.Data.CommandType.StoredProcedure;
 cmd.CommandTimeout = 600;
 cmd.Parameters.Add(new SqlParameter("@x1", Convert.ToString(x)));
 cmd.Parameters.Add(new SqlParameter("@y1", y));
 cmd.Parameters.Add(new SqlParameter("@z1", z));
 SqlDataReader reader = cmd.ExecuteReader();
 while (reader.Read())
 {
 MyListOfClasses.Add(new MyDataClass(reader.GetInt32(reader.GetOrdinal("x")) 
 reader.GetDouble(reader.GetOrdinal("y")),
 reader.GetDouble(reader.GetOrdinal("a")),
 reader.GetDouble(reader.GetOrdinal("b"))));
 }
 reader.Close();
}
conn.Close();

I realise F# is not quite as straight forward perhaps as this, but I need to get this data into F# lists in a similar way. Also would prefer suggestions that were not functional in nature and followed a similar pattern to C# code.

In a previous thread someone suggested using records, but still this was not in relation to SqlDataReader. It would be preferable to have a list of classes so I can use getters and setters on each item.

I should add before the inevitable comments of "why not just use C#". Well obviously I can use C#, but I am exploring possibilities of writing algorithms in F# and to do that I need to get my raw data from SQL Server.

• Mauricio Scheffer: bugsquash.blogspot.com/2010/10/… github.com/mausch/FsSql/blob/master/FsSql.Tests/Samples.fsx

Answer 1

If you would like to return results in F# list, list comprehension is suitable to use here. And with use keyword, you don't need to explicitly dispose objects.

use conn = (* Initialize sql connection here *)
conn.Open()
use cmd = new SqlCommand("dbo.query_here", conn)
cmd.CommandText <- "dbo.query_here"
cmd.CommandType <- System.Data.CommandType.StoredProcedure
cmd.CommandTimeout <- 600
cmd.Parameters.Add(new SqlParameter("@x1", Convert.ToString(x)))
cmd.Parameters.Add(new SqlParameter("@y1", y))
cmd.Parameters.Add(new SqlParameter("@z1", z))
use reader = cmd.ExecuteReader()
let results =
 [ while reader.Read() do
 yield new MyDataClass(reader.GetInt32(reader.GetOrdinal("x")), 
 reader.GetDouble(reader.GetOrdinal("y")),
 reader.GetDouble(reader.GetOrdinal("a")),
 reader.GetDouble(reader.GetOrdinal("b"))) ]

• Richard Todd: This is the answer i wanted. Thank you sir. I know i don't need to dispose really, habit from VBA i guess :(.

Answer 2

Working with databases using plain old ADO.NET can be actually quite nice using the F# dynamic operator ?. I wrote an MSDN article that implements a helper that lets you write something like:

let myListOfClasses = 
 [ let db = new DynamicDatabase(connectionString)
 let rows = db.Query?query_here(string x, y, z)
 for r in rows do 
 yield MyDataClass(row?x, row?y, row?a, row?b) ]

This uses the dynamic invoke operator ? to call the stored procedure (db.Query?query_here). When you call it, you can give it parameters as if it was a normal function call (it will add them as SQL parameters). Then you can also use row?x to read the properties - this infers the type from the type of MyDataClass arguments, so assuming these match, you do not need to write type conversions yourself.

Here are links to a MSDN tutorial that discusses this:

• How to: Dynamically Invoke a Stored Procedure
• Step 1: Create a Database and Show the Poll Options
• Step 2: Implement Voting for an Option

• Richard Todd: I like this but it is just too complicated for the simple task I'm trying to do, is there not just a really simple way of using the C# method but instead of List (ResizeArray) we use F# list? Or to convert from one to the other?
• Tomas Petricek: @RichardTodd Yep, agreed - for a single task, this is too complex. You may still define (?) operator to access properties from SqlDataReader - that is quite easy. I'm not sure what you're asking regarding lists? (You can get F# list from any IEnumerable using List.ofSeq...)
• user6996876: Thank you, I guess it can be helpful for me together with this other answer

Answer 3

You can read this

Link : http://www.codeproject.com/Articles/95656/Using-a-DataRader-like-a-List-in-F

let rdr = cmd.ExecuteReader()
rdr
|> SomeRecord.asSeq // project datareader into seq<SomeRecord>
|> Seq.sumBy (fun r-> r.val1 * r.val2) // now you can use all the Seq operators 
 // that everybody knows.

Answer 4

Untested translation to F#

use conn = ???
conn.Open()
using new SqlCommand("dbo.query_here", conn)(fun cmd ->
 cmd.CommandText <- "dbo.query_here"
 cmd.CommandType <- System.Data.CommandType.StoredProcedure
 cmd.CommandTimeout <- 600
 cmd.Parameters.Add(new SqlParameter("@x1", Convert.ToString(x)))
 cmd.Parameters.Add(new SqlParameter("@y1", y))
 cmd.Parameters.Add(new SqlParameter("@z1", z))
 let reader = cmd.ExecuteReader()
 while (reader.Read()) do
 MyListOfClasses.Add(new MyDataClass(reader.GetInt32(reader.GetOrdinal("x")) 
 reader.GetDouble(reader.GetOrdinal("y")),
 reader.GetDouble(reader.GetOrdinal("a")),
 reader.GetDouble(reader.GetOrdinal("b"))))
 reader.Close()
)
conn.Close()
MyListOfClasses.ToArray() |> List.ofArray

• Richard Todd: the output needs to be an F# immutable list, else I would just use C#
• Richard Todd: Thanks, like the simplicity too. I'd mark answered but the one above uses list comp which is quite nice.