Back to Questions
1.4k
views5
comments
Happ New Year! (SQL generated array question)
f#compiler

Dear All:
First of all, Happy New Year to you all!
Now I have a question:
I have an array of 3 tuples, and I want to put the array into a dictionary, so I can look up the dictionary, and it worked well:
> #light
open System
open System.Collections.Generic

let arryDict = [|('A', 1.0); ('B', 2.0); ('C', 3.0)|]
let d = new Dictionary<string,float>()
for j = 0 to arryDict.Length - 1 do
d.Add((fst arryDict.[j]).ToString(), (snd arryDict.[j]));;

val arryDict : (char * float) [] = [|('A', 1.0); ('B', 2.0); ('C', 3.0)|]
val d : Dictionary<string,float> = dict [("A", 1.0); ("B", 2.0); ("C", 3.0)]

But acutally, what I really have is the data from a data table:
CREATE TABLE [dbo].[Table1] (
[NAME] [nvarchar](1) NOT NULL,
[Value] [float] NOT NULL )
INSERT INTO Table1 VALUES('A',1.0)
INSERT INTO Table1 VALUES('B',2.0)
INSERT INTO Table1 VALUES('C',3.0)

Now I use the following function to read the value into an array of tuples:
let sql_ConnString = "server=(local);Integrated Security=True;Database=LocalDB"
let connDB = new SqlConnection(sql_ConnString)

let sqlRead cmdText =
use cmd = new SqlCommand(cmdText, connDB)
connDB.Open()
let reader = cmd.ExecuteReader()
seq { while reader.Read() do
yield reader.GetSqlValue(0)
reader.Close()
connDB.Close() }

let arrayDict = sqlRead "SELECT Name, Value FROM Table1"
|> Seq.toArray

Then, I want to use the same code to put the array generted by SQL statement into a dictionary:

let d = new Dictionary<string,float>()
for j = 0 to arrayDict.Length - 1 do
d.Add((fst arrayDict.[j]).ToString(), (snd arrayDict.[j]))

But I got the compiler errors:
This expression was expected to have type 'a * 'b but here has type obj
This expression was expected to have type 'a * float but here has type obj

The same code worked for directly assigned array, but not for the array generated by SQL statement.
I can see the array was correctly generated by the SQL statement. May be the type inference can not guess the correct type of the array generated by SQL satement.
Let me know if I made any mistake or show me the correct code which can work in my case.
Thanks and happy new year to you!

.

what else can you expect ? reader.GetSqlValue(0) would conceptually return '1 ROW of data' which is basically 'object' that the compiler is telling you.

I believe you need to decompose that into your own array element structure.

By on 1/1/2010 12:58 PM ()

Hello, Gary:
Thank you very much for reminding. I have changed the code, and it worked.
let sqlRead2 cmdText =
use cmd = new SqlCommand(cmdText, connPennyDB)
connPennyDB.Open()
let reader = cmd.ExecuteReader()
seq { while reader.Read() do
yield (reader.GetSqlValue(0), reader.GetSqlValue(1))
reader.Close()
connPennyDB.Close() }

let arrayDict = sqlRead2 "SELECT Name, Value FROM Table1"
|> Seq.toArray
let d = new Dictionary<string, obj>()
for j = 0 to arrayDict.Length - 1 do
printfn "Done"
d.Add((fst arrayDict.[j]).ToString(), (snd arrayDict.[j]))

However, is it possible that I use "float" or some other more specific type instead of using "obj" in the Dictionary binding?
I found it is rather difficult as, the type inference has to make sure the data type returned by SQL statement and the dictionary data type have to match.
Do you have a good idea?
Thanks and Happy New Year to you!

By on 1/1/2010 2:51 PM ()

Since the SQL statement is not inspectable at compile time (in contrast to LINQ2SQL and such), you need dynamic casts.

1

sqlRead2 "hi" |> Seq.map (fun (s,f) -> s :?> string, f :?> float) |> dict

If you ever want to increase the level of abstraction, I'd use a 3rd party library like LINQ2SQL, EF or NHibernate.

PS: Why use GetSqlValue instead of GetValue?

By on 1/2/2010 4:50 AM ()

Hello, Kha:
Thank you very much for your reply.
However, I don't quite understand your meaning.
Could you please show the whole code sample? I mean from this statement:
let arrayDict = sqlRead "SELECT Name, Value FROM Table1"
So, I will understand better.
I have tried to change getsqlvalue to getvalue, when still using obj, it works, there is no difference between getsqlvalue and getvalue when using obj; but if I use float, I got compiler error for getvalue:
Type mismatch. Expecting a obj * float but given a obj * obj
The type 'float' does not match the type 'obj'
The following is the code:
let arrayDict = sqlRead2 "SELECT Name, Value FROM Table1"
|> Seq.toArray
let d = new Dictionary<string, float>()
for j = 0 to arrayDict.Length - 1 do
printfn "Done"
d.Add((fst arrayDict.[j]).ToString(), (snd arrayDict.[j]))

Thanks

By on 1/2/2010 2:34 PM ()

Well, there's not much to be added.

1
2
3
4

let arrayDict =
    sqlRead2 "SELECT Name, Value FROM Table1"
    |> Seq.map (fun (s,f) -> s :?> string, f :?> float)
    |> dict

I have tried to change getsqlvalue to getvalue, when still using obj, it works, there is no difference between getsqlvalue and getvalue when using obj; but if I use float, I got compiler error for getvalue:

That's quite strange; GetValue and GetSqlValue have the same signature, so how could exchanging them possibly yield a type error?

By on 1/3/2010 1:55 AM ()

    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