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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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 | |
| ( << A GIGANTIC LIST OF ACCOUNTS >> );" | |
| let connectionString = "Server=myserver.redacted.org; | |
| Database=MY_BACKUP_DB; | |
| Trusted_Connection=True;" | |
| let queryForAccounts() = | |
| use conn = new SqlConnection(connectionString) | |
| use command = new SqlCommand(bigGrossQuery, conn) | |
| command.CommandType <- CommandType.Text | |
| conn.Open() | |
| 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 | |
| command.Parameters.Add(param) | |
| |> ignore | |
| dataAdapter.SelectCommand <- command | |
| conn.Open() | |
| dataAdapter.Fill(dataSet) | |
| |> ignore | |
| let filtered = dataSet.Tables.[1].Select("myFilter = 1") | |
| filtered | |
| |> 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" |
Christopher Brown's - Blog 