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()
tshell :: Mar.05.2007 ::
.NET, HowTo, Powershell, SQL ::
No Comments »
) How much I learned this past week.