Posts RSS Comments RSS 253 Posts and 393 Comments till now

C# to PowerShell Translation Thought Process

A gentleman on the powershell news group the other day was asking about executing SQL stored procedures and I provided him with example that I had posted earlier in my blog (Click Here) While I’m not sure my answer was exactly what he was looking for he was a little curious as to how I went about translating the original C# code. To be clear I am not a coder nor do I truly know C#, but I know enough to translate. Anyway, I decided this would be a good idea for a post

So… Here we GO!

First… I used this as the C# example

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

I will show you my thought process by section I will include my comments and after I will post Both code Sections

This was fairly simple. In C# you have the ability to take namespace shortcuts by using ‘using ;’ In PowerShell we dont as of yet have that ability so I had to figure out what class SqlConnection was. A MSDN query returned System.Data.SqlClient.SqlConnection. The resulting PowerShell code is

The C# Code:

// Setup
conn = new SqlConnection("Server=(local);DataBase=Northwind;Integrated Security=SSPI");
conn.Open();

The Powershell Code:

$srv = "srv1"
$db = "Northwind"
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=$srv;DataBase=$db;IntegratedSecurity=SSPI")
$conn.Open() | out-null # The out-null is because the method returns a value and I dont want that output

Again… I had to find out what SqlCommand was referencing so… back to MSDN… System.Data.SqlClient.SqlCommand. BTW… I think I should take time now to tell you it is a REALLY GOOD idea to get use to the idea of constructors (how the object should be created) and how to use MSDN to determine the correct way to create an instance of the class/object. It really helps to know what a class is expecting. In this example its good to know the constructor is wanting a string of the SP and A connection OBJECT to use.

The C# Code:

// 1.  create a command object identifying
//     the stored procedure
SqlCommand cmd  = new SqlCommand("CustOrderHist", conn);

Here is the PowerShell Code:

$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)

Here was the tricky part (at least sorta.) From the C# code its not clear if the CommandType.StoredProcedure is a property and it turns out its not. It is an enumeration. It took me a few clicks to figure it out. The first clue was when looking for CommandType… I got an enum and it turns out the valid options was StoredProcedure, TableDirect, or Text (MSDN LINK) Clearly this was an enum, but I was a little unsure how do to enums in powershell. I found this blog by /\/\0\/\/ that helped a lot (/\/\o\/\/ Link)

The C# Code:

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

I ended up with this Powershell Line:

$cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’

This was fairly simple as well… Just had to drop the new sqlparameter because strongly typing was not required. Again the out-null was because the method returns data I did not want as well as set the parameters.

The C# Code:

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

The PowerShell Code:

$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null

These two parts are really just the excution of the the previous code. I think the only difference is the way PowerShell Writes output

The C# Code:

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

// iterate through results, printing each to console
while (rdr.Read())
{
 Console.WriteLine(
 "Product: {0,-35} Total: {1,2}",
 rdr["ProductName"],
 rdr["Total"]);
}

The Powershell Code:

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

Here is the Complete PowerShell Code.

$srv = "srv1"
$db = "Northwind"
$conn = new-Object System.Data.SqlClient.SqlConnection("Server=$srv1;DataBase=$db;Integrated Security=SSPI")
$conn.Open() | out-null
$cmd = new-Object System.Data.SqlClient.SqlCommand("CustOrderHist", $conn)
$cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’
$cmd.Parameters.Add("@CustomerID","ANATR") | out-Null
$rdr = $cmd.ExecuteReader()
While($rdr.Read()){
    Write-Host "Product Name: " $rdr[‘ProductName’]
    Write-Host "Total: " $rdr[‘Total’]
}
$conn.Close()
$rdr.Close()

:: Mar.05.2007 :: .NET, HowTo, Powershell, SQL :: 6 Comments »

6 Responses to “C# to PowerShell Translation Thought Process”

  1. on 06 Mar 2007 at 1:13 am1Jeffrey Snover

    $cmd.CommandType = [System.Data.CommandType]‘StoredProcedure’

    You should be able to do it a little easier:
    $cmd.CommandType = ‘StoredProcedure’

    Does that work for you?

    Jeffrey Snover [MSFT]
    Windows PowerShell/MMC Architect
    Visit the Windows PowerShell Team blog at: http://blogs.msdn.com/PowerShell
    Visit the Windows PowerShell ScriptCenter at: http://www.microsoft.com/technet/scriptcenter/hubs/msh.mspx

  2. on 06 Mar 2007 at 8:10 am2Brandon

    Thanks Jeffrey, I could have swore I had tried that before… first actually, but I guess I didn’t because it does work.

  3. on 20 Mar 2007 at 8:29 am3Thomas Lee

    The line:

    $conn = new-Object System.Data.SqlClient.SqlConnection(

  4. on 20 Mar 2007 at 9:43 am4Brandon

    Thanks Thomas… Fixed :)

  5. on 01 Apr 2007 at 6:15 am5The PowerShell Guy : PowerShell Wake-on-lan script

    [...] As I already did read this post, C# to PowerShell Translation Thought Process , and to make it easier I added C# to my search. [...]

  6. on 22 May 2007 at 9:05 am6The PowerShell Guy : PowerShell Wake-on-lan script

    [?] As I already did read this post, C# to PowerShell Translation Thought Process

Trackback this post | Feed on Comments to this post

Leave a Reply

You must be logged in to post a comment.