I have a list of queries to use for an array of associated output files. I'm having a block as to what a good design would be to put it all together for my queries to be executed and the output sent to the files in a smooth way. Usually I don't have the same thing that needs doing so many times. This is with powershell but I should be able to use most design ideas given. It looks like this:
$SQLquery_1 = @" blah "@ $SQLquery_2 = @" blah "@ #There's maybe 10 different queries #The output files $extractFiles = @("C:\temp\ProfileLongLangBak.csv","C:\temp\ProfileLongCredBak.csv", blah blah list of files) #and of course there's about 10 powershell commands to do the sql query which works just fine. The below hasn't incorporated separate sql queries and output files yet #create sql connection to connect to the sql DB $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "Server=sqldev;Database=M_Test;User ID=blah;Password=blah" $sqlConnection.Open() #Create a SqlCommand object to define the query $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlCmd.CommandText = $SQLquery_Languages $sqlCmd.Connection = $sqlConnection #create a SqlAdapter that actually does the work and manages everything $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlAdapter.SelectCommand = $sqlCmd #create an empty dataSet for the query to fill with its results $dataSet = New-Object System.Data.DataSet #execute the query and fill the dataSet (then disconnect) $sqlAdapter.Fill($dataSet) $sqlConnection.Close() #dump the data to csv $DataSet.Tables | Export-Csv $extractFile #this may not be comma delimited...need to check
My first thought is to have the sql queries in an array and loop through both arrays with a for loop starting at $i=0. But the queries are too long to have in an array.
I was thinking of putting all the powershell commands for the query in a function and call the function with each query and output file as input and with each call another file of results would be written.
Does anyone have a good idea how to handle the long query strings in a smooth design to go through the long list of queries and output files? Thanks!