F# Get database table data

2
votes

I'm trying to get data from an Azure SQL Server. I've Been able to get the data through this method:

let db = dbSchema.GetDataContext()
    let serviceType = db.table
    serviceType

I then go on to do some type casting where I get the actual data. But as my program has progressed I need a new way of getting the data.

I'm able to get a list of column names with this piece of code:

 let columnList = (new SqlCommandProvider<"select * from Information_schema.Columns where table_name = @tableName",connectionString).Execute(tableName)

I'm wondering if there's a similar way to get the data.

I've tried:

let data = (new SqlCommandProvider<"select * from @tableName",connectionstring).Execute(tableName)

But I get this error: "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 object. This may allow the lookup to be resolved."

2
sqlf#
the second example should help: fsprojects.github.io/FSharp.Data.SqlClient - s952163
The probability provider? - Mark
no, the SqlCommandProvider - s952163
I've made a number of attempts at it but I still end up with either that line I've already tried, or an error stating the table name input variable is not defined. - Mark
Maybe that could be a different Q, on how to parametrize (if possible) the table name in the query. - s952163

2 Answers

2
votes

I came up with this.

let GetData (tableName : string) =
    let cn = new SqlConnection(connectionstring)
    cn.Open()
    let sQL = "select * from [" + tableName + "]"
    let db = new SqlCommand(sQL, cn)
    db.ExecuteReader()

From here you can access your data. So assign db.ExecuteReader() to a variable then...

let dataSource = db.ExecuteReader()
let mutable tableData = List.empty

while dataSource.Read() do
    let rowLength = dataSource.FieldCount
    let rowData = Array.zeroCreate(rowLength)

    for i = 0 to dataSource.FieldCount-1 do 
        rowData.SetValue(dataSource.GetValue(i),i)

    tableData <- rowData :: tableData

tableData |> List.toArray

This returns all the values in the table

0
votes

This is one way (using the SqlClient type provider) to select all data from a table. If your table is too large it will return a lot. So I just select the Top 1000 rows. Replace tablenn with your choice of table name. You can parametrize the columns, but if you need to put the table name itself as a parameter you might need to use a Stored Procedure that takes a Table as a parameter (or use quotations in other type providers).

However, this is a string so it's very easy to build it. Then again, it has to be a literal.

#r @"..\packages\FSharp.Data.SqlClient.1.8.2\lib\net40\FSharp.Data.SqlClient.dll"

open FSharp.Data
open System

[<Literal>]
let connectionString = @"Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=C:\Users\userName\Documents\Test.sdf.mdf;Integrated Security=True;Connect Timeout=10"

[<Literal>]
let tblnn = "MyBigTable"

[<Literal>]
let qry = "SELECT TOP 1000 * FROM " + tblnn

let cmd = new SqlCommandProvider<qry, connectionString>(connectionString)
cmd.Execute() |> Seq.toArray

You can also use the stock SqlDataConnection type provider. It also makes it easy to select all data from a table.