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"?
-
Have you checked this - stackoverflow.com/questions/25682703/… Once you get data into DataSet you can easily get column by referencing the table.Mitul– Mitul2015-11-06 17:54:44 +00:00Commented Nov 6, 2015 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– barrypicker2015-11-06 18:00:38 +00:00Commented Nov 6, 2015 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– Mitul2015-11-06 18:03:09 +00:00Commented Nov 6, 2015 at 18:03
-
I would prefer to use the SqlDataReader rather than DataSet or DataTables...barrypicker– barrypicker2015-11-06 18:32:24 +00:00Commented Nov 6, 2015 at 18:32
-
Not that different if you know basic ADO.NET - social.technet.microsoft.com/Forums/en-US/…Mitul– Mitul2015-11-06 18:34:23 +00:00Commented Nov 6, 2015 at 18:34
Add a comment
|
1 Answer
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.
