Persisting objects in a database


  • Trolleybus Mechanic

    More of a coding philosophy question.

    So I have a bog-standard ASP.Net application. It has complex objects.

    I have a database. It's a regular old MSSQL database.

    I need to persist those objects to the database.

    Every time I've ever done this ever, it's been by calling a stored proc, passing in the ID and values of the object, and doing an INSERT/UPDATE.

    So if I'm persisting Foo { id:int, name:string }, I have:

    stored proc SaveFoo(@id:int, @name:nvarchar(50))
    if exists (select 1 from Foo where id=@id)
    update foo set name=@name where id=@id
    else
    begin
     insert into foo(name) values (@name)
     set @id= lastid()
    end
    select @id as foo_id
    

    As Foo gets more fields, I add a field to the stored proc signature, and the associated insert/updates.

    Is this the best way to do this? I suppose in theory I could serialize the object, send it to the DB as XML (in a single variable), parse it in SQL into field variables.

    I obviously don't want to handroll a "constructed query" (you know sql = "UPDATE " + obj.name + " SET " (foreach field....))

    And I've never felt right about serializing the object and saving the blob to the db, since I can't query anything.

    Any other CRUD philosophies or utilities peeps use?


  • Notification Spam Recipient

    Aside from using the newer MERGE syntax, seems fine to me I guess. I don't do much data-basing when I can.

    I mean, it beats using 3nF to spaghetti-fi your object...


  • Discourse touched me in a no-no place

    @Lorne-Kates said in Persisting objects in a database:

    And I've never felt right about serializing the object and saving the blob to the db, since I can't query anything.

    The easiest approach is to do exactly that, while keeping an extra copy of the fields you need to query over. If you find you need to keep virtually all the fields for querying, might as well keep 'em all (unless you have the odd one that is better done with a big bad serialized blob). The fields to beware of are the ones that have a big graph of things hanging off the back end; if you don't have any of them, the object will map to a table pretty neatly however you do it.

    The bigger question is whether or not you use a ORM to do the scut work of mapping.



  • Table valued parameters. Microsoft created those specifically to replace serialized or delimited data in a single parameter. You get it set up right, and you can essentially pass a DataTable to an sproc whole-hog. Then you can use set logic and upsert the information like you merge update any two tables.


  • FoxDev

    @BaconBits said in Persisting objects in a database:

    Table valued parameters. Microsoft created those specifically to replace serialized or delimited data in a single parameter. You get it set up right, and you can essentially pass a DataTable to an sproc whole-hog. Then you can use set logic and upsert the information like you merge update any two tables.

    hell, you set it up right and your linq to sql or entity framework models class does all that hooking up stuff for you. just fill the object and shove it into a list. pass that list to the method that represents the sproc and it does all the translation for you.


  • Impossible Mission - B

    @BaconBits That looks very interesting.

    Is there any way to access it from native code?



  • I'm not a big fan of ORMs like Entity Framework (I like to call it 'Failwork') as I feel that there's too much magic going on underneath the hood, comes with some limitations that aren't always immediately obvious, has an impact on performance, and it can be pretty painful to investigate or tweak its behaviour when things go titsup.

    However, completely hand rolling CRUD operations isn't really something I like doing either - mapping code is boring to write.

    For those reason, I'm still wanting to give Petapoco a try: http://www.toptensoftware.com/petapoco/


  • Winner of the 2016 Presidential Election

    @AlexMedia said in Persisting objects in a database:

    comes with some limitations that aren't always immediately obvious

    If SQLAlchemy (Python) has any serious limitations, I haven't come across them yet. I've always managed to make it produce the queries I want with little effort.

    ORMs aren't necessarily bad if they're well-designed and don't make too many assumptions. Unfortunately, many of them are quite limited or hard to configure, which is the reason for their bad reputation.



  • @AlexMedia said in Persisting objects in a database:

    I'm not a big fan of ORMs like Entity Framework (I like to call it 'Failwork') as I feel that there's too much magic going on underneath the hood, comes with some limitations that aren't always immediately obvious, has an impact on performance, and it can be pretty painful to investigate or tweak its behaviour when things go titsup.

    Eh... it's a question of experience. Once you figure out what can and can't be translated to SQL, EF can be immensely powerful, and performance isn't much of an issue if you don't stupidly ToList() stuff. There are still some kinks, mostly related to lazy loading, but for me it works quite well.

    Then again, we mostly isolate EF in a DAO layer and build the business layer on top of that instead of MS' idealistic "pass entities all the way to the front end" idea (which is great if you have 100% control over the schema and impossible in any other case). But it still gives you strong typing in your business code at little cost.



  • @Maciejasjmj said in Persisting objects in a database:

    Eh... it's a question of experience. Once you figure out what can and can't be translated to SQL, EF can be immensely powerful, and performance isn't much of an issue if you don't stupidly ToList() stuff. There are still some kinks, mostly related to lazy loading, but for me it works quite well.

    I've gotten quite some experience with EF throughout the years, and that has only made me dislike it even more. There are some really obnoxious quirks in how EF generates queries under particular circumstances, even if you don't just blatantly ToList and Include everything.

    Plus, the change tracker. That thing needs to get really intimate with a purple cactus.

    Apart from that, I don't like the whole idea of abstracting the database away from a developer. A RDBMS is a very powerful tool that can do so many things for you - far more efficiently than can be done in code. I feel it's a waste to pretend like a database is some sort of magical box that gives you objects but of which you don't know what's going on under the hood.

    Then again, we mostly isolate EF in a DAO layer and build the business layer on top of that instead of MS' idealistic "pass entities all the way to the front end" idea (which is great if you have 100% control over the schema and impossible in any other case). But it still gives you strong typing in your business code at little cost.

    So in your case, what benefit do you get from using EF that just writing SQL queries in your DAO doesn't bring you? What value does EF add that something like Petapoco doesn't?



  • @AlexMedia said in Persisting objects in a database:

    Apart from that, I don't like the whole idea of abstracting the database away from a developer. A RDBMS is a very powerful tool that can do so many things for you - far more efficiently than can be done in code.

    Yeah, but you bundle that in stored procedures if you have the need to do so. Which is far from ideal too (versioning in particular can be a bitch, they're deployed separately from the application code, unit testing is hard, etc.), but better than whipping strings.

    @AlexMedia said in Persisting objects in a database:

    So in your case, what benefit do you get from using EF that just writing SQL queries in your DAO doesn't bring you? What value does EF add that something like Petapoco doesn't?

    LINQ querying instead of stringification is the major benefit. And with some dabbling in expression trees it can be just as powerful. For most things we could probably do with a micro-ORM like Dapper, but really, LINQ is hot shit. That doesn't get brought down by a typo.


  • Winner of the 2016 Presidential Election

    @AlexMedia said in Persisting objects in a database:

    I feel it's a waste to pretend like a database is some sort of magical box that gives you objects but of which you don't know what's going on under the hood.

    Well, ideally, you define your object and the mapping in a way that makes the obvious queries on those objects efficient. If your ORM generates inefficient queries or does weird stuff under the hood, that may not necessarily the ORM's fault.



  • @Lorne-Kates it's ASP.net, you're missing the awesomeness of Linq2sql



  • I am only just getting into .NET and EF. Though I have almost 20 years of database experience.

    I've resisted ORMs for years because I like SQL.

    Our .NET projects use the repository pattern to access the DB, then we have a DAL, a BL, and a web layer.

    For my first .NET project, I designed the database first. I liked EF for the simpler DB aspects and did the rest in stored procedures.


  • Notification Spam Recipient

    @Karla said in Persisting objects in a database:

    I liked EF for the simpler DB aspects and did the rest in stored procedures.

    👍🏻 I have a somewhat unobvious pairing in two tables (yes they have a foreign key set), and it was much more efficient to script out stored procs to do the manipulation of them, while still allowing EF to get me base edit-ability to the tables themselves.


Log in to reply