Stored procedure output parameters w/ generated code

[Steve]

I have been converting some Entity Framework code over to OpenAccess. All of my data access is CRUD based. So far I don't have any problems with RUD part of  CRUD. But the create part is giving me some challenges.

All of my exiting insert stored procedures use output parameters to return the identity value of the record just inserted. I am able to all of the generated Domain Methods excepts those calling insert stored procedures.

This is the signature of the generated domain method:
-----------------------------------------------------------------------------

public System.Int32 AddWorkRequest(string subject, string description, int? clientID, string client, string jobNumber, Guid? requestedBy, DateTime? requestedDate, int? priorityID, bool? billClient, DateTime? startDate, DateTime? completionDate, DateTime? estCompletionDate, DateTime? dueDate, int? timeToCompleteHours, int? timeToCompleteMinutes, bool? reproduced, string comments, Guid? userId, ref int? workRequestID)

-----------------------------------------------------------------------------

The last parameter is: ref int? workRequestID
That means workRequestID is intended to return the identity value from the AddWorkRequest domain method.

This is the code at the end of the domain method:
-----------------------------------------------------------------------------

OAParameter parameterWorkRequestID = new OAParameter();
parameterWorkRequestID.ParameterName = "WorkRequestID";
parameterWorkRequestID.Direction = ParameterDirection.InputOutput;
if(workRequestID.HasValue)
{
    parameterWorkRequestID.Value = workRequestID.Value;
}
else
{
    parameterWorkRequestID.DbType = DbType.Int32;
    parameterWorkRequestID.Value = DBNull.Value;
}

System.Int32 queryResult = this.ExecuteScalar<System.Int32>("[web_apA_WorkRequest]", CommandType.StoredProcedure, parameterSubject, parameterDescription, parameterClientID, parameterClient, parameterJobNumber, parameterRequestedBy, parameterRequestedDate, parameterPriorityID, parameterBillClient, parameterStartDate, parameterCompletionDate, parameterEstCompletionDate, parameterDueDate, parameterTimeToCompleteHours, parameterTimeToCompleteMinutes, parameterReproduced, parameterComments, parameterUserId, parameterWorkRequestID);

return queryResult;

-----------------------------------------------------------------------------

The problem is that the workRequestID variable is never assigned from parameterWorkRequestID.Value. There should be more generated code AFTER the ExecuteScalar statement.

Is there a way to make the ORM generate proper code for output parameters? This is a show stopper. I have manually coded a helper method based on the generated code. But I prefer that the ORM generate the proper domain methods instead.

[Steve]

I solved my problem. After looking at the Context.ttinclude code generation template, I discovered I had selected a Scalar type of System.Int32 as the Return Type, rather than None.

After changing two stored procedures to None, the ORM generated the proper code for the output parameters. A quick test showed the identity value is now populating the ref parameter of the domain method call.

This means OpenAccess is a viable ORM for our company. We are burden w/ legacy SQL Server 2000 databases, which are not supported by the Entity Framework nor Linq to SQL ORMs.

[Ivailo]

Hi Steve,

We are glad to hear that the issue is resolved.

I just want to mention that in case you experience any issues in the future with the already generated domain methods, you can always check and edit them using the Domain Method Editor. I hope that helps.

Greetings,
Ivailo
the Telerik team

[mike okon]

Hi

I am new to ORM and stored procedures and would like some help on this simple issue.

I am able to follow the import of the stored procedures , However when I try to bind the result to a (RadListBox) no data is displayed. The stored procedure returns all the Full names in a table  after some logic is applied

my code is as follows: auto generated by ORM

public IEnumerable<MyClub.Web.User_Info> My_Friendly_Names()

{

IEnumerable<MyClub.Web.User_Info> queryResult = this.ExecuteQuery<MyClub.Web.User_Info>("[my_Friendly_Names]", CommandType.StoredProcedure);

return queryResult;

}

later on

IEnumerable<MyClub.Web.User_Info

 

> My_Friendly_Names();


my xaml:

<telerik:RadListBox Grid.Row="2" 
                    telerik:StyleManager.Theme="Transparent" 
                    ItemsSource="{Binding My_Friendly_Names}"/>

 

can you please point out where Iv missed a step?

regards
mike

 

 

[Doroteya]

Hello Mike,

Telerik OpenAccess ORM offers a very useful tool called Telerik OpenAccess Samples Kit that contains a set of practical examples related to the usage of our product in general and tutorials how to reproduce them.

I suggest that you take a look at these two examples: Sofia Car Rental - WCF RIA Services and Stored Procedures and Functions with OpenAccess. In the first one you will find the best practices recommended for using Silverlight and WCF RIA services, including how to bind your controls to data sources. The second one demonstrates the different approaches offered by OpenAccess ORM to execute stored procedures and functions.

I hope that helps. If you have any other questions, do not hesitate to get back to us.

 

Kind regards,
Doroteya
the Telerik team

[mike okon]

Hi thanks for the location to the resources however they do not shed any light on my problems.

The Sofia Car rental example has NO stored procedures in it! and the Stored procedure example has no xaml pages in it and does not run at all in any way. In fact I would say it is similar to code which is generated by ORM. It does not function or help my original question on how to access the SP from the xaml page? I look forward to receiving input which helps me solve my issue.

regards
mike okon

[Doroteya]

Hello Mike,

The examples in Telerik OpenAccess Samples Kit demonstrate the solutions to general problems like: using stored procedures with OpenAccess or implementation of cascading delete with OpenAccess, or managing OpenAccess context in web, or binding controls to a data source and etc.

A custom scenario like yours could be split to a few general ones. For example:
- using stored procedures with OpenAccess
- binding XAML controls to a datasource

Each of these scenarios is covered by a sample in Samples Kit.

I suggest that you take a look at the Sofia Car Rental WCF RIA Services where you can see an example for the binding part of your scenario and at the Stored Procedures and Functions with OpenAccess example that covers all possible ways to make use of stored procedures with OpenAccess and adapt the most suitable one in your case.

If you experience any difficulties, do not hesitate to get back to us.


All the best,
Doroteya
the Telerik team

[mike okon]

Hi

the stored procedure example has a DB. However I am not able to locate it to see it in SQL server. Can you make a copy of the NorthwindAO_v1.1.mdf available


***. I have just noticed it was built into SQL server by itself.



regards
mike

[Doroteya]

Hi Mike,

The NorthwindOA_v1.1 database is delivered to you as part of the Samples Kit installation. You can either attach it manually to your server, taking the files from here: ~\{OpenAccess ORM Installation Directory on your PC}\ProductSdk\Content\DatabaseData, or let Samples Kit attach it for you by simply clicking on the Open Test Solution option for the Stored Procedures and Functions sample.

I also suggest you taking a look at this video that demonstrates how to work with Samples Kit.

I hope that works for you. If you have other questions, do not hesitate to get back to us.

 

All the best,
Doroteya
the Telerik team

[mike okon]

Hi

I think I can pinpoint my problem to one issue. How do I reference the DBContext class created within the xxx.rlinq file (in the web project)from the xamal pages xxx.cs class file (in the client project). Since the xxx.rlinq file is on the name.web project I have added this to the using statements. However the xamal page .cs file still does not recognise the DBContext created by ORM when importing stored procedures?

regards
Mike Okon 

[Doroteya]

Hi Mike,

In order to setup a project to consume a domain model that resides in a different project, you need to perform the following steps:
- In the consuming project, add these references: to the model project, to Telerik.OpenAccess.dll and to Telerik.OpenAccess.35.Extensions.dll
- Copy the connection string from the App.config file of the model project to the .config file of the consuming project.

Both of these steps are described in detail in this documentation article.

I that does not solve the issue or you have additional questions, do not hesitate to get back to us.


Regards,
Doroteya
the Telerik team

[mike okon]

Hi, I have found a thread in the forums which has solved all my issues.

thanks for your patience

mike