locked
creating .NET objects thru SP on MSSQL RRS feed

  • Question

  • My boss insists on creating objects for .NET WinForms through stored procedures in MS SQL Server 2005.

     

    I do not quite understand what he means since he insists that "it is very simple" and  and that RDBMS's purpose is for (storing, searching and) creating objects.  

     

    Can anybody explain me what he means?

    Tuesday, July 17, 2007 6:12 AM

Answers

  • Having not implemented a complete EAV/CR methodology I can't really comment but after a bit of googling and reading it appears that EAV/CR wouldn't be best suited compared to more current solutions.

     

    I found the following link, http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm

     

    it contains a list of disadvantages, listed below:

     

    • Considerable up-front programming (wheel reinvention) is needed to do the tasks that a conventional architecture would do automatically. However, such programming needs to be done only once, and availability of generic EAV tools could remove this limitation.
    • EAV design is less efficient than a conventional structure for retrieving data in bulk on numerous objects at a time. (For object-at-a-time retrieval, such as through a Web-based browsing interface, the volume of data is small enough that the difference is not noticeable.)
    • Performing complex attribute-centric queries is both significantly less efficient as well as technically more difficult. This needs a query generator. However, most queries on scientific databases are relatively straightforward, and directed toward specific objects of interest.
    • For schemas that are relatively static and/or simple (e.g., databases for business applications, such as inventory or accounting), the overhead of EAV design exceeds its advantages.
    • If particular classes of data will be used across multiple databases in different domains, and/or there is a large amount of existing code that manipulates them, it should probably be left in its original conventional form. This is the reason why we have chosen to store bibliographic citations (that are not in external databases) conventionally. We similarly store certain genetics-related data conventionally because of a large body of code (from other databases previously created by our group, such as PhenoDB ) that performs computations such as Hardy-Weinberg frequencies and tests of significance.

     

     HTH

     

    Ollie Riches

    Tuesday, July 17, 2007 1:35 PM
  • Dudes,

    I have been following this thread for 3 days now.  Guennadiy Vanine  if your boss wants entities generated from tables then use a Codegenerator like NETTiers or some other Codesmith template. I see no reason from all I have read in teh forum for you to write the entities yourself, with code genertators like NETTiers you can even get the procedures generated for you.

     

    Please tell me if I have oversimplified the problem and if you have considered the option of generating code before.

    Wednesday, July 18, 2007 5:34 PM
  •  

    Guennadiy,

     

    My apologies if my previous answer wasn't heading in the right direction, just difficult to figure out what advice you're seeking.

     

    I think that you're almost asking the wrong question here, you're trying to defend against a decision to implement everything in the database, including the models for code.  My question would be based around what is the problem space, and how often are your schemas likely to change?  How does the solution have to perform? 

     

    If the reason for the question is because over iterations your customers requirements will change, I'd expect the suggested solution from your boss would not be the most optimal.  You would be providing a very generalised solution to a specific problem.

     

    I would approach by designing a solution that fits the requirements, and if they change, refactor the solution.  Obviously having unit tests in there will make that process predictable, and a lot less painful.

     

    I don't know how the technology you mention works, but it isn't something that I'd personally do.  Perhaps you should get him to justify the decision instead?  I can't imagine it would be very maintainable, but as I say, I'm not familiar with the methodology.

     

    Cheers,

     

    Martin.

    Thursday, July 19, 2007 12:38 AM

