title: Store and then re-use sql connection strings in powershell
body: <p>Is there a way to save sql connection details and re-use them in the same script. I've written the below that exports the contents of 3 sql tables on the same database. It works but it re-uses a load of code and I can't figure out how to reduce it.</p>
  
  <p>I've tried declaring all the connection information and then stating it where the full code is now, but that just outputs the first table 3 times.</p>
  
  <p>I've also tried stating it once at the beginning which again didn't work.</p>
  
  <pre><code>#agent_lookup
  
  $server = "test"
  $database = "MI_Lookups"
  $query = "EXEC [dbo].[proc_update_new_agents]
            SELECT  [l].agent_key AS 'Agent_Key'
                    ,ISNULL([l].agent_name,'') AS 'Agent_Name'
                    ,ISNULL([l].agent_name_1,'') AS 'Agent_Name_1'
                    ,ISNULL([l].agent_type,'') AS 'Agent_Type'
            FROM [dbo].[agent_test] AS [l]
            ORDER by [l].agent_key asc"
  
  $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")
  $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
  $DataSet = New-Object System.Data.DataSet
  
  $SqlAdapter.Fill($DataSet)
  $DataSet.Tables[0] | Export-Csv $saveloaction\"Agent_Lookup.csv"
  $connection.Close()
  
  #organisation_lookup
  
  $server = "test"
  $database = "MI_Lookups"
  $query = "EXEC [dbo].[proc_update_new_organisations]
            SELECT  [l].organisation_key AS 'Organisation_Key'
                    ,ISNULL([l].broker_name,'') AS 'Broker_Name'
                    ,ISNULL([l].team_member,'') AS 'Team_Member'
                    ,ISNULL([l].team_leader,'') AS 'Team_Leader'
            FROM [dbo].[orgnisation_test] as [l]
            ORDER BY [l].organisation_key asc"
  
  $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")
  $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
  $DataSet = New-Object System.Data.DataSet
  
  $SqlAdapter.Fill($DataSet)
  $DataSet.Tables[0] | Export-Csv $saveloaction\"Organisation_Lookup.csv"
  $connection.Close()
  
  #insurer_lookup
  
  $server = "test"
  $database = "MI_Lookups"
  $query = "EXEC [dbo].[proc_update_new_insurers]
            SELECT  [l].insurer_key AS 'Insurer_Key'
                    ,ISNULL([l].insurer_name,'') AS 'Insurer_Name'
                    ,ISNULL([l].insurer_name_1,'') AS 'Insurer_Name_1'
                    ,ISNULL([l].team_member,'') AS 'Team_Member'
                    ,ISNULL([l].team_leader,'') AS 'Team_Leader'
            FROM [dbo].[insurer_test] AS [l]
            ORDER BY [l].insurer_key asc"
  
  $connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=$server;Integrated Security=SSPI;Initial Catalog=$database;")
  $command = New-Object System.Data.SqlClient.SqlCommand($query, $connection)
  $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
  $DataSet = New-Object System.Data.DataSet
  
  $SqlAdapter.Fill($DataSet)
  $DataSet.Tables[0] | Export-Csv $saveloaction\"Insurer_Lookup.csv"
  $connection.Close()
  </code></pre>
  
  <p>It would seem logical to me to only need to write the connection information once, but I'm new to powershell and struggling to make this as simple as I'm sure it should be. The above is basically the same script repeated three times with a different table referenced.</p>
  
  <p>Thanks as always.</p>