/\/\o\/\/ PowerShelled
This blog has moved to http://ThePowerShellGuy.com Greetings /\/\o\/\/
$PreviousItems = (" working with CSV files in MSH (part two) "," working with CSV files in MSH (part one) "," and another source for MSH scripts is born "," VBscript hosting in MSH, Inputbox in Monad part 2 "," Some Monad Com hacks "," It's busy at the Monad front "," Monad Remoting "," How MSH Works "," Hosting an MSH runspace from Monad "," MSH get-WmiMethodHelp function Update 3 "," ")
Monday, April 03, 2006
more Monad scripts, and a bit more CSV
more and more sample scripts for Monad are posted,
On scripts.readify.net under scripts 5 scripts are added this week, you can get 12 examples there now, and the number seems to grow fast, they also have a RRS feed so you can get notified when new scripts are added , I'm subscribed !.
In the last script in the script session , "Read Records from a Database" you can see how te read the data from the connection without putting it in a dataset first and work with it ofline, as I mostly do see last 2 post (" working with CSV files in MSH (part two) "," working with CSV files in MSH (part one) and the links to connecting other datasources in part 1, you can use this method with all conections mentioned.
if you only want to looponce trough the records this is not so resource intensive (E.g. do something for each record in a CSV file), then using just a reader,
as I wil show in the examples below.
if you only need the first field of the first record found a executeScalar is even faster.
Also on www.monadsource.com in Downloads new Scripts are added, some of them are from my blog, e.g. Remote Desktop Management, Cluster Watch, WMI Data Grid , and some more (not all links are working yet at the moment, but I'm sure this get fixed) , but scripts from all kind of sources are added here, making it more handy to get to the script.
Examples :
function new-DbCommand {
Param ($strCmd )
$cmd = new-object System.Data.OleDb.OleDbCommand($strCmd,$Conn)
return $cmd
}
MSH>$AdComputers = new-DbCommand "Select * from ADComputers#csv"
MSH>$computers = $adcomputers.ExecuteReader()
# get the names of the tables
MSH>$computers.GetName(1)
logon
MSH>$computers.GetName(0)
name
# now we first need to read
MSH>$computers[0]
Unable to index into an object of type System.Data.OleDb.OleDbDataReader.
At line:1 char:12
+ $computers[0 <<<< ]
MSH>$computers.read()
True
# now this works
MSH>$computers[0]
PC42
MSH>$computers[1]
10/17/2005 11:02:32 AM
MSH>$computers[2]
Foo
#read next record
MSH>$computers.read()
True
MSH>$computers[0]
PC81
# for a real quick way (just to check a computer is there this reurns only the first field of the first record):
MSH>(new-DbCommand "Select name from [ADComputers#csv] where name = 'PC41'").executescalar()
MSH>(new-DbCommand "Select name from [ADComputers#csv] where name = 'PC42'").executescalar()
PC42
Param ($strCmd )
$cmd = new-object System.Data.OleDb.OleDbCommand($strCmd,$Conn)
return $cmd
}
MSH>$AdComputers = new-DbCommand "Select * from ADComputers#csv"
MSH>$computers = $adcomputers.ExecuteReader()
# get the names of the tables
MSH>$computers.GetName(1)
logon
MSH>$computers.GetName(0)
name
# now we first need to read
MSH>$computers[0]
Unable to index into an object of type System.Data.OleDb.OleDbDataReader.
At line:1 char:12
+ $computers[0 <<<< ]
MSH>$computers.read()
True
# now this works
MSH>$computers[0]
PC42
MSH>$computers[1]
10/17/2005 11:02:32 AM
MSH>$computers[2]
Foo
#read next record
MSH>$computers.read()
True
MSH>$computers[0]
PC81
# for a real quick way (just to check a computer is there this reurns only the first field of the first record):
MSH>(new-DbCommand "Select name from [ADComputers#csv] where name = 'PC41'").executescalar()
MSH>(new-DbCommand "Select name from [ADComputers#csv] where name = 'PC42'").executescalar()
PC42
*edit* commented this also, but think this deserves an edit, don't forget the MSH "shortcuts" to arrays(enums) can be used here also
MSH>$computers[0,2]
PC42
Foo
MSH>$computers[0..2]
PC42
10/17/2005 11:02:32 AM
Foo
MSH>$computers[0..($computers.fieldcount - 1)]
PC42
10/17/2005 11:02:32 AM
Foo
PC42
Foo
MSH>$computers[0..2]
PC42
10/17/2005 11:02:32 AM
Foo
MSH>$computers[0..($computers.fieldcount - 1)]
PC42
10/17/2005 11:02:32 AM
Foo
MSH>$computers['name']
PC42
MSH>$computers['logon']
10/17/2005 11:02:32 AM
MSH>$computers['description']
Foo
You can see from the interactive examples above, and the SQL script from scripts.readify.net that does a loop (as you did see in the examples above this works also for a CSV, Excel of Access ADO connection), that this is a great way to use those connections to make action upon .(I'm sure I will show it a later example on my blog)
enjoy,
Greetings /\/\o\/\/
Tags : Monad msh