All replies

  • From the little information you have provided I believe he might mean the use of domain entities versus the use of generic or typed  DataSet objects. So he expects you to create .Net classes that represents domain entities and then populate these via a data access layer (DAL) as apposed to populating a DataSet and passing this around your application.

     

    IMO both approaches have benefits but they dependly greatly on the scale of the application\system you are building, there is plenty of discussion out there on this topic.

     

    HTH

     

    Ollie Riches

     

     

    Tuesday, July 17, 2007 9:27 AM
  •  Really I had already written this stored procedure as he wanted but I cannot convince him that it was a waste of time for me and there is no point to continue in this vein.
     
    This is in EAV/CR style,  i.e. 1-2 tables for all things in the world
    It takes an ID of object as input parameter, makes recursively a copy (insert into) of template object (few lines of attributes).
    "Recursively"" means that an attribute line may contain a reference ID of a template subobject, in which case all subobjects are traversed and  copied receiving  the copy of complex object in table.
     
    It is all inside MS SQL Server.
     
    I guess he got such ideas from OOP in SQL forums. He is MS Access programmer. I have no experience with MS Access but might be the objects are created in MS Access in such manner????
    I have no clue.
    I would like to understand
     
    Really my question is how to convince him that this does not make neither  sense nor help for WinForm app development in .NET.
     
    What are possible arguments?
    I really exhausted all mines and hope for some external help 
    Tuesday, July 17, 2007 10:58 AM
  • Having not implemented a complete EAV/CR methodology I can't really comment but after a bit of googling and reading it appears that EAV/CR wouldn't be best suited compared to more current solutions.

     

    I found the following link, http://ycmi.med.yale.edu/nadkarni/eav_CR_contents.htm

     

    it contains a list of disadvantages, listed below:

     

    • Considerable up-front programming (wheel reinvention) is needed to do the tasks that a conventional architecture would do automatically. However, such programming needs to be done only once, and availability of generic EAV tools could remove this limitation.
    • EAV design is less efficient than a conventional structure for retrieving data in bulk on numerous objects at a time. (For object-at-a-time retrieval, such as through a Web-based browsing interface, the volume of data is small enough that the difference is not noticeable.)
    • Performing complex attribute-centric queries is both significantly less efficient as well as technically more difficult. This needs a query generator. However, most queries on scientific databases are relatively straightforward, and directed toward specific objects of interest.
    • For schemas that are relatively static and/or simple (e.g., databases for business applications, such as inventory or accounting), the overhead of EAV design exceeds its advantages.
    • If particular classes of data will be used across multiple databases in different domains, and/or there is a large amount of existing code that manipulates them, it should probably be left in its original conventional form. This is the reason why we have chosen to store bibliographic citations (that are not in external databases) conventionally. We similarly store certain genetics-related data conventionally because of a large body of code (from other databases previously created by our group, such as PhenoDB ) that performs computations such as Hardy-Weinberg frequencies and tests of significance.

     

     HTH

     

    Ollie Riches

    Tuesday, July 17, 2007 1:35 PM
  •  

    Ollie,

    thanks a lot.

     

    I saw this (and many more non-English, since I know a few languages, references)

    There is also the discussion of it
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1731886&SiteID=1

    initiated by me.

     

    Just now I really want to understand how the person programming during 10-15 years insists that I create .NET objects by cloning data by T-SQL script in RDBMS????

     

    What, how and where do I miss? I feel me bad since I cannot understand the other and cannot explain obvious things to others.

    Tuesday, July 17, 2007 11:43 PM
  •  

     

    Guennady,

     

    From what I'm reading, it seems that you're after an explanation of why you should use .NET objects instead of reading the data from sql directly?

     

    If you use either a DataSet or a bit of .NET code as your entity, it allows you to pass your data around between layers in your application.  I think all that your boss really means is that you're to store the data in .NET objects, to pass around?

     

    Perhaps you can get a little clarification on what your boss means?

     

    cheers,

     

    Martin.

    Wednesday, July 18, 2007 3:38 AM
  • Well, I am after explanation why I was insisted  to create data structures for objects in database table before everything else.

     

    The question is why do I need dummy data (structures) in database tables before having any objects and in order to create objects

     

    IMO the .NET objects are created by client .NET code, the user enters data through GUI into .NET objects that are to be threafter inserted (or updated)  , really synchronized from datasets into database.

    I do not need to have anything in database tables in order to have .NET objects. I need the data in tables only to persist objects.
    IMO .NET objects are first/original to their storage

     

    What I am told and would like to understand is the creation of dummy database structures immitationg the .NET objects first and before anything else in order to create objects thereafter/secondly.
    This is what I cannot understand

    The logic and rationale is upside down.

     

    I do not need to have storage of any dummy database data before and for creation of objects

    Whenever and after  I create .NET objects  and if I have necessity to persist them, then I insert data into dtabase,
    that is I create their persistence storage.
    But why am I told to multiply dummy data inside database before ever having the .NET object and in order to have it?

     

    Well, I asked many times my boss and he insists that I should create database data in order to create objects

    Remeber that it is EAV/CR and I am free to insert whatever I want into table.

     

    And again the stored procedure in T-SQL that clones structures (just recursively inserting rows into table according to existing in the same table template) already exists (without having any .NET code)
    This is what I cannot understand - my boss inisists to have replicas of future .NET objects even before starting to design any client application .NET code

     

    Let me repeat my question:

    I do not need explanation how to construct .NET applications (I am doing this for 5 years now and I am MCSD for .NET)

    I want to understand the rationale of the person in order to dissuade him from pushing me to do monkey work

    I also need some fresh advices and arguments.

     

    Again my boss calls his approach implementation of OOP in SQL, gives me references to forums list 

    http://www.sql.ru/forum/actualtopics.aspx?search=%F2%F0%E5%F5%E7%E2%E5%ED%EA%E0&bid=58

     

    I read it all. It does not explain me the point

    As a matter of fact it even does not have much connected to .NET!

    Wednesday, July 18, 2007 3:38 PM
  • From what you are saying it appears your boss is 'over engineering' the solution to the problem, it appears he wants you to populate the database with .Net object definitions even before the problem(s) has been defined, almost as if he wants to complete implement a domain model even though at the moment you are only know and working on a small part of the model.

     

    Does this sound right?

     

    If this is true then I don't understand either why he wants you to do this...

     

    HTH

     

    Ollie Riches

     

     

     

    Wednesday, July 18, 2007 3:51 PM
  • Well, the idea is to account for objects of any structure and types, to have generic approach to all possible changes in application and in the database.

     

    Then let me explain or repeat what the T-SQL stored procedure does. It clones some hierarchical object by scanning the table recursivelyaccording to already existing hierarchical class/template data. There is a rerence column for subobjects IDs

    Well, each object is a few lines of attributes (EAV/CR)

     

    But it neither helps nor saves nor guides me to anything. It is EAV/CR there are no relations, types in  in db (any data is sql_variant) and anyway I should repeat the algorithm, relations and unroll structures in C# (client .NET code) dataset, or whatever

     

    Just what is the point of cloning instances of objects in database before anything else? 

    Wednesday, July 18, 2007 4:34 PM
  • I work on the whole model whatever exists and whatever would appear in the future because the whole idea of EAV/CR is the abscence of any rigid/fixed dat types or model/schema

     

    It is to account for any changes in db schemas by having no schema at all. The EAV/CR table, and therefore all possible models through it,  exists

     

    The problem is that I cannot start coding because I am forced to follow stupid instructions that does not have any sense

     

    And I cannot get even the rationale of what I am told to do (i.e. T-SQl scripting for cloning of so called OOP objects inside the table)

    Wednesday, July 18, 2007 4:38 PM
  • Dudes,

    I have been following this thread for 3 days now.  Guennadiy Vanine  if your boss wants entities generated from tables then use a Codegenerator like NETTiers or some other Codesmith template. I see no reason from all I have read in teh forum for you to write the entities yourself, with code genertators like NETTiers you can even get the procedures generated for you.

     

    Please tell me if I have oversimplified the problem and if you have considered the option of generating code before.

    Wednesday, July 18, 2007 5:34 PM
  •  

    Guennadiy,

     

    My apologies if my previous answer wasn't heading in the right direction, just difficult to figure out what advice you're seeking.

     

    I think that you're almost asking the wrong question here, you're trying to defend against a decision to implement everything in the database, including the models for code.  My question would be based around what is the problem space, and how often are your schemas likely to change?  How does the solution have to perform? 

     

    If the reason for the question is because over iterations your customers requirements will change, I'd expect the suggested solution from your boss would not be the most optimal.  You would be providing a very generalised solution to a specific problem.

     

    I would approach by designing a solution that fits the requirements, and if they change, refactor the solution.  Obviously having unit tests in there will make that process predictable, and a lot less painful.

     

    I don't know how the technology you mention works, but it isn't something that I'd personally do.  Perhaps you should get him to justify the decision instead?  I can't imagine it would be very maintainable, but as I say, I'm not familiar with the methodology.

     

    Cheers,

     

    Martin.

    Thursday, July 19, 2007 12:38 AM
  • LiveToCodeCodeToLive,
    thanks, that was my first proposals - to use code and sql queries generators (and NHibernate).

    I had not used it for a long time now... Whether CodeSmith works with .NET2.0 already?

    Just now I have got some more explanations. My boss insists to write all tiers (DAL, BLL might be except Presentation tier) in T-SQL??? while I heard before about .NET

     

    Martin Platt,
    yes, really - well formulated question is already not a question (but answer).


    And I start to understand something more, especially after hearing some aditional answers from my boss that he plans to make almost all in T-SQL
    while I was hired as .NET developer and to develop in .NET
    That's is a surprise - I am continuously having contradictory information and orders
    (that is I was told a general and architectural phrases about .NET and concrete instructions to proceed with SQL)

     

    The application is internal. So the customer coincides with my boss.

    The existing application is MS SQL Server 2005 database (I believe migrated from MS Access) with MS Access GUI.

    It is very responsive. And I have never seen in my life so much SQL (many page triggers, stored procedures) in database
    It accomplishes almost all through SQL!

     

    There are new requirements that is frozen until the creation of the new database and application

    It is not because aplication would change. It is strategic wish that every task in company,
    some are quite disparate from one another would be done through a single application
    And my boss, the head of IT department whishes to base it on the single EAV/CR table

    Well, I have orders to avoid digging into existing solution since we should substitute it with the new one I am creating and for this I should not know anything from the past or around me


    Even more some of the new requirements that I have implemented into old database and application were eventually frozen

     

    Ok, thanks. It is like in anecdote. I answered, answered till I became to understand what I am explaining

    Thursday, July 19, 2007 4:40 AM
  • Ok.
    I understood that my boss uses the term .NET and SQL unterchangeably while meaning really only SQL

     

    Thanks to everybody for your time

    Thursday, July 19, 2007 6:15 AM