/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$PreviousItems = (" MSH Cluster Watcher script "," Monad NewYears Clock "," Using ADO to get data from Excel to Dataset in Monad. "," Monad Really Does Rock "," Passing (not so) mandatory Parameters to WMI metho... "," Saving a WMI instance to a XML-file from MSH "," MSH Orphan share remover Tool "," MSH Welcome screen "," Read the raw Monad XML helpfiles "," MSH prompt-User function with Help "," ")
Thursday, December 29, 2005
Getting and Working With SQL Server data in Monad
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
$da.fill($ds)
$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
$da.fill($ds)
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[0].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[0].Columns | select ColumnName
# Users vandaag binnen :
MSH H:\> ($ds.tables[0].select("adu_LastLogon > '$((get-date).date)'") | ft | Measure-Object).count
115
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
