/\/\o\/\/ PowerShelled

This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$AtomFeed = ("Atom.xml")
$PreviousItems = (" MSH Cluster Watcher script "," Monad NewYears Clock "," Using ADO to get data from Excel to Dataset in Mon... "," 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)


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 :


Comments:
Blogger /\/\o\/\/
I did just find another example posted today about SQL and MSH using SMO

http://pluralsight.com/blogs/dan/archive/2005/12/29/17703.aspx

gr /\/\o\/\/
 
Blogger Spindrift
This comment has been removed by a blog administrator.
 
Post a Comment

Links to this post:

Create a Link



<< Home

Archives

October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006  

$Links = ("PowerShell RC1 Docs"," PowerShell RC1 X86"," PowerShell RC1 X64"," Monad GettingStarted guide"," Monad Progamming Guide"," Monad SDK"," Monad videos on Channel 9"," MSH Community Workspace"," scripts.readify.net "," MonadSource"," www.reskit.net"," PowerShell Blog"," Under The Stairs"," computerperformance powershell Home"," proudlyserving"," MSH on wikipedia"," MSHWiki Channel 9"," Keith Hill's Blog"," Precision Computing"," PowerShell for fun"," MSH Memo (Japanese)"," monadblog")

find-blog -about "PowerShell","Monad" | out-Technorati.
find-blog -contains "","" | out-Technorati.
Google
 
Web mow001.blogspot.com

This page is powered by Blogger. Isn't yours?