Need help on DAL architecture RRS feed

  • Question

  • Hi,

    I am building a product in which we have a master of 2000 properties of an object whose values has to be fecthed from the database.

    This product will be shiped to different customer.Customer, according to their need, will create statements which will ask for values of these properties.

    Now problem is that we don't know as which of the properties will be used as statements by the customer before we package and ship the product to customer.

    What should our approach to fetch these property/entity values from the database.These entities can reside in single or in multiple tables. Would ORM will be of any help or should i use stored procedure which will take all the properties as input array, will have all the queries required to fetch the data and will select which all queries to be fired based on property requirement at runtime. I am thinking of stored procedure as it will reduce number of queries which will be hitting the database server from webserver.

    Kindly help



    Tuesday, March 30, 2010 12:10 PM

All replies

  • That seems like a lot of properties.

    You're going to have a set of stored procedures per client?

    Assuming you're using sql server.

    There is very little performance gain in having stored procedures ready compiled for this sort of scenario.  I would use linq or a query string.  Once run, the compiled version of each query will be held in memory and re-used unless something pushes it out of memory.

    I don't really see how an ORM helps you.  Once the application is built, it's too late to create types unless you can use emit.

    I am currently working on an application (wpf) that is extremely configurable.  One core type is configurable and has mandatory and user configurable columns.  The user configurable ones are handled with a dictionary, so I can have a concrete type.  I also generate XAML which is used to define things like datagrids.  One of these binds to elements in the dictionary - which is an observable dictionary.

    No idea if this is an option for you but it means a power user at the end customer can configure things as they wish.

    Tuesday, March 30, 2010 2:57 PM
  • Hi Andy,

    I am working on too many projects so might have not put up the query correctly.My Mistake :)

    Let me clarify the problem statement in a bit more detail.

    This is an Transport regulatory system which will be used by different counties. Depending on the counties, the requirement of data to be required and hence to be fetched from the database changes.So we want the data fields should be configurable at the county level and accordingly the entities can be populated from the database.

    So in a particular webpage, for a particular banner, if we need values of 10 data fields(variables) which are residing in 4-5 different tables, i have to fire at least 4-5 queries on the database to create that banner, which is going to consume 5 connections from the connections pool. However, if i create stored procedure and pass array of variable name, we can process all the needed queries there and send back only the result, hence only one database connection will be used.

    ORM or Linq to SQL in this case will not solve number of database connection problems but can get us rid of writing quries manully. 

    Dark side of usinf stored procedure is that if the set of variables goes too high, the size of stored procedure might not be manageble and performing.

    May be I am wrong, please share your views and any other better alternative to achieve this.


    Wednesday, March 31, 2010 12:33 AM
  • I have like 15 years or so writing sql server stored procedures so they always seem easier to me than linq.

    To use stored procs you must know in advance what the end user's requirements are. I take it that is probably the case.  You could have a stored proc per county.

     Variable types are a nightmare and you should avoid them if you can.  I have to use dictionaries in the thing I'm working on now and believe me, you don't want to go there if you can avoid it.

    The thing about regular ORM is that they're a type per table orientated really.  I would think you might do better using some sort of purpose built code generator.  I wrote one that works for sql strings or stored procs and would spit out a type.  You can have a copy if that sounds useful.  I wrote it just for myself so it is not bullet proof.

    Remember with Linq, it's going to generate sql in any case.  ORM will generate sql.  You could generate SQL on the fly or as stored procs.  There is no difference necessarilly between them in terms of connections used.

    I find complex linq is way harder than SQL and you can generate a sql statement per row in some cases.  Like I said, I'm probably biased but I would tend to avoid linq for complex queries. 

    Wednesday, March 31, 2010 8:19 AM
  • Another thought popped into my mind whilst I should have been doing my own work.


    Pipes and filters or intercepting filter pattern.

    Linq is a bit counter-intuitive in that the sql it emits isn't generated until you iterate the collection.  You can have a linq query based on a linq query and chain ad finitum.  So you could write complex table spanning linq queries and then narrow the fields in another linq statement that you pipeline with the first.  Perhaps simplifying the issue by separating out choosing the fields from the connecting all the tables.  Might help.

    Wednesday, March 31, 2010 9:00 AM
  •  I couldn't understand much about how you proposes, might be because i am not an expert of SQL server or quries.

    Can you please share your tool.It would be of great help for me.Can i write to you on any of your email id or blog for this.

    Your other Comment

    "I am currently working on an application (wpf) that is extremely configurable.  One core type is configurable and has mandatory and user configurable columns.  The user configurable ones are handled with a dictionary, so I can have a concrete type.  I also generate XAML which is used to define things like datagrids.  One of these binds to elements in the dictionary - which is an observable dictionary."

    I am also making an editor which can change and add rows of types required by user. Any tips which you want to give for this. i was thinking of using XSLT based XML to create UI for this.What benefits do you think in XAML/dictionary based approach



    Thursday, April 1, 2010 3:59 PM
  • You can email me at andyo,dev   I'm on googlemail.  Bear in mind the "tool" I have is not really intended for anyone else, I cobbled it together for myself .  You can have it but no guarantees and don't be surprised if you look under the covers and it ain't pretty.

    WPF is just great to work with generally but the binding capabilities are really impressive.

    I present a type to the interface which has a bunch of properties and a dictionary.  The xaml can bind to the dictionary and update fields in it.  It just handles it.  I don't think that would possibly work in that way in winforms.  You'd need a load of conversion/plumbing code.  You could have all your fields work in that way probably but just don't expect much performance out of large numbers of rows + columns.

    I would think you could have an intermediate layer such as a wcf service that sent regular xml to be transformed on the client via linq to xml.

    With xaml you can make your user interface look good without hardly trying.  It also offers the separation of code from presentation similar to html + code of the web world and in a much easier fashion than trying to shoehorn MVP or MVC into winforms.

    Thursday, April 1, 2010 5:46 PM
  • I will suggest the following database structure for you rather than creating 2000 columns in a single table where you may end in performance issues because of large product objects

    Product Table


    Id and other important fields






    PropertyType (Int,String,Date etc)






    //there can be two implementations of the value field. One to keep value are string and enter all other types are string OR you can create multiple value columns for each type you are supporting

    Value as string //option 1

    Value1 as string // option2

    Value2 as Date// option2




    At the object level you will have product with the important fields as member attributes. All other property will be accessed through propertybag kind of approach which is similar to dictionary.

    Thursday, April 1, 2010 8:46 PM
  • it's never easy to shoot a moving target, so the first question I'd ask is that if we can nail down something on the ground. If I understand your scenario correctly, different customers will have different set of properties. However for a particular customer the set if pretty much fixed. So you can create a view (or a stored procedure) that contains all the properties in the set and return the set when user queries it. This view can be customized per installation but the upstream code should not be affected. If you were using a stored procedure you don't need to worry about the list of parameters because it returns a fixed set (for this customer) every time.

    Saturday, April 3, 2010 12:43 AM