Quick ADO.NET in FSI

This morning, a user came to me with a nasty problem. At {Redacted}, we have an application which shows Account Data, one account at a time called AccountView. It’s an app that was developed years ago, which functioned admirably, but given {Redacted}’s success, the one at a time nature of the application is showing it’s age. But, like many enterprise apps in the world, we’ve just let it go on, figuring we’ll eventually get it to.

See the application is a simple one that shows a simple drop-down list of accounts, and allows the user to select one. Once selected, it fills up 3 grids with various tables after executing a fairly complex stored procedure with the selected account’s identifier as a parameter.  That stored procedure performs some pretty weighty calculations.

Still, my user came with a nasty issue. 2000 accounts had been audited, and she needed a simple sum of values. As far as she knew, the only way to get that data would be to use AccountView and hit each account, copy the grid data, sum it up, and then put it in a spreadsheet.  She was estimating about a week of doing this, and cross-checking it.

I did it using F# and Ionide in VS Code in about 20 minutes. I show it here, leaving out any proprietary bits that may cause {Redacted} to have a fit. Here’s the code. Hopefully it helps you out in your next “hey can you get me a ton of data that requires a stored procedure to get at” adventure.

open System
open System.Data
open System.Data.SqlClient
let bigGrossQuery = "SELECT c.id, c.accountName
FROM dbo.accounts AS c
WHERE c.accountName IN
let connectionString = "Server=myserver.redacted.org;
let queryForAccounts() =
use conn = new SqlConnection(connectionString)
use command = new SqlCommand(bigGrossQuery, conn)
command.CommandType <- CommandType.Text
let reader = command.ExecuteReader(CommandBehavior.CloseConnection)
[while reader.Read()
do yield (reader.GetInt32(0), reader.GetString(1)) ]
let executeSproc (accountId : int) =
use conn = new SqlConnection(connectionString)
use dataAdapter = new SqlDataAdapter()
use command = new SqlCommand("mySproc", conn)
let param = SqlParameter("@id", SqlDbType.Int)
let dataSet = new DataSet()
command.CommandType <- CommandType.StoredProcedure
param.Value <- accountId
|> ignore
dataAdapter.SelectCommand <- command
|> ignore
let filtered = dataSet.Tables.[1].Select("myFilter = 1")
|> Array.sumBy (fun a -> System.Decimal.Parse(a.["summable_column"].ToString()))
let main filePath =
let accounts = queryForAccounts()
let lines = accounts
|> List.map (fun (id, name) ->
let result = executeSproc id
sprintf "%s,%f2" name result
|> List.toArray
System.IO.File.WriteAllLines(filePath, lines)
main @"\\server\file.csv"

view raw


hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s