I recently spoke at the Microsoft Management Summit. My talks were introductory PowerShell talks. Yesterday one of the conference attendees asked me if it is possible to call a SQL stored procedure using PowerShell. I answered I wasn’t sure. So, I sat down and determined that you can in fact call a SQL stored procedure using PowerShell. The basic idea is to use “LINQ to SQL” (formerly called DLINQ). Suppose you have an existing database with a stored procedure. The steps are to first use the sqlmetal.exe tool to generate a C# wrapper file that contains all the code you need to interact with the database. Next you compile the C# code into a DLL library. Then you can use PowerShell to instantiate an object which is a proxy for the database. And then you can use that proxy object to call the stored procedure. Let me illustrate. Suppose you have a SQL database named dbMovies which has a stored procedure usp_GetMovieDataByPrice. First launch a Visual Studio command shell and issue the command: >sqlmetal.exe /server:(local) /database:dbMovies /sprocs /code:mapping.cs This creates a C# file named mapping.cs. Next issue the command: >csc.exe mapping.cs /target:library This compiles the C# proxy code into a DLL library named mapping.dll which PowerShell can access. Now launch PowerShell and issue these commands: > [Reflection.Assembly]::LoadFile('C:\mapping.dll')
> $cs = "server=(local);database=dbMovies;Trusted_Connection=true"
> $o = new-object dbMovies($cs)
> $o | get-member
> $ans = $o.Usp_GetMovieDataByPrice(11.11)
> $ans And presto! You have called a SQL stored procedure using PowerShell. Very neat and easy. Once you can call a stored procedure, you can write a test harness which feeds input to the stored procedure and checks for an expected result to determine a pass/fail result.
