The ObjectMapper .NET Project

Sometimes it is useful and necessary to call stored procedures. Imagine you have the following Stored Procedure code within your application.

                  SET ANSI_NULLS ON

                  GO

                  SET QUOTED_IDENTIFIER ON

                  GO

                  CREATE PROCEDURE SayHello

                        – Add the parameters for the stored procedure here

                        @Param1 as VARCHAR(255) = ‘everybody’

                  AS

                  BEGIN

                        – SET NOCOUNT ON added to prevent extra result sets from

                        – interfering with SELECT statements.

                        SET NOCOUNT ON;

                      – Insert statements for procedure here

                        SELECT ‘Say hello to ‘ + @Param1 as ResultValue, NEWID() as Id

                  END

                  GO

Due to the fact that stored procedures are specific to the SQL Server (Oracle are using PL SQL Blocks) this is handled by a special Interface. You will get that interface by casting the used persister to INativePersister. After that you can use the Execute and ExecuteWithParameter Method to call the stored procedure.

                  /*

                   * Get the low level Native Persister from the SQL Persister

                   */

                  INativePersister nativePersister = sqlPersister as INativePersister;

                  // First example without Parameter

                  nativePersister.Execute("EXEC dbo.SayHello");

                  // Second example is using Parameters

                  nativePersister.ExecuteWithParameter("EXEC dbo.SayHello @p01", "Peter");

Remark: Because of the internal parameter handling it’s important that you enumerate your parameters from p01 to pNN.

Now we want to map that stored procedure to an object. Because it is necessary to have the mapping functionality this has to be done using the ObjectMapper class itself. 

                  /*

                   * Mapping the result of a stored procedures to an object

                   */

                  SortedList parameter = new SortedList();

                  parameter.Add("p01", "Peter") ;

                  IList resultSet = mapper.SelectNative(

                        typeof(Result), "EXEC dbo.SayHello @p01", parameter);

                  if (resultSet.Count>0)

                  {

                        Result peter = resultSet[0] as Result;

                        Console.WriteLine(peter.Value);

                  }

Remark: In order to map a stored procedure to a class it is important that the stored procedure additionaly returns a column named "Id" that contains a unique identifier for the object.

As you can see it is quite simple to map a stored procedure to a class using the ObjectMapper .NET. The working example can be downloaded using the following link:

Example: Calling and Mapping Stored Procedures