This question is locked. New answers and comments are not allowed.
Adam Thompson
Top achievements
Rank 1
Rank 1
Adam Thompson
asked on 30 Nov 2009, 05:04 PM
Hi,
I have used ADO.NET Entities, and Linq to SQL on previous projects and find using an ORM to be a big time saver and am considing using OA on and an upcomming project. When our company purchased the entire Telerk Suite we got it mainly for the UI tools but I was intrested in looking at the ORM since we had it and my initial thoughts are that it could be a big win for us. In about an hour or so I had a prototype up an running, the ease of use is amaising and the actual implementation itself is really good too. The way the scope provider uses generics to to determine the type it will be working with is pretty cool. I did something similay using Entities Framework but it was a lot of work.
I am enjoying the process of becomming more familure with the product but have a meeting tomorrow that I would like to use as an opportunity to present OA as an option for an upcomming project and just needed a few things clairified.
The thing is with this project is that there are a number of Stored Proceedure that are allreay in use that I know they are going to want to leverage. From what I can tell it seems that with this product I have the ability to make a stored proceedure map to an object that I have created from a table. If this works as it apears to, this is big.
I want to be able to generate an object model form the tables. This will make updates inserts and deletes a snap, and give me an object model for my application. And then it seems I can map the procs to return the object types I have created from the table. This is great because I can return a customer object form the proc and then preform an update on the same object. My question is this.
What if the proc has joins? How do I handel that? Let say that the Proc joins the rep table to get the rep name. well the student object I generated form the table does not have that field. Also, once I map the proc to return that object i do not see that property added to the student object. So what is an elegant way to address this problem?
The other question I have is regarding containment. what I mean is can the student object contain a rep object, or for example we have a lot of lookup tables. It would be nice if a student object could contain a status object as oposeed to an int statusID. Is this possable using the mapping tool?
These are things I'm sure I could hash out with some time but I want to discuss these items with the rest of our development team tomorrow and it will also be nice to have an official answe form telerik to show them.
Thanks so much,
-Adam
3 Answers, 1 is accepted
0
Hi Adam Thompson,
I am glad to see that you are interested in our product.
There are two ways of mapping stored procedures - to a persistent class or to an object array. The first one is easier to use and the only requirement is that the procedure should return at least the ID column of the table. By getting the ID OpenAccess is able to read the whole object from the database. However, you lose the columns from other tables if the procedure returns a projection (join).
The second approach returns an object array representing a row returned by the stored procedure. Each element is an object array itself as the row contains many columns. This way you get the exact result from the procedure but it is harder to traverse such collections.
As you have figured out, the best way is to have references between the objects (like Student.Status) to navigate through the relations. This is absolutely possible as long as you have foreign key columns in the tables. Additionally, if you have foreign key constraints defined, they will be detected and the corresponding references will be automatically generated in the classes.
To sum up, you are able to map your stored procedures to classes and use the object references to access the related objects afterwards.
Hope that sheds some light on the topic. Please feel free to ask if you have any further questions.
Sincerely yours,
Alexander
the Telerik team
Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
I am glad to see that you are interested in our product.
There are two ways of mapping stored procedures - to a persistent class or to an object array. The first one is easier to use and the only requirement is that the procedure should return at least the ID column of the table. By getting the ID OpenAccess is able to read the whole object from the database. However, you lose the columns from other tables if the procedure returns a projection (join).
The second approach returns an object array representing a row returned by the stored procedure. Each element is an object array itself as the row contains many columns. This way you get the exact result from the procedure but it is harder to traverse such collections.
As you have figured out, the best way is to have references between the objects (like Student.Status) to navigate through the relations. This is absolutely possible as long as you have foreign key columns in the tables. Additionally, if you have foreign key constraints defined, they will be detected and the corresponding references will be automatically generated in the classes.
To sum up, you are able to map your stored procedures to classes and use the object references to access the related objects afterwards.
Hope that sheds some light on the topic. Please feel free to ask if you have any further questions.
Sincerely yours,
Alexander
the Telerik team
Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
0
gmendez
Top achievements
Rank 1
Rank 1
answered on 30 Apr 2010, 07:14 PM
Hi Guys,
I found the topics covered in this thread very interesting.
I'm working on a web project wich imposes several design restrictions related to security. One of those restrictions states that every time the system accesses the database, it must use stored procedures no matter it is reading or writing data.
As for the previous posts I think I could achieve this by providing CRUD store procedures for each table and then reverse-map them to persistent classes. I guess those procedures will have to return (in case of Read) all the fields of the underlying table and also receive a set of arguments (in case of Create or Update) representing all the fields of the underlying table as well. In case of Delete, only the id would be required. Right?
Is this approach possible?
In case yes, I guess that following that procedure OA will be able to handle complex object networks... right?
What would recommend in a scenario like this one? Which steps would you take?
Any feedback will be very apreciated.
Thanks in advance and best wishes.
Gonzalo
I found the topics covered in this thread very interesting.
I'm working on a web project wich imposes several design restrictions related to security. One of those restrictions states that every time the system accesses the database, it must use stored procedures no matter it is reading or writing data.
As for the previous posts I think I could achieve this by providing CRUD store procedures for each table and then reverse-map them to persistent classes. I guess those procedures will have to return (in case of Read) all the fields of the underlying table and also receive a set of arguments (in case of Create or Update) representing all the fields of the underlying table as well. In case of Delete, only the id would be required. Right?
Is this approach possible?
In case yes, I guess that following that procedure OA will be able to handle complex object networks... right?
What would recommend in a scenario like this one? Which steps would you take?
Any feedback will be very apreciated.
Thanks in advance and best wishes.
Gonzalo
0
Hello gmendez,
Yes, generally accessing the database with stored procedures only is possible.
However, what we actually have is not CRUD mapping, but CUD mapping (notice that the R is missing).
Now, depending on the version of OpenAccess and the approach that you are using there are two options:
1. If you are using the ObjectScope, basically what you can do is configure CUD mapping and also have your stored procedures for reading mapped so that they return strongly typed results.
You could take a look at the following example (there is also a video):
http://demos.telerik.com/orm/storedProcedures/storedProceduresDemo.aspx
The example should also clarify what the stored procedures should look like.
2. If you are using the new visual designer, unfortunately you cannot use the CUD mapping as there is no way currently to map the reading stored procedures so that they return strongly typed result (we are working on that). In this case you will have to map all your stored procedures in the context (and not on the persistent classes).
I hope this helps. Do not hesitate to write again if you have more questions or suggestions.
Greetings,
Jordan
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Yes, generally accessing the database with stored procedures only is possible.
However, what we actually have is not CRUD mapping, but CUD mapping (notice that the R is missing).
Now, depending on the version of OpenAccess and the approach that you are using there are two options:
1. If you are using the ObjectScope, basically what you can do is configure CUD mapping and also have your stored procedures for reading mapped so that they return strongly typed results.
You could take a look at the following example (there is also a video):
http://demos.telerik.com/orm/storedProcedures/storedProceduresDemo.aspx
The example should also clarify what the stored procedures should look like.
2. If you are using the new visual designer, unfortunately you cannot use the CUD mapping as there is no way currently to map the reading stored procedures so that they return strongly typed result (we are working on that). In this case you will have to map all your stored procedures in the context (and not on the persistent classes).
I hope this helps. Do not hesitate to write again if you have more questions or suggestions.
Greetings,
Jordan
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.