Back to Questions
2.4k
views21
comments
Fetch data in an array of tuples.
f#

Hello!
I want to write a function to get

the records from one data table in SQL Server database. But I want it to be general, so it can get

the records from one table, but it can also get the data from another table. I want to put the result into an array of

tuples, so I can use it later on.

Now, begin the process with a simple

data table with only 2 fields:

CREATE TABLE [dbo].[Table1] (

[Field1] [int] NOT NULL ,

[Field2] [nvarchar] (1) NOT NULL

CONSTRAINT PK_Table1 PRIMARY KEY(Field1));

GO

Also, I provided some data in the

table:

INSERT INTO Table1 VALUES(1, 'A')

INSERT INTO Table1 VALUES(2, 'B')

I have some code, but it is not

working.

let

sql_ConnDB = "server=(local);Integrated

Security=True;Database=localDB"

let

connDB = new SqlConnection(sql_ConnDB)

let

GetTable1 field1 field2 =

use connDB = new

SqlConnection(sql_ConnDB)

use cmd = new

SqlCommand(""SELECT Field1,

Field2 FROM Table1", connDB)

cmd.CommandType

<- CommandType.StoredProcedure

cmd.Parameters.Add(SqlParameter("@Field1",

field1)) |> ignore

cmd.Parameters.Add(SqlParameter("@Field2",

field2)) |> ignore

connDB.Open()

let list = new

ResizeArray<_>()

use reader = cmd.ExecuteReader()

while (reader.Read()) do

list.Add({ field1 = unbox (reader.get_Item("Field1"))

field2 = unbox (reader.get_Item("Field2"))

})

// Return the created collection

list

let

sqlGet = GetTable1 field1 field2

I want the result saved in sqlGet =

[|(1,’A’);(2,’B’)|]

But I got the error message: “The

record label 'field1' is not defined” for the first “unbox” statement, if I

deleted field1, then field2 is not defined.

If some one knows how to do it, please show me your code.

Beside, if the table has 3 fields, what I can do then?

Thanks and happy new year!

.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

open System
open System.Data
open System.Data.SqlClient
open System.Collections.Generic


let sqlConnStr =
        let sqlConnStrBuilder = new SqlConnectionStringBuilder()
            sqlConnStrBuilder.DataSource <- "(local)"
            sqlConnStrBuilder.InitialCatalog <- "localDB"
            sqlConnStrBuilder.IntegratedSecurity <- true
            sqlConnStrBuilder.ConnectionString

printfn "%s" sqlConnStr

let GetSqlData() =
    use connDB = new SqlConnection( sqlConnStr )
    use cmd = connDB.CreateCommand()
    cmd.CommandType <- CommandType.Text
    cmd.CommandText <- "SELECT Field1, Field2 FROM Table1"
    connDB.Open()
    let result = new ResizeArray<int*string>()
    use reader = cmd.ExecuteReader()
    while( reader.Read()) do
        result.Add( unbox reader.["Field1"], unbox reader.["Field2"] )
        result.ToArray()

let sqlData = GetSqlData()
By on 1/7/2010 4:43 PM ()

Hi, Marekb:
Thank you very much, your code works.
However, if you read my question, I want to have a "general" version for different number of fields. Your code can work for data table with 2 fields, how about the data table with 3 fields?
Like Table2(Field1[nvarchar](5), Field2[int], Field3[xml])?
In this case, I have to re-write the code to get a new function.
But is it possible that use one function to cover the data table with many fields: 3, 4, 5, 6, 7, 8.
Just when I call the function, I provide different parameter, like commendText, and field name? The best is just to provide the SQL statement like: SELECT Field1,Field2,Field3 FROM Table2
Then the function can automatically fetch the data into the array of tuples.
But thank you very much first for your excellent code!
Have a nice weekend.

By on 1/8/2010 2:30 PM ()

Hi, Marekb:
Thank you very much, your code works.
However, if you read my question, I want to have a "general" version for different number of fields.  Your code can work for data table with 2 fields, how about the data table with 3 fields?
Like Table2(Field1[nvarchar](5), Field2[int], Field3[xml])?
In this case, I have to re-write the code to get a new function.
But is it possible that use one function to cover the data table with many fields: 3, 4, 5, 6, 7, 8.
Just when I call the function, I provide different parameter, like commendText, and field name?  The best is just to provide the SQL statement like: SELECT Field1,Field2,Field3 FROM Table2
Then the function can automatically fetch the data into the array of tuples.
But thank you very much first for your excellent code!
Have a nice weekend.

Either you are using the wrong language or your are using the wrong data structure. In F#, n-tuple is a static type and it is of little use even you can dynamically create them in this function. As no other part of your code can use it unless you use reflection all the time. If that is the case, may be JronPython/IronRuby is more suitable.

You may use Dictionary<string,obj> as your object container instead of n-tuple but that again basically throw away F#'s type system.

And from your other questions I have seen so far, you may be better off using C#/VB or IronPython/IronRuby

By on 1/8/2010 5:28 PM ()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

open System
open System.Data
open System.Data.SqlClient
open System.Collections.Generic

type RowTuple = obj * obj * obj * obj * obj * obj * obj * obj

let sqlConnStr = 

    let sqlConnStrBuilder = new SqlConnectionStringBuilder()
    sqlConnStrBuilder.DataSource <- "(local)"
    sqlConnStrBuilder.InitialCatalog <- "DBTest"
    sqlConnStrBuilder.IntegratedSecurity <- true
    sqlConnStrBuilder.ConnectionString


printfn "%s" sqlConnStr

let GetSqlData() =

try 

    use connDB = new SqlConnection( sqlConnStr )
    use cmd = connDB.CreateCommand()
    cmd.CommandType <- CommandType.Text 
    cmd.CommandText <- "Select * FROM Table1"
    connDB.Open()

    let result = new ResizeArray<RowTuple>()
    use reader = cmd.ExecuteReader()
    let lnCountFields = reader.FieldCount
    let arrTuple = Array.create 8 null

    while( reader.Read()) do 
        for index = 0 to arrTuple.Length - 1 do
            arrTuple.[index] <- null 
        for index in 0 .. lnCountFields-1 do 

        if index < 8 then arrTuple.[index] <- unbox reader.[reader.GetName( index ) ] 

        let tuple = arrTuple[ 0 ],arrTuple.[ 1 ],arrTuple[ 2 ],arrTuple[ 3 ],arrTuple.[ 4 ],arrTuple[ 5 ],arrTuple[ 6 ],arrTuple[ 7 ] 

        result.Add ( tuple ) 
    Some( result.ToArray() ), lnCountFields

with 

    | ex -> printfn "%s" ex.Message

        None,0 

let sqlData,countFields = GetSqlData()
if sqlData.IsSome then 
    printfn "%A" sqlData.Value
else
    printfn "No data in database !"
By on 1/9/2010 4:53 PM ()

Hello, Marekb:
Thank you very much. Your code in January 7 seems to be a good one.
I have changed it a little, so it looks like:
let GetSqlData3(sqlCmd: string) =
use connDB = new SqlConnection( sql_ConnDB )
use cmd = connDB.CreateCommand()
cmd.CommandType <- CommandType.Text
cmd.CommandText <- sqlCmd
connDB.Open()
let result = new ResizeArray<Dictionary<string,obj>>()
use reader = cmd.ExecuteReader()
let lnCountFields = reader.FieldCount
while( reader.Read()) do
// tuple as Dictionary
let tuple = new Dictionary<string,obj>()
for index in 0 .. lnCountFields-1 do
tuple.Add ( reader.GetName(index),reader.[reader.GetName(index)])
result.Add( tuple)
result.ToArray()

And I use this to get the result:
let dataTable2 = "SELECT Field1, Field2 FROM TABLE2"
I also tried data tables with different fields, including 2, 3, 4, 5, 9 and they all worked.
However, I want one more step further. If I get only one result set, I want to put the result into a tuple, so I can easily use the value, how I can do this?
For example, I want to use something like this:
let (aF1, aF2) = "SELECT TOP 1 Field1, Field2 FROM Table2"
But I got the error message:
This expression was expected to have type 'a * 'b but here has type Dictionary<string,obj> []
Any idea?
Thanks,

By on 1/10/2010 7:54 AM ()

This expression was expected to have type 'a * 'b but here has type     Dictionary<string,obj> []
Any idea?
Thanks,

That tells you exactly what is wrong, the return type of your 'generic' function doesn't match what you expect in the caller.

If you really want it sort of generic, the following is the closest I can get, making use of the nice LINQ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

type Region() =

  [<DefaultValue>]val mutable private _RegionId : int

  [<DefaultValue>]val mutable private _RegionDescription : string

  

  member public this.RegionId

    with get() = this._RegionId

    and set v = this._RegionId <- v

    

  member public this.RegionDescription

    with get() = this._RegionDescription

    and set v = this._RegionDescription <- v

  

let inline Query2StrongType<'a when 'a:(new: unit->'a)> c q =

  let conn = new SqlConnection(c)

  let cmd = conn.CreateCommand()

  cmd.CommandText <- q

  cmd.CommandType <- CommandType.Text

  let res = cmd.ExecuteReader()

  let nw = new DataContext(conn)

  let d = nw.Translate<'a>(res)

  d

let x = Query2StrongType<Region> "abc" "select * from Region"

However, I would prefer C# rather than F# for this particular case :

1. all the members needs to be mutable so C# is shorter than F#

2. the auto get;set; in C# makes it even shorter.

PS. how can one properly include code in post ?

By on 1/10/2010 10:51 AM ()

Hello, Gary:
Thank you very much for your code.
But I think there is some HTML encoding issue, your code seems to be not correctly shown on the page. I have tried to use the following code:

type Region() = class
val mutable private _RegionId : int
val mutable private _RegionDescription : string

member public this.RegionId
with get() = this._RegionId
and set v = this._RegionId
member public this.RegionDescription
with get() = this._RegionDescription
and set v = this._RegionDescription
let inline Query2StrongType'a)> c q =
let conn = new SqlConnection(c)
let cmd = conn.CreateCommand()
cmd.CommandText <- q
cmd.CommandType <- CommandType.Text
let res = cmd.ExecuteReader()
let nw = new DataContext(conn)
let d = nw.Translate(res)
d

let x = Query2StrongType "abc" "select * from Region"

end

But I got the error message:
Incomplete structured construct at or before this point in pattern
For the statement: let inline Query2StrongType'a)> c q =
What did you really want for this statement.
Besides, can you explain how to use your code.
Thanks,

By on 1/10/2010 2:06 PM ()

I have no idea how to include the < properly in the post.

The basic idea is to use the already created function(which you want) describe here [link:msdn.microsoft.com]

to turn a query result into array of a strong type. You would need to define the strong type once(but that is a good thing for C#/F# so you can minimize runtime type mismatch handling, or least localize it).

My code is just a demonstration of the idea, and don't expect it can be used unmodifed.

By on 1/10/2010 4:01 PM ()

Hello, Gary:
I took a look at the MS web site, but I do not understand. Therefore, your code is rather important.
I think it is easy to show <
Just type in this forum without copying and pasting from F#, it should be OK. I guess < is just "<-", right? So if I can show you this, then it is the same for you. By the way, I am using FireFox, not IE on the forum.
Please let me know what you really want for this statement:
let inline Query2StrongType'a)> c q =
Thanks,

By on 1/11/2010 10:11 AM ()

well, I have no idea how to put the angle brackets

it is basically

Query2StrongType['a]

but replace the square with angel

By on 1/11/2010 10:55 AM ()

Hi, Gary:
Do you mean this?
let inline Query2StrongType<'a> c q =

But for me, it seems rather strange.
What does it mean?

By on 1/11/2010 12:06 PM ()

yes I meant that.

This is called 'generic' which is what you are asking for, "one function that don't need to be changed in the future". This is such a function, you supply the desired strong type and it would fill out the fields using column name matches.

I believe you need some basic understanding of C# and the .NET framework if you want to write such a function.

That said, 80% of it has been written in LINQ, you just need to put them together, like my code showed.

BTW

it should also be read as nw.Translate['a](...) again replace square with angel

By on 1/11/2010 12:29 PM ()

Hello, Gary:
I have tried to create a class using your code.
The following is my code.

#light
open System
open System.Collections.Generic
open System.Data
open System.Data.SqlClient

type Region() = class
val mutable private _RegionId : int
val mutable private _RegionDescription : string

member public this.RegionId
with get() = this._RegionId
and set v = this._RegionId
member public this.RegionDescription
with get() = this._RegionDescription
and set v = this._RegionDescription
let inline Query2StrongType<'a> c q =
let conn = new SqlConnection(c)
let cmd = conn.CreateCommand()
cmd.CommandText <- q
cmd.CommandType <- CommandType.Text
let res = cmd.ExecuteReader()
let nw = new DataContext(conn)
let d = nw.Translate(res)
d

end

However, I got the following errors:
Unintialized 'val' fields in implicit construction types must be mutable and marked with the '[<DefaultValue>]' attribute. Consider using a 'let' binding instead of a 'val' field. For the following statements:
val mutable private _RegionId : int
val mutable private _RegionDescription : string

This expression was expected to have type unit but here has type int for this:
and set v = this._RegionId

This expression was expected to have type unit but here has type string for this:
and set v = this._RegionDescription

I don't have enough knowledge about C#, but I have adequate knowledge about VB.NET.
Let me know how to make it compiled in F#, and how I can use it.
Thanks,

By on 1/11/2010 1:01 PM ()

unless I find a way to post proper code here, there isn't much I can do.

the [<DefaultValue>] error is just that, read the error message and you can find out why. I encountered the same thing and add it back after reading the error message but it got eaten by the forum.

same goes for the other errors as they are all indentation problems which got eaten and you should not have problem putting them back. I would recommend you get a book of F# and learn the basic things first, like how a F# program is formatted like white space is significant etc.

The only thing is that the 'let Query2...' is not inside the Region class, it is a stand alone function.

If you are coming from a VB.NET background, please get yourself familiar with 'generics' in VB.NET as this is a very common thing in F#. Not knowing that won't get you too far no matter what.

By on 1/11/2010 1:22 PM ()

Hello, Gary:
I changed the code, so it looks like this:

#light
open System
open System.Collections.Generic
open System.Data
open System.Data.SqlClient

type Region() = class
val mutable private _RegionId : int
val mutable private _RegionDescription : string

member public this.RegionId
with get() = this._RegionId
and set v = this._RegionId
member public this.RegionDescription
with get() = this._RegionDescription
and set v = this._RegionDescription

let inline Query2StrongType<'a> c q =
let conn = new SqlConnection(c)
let cmd = conn.CreateCommand()
cmd.CommandText <- q
cmd.CommandType <- CommandType.Text
let res = cmd.ExecuteReader()
let nw = new DataContext(conn)
let d = nw.Translate(res)
d

end

There are 2 error:
Block following this 'let' is unfinished. Expect an expression. For this:
let d = nw.Translate(res)

Unexpected identifier in member definition for this: d

According to MS web site: [link:msdn.microsoft.com]

Each row in the IDataReader is converted to an object in the IEnumerable<(Of <(T>)>).
But don’t quite understand the meaning of this statement, could you provide some sample on how to use it? But first, how I can make it compiled in F#, there are not many errors this time.
Thanks,

By on 1/11/2010 2:00 PM ()

pull this line

let inline Query2StrongType

to the left(out dent)

then move the whole thing(all the way to the line d) out of the class. this is a function outside the class definition

and as I said, Translate should be Translate['a], replace square with angle

By on 1/11/2010 2:06 PM ()

Hello, Gary:
I changed the code to look like this:
#light
open System
open System.Collections.Generic
open System.Data
open System.Data.SqlClient

type Region() = class
val mutable private _RegionId : int
val mutable private _RegionDescription : string

member public this.RegionId
with get() = this._RegionId
and set v = this._RegionId
member public this.RegionDescription
with get() = this._RegionDescription
and set v = this._RegionDescription
end

let inline Query2StrongType<'a> c q =
let conn = new SqlConnection(c)
let cmd = conn.CreateCommand()
cmd.CommandText <- q
cmd.CommandType <- CommandType.Text
let res = cmd.ExecuteReader()
let nw = new DataContext(conn)
let d = nw.Translate(res)
d

However, I got the following errors:
Unintialized 'val' fields in implicit construction types must be mutable and marked with the '[<DefaultValue>]' attribute. Consider using a 'let' binding instead of a 'val' field. For the following statements:
val mutable private _RegionId : int
val mutable private _RegionDescription : string

This expression was expected to have type unit but here has type int for this:
and set v = this._RegionId

This expression was expected to have type unit but here has type string for this:
and set v = this._RegionDescription
The type 'DataContext' is not defined
let nw = new DataContext(conn)

If my code is different from your code, let me know.
But I think it seems the class definition is missing something: there is no method defined, right?
Thanks,

By on 1/12/2010 10:40 AM ()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

type Region() =
  [<DefaultValue>]val mutable private _RegionId : int
  [<DefaultValue>]val mutable private _RegionDescription : string
  
  member public this.RegionId
    with get() = this._RegionId
    and set v = this._RegionId <- v
    
  member public this.RegionDescription
    with get() = this._RegionDescription
    and set v = this._RegionDescription <- v
  
let inline Query2StrongType<'a when 'a:(new: unit->'a)> c q =
  let conn = new SqlConnection(c)
  let cmd = conn.CreateCommand()
  cmd.CommandText <- q
  cmd.CommandType <- CommandType.Text
  let res = cmd.ExecuteReader()
  let nw = new DataContext(conn)
  let d = nw.Translate<'a>(res)
  d

let x = Query2StrongType<Region> "abc" "select * from Region"
By on 1/12/2010 4:11 PM ()

Hello, Gary:
Thank you for your code. I have changed my code, and I post all of them here:
#light
open System.Data
open System.Data.SqlClient

type Region() =
[<DefaultValue>]val mutable private _RegionId : int
[<DefaultValue>]val mutable private _RegionDescription : string

member public this.RegionId
with get() = this._RegionId
and set v = this._RegionId <- v

member public this.RegionDescription
with get() = this._RegionDescription
and set v = this._RegionDescription <- v

let inline Query2StrongType<'a when 'a:(new: unit->'a)> c q =
let conn = new SqlConnection(c)
let cmd = conn.CreateCommand()
cmd.CommandText <- q
cmd.CommandType <- CommandType.Text
let res = cmd.ExecuteReader()
let nw = new DataContext(conn)
let d = nw.Translate<'a>(res)
d

let x = Query2StrongType<Region> "abc" "select * from Region"

I got 2 errors:
1. The type 'DataContext' is not defined: for let nw = new DataContext(conn)

2. Lookup on object of indeterminate type based on information prior to this program point. A type annotation may be needed prior to this program point to constrain the type of the object. This may allow the lookup to be resolved. It is for this statement: let d = nw.Translate<'a>(res)

By the way, I am using VS2010 with F# 1.9.7.4
Any idea?
Thanks,

By on 1/13/2010 11:22 AM ()

you need to add the system.data.linq assembly to the reference.

the microsoft documentation about the translate method will show you exactly which name space it is in.

I continue to suggest that you spend some time understanding basic stuff as otherwise, if you take this code, you won't help much in incorporating it into your over all system

people can only give you rough ideas/directions like the code I show is not doing proper disposal of those objects(and I didn't intend to, it was a show case of the LINQ Translate function, nothing else).

By on 1/13/2010 11:54 AM ()

Hi
I don't know how to create dynamic tuple ( unlimited size ) in F#,
but i try this simulate using Dictionary. Then result of my function is array of "dynamic tuples". 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

 

open System
open System.Data
open System.Data.SqlClient
open System.Collections.Generic


let sqlConnStr = 
    let sqlConnStrBuilder = new SqlConnectionStringBuilder()
    sqlConnStrBuilder.DataSource <- "(local)"
    sqlConnStrBuilder.InitialCatalog <- "localDb"
    sqlConnStrBuilder.IntegratedSecurity <- true
    sqlConnStrBuilder.ConnectionString


printfn "%s" sqlConnStr

let GetSqlData() =

try 

use connDB = new SqlConnection( sqlConnStr )
use cmd = connDB.CreateCommand()
cmd.CommandType <- CommandType.Text
cmd.CommandText <- "Select * FROM Table1"
connDB.Open()
let result = new ResizeArray<Dictionary<string,obj>>()
use reader = cmd.ExecuteReader()
let lnCountFields = reader.FieldCount

while( reader.Read()) do

// tuple as Dictionary
    let tuple = new Dictionary<string,obj>() 
    for index in 0 .. lnCountFields-1 do 
    tuple.Add ( reader.GetName(index),reader.[reader.GetName(index)])
    result.Add( tuple)
Some( result.ToArray() )

with 
    | ex -> printfn "%s" ex.Message
    None 

let sqlData = GetSqlData()
if sqlData.IsSome then 
    printfn "%A" sqlData.Value
else
    printfn "No data in database !"
By on 1/8/2010 4:16 PM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper