Last active
October 5, 2017 20:15
-
-
Save cmbrown1598/a2db5caf961b7592da3ba2535996ebfc to your computer and use it in GitHub Desktop.
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" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment