none
RIA Services, stored procedures and databases

    Question

  • So much info on .Net RIA services, but I'm still trying to get a handle on all this.

    When we (my company) build 'traditional' data driven apps, mostly ASP.Net these days, we follow the well proven method of using stored procedures in SQL Server for all access to data.  Code NEVER has embedded SQL.  And you never directly access a table.  All CRUD operations are through stored procedures.  But beyond that, there are many other stored procedures the apps use that make full use of the database engine to do more complex querying, or data manipulation.  Search forms, and reporting are just two examples of application interfaces that use stored procedures beyond the simple CRUD ones.  When doing work on existing systems, I routinely am able to strip out large blocks of procedural code and replace it with a much smaller chunk of SQL in a stored procedure.  Makes the apps easier to maintain, and often, run faster.

     Used to be that this way of doing things was what was taught, and talked about heavily by Microsoft and the development community.  I learned to move smoothly between the relational data world and the code/OOP world.  Now I don't see it as much, except by maybe the SQL Server people.

    So where does this methodology fit in with .Net RIA services?  Everything seems to be 'entity' oriented now, which I find to be an extremely limiting way to think of a database.  Will .Net RIA services fit well with our current database structures?  I don't plan on giving up on a very time-tested, proven way of developing databases, just to be able to use it with Silverlight.  Will the RIA services allow for complete stored procedure access?  Will it provide for data access and manipulation that is more in-depth than mere CRUD operations?  And without requiring tons of coding work?

     If anyone has thoughts on this, or an online resource that points to this exact issue (not just general stuff) I would appreciate it.

     Thanks

    Monday, June 29, 2009 7:19 PM

Answers

  • Open products in dataset with linq query or whatever
    Loop through rows
        If product is active
            row("price") = row("price") + row("price") * .1
            Update row  --this is one hit to the database per row, even if you wait til the end
        end if
    end loop

    One minor correction, the Update row is not one hit to the database per row even if you wait til the end, the updates are batched. That doesn't invalidate your point, currently the above code would be using seperate Update commands for each row and if you had lots of rows your performance would be bad. If Linq to SQL and Entity Data Model start using the SQL Server 2008 Merge command then this would be less of an issue.

    Thursday, July 02, 2009 1:21 PM

All replies

  • RIA Services doesn't really restrict you. If you don't want to use generated SQL you will however loose some features like Linq at RIA Services. But it's very well possible to use RIA Services with for example the Entity Framework, which for example makes use of Stored Procedures. I'm only not sure if this is possible with the completely generated RIA Service. I'm sure it's possible if you create your RIA Service manually.

    Tuesday, June 30, 2009 8:16 AM
  • RIA Services never communicates directly to a database, it communicates with the data layer (or Model) which then communicates with the database. If your data layer supports stored procedures then RIA Services supports stored procedures. For example, both LINQ to SQL and EM support stored procedures.

    As a former card carrying member of the "you will take stored procedures away from me over my dead body" club and a former DBA, I understand where you are coming from but don't be surprised if one day you find your mind changed. I know my friends were quite surprised when one day I simply stopped using stored procedures. I really was quite militant about stored procedures before then.

    Tuesday, June 30, 2009 4:29 PM
  • I spent years writing stored procs and I can tell you that after having moved to Linq to SQL I will never go back.

    That is not to say L2S is perfect, in fact if you write web apps you can only use about 40-50 percent of L2S functionality. 

    I'm new to Silverlight and RIA (and struggling with it) but I believe (perhaps wrongly) that it addresses some of the shortcomings in L2S.

    Bottom line in my book:  Linq is must-know technology if you want to stay competitive.  I'm not qualified to comment on Silverlight except to say that it looks very promising. The more I learn about SL, the more I like it.

    >I don't plan on giving up on a very time-tested, proven way of developing databases, just to be able to use it with Silverlight.

    Dont limit yourself this way, friend.  There once was a time-tested and proven way of making buggy whips....

    >If anyone has thoughts on this, or an online resource that points to this exact issue (not just general stuff) I would appreciate it.

     You probably dont want to hear this but Linq + SL is a new way of thinking.  The answers to your questions are long and will raise many more questions.  My opinion is that taking a fresh look at things and putting in the effort to understand and use these new technologies will be well worth the effort.  The HR app found here is a good starting place. 

     

    Regards,

    Sam

     

     

    Tuesday, June 30, 2009 4:43 PM
  • Colin,

    Thanks for writing. 

    RIA Services never communicates directly to a database, it communicates with the data layer (or Model) which then communicates with the database. If your data layer supports stored procedures then RIA Services supports stored procedures. For example, both LINQ to SQL and EM support stored procedures.

    OK, so RIA services is layered on a 'data layer'.  Guess I need to dig in deeper to that.

    I know my friends were quite surprised when one day I simply stopped using stored procedures.

    I'd like to hear your reasons for stopping.  Did it make your applications (and very importantly, databases) more efficient, better, or was it just because the current toolsets are leading you in that direction?  Are you still able to take advantage of the amazing things that SQL Server, as a database engine can do for you?  If the entity models support stored procedures, and RIA services can use the entity models, then it would appear that I could have the best of both worlds.  That is what I am ultimately trying to find out before I jump with both feet into Silverlight based business apps.

    I spent a long time in development shops watching the java developers go on and on about their great platform with all their ORM and data layers.  In the end, their web sites were too slow and too difficult to maintain, while the lean and mean ASP.Net apps were always better performers and were much easier to modify, even though they were tied more closely to the database.  I just don't want to end up in the same rut with Silverlight.

    Tuesday, June 30, 2009 5:58 PM
  • Well my friend, at this point I will still maintain that a good understanding of a relational database engine and how to use it to its full potential for applications is far less limiting a viewpoint than just thinking of your database as just a bunch of entities that have to be modeled, and abstracted and have layers upon layers of generated code built just to be able to build a data driven app. 

    I haven't seen any evidence yet that my database development methodology is in any way a 'buggy whip'.  Really, linq is a sneaky way of having embedded SQL in your app. There are some scenarios where it is appropriate, but not everywhere.  In client running apps, yes, in ASP.Net, pointless.  Linq to XML I think is cool though, never liked having to write XPath :)  And I've learned over the years to not be too quick to change just because of some shiny new technology that comes out. Your new way might still end up being an Edsel in the end.  All this stuff is really new and it will take awhile to settle.  And in the end, the best method of Silverlight data access might not actually come from Microsoft...

    I get it that Silverlight is a client-side app that doesn't have the abiltity to directly acces the database engine in the way that an ASP.Net app does.  There has to be something there to bridge the gap.  I just want to make sure that 'something' is lean and efficient and doesn't try to lead you away from proven best practices. 

    Databases are the foundation of the kind of applications I develop.  And they can very quickly become the limiting factor if they aren't done well.  On the other hand, if they are done well, they can greatly diminish the amount and complexity of the application code.  So I think you do your foundation in the best way you know how, then you build your app in a way that works with your foundation.  Not the other way around.

     

    Tuesday, June 30, 2009 6:29 PM
  • Everything seems to be 'entity' oriented now, which I find to be an extremely limiting way to think of a database.  W

    Well you could also think about RIA entities as data transfer objects that can hold some additional meta data. What you do with them once you're in your domain service is completely up to you. Since it's all about abstracting the database access, you have to start thinking in terms of "views" or "snapshots of data". You trigger a request in Silverlight, some magic happens in the middle tier and data is returned. So the domain service is all about shaping your data in my opinion...

    Wednesday, July 01, 2009 3:54 AM
  • I know my friends were quite surprised when one day I simply stopped using stored procedures.

    I'd like to hear your reasons for stopping.  Did it make your applications (and very importantly, databases) more efficient, better, or was it just because the current toolsets are leading you in that direction?  Are you still able to take advantage of the amazing things that SQL Server, as a database engine can do for you?  If the entity models support stored procedures, and RIA services can use the entity models, then it would appear that I could have the best of both worlds.  That is what I am ultimately trying to find out before I jump with both feet into Silverlight based business apps.

    I spent a long time in development shops watching the java developers go on and on about their great platform with all their ORM and data layers.  In the end, their web sites were too slow and too difficult to maintain, while the lean and mean ASP.Net apps were always better performers and were much easier to modify, even though they were tied more closely to the database.  I just don't want to end up in the same rut with Silverlight.

    First off I haven't completely abandoned Stored Procedures, I just don't make them mandatory anymore. I leave the simple CRUD operations up to the Entity Framework and use stored procedures for any complex query based operation. I try to use the Service Broker instead of triggers unless I really need to be able to cancel the original transaction. As for the whys, I came to the following realizations:

    1. SQL Server has grown beyond the need for stored procedures. Stored procs used to have performance advantages that simply don't exist anymore because SQL Server itself has gotten that much better at evaluating queries.
    2. Current data frameworks do not have injection problems.
    3. SQL Server is not designed to allow asp.net applications to flow per user security all the way into the database. If each user is impersonated performance suffers too much so setting security roles on the stored procedures themselves just doesn't work.
    4. I was spending way too much time writing these massive Select stored procedures with multi-page where statements with code like (@FirstName is NULL or Person.FirstName = @FirstName) that were really hard to maintain for anybody but myself. Then having to run herd on people who got the ordering wrong (there is an order of magnitude performance difference between "@FirstName IS NULL or Person.FirstName = @FirstName" and "Person.FirstName = @FirstName or @FirstName IS NULL".
    5. I was looking at stored procedure generation tools to generate the massive number of cookie cutter CRUD stored procedures I needed. If I was going to generate them anyway, why have them at all?
    6. I finally had ORM tools in LINQ to SQL and the EF that had better support for database first development because, IMHO, the databases are the single most important asset to any organization and I never compromise on proper database design.
    7. Stored procedures are still embedded SQL, it is just embedded within the SQL Server itself instead of the application. LINQ to (entity, SQL) is dynamically generated SQL based on the structure of the database, the structure of the model, and the runtime requirements of the application. Once you realise that SQL Server can execute the runtime generated query at the same speed as a stored procedure (within limits of your LINQ design of course) and that there are no injection problems this arrangement starts looking pretty good.

    Your comments about the Java developers and their data frameworks is where number 5 came in for me. I found that the people using ORM tools of the time tended to be doing model first development where the database just becomes an offline cache for the application. As an ex-DBA and general SQL Server guru, that is a viewpoint I could never support. What I love about the Entity Framework (and LINQ 2 SQL) is that the model can be generated directly from the database and left alone. Other then hiding the many-to-many linking tables as many-to-many associations, the model and the database can look exactly the same. I don't find this limiting at all, any time I modify the database I just open the model, right click, and tell it to refresh itself from the database. Any changes that affect my code will be found by recompiling, I love it.

    Wednesday, July 01, 2009 11:30 AM
  • Thanks for the input.  I could see letting the CRUD stuff be automagically handled in most cases, although I don't have any problem cranking out the SPs for them, I have a set pattern that I can do pretty quickly.  But the more complicated stuff, I agree, won't be leaving SPs any time soon.  The thing about some of those seemingly massive SQL statements is that they have replaced even more massive, and fragile procedural application code. 

    Yeah SPs are embedded in SQL Server, but there are reasons we went that way.  After years of trying to maintain apps with embedded SQL it became clear to developers that you could really clean up your code, get huge re-usabilty gains, performance gains (not just in SQL Server but in the application itself) by factoring out the SQL and putting it where it belongs, with the database.  Much easier to maintain there. Remember those long string concatenations with chunks of SQL interspersed with code logic and variables?  Ugh. 

    Later I began to realize what we were doing was in some ways the same thing these ORM tools try to do.  Well sort of.  With SPs and views we were creating a layer that could simplify how the application views and interacts with the database.  But it is more task oriented than model oriented (insert this, update that, do these 10 things and only commit if they all succeed).  The application doesn't always need to be arranged around the exact table model, it just needs to know what function to call to get what it needs.  That would go against most people, I guess but I actually prefer to write .Net classes that way, more task oriented. 

    Maybe that's why I never had a real 'impedence mismatch' between relational and OO.  You model the database according the user's data, you model the application according to the tasks they need to do (editing, reporting etc.) and the natural bridge is T-SQL.  Now the natural bridge may be RIA services, as long as it is building upon what we already know works well.

    IMHO, the databases are the single most important asset to any organization and I never compromise on proper database design

    I am glad you wrote that and I wish all developers understood.  I see so many young developers who seem to just think that a database is a collection of tables and all data processing work must be done in code.

    One other thing that was important in my learning of this is that I would like to investigate putting a new Silverlight front end on existing applications, with exsiting well developed databases.  So there it would be essential that the Entity Framework (or whatever I use) can use what is already there.  Sounds like that may be the case.  More learning to do...

    Wednesday, July 01, 2009 7:13 PM
  •  

    IMHO, the databases are the single most important asset to any organization and I never compromise on proper database design

    >>I am glad you wrote that and I wish all developers understood.  I see so many young developers who seem to just think that a database is a collection of tables and all data processing work must be done in code.

    Your inference of a relationship between quality of database design and choice of access layer is not correct. At the moment I'm writing  a little app with one hundred forty four tables and not one stored proc. Not one.  And not one line of SQL.   If I moved all my linq statements to stored procs would it make my database design better? No, it would just make my life miserable. 

    >>>After years of trying to maintain apps with embedded SQL it became clear to developers that you could really clean up your code, get huge re-usabilty gains, performance gains (not just in SQL Server but in the application itself) by factoring out the SQL and putting it where it belongs, with the database.  Much easier to maintain there. Remember those long string concatenations with chunks of SQL interspersed with code logic and variables?  Ugh.

    As a linq  devloper I can assure you the above is no longer true.

    >>The thing about some of those seemingly massive SQL statements is that they have replaced even more massive, and fragile procedural application code.

    I love my massive, fragile, strongly typed entities, my compile time error checking, and the much more robust error handling I'm able to implement in far less code.  I will gladly move my business logic to fast, robust complied code and save the SQL for mundane data transfers.   I'm looking forward to the day LINQ is interpreted directly by  the database server and the whole SQL translation layer is tossed out.  If I never write another line of SQL in my life it will be too soon.


     

     

    Wednesday, July 01, 2009 11:38 PM
  • Your inference of a relationship between quality of database design and choice of access layer is not correct.

    I don't make inferences, I am a very literal person.  But the point is, a database design to me includes the tables, and also the processing you ask the database engine to do.  The 'junior developer' code I see would be something like this:

    Open products in dataset with linq query or whatever
    Loop through rows
        If product is active
            row("price") = row("price") + row("price") * .1
            Update row  --this is one hit to the database per row, even if you wait til the end
        end if
    end loop

     That is a made up example, but that's the kind of thing I am talking about.  I actually saw production code where a guy was looping through deleting every record as he went!  When their tables get too big and their code slows down, they call me and I replace the code with a SQL Update statement (make the datbase engine do the work instead of .Net code), and everyone is happy again. I am not inferring that you as an experienced dev would ever do that, but it is out there...

    If I moved all my linq statements to stored procs would it make my database design better?

    I am really not criticizing you here, but if you have a bunch of sql looking linq statements in your code vs. bunch of SQL in your database...  I don't see the need to change yet.  Especially with legacy databases.  In the future who knows?  I have tons to learn just in Silverlight and XAML before we even get to data.

    I will gladly move my business logic to fast, robust complied code and save the SQL for mundane data transfers.  

    If it makes your app easier to work with and maintain, great!  Just don't think your compiled .Net code is ever going to be faster than direct SQL Server with a compiled cached stored proc. 

    I'm looking forward to the day LINQ is interpreted directly by  the database server and the whole SQL translation layer is tossed out. 

    But then your linq IS SQL... 

    Oh and I did find this:  http://www.componentone.com/SuperProducts/DataSilverlight/  Don't know if in the long run it will fly, but then there's no guarantee about anything in software development.  I'll give it a fair test along with the MS stuff and whatever else I find.

    Anyway, I must move on from this thread and get working.  Thanks for all the valuable input, and anyone else can feel free to get the last word in.

    Thursday, July 02, 2009 12:55 PM
  • Open products in dataset with linq query or whatever
    Loop through rows
        If product is active
            row("price") = row("price") + row("price") * .1
            Update row  --this is one hit to the database per row, even if you wait til the end
        end if
    end loop

    One minor correction, the Update row is not one hit to the database per row even if you wait til the end, the updates are batched. That doesn't invalidate your point, currently the above code would be using seperate Update commands for each row and if you had lots of rows your performance would be bad. If Linq to SQL and Entity Data Model start using the SQL Server 2008 Merge command then this would be less of an issue.

    Thursday, July 02, 2009 1:21 PM
  • :-) Interesting Thread.

    This discutions make me remember when WWW/Html appears some years ago. Before that we work with advanced development tools that create fat clients but they allow us to develop really fast and allow to create great LOB applications for our clients(ex:Powerbuilder).

    After this, WWW is "Every thing" and HTML become a promise to change every thing (even mainframe :-) ) . If we look back for LOB apps we only simplified the Deployment proccess. New www LOB app offer a poor UI to our clients, and more difficult to maintain because we spread our code in new layers and languanges.

    I am waiting for a long time (years) to begin to see this issue corrected.

    But now for LOB Apps, we have things like Flex and Silverligh. We can start change things.

    Reading your posts i am affraid. Pay attention to what Clients apps need. 

    We just want a UI with impressive functionalities and write logic in server (with a simple language).

    UI is RIA. SOA i also buy, because it will facilitate creation news services to clientes. Well structured client information and Logic ?? Why not BD ??

    - SP's are good to develop (SQL is not only TSql see also PL SQL). 

    - Craete less Lines of code (less lines of code better to maintain).

    - Fast capacity to understand a system and the impact of ChangeRequests ( automatic CRUD matrix, simples text queries to see impacts) .

    Just one more prespective

    Wednesday, July 08, 2009 1:34 PM
  • You can create empty view with the same structure of your sproc and map that stored procedure to your function in your DomainService.

     See sample on http://cid-289eaf995528b9fd.skydrive.live.com/self.aspx/Public/sproc.zip

    Thursday, September 24, 2009 9:40 PM