0

I might be way off here, and this question probably bordering subjective, but here goes anyway.

Currently I use IList<T> to cache information from the database in memory so I can use LINQ to query information from them. I have a ORM'ish layer I've written with the help of some questions here on SO, to easily query the information I need from the DB. For example:

IList<Customer> customers = DB.GetDataTable("Select * FROM Customers").ToList<Customer>();

Its been working fine. I also have extension methods to do CRUD updates on single items within these lists:

DB.Update<Customer>(customers(0));

Again working quite well.

Now in the GUI layer of my app, specifically when binding DataGridView's for the user to edit the data, i find myself bypassing this DAL layer and directly using TableAdapters within the forms which kind of breaks the layered architecture which smells a bit to me. I've also found the fact that I'm using TableAdapters here and ILists there, there are differing standards followed throughout my code which I would like to consolidate into one.

Ideally, I would like to be able to bind to these lists and then have the DAL update the list's 'dirty' data for me. To me, this process would involve the following:

  1. Traversing the list for any 'dirty' items
  2. For each of these, see if there is already an item with the PK in the DB
  3. If (2), then update, else insert
  4. Finally, perform a Delete FROM * WHERE ID NOT IN('all ids in list') query

I'm not entirely sure how this is handled in a TableAdapter, but I can see the performance of this method dropping significantly and quite quickly with increasing items in the list.

So my question is this:

Is there an easier way of committing List to a database? Note the word commit, as it may be an insert/update or delete.

Should I maybe convert to DataTable? e.g. here

I'm sure some of the more advanced ORM's will perform this type of thing, however is there any mini-orm (e.g. dapper/Petapoco/Simple.data etc) that can do this for me? I want to keep it simple (as is with my current DAL) and flexible (I don't mind writing the SQL if its gets me exactly what I need).

Community
  • 1
  • 1
Simon
  • 9,197
  • 13
  • 72
  • 115
  • Is there a compelling reason you aren't using a feature rich ORM? What you are doing sounds quite a ways off the beaten path. – Michael Maddox Oct 11 '12 at 12:06
  • Yes, I'm starting to think that may be best – Simon Oct 11 '12 at 15:35
  • 1
    Building up a custom ORM from scratch is tempting and easy to fall into. You are at the inflection point right now and it is best to stop developing your custom ORM and go with a real ORM if you can do so. Modern ORMs have solved a lot of complex problems really well and reinventing that wheel is not likely in the best interest of your customer. – Michael Maddox Oct 12 '12 at 12:56

1 Answers1

1

Currently I use IList to cache information from the database in memory so I can use LINQ to query information from them.

Linq also has a department called Linq-to-Datasets so this is not a compelling reason.

Better decide what you really want/need:

  • a full ORM like Entity Framework
  • use DataSets with DataDapters
  • use basic ADO.NET (DataReader and List<>) and implement your own change-tracking.

You can mix them to some extent but like you noted it's better to pick one.

H H
  • 263,252
  • 30
  • 330
  • 514
  • Thanks Henk, I didnt realize I could bypass the whole cached lists. So I'm thinking I could then use DataSets to store cached information, but still map to POCO's to retreive info when I need. – Simon Oct 10 '12 at 14:40
  • Maybe second thoughts on the Dataset... probably an ORM is best here – Simon Oct 11 '12 at 15:36