0

Say I have a table with 3 columns - "Column1", "Column2", and "Column3" - datatype is varchar(100) for all 3.

Using PowerShell, how do I connect to SQL Server and use SqlDataReader and ForEach operator to view the contents of "Column2"?

Tseng
  • 61,549
  • 15
  • 193
  • 205
barrypicker
  • 9,740
  • 11
  • 65
  • 79
  • Have you checked this - http://stackoverflow.com/questions/25682703/connect-to-sql-server-database-from-powershell Once you get data into DataSet you can easily get column by referencing the table. – Mitul Nov 06 '15 at 17:54
  • @Mitul - your link shows example code of connecting and issuing a query, but it does not show iterating the result set and evaluation of a particular column... – barrypicker Nov 06 '15 at 18:00
  • So can you try $DataSet.Tables[0].Rows[0]["Column2"] to see it works. That's how it works in .Net. – Mitul Nov 06 '15 at 18:03
  • I would prefer to use the SqlDataReader rather than DataSet or DataTables... – barrypicker Nov 06 '15 at 18:32
  • Not that different if you know basic ADO.NET - https://social.technet.microsoft.com/Forums/en-US/1ccf16ce-d771-49cb-825e-8330bf2e1e99/powershell-possible-to-return-a-datareader-object-from-a-function-call?forum=ITCG – Mitul Nov 06 '15 at 18:34

1 Answers1

6

Here's roughly how I'm doing it:

$SqlServer = 'sql.example.com';
$SqlDatabase = 'MyDB';

$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;
$SqlQuery = "SELECT Name FROM dbo.Person ORDER BY Name;";

$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;
$SqlCommand = $SqlConnection.CreateCommand();
$SqlCommand.CommandText = $SqlQuery;

$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader();

#Fetch data and write out to files
while ($SqlDataReader.Read()) {
    Write-Output $SqlDataReader['Name'];
}
$SqlConnection.Close();
$SqlConnection.Dispose();

If I remember right, I basically refactored the code from the MSDN example.

For those wondering why I'm using SqlDataReader: Most of my scripts use SqlDataAdapter, but this one retrieves about 8,000 PDFs from a database so I wasn't really interested in calling SqlDataAdapter.Fill(). In exchange for holding shared locks on the table much longer than SqlDataAdapter.Fill() would, SqlDataReader.Read() keeps memory usage down to a manageable level for the client by fetching one record at a time.

Bacon Bits
  • 30,782
  • 5
  • 59
  • 66