Reverse Mapping Oracle Stored Procedures

[David Thompson]

Hello,

I am very excited about the ORM product and I am making some great progress but I am having trouble with the simple stored procedures.  I am NOT using them as CRUD procedures and instead would rather have the ORM handle insert/update/deletes out of the box.  I am interested in calling a stored procedure before the application makes any changes to the database for internal audit purposes and security.  I have tried to reverse this into my model but it comes in without any parameters.  Here is what I get:

public object[] IDENTIFYUSERSESSION()
{
    object[] queryResult = this.ExecuteStoredProcedure<object>("'IDENTIFY_USER_SESSION' ", null);
    return queryResult;
}

Here is the original stored procedure declaration:

PROCEDURE Identify_User_Session (InSessionId VARCHAR2, InModule VARCHAR2);

My issues is twofold:

a) How can I get the top procedure defined so it works properly (please just paste the complete code)
b) If I modify this in the cs file under my model, it will be removed if I reverse engineer the model again, so how can I execute a procedure in the same scope as my object updates?  (again, an example would be great).

Thanks,
David Thompson

[PetarP]

Hi David Thompson,

 Please find the modified code in the code block below:

public object[] TenMostExpensiveProducts(string InSessionId, string InModule)
{
    SqlParameter parameterInSessionId = new SqlParameter("InSessionId", OpenAccessType.Varchar);
    SqlParameter parameterInModule = new SqlParameter("InModule", OpenAccessType.Varchar);

    List<SqlParameter> sqlParameters = new List<SqlParameter>()
    {
        parameterInSessionId, parameterInModule
    };

    object[] queryResult = this.ExecuteStoredProcedure<object>("'IDENTIFY_USER_SESSION' ?,?", sqlParameters ,InSessionId, InModule);
    return queryResult;
}

I am afraid that I was not able to fully understand your second inquiry. Are you trying to find a place to execute your stored procedure just before your object updates? If that is the case we do provide some Events that will help you out. Please share with me if that is indeed what you are looking for and if so I will walk you through what you need to do in order to expose the API.

Regards,
Petar
the Telerik team

[David Thompson]

Great, Thanks!

My second issue is that this is not reverse engineered from the database correctly so how can I update it in the model so it generates the code you gave me without me needing to rebuild this procedure and others like it each time I save the model?

[PetarP]

Hi David Thompson,

 You can create a partial class that extends your context class and copy the methods there. You can later delete the original methods from your original context. This way your methods will not be overwritten on each code generation. So for example if you context class goes by the name NorthwindEntityDiagrams you can create a class that extends the context in the following way:

public partial class NorthwindEntityDiagrams
   {
       public object[] TenMostExpensiveProducts(bool? alias)
       {
           SqlParameter parameterAlias = new SqlParameter("alias", OpenAccessType.Bit);

           List<SqlParameter> sqlParameters = new List<SqlParameter>()
           {
               parameterAlias
           };

           object[] queryResult = this.ExecuteStoredProcedure<object>("'Ten Most Expensive Products' ?", sqlParameters, alias);
           return queryResult;
       }
   }

This should solve your problem. Please do not hesitate to contact us back in case you face any further difficulties.

Best wishes,
Petar
the Telerik team

[David Thompson]

FYI - ORM Q3-2010 upgrade now reverses all my stored procedures correctly WITH the parameters!!!

[PetarP]

Hello David Thompson,

 Basically the functionality should work out of the box and you should not have any problems at all. However you can use the information provided in this topic to handle more specific cases where you would like to customize the method executing your stored procedure.
Please feel free to contact us back should you face any further difficulties.

Best wishes,
Petar
the Telerik team