1

I'm trying to return data from a stored proc on SQL Server 2008 R2. (The stored proc didn't play nicely with Microsoft.FSharp.Data.TypeProviders and I wasted most of the morning going down this seemingly futile path.)

So I get a sequence of tables from the following code:

open System.Data
open System.Data.SqlClient

let connString = @"Data Source=someserver;Initial Catalog=somedb;Integrated Security=True"
let conn = new SqlConnection(connString)

let GetTables spName baseTableName =
    use comm = new SqlCommand(spName, conn, CommandType=CommandType.StoredProcedure)
    comm.CommandTimeout <- 90
    comm.Parameters.AddWithValue("@TableName", baseTableName) |> ignore
    use adapter = new SqlDataAdapter(comm)
    use dataSet = new DataSet()
    adapter.Fill(dataSet) |> ignore
    dataSet.Tables |> Seq.cast<DataTable>

let tableSeq = GetTables @"dbo.uspGetPremium" "0123_Premium_99"

Ultimately I want to extract each column into a separate array (or maybe list or sequence). Some columns are string, some are int, and some are float.

I figured it would be easy to do some nested loop or maybe a .Map or .Iter function to iterate through all rows and columns and store the value to the proper column array. (If there's a better way to do this--and I feel like there should be--any suggestions are appreciated!)

But the returned value is of type obj and I can't figure out how to downcast to the proper type.

let table0 = tableSeq |> Seq.item 0
table0.Rows.[0].[1];;

val table0 : DataTable = Table
val it : obj = 134

From the code below, I can tell Columns.[1] has type System.Int32.

table0.Columns.[1].DataType

But if I take the first element from that column, how can I downcast to the same type specified in .DataType above? This fails:

table0.Rows.[0].[1] :?> table0.Columns.[1].DataType
Talmage
  • 343
  • 1
  • 11

1 Answers1

4

The target type of a cast must be statically known (it can't be an arbitrary runtime expression like you're using), so you have to use

table0.Rows.[0].[1] :?> int

once you've figured out the type.

kvb
  • 54,864
  • 2
  • 91
  • 133
  • I see. I suppose I'll need to have some mapping of column names anyway to know what to do with the contents so perhaps there wouldn't have been much gained by being able to dynamically downcast anyway. – Talmage Apr 07 '17 at 03:33