Part 3 of 3
Now, here's the sample code were you can connect and data from a SQL server. Using the same connection type from the previous samples. Now, we can run a SQL Query using the 'Select ...from..." and load the data into our PowerShell variable. We accomplished this task using the
SqlClient.SqlDataAdapter class. Let's chek it out:
Read/Display Data from SQL table
|
## - Defining your SQL Server Parameters
$SqlServer = "YourSQLServerName";
$SqlDatabase = "YourDatabaseName";
## - Building the SQL connection string
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlDatabase; " `
+ "Integrated Security = True; Connect TimeOut=300";
## - Open connection to SQL Server
$SqlConnection.Open()
## - Define your SQL statement
$SqlQuery = "Select LastName, FirstName, City, State from tblPerson";
## - Section to SQLQuery
$SqlCmd.CommandText = $SqlQuery;
$SqlCmd.Connection = $SqlConnection;
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
## - Building the Dataset collection objects that will store the results of the query
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet);
## - Close connection to SQL Server
$SqlConnection.Close();
## - Pipe the Dataset collection and display the records on screen
$DataSet.Tables[0] | Select LastName, FirstName, City, State | FT -autosize ;
## - End of Script
|
Please, to test this script you need to build a table to similate the sample. But this code will work, So at the end you will be able to list the records from the table. The rest you could use your imagination.
This is the end of the 3 part shortcut series,