Executing Stored Procedure from Powershell

A

ASCHNEIDER146

I saw an example of using System.Data.SqlClient.SqlCommand on at

http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html

I was wondering how I could use the SqlCommand Object to execute a
stored proc on a sql database and pass it paramters.

I found this C# example on how to execute Stored Procs

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx

But I can't figure out how to translate the C# example into Powershell

Any help would be greatly appreciated

Thanks,

Andy
 

My Computer

B

Brandon Shell

As a general rule:

Replace
Type foo = type(parameters)
With
$foo = system.type(parameter)

if your case:

c#
-----------------------------
using System.Data.SqlClient
SqlConnection conn = new
SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
rdr = cmd.ExecuteReader();

PowerShell:
------------
$conn = new-Object
System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
Security=SSPI")
$cmd = new-Object System.Data.SqlClient.SqlCommand("Ten Most Expensive
Products", $conn)
$rdr = $cmd.EndExecuteReader()

I haven't tested these, but effectively that should do it. Simple put... you
have direct access to the .NET objects, but you have to fully qualify them.

Hoped this helped.

"ASCHNEIDER146" <[email protected]> wrote in message
news:[email protected]
>I saw an example of using System.Data.SqlClient.SqlCommand on at
>
> http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html
>
> I was wondering how I could use the SqlCommand Object to execute a
> stored proc on a sql database and pass it paramters.
>
> I found this C# example on how to execute Stored Procs
>
> http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
>
> But I can't figure out how to translate the C# example into Powershell
>
> Any help would be greatly appreciated
>
> Thanks,
>
> Andy
>
 

My Computer

A

ASCHNEIDER146

Thanks Brandon,

That definitely helps me get started.

However I am still stuck with figuring out how to add parameters. Just
to be clear, I am not a C# developer so all this dot net stuff is prety
new to me. I imagine I could do it with COM and ADO but I am trying to
learn to use .NET.

I am trying to figure out how to set the CommandType and then Add the
parameters. Here is the C# I found:

SqlCommand cmd = new SqlCommand(
"CustOrderHist", conn);

// 2. set the command object so it knows
// to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;

// 3. add parameter to command, which
// will be passed to the stored procedure
cmd.Parameters.Add(
new SqlParameter("@CustomerID", custId));

// execute the command
rdr = cmd.ExecuteReader();

Thanks,

Andy


Brandon Shell wrote:
> As a general rule:
>
> Replace
> Type foo = type(parameters)
> With
> $foo = system.type(parameter)
>
> if your case:
>
> c#
> -----------------------------
> using System.Data.SqlClient
> SqlConnection conn = new
> SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
> SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
> rdr = cmd.ExecuteReader();
>
> PowerShell:
> ------------
> $conn = new-Object
> System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
> Security=SSPI")
> $cmd = new-Object System.Data.SqlClient.SqlCommand("Ten Most Expensive
> Products", $conn)
> $rdr = $cmd.EndExecuteReader()
>
> I haven't tested these, but effectively that should do it. Simple put... you
> have direct access to the .NET objects, but you have to fully qualify them.
>
> Hoped this helped.
>
> "ASCHNEIDER146" <[email protected]> wrote in message
> news:[email protected]
> >I saw an example of using System.Data.SqlClient.SqlCommand on at
> >
> > http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html
> >
> > I was wondering how I could use the SqlCommand Object to execute a
> > stored proc on a sql database and pass it paramters.
> >
> > I found this C# example on how to execute Stored Procs
> >
> > http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
> >
> > But I can't figure out how to translate the C# example into Powershell
> >
> > Any help would be greatly appreciated
> >
> > Thanks,
> >
> > Andy
> >
 

My Computer

B

Brandon Shell

Be careful that you only supply parameters to Stored Procedures that accept
it or you will get an error.

In this case I used CustOrdHist SP.

Code:
-------
$conn = new-Object
System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
Security=SSPI")
$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)
$cmd.CommandType = [System.Data.CommandType]'StoredProcedure'

# This Parameter Line This will error if Parameters are not accepted by
Stored Procedure.
$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null

$rdr = $cmd.ExecuteReader()
While($rdr.Read()){
Write-Host "Product Name: " $rdr['ProductName']
}

$conn.Close()
$rdr.Close()

------
Brandon Shell
http://mybsinfo.blogspot.com/ Stop by some time :)

"ASCHNEIDER146" <[email protected]> wrote in message
news:[email protected]
> Thanks Brandon,
>
> That definitely helps me get started.
>
> However I am still stuck with figuring out how to add parameters. Just
> to be clear, I am not a C# developer so all this dot net stuff is prety
> new to me. I imagine I could do it with COM and ADO but I am trying to
> learn to use .NET.
>
> I am trying to figure out how to set the CommandType and then Add the
> parameters. Here is the C# I found:
>
> SqlCommand cmd = new SqlCommand(
> "CustOrderHist", conn);
>
> // 2. set the command object so it knows
> // to execute a stored procedure
> cmd.CommandType = CommandType.StoredProcedure;
>
> // 3. add parameter to command, which
> // will be passed to the stored procedure
> cmd.Parameters.Add(
> new SqlParameter("@CustomerID", custId));
>
> // execute the command
> rdr = cmd.ExecuteReader();
>
> Thanks,
>
> Andy
>
>
> Brandon Shell wrote:
>> As a general rule:
>>
>> Replace
>> Type foo = type(parameters)
>> With
>> $foo = system.type(parameter)
>>
>> if your case:
>>
>> c#
>> -----------------------------
>> using System.Data.SqlClient
>> SqlConnection conn = new
>> SqlConnection("Server=(local);DataBase=Northwind;Integrated
>> Security=SSPI");
>> SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
>> rdr = cmd.ExecuteReader();
>>
>> PowerShell:
>> ------------
>> $conn = new-Object
>> System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
>> Security=SSPI")
>> $cmd = new-Object System.Data.SqlClient.SqlCommand("Ten Most Expensive
>> Products", $conn)
>> $rdr = $cmd.EndExecuteReader()
>>
>> I haven't tested these, but effectively that should do it. Simple put...
>> you
>> have direct access to the .NET objects, but you have to fully qualify
>> them.
>>
>> Hoped this helped.
>>
>> "ASCHNEIDER146" <[email protected]> wrote in message
>> news:[email protected]
>> >I saw an example of using System.Data.SqlClient.SqlCommand on at
>> >
>> > http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html
>> >
>> > I was wondering how I could use the SqlCommand Object to execute a
>> > stored proc on a sql database and pass it paramters.
>> >
>> > I found this C# example on how to execute Stored Procs
>> >
>> > http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
>> >
>> > But I can't figure out how to translate the C# example into Powershell
>> >
>> > Any help would be greatly appreciated
>> >
>> > Thanks,
>> >
>> > Andy
>> >

>
 

My Computer

A

ASCHNEIDER146

Sweet.. that is exactly what I was looking for !!

-Andy

Brandon Shell wrote:
> Be careful that you only supply parameters to Stored Procedures that accept
> it or you will get an error.
>
> In this case I used CustOrdHist SP.
>
> Code:
> -------
> $conn = new-Object
> System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
> Security=SSPI")
> $conn.Open() | out-null
> $cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)
> $cmd.CommandType = [System.Data.CommandType]'StoredProcedure'
>
> # This Parameter Line This will error if Parameters are not accepted by
> Stored Procedure.
> $cmd.Parameters.Add("@CustomerID","ANATR") | out-Null
>
> $rdr = $cmd.ExecuteReader()
> While($rdr.Read()){
> Write-Host "Product Name: " $rdr['ProductName']
> }
>
> $conn.Close()
> $rdr.Close()
>
> ------
> Brandon Shell
> http://mybsinfo.blogspot.com/ Stop by some time :)
>
> "ASCHNEIDER146" <[email protected]> wrote in message
> news:[email protected]
> > Thanks Brandon,
> >
> > That definitely helps me get started.
> >
> > However I am still stuck with figuring out how to add parameters. Just
> > to be clear, I am not a C# developer so all this dot net stuff is prety
> > new to me. I imagine I could do it with COM and ADO but I am trying to
> > learn to use .NET.
> >
> > I am trying to figure out how to set the CommandType and then Add the
> > parameters. Here is the C# I found:
> >
> > SqlCommand cmd = new SqlCommand(
> > "CustOrderHist", conn);
> >
> > // 2. set the command object so it knows
> > // to execute a stored procedure
> > cmd.CommandType = CommandType.StoredProcedure;
> >
> > // 3. add parameter to command, which
> > // will be passed to the stored procedure
> > cmd.Parameters.Add(
> > new SqlParameter("@CustomerID", custId));
> >
> > // execute the command
> > rdr = cmd.ExecuteReader();
> >
> > Thanks,
> >
> > Andy
> >
> >
> > Brandon Shell wrote:
> >> As a general rule:
> >>
> >> Replace
> >> Type foo = type(parameters)
> >> With
> >> $foo = system.type(parameter)
> >>
> >> if your case:
> >>
> >> c#
> >> -----------------------------
> >> using System.Data.SqlClient
> >> SqlConnection conn = new
> >> SqlConnection("Server=(local);DataBase=Northwind;Integrated
> >> Security=SSPI");
> >> SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
> >> rdr = cmd.ExecuteReader();
> >>
> >> PowerShell:
> >> ------------
> >> $conn = new-Object
> >> System.Data.SqlClient.SqlConnection("Server=nybba0sql;DataBase=Northwind;Integrated
> >> Security=SSPI")
> >> $cmd = new-Object System.Data.SqlClient.SqlCommand("Ten Most Expensive
> >> Products", $conn)
> >> $rdr = $cmd.EndExecuteReader()
> >>
> >> I haven't tested these, but effectively that should do it. Simple put...
> >> you
> >> have direct access to the .NET objects, but you have to fully qualify
> >> them.
> >>
> >> Hoped this helped.
> >>
> >> "ASCHNEIDER146" <[email protected]> wrote in message
> >> news:[email protected]
> >> >I saw an example of using System.Data.SqlClient.SqlCommand on at
> >> >
> >> > http://mow001.blogspot.com/2005/12/getting-and-working-with-sql-server.html
> >> >
> >> > I was wondering how I could use the SqlCommand Object to execute a
> >> > stored proc on a sql database and pass it paramters.
> >> >
> >> > I found this C# example on how to execute Stored Procs
> >> >
> >> > http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson07.aspx
> >> >
> >> > But I can't figure out how to translate the C# example into Powershell
> >> >
> >> > Any help would be greatly appreciated
> >> >
> >> > Thanks,
> >> >
> >> > Andy
> >> >

> >
 

My Computer

Top