title: ORM Architecture
body: <p>Background: I have a project that is not at all large scale (2 ASP.NET web applications with like 10 pages each).  These two web applications share a lot of back-end requirements, specifically the ability to manipulate a set of around 50 tables.  They both hit the same RDBMS, which will always be MySQL.</p>
  
  <p>Problem: A large feature-rich ORM library (Subsonic) was added to the project awhile back, which it turns out was just too much.  It's too big, there's too many parts, there's too much overhead.  Some Collections that are generated by Subsonic take >1min to create, whereas raw inline queries (bad I know) that accomplish the same take ~2s.  It's not just Subsonic that is the problem, NHibernate and other ORM tools are just <em>too big</em>, with way too many features that will never be used.  The scope of these projects will never get huge either.  Also database agnosticism is unnecessary as we will never switch away from MySQL, and no one will ever use this tool except internally.</p>
  
  <p>Clearly inlining queries is bad, unsafe, wrong.  Linq is also not an option.  Thus there is a need for some small custom ORM tool for me to package up in a library.  My thought is to include:</p>
  
  <ul>
  <li>the foundation is a the Table class, whose constructor is just fed a db name and a table name</li>
  <li>the Table class would expose a few methods publicly</li>
  <li>public DataRow Record(string primaryKeyValue) //fetches a single records</li>
  <li>public DataRowCollection Records(string whereClauseOrRangeOfKeyValues) //fetches a collection of records</li>
  <li>public bool Save() //save the current state of the database by updating/inserting any rows that have changed; not sure the best way to maintain which records are dirty or not</li>
  <li>public bool Loaded(string primaryKeyValue) //tells whether a given record exists or not</li>
  <li>public DataRowCollection Query(string mysqlQuery); //run a specific custom query</li>
  <li>privately there would be pretty much only a few things: a sanitizer, a method to do selects, a method to do upserts and not much else</li>
  </ul>
  
  <p>Perhaps the pooled knowledge of StackOverflow can highlight shortcomings of this basic setup or offer improvements.  Thanks in advance.</p>
  
  <p>EDIT: One constraint (not sure how firm it is at this point) is that I not used a pre-built library.  Higher ups are so turned off by Subsonic that any third party utility is likely to be rejected.  So it's more of an architecture choice as opposed to a utility choice.</p>