| Author |
 Topic  |
|
|
apriljuly
Starting Member
3 Posts |
 Posted - 12/27/2012 : 04:52:33
|
I wrote a stored procedure, the stored procedure can display some command to draw a report. Now I want to use powershell to execute each row of the output of the stored procedure.
I have a code like this
`$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=T420s-JLI-W7;Database=AMSNewDataWarehouse;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "YQBreport1"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] `
However it only list the output, instead of execute each row of the output. How can I let it execute each row of output?
An example of the output row is like
$shape1=$page.DrawRectangle(7.81250000000,6.78,8.29166666660,7.025); |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/27/2012 : 06:34:02
|
After you get the data into the data table, have to do something like shown below:
....
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
foreach ($datarow in $DataSet.Tables[0].Rows)
{
$page.DrawRectangle($datarow[0],$datarow[1],$datarow[2],$datarow[3]);
} |
 |
|
|
apriljuly
Starting Member
3 Posts |
Posted - 12/27/2012 : 06:53:34
|
Hi Sunitabeck,
Thank you for your help.
I should've been more clearer.
Below is just small part of the result after running the store procedure. If I export the result into an excel, then copy from excel to powershell, it will be executed and draw me the report.
However, I would like powershell to execute the output automatically without the manual copy paste. Is it possible?
$application = New-Object -ComObject Visio.Application
$documents = $application.Documents
$document = $documents.Add("AMSGantt.vst")
$pages = $application.ActiveDocument.Pages
$page = $pages.Item(1)
$shape500 = $page.DrawLine(2,7.9,11,7.9)
$shape500.TextStyle = "Title"
$shape500.LineStyle = "Title"
$shape500.Text = "Assignation de Barrières - Tuesday, December 18, 2012"
$shape1 = $page.DrawRectangle(1,2.78,3.1875,3.025)
$shape1.LineStyle = "Gantt"
$shape1.TextStyle = "Gantt"
$shape1.FillStyle = "Gantt"
$shape1.Text = "C69802 / 73H / C6624"
$shape101 = $page.DrawLine(0.5,2.75,1.5,2.75)
$shape101.Text = "17:30"
$shape101.TextStyle = "Times"
$shape101.LineStyle = "Times"
$shape201 = $page.DrawLine(3.0875,2.75,3.2875,2.75)
$shape201.Text = "05:15"
$shape201.TextStyle = "Times"
$shape201.LineStyle = "Times"
$shape2 = $page.DrawRectangle(3.1875,4.78,3.8125,5.025)
$shape2.LineStyle = "Gantt"
$shape2.TextStyle = "Gantt"
$shape2.FillStyle = "Gantt"
$shape2.Text = "C69802 / 73H / C6624"
$shape102 = $page.DrawLine(2.6875,4.75,3.6875,4.75)
$shape102.Text = "05:15"
$shape102.TextStyle = "Times"
$shape102.LineStyle = "Times"
$shape202 = $page.DrawLine(3.7125,4.75,3.9125,4.75)
$shape202.Text = "06:45"
$shape202.TextStyle = "Times"
quote:
Originally posted by sunitabeck
After you get the data into the data table, have to do something like shown below:
....
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
foreach ($datarow in $DataSet.Tables[0].Rows)
{
$page.DrawRectangle($datarow[0],$datarow[1],$datarow[2],$datarow[3]);
}
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/27/2012 : 07:20:48
|
I am not familiar with Visio's COM API - however, I would think that you should be able to do it all in one Powershell script by first getting the data into a DataTable (like you have already done) and accessing the that data via the elements in each of the data rows of the data table as I had described in my previous post.
I know I am speaking in generalities - unfortunately, I don't have the skills or familiarity with your data to offer anything more concrete. |
|
|
|
apriljuly
Starting Member
3 Posts |
Posted - 12/27/2012 : 07:46:49
|
Thank you all the same!
quote:
Originally posted by sunitabeck
I am not familiar with Visio's COM API - however, I would think that you should be able to do it all in one Powershell script by first getting the data into a DataTable (like you have already done) and accessing the that data via the elements in each of the data rows of the data table as I had described in my previous post.
I know I am speaking in generalities - unfortunately, I don't have the skills or familiarity with your data to offer anything more concrete.
|
|
|
| |
Topic |
|
|
|
Use powershell to execute output of a SP
