This blog has moved to http://ThePowerShellGuy.com
In my post about getting data from Excel in MSH : Using ADO to get data from Excel to Dataset in Monad.
, I promised an SQL server example.
so here an example to get the data from a table or view (query) in a SQL server to a DataSet in Monad.
we do not use the System.Data.OleDb here, for a SQL server connection there is a special namespace in .NET, system.data.sqlclient.
as indicated in the Excel post for the rest is basicly the same, only I did format the Connectionstring a bit by using a Stringbuilder to split it up in Parts.
you just fill the parameters,
$source = SQL server name
$Catalog = the DateBase to Connect to :
$view = the View or Table to load.
I HardCoded the "Select *", as you can filter later, but you can also change this.
So now it looks like this.
$Source = "SQLServer"
$Catalog = "MowMON"
$view = "tblAdUsers"
$SB = New-object System.Text.StringBuilder
$SB = $SB.Append("Integrated Security=SSPI")
$SB = $SB.Append(";Persist Security Info=False")
$SB = $SB.Append(";Initial Catalog=$Catalog")
$SB = $SB.Append(";Data Source=$Source")
$SQLcon = New-object system.data.sqlclient.SqlConnection
$SQLcon.ConnectionString = $SB.ToString()
$SelectCMD = New-object system.data.sqlclient.SqlCommand
$SelectCMD.CommandTimeout = 30
$SelectCMD.Connection = $SQLCon
$SelectCMD.CommandText = "Select * from $view"
$da = new-object System.Data.SqlClient.SQLDataAdapter($SelectCMD)
$ds = new-object System.Data.dataset
Afer running this you can do things like this :(I had a Table with all users Loaded)
(only I few as there are already more in the Excel example)
the first export a selection of the table to CSV file.
The second example does get the colums, I added this example, because the whole Table is a Property of the Column (and gets Partly Expanded), so If you do not do this you can get a lot of output (I had 4000+ Records in my Dataset ;-)).
the last one, was a check how many users where in today (vacationtime ;-))
# Export All Users starting
$ds.tables.select("adu_Name like 'Mow12*'") | export-csv
# Get all ColumnNames
# (don't forget the Select on a Big Table, as the Whole Table is also a Property)
$ds.tables.Columns | select ColumnName
# Users vandaag binnen :
MSH H:\> ($ds.tables.select("adu_LastLogon > '$((get-date).date)'") | ft | Measure-Object).count
For some more Examples on using the DataSet you can look in the Exel Example :Using ADO to get data from Excel to Dataset in Monad.
For an example to Manualy Make a DataSet and an example of loading the dataSet into a DataGrid and show it on a Form you can look here :WMI viewer script for MSH (GUI)
You can see have SQL data in the MSH CLI is very handy for scripts that take a list, you don't need to export it first.
greetings /\/\o\/\/Tags : Monad msh