views:

470

answers:

5

I am trying to leverage ORM given the following requirements:

1) Using .NET Framework (latest Framework is okay)
2) Must be able to use Sybase, Oracle, MSSQL interchangeably
3) The schema is mostly static, BUT there are dynamic parts.

I am somewhat familiar with SubSonic and NHibernate, but not deeply.
I get the nagging feeling that the ORM can do what I want, but I don't know how to leverage it at the moment.

SubSonic probably isn't optimal, since it doesn't currently support Sybase, and writing my own provider for it is beyond my resources and ability right now.

For #3 (above), there are a couple of metadata tables, which describe tables which the vendors can "staple on" to the existing database.
Let's call these MetaTables, and MetaFields.

There is a base static schema, which the ORM (NHibernate ATM) handles nicely.
However, a vendor can add a table to the database (physically) as long as they also add the data to the metadata tables to describe their structure.

What I'd really like is for me to be able to somehow "feed" the ORM with that metadata (in a way that it understands) and have it at that point allow me to manipulate the data.

My primary goal is to reduce the amount of generic SQL statement building I have to do on these dynamic tables.
I'd also like to avoid having to worry about the differences in SQL being sent to Sybase,Oracle, or MSSQL.

My primary problem is that I don't have a way to let ORM know about the dynamic tables until runtime, when I'll have access to the metadata

Edit: An example of the usage might be like the one outlined here:

IDataReader rdr=new Query("DynamicTable1").WHERE("ArbitraryId",2).ExecuteReader();

(However, it doesn't look like SubSonic will work, as there is no Sybase provider (see above)

A: 

I am a little confused as to how the orm would be used then at runtime? If the ORM would dynamically build something at runtime, how does the runtime code know what the orm did dynamically?

"have it at that point allow me to manipulate the data" - What is manipulating the data?

I may be missing something here and i aplogize if thats the case. (I only have really used bottom up approach with ORM)

mattlant
edited above, thanks. Sorry, perhaps "manipulating the data" is a bit wordy.. I just meant regular CRUD (in whatever form I can get it)
Kevin Radcliffe
AHh, now i see what you mean, and how you plan to use it. I use LLBLGen quite a bit, and it generates classes based on the DB. However it comes with a very comprehensive dynamic query engine. It does sybase too. Give me a bit and i will see if i can write up some code and paste it. who knows..
mattlant
Sorry but i couldnt get anything beyond using SQL Text strings. I dont know if you require more abstraction than that though.
mattlant
Hmm.. probably, depends on the strings, and how vars are passed around.. One of the problems for example is that parameters passed into a query look different in Oracle than they do for MSSQL (yay standards). I'd like to not have to worry about that detail.Thanks VERY much for helping though
Kevin Radcliffe
Hrm. it would be interesting to know for me anyways, as they support multiple backend DB's so it must at some point buolt the queries different based on the db. I i happen to see anything else i will post again.
mattlant
A: 

IDataReader doesn't map anything to an object you know. So your example should be written using classic query builder.

Orlangur
IDataReader is just what this ORM returned (which has knowledge of the underlying database). It could return a scalar, or make an update based on what type of query was done. I'm trying to abstract away from the database here, what is the classic query builder I'd use here that abstracts from db?
Kevin Radcliffe
+1  A: 

We did some of the using NHibernate, however we stopped the project since it didn't provide us with the ROI we wanted. We ended up writing our own ORM/SQL layer which worked very well (worked since I no longer work there, I'm guessing it still works).

Our system used a open source project to generate the SQL (don't remember the name any more) and we built all our queries in our own Xml based language (Query Markup Language - QML). We could then build an xmlDocument with selects, wheres, groups etc. and then send that to the SqlEngine that would turn it into a Sql statement and execute it. We discusse, but never implemented, a cache in all of this. That would've allowed us to cache the Qmls for frequently used queries.

noocyte
That sounds like a very interesting way to do it. Thanks for your input/experience. Unfortunately, my dev team is just 2 people (that does include me). I don't think creating an ORM and accounting for all the differences in SQL between Oracle,Sybase,MSSQL will be worthwhile if it can be avoided.
Kevin Radcliffe
+1  A: 

Have you looked into using the ADO.NET Entity Framework?

MSDN: LINQ to Entities

It allows you to map database tables to an object model in such a manner that you can code without thinking about which database vendor is being used, and without worrying about minor variations made by a DBA to the actual tables. The mapping is kept in configuration files that can be modified when the db tables are modified without requiring a recompile.

Also, using LINQ to Entities, you can build queries in an OO manner, so you aren't writing actual SQL query strings.

hurst
I have looked at it a bit. Do you know if there is a way to dynamically inject "entities" into the system by using metadata similar to what I have described above? I didn't see anything like that, but since you are suggesting it here, maybe you saw something I didn't? If so, this would likely fit!
Kevin Radcliffe
+1  A: 

Acording to this blog you can in fact use NHibernate with dynamic mapping. It takes a bit of tweaking though...

Markowitch
I've looked at that example before, but I'm glad you've led me to take another look. The problem with this is that it maps to defined types ultimately (which I don't know until runtime). I basically JUST want the metadata there, and be able to use it to manipulate data dynamically. Thanks.
Kevin Radcliffe
Ended up w/ NHibernate, because it supports the most legacy dbs. Updating the metadata behind the scenes seems very unintuitive to me though NHibernate, but it suffices for now. I'm hoping to use ActiveRecord though IronRuby for another project though, it seems to be much better at handling dynamic schemas.
Kevin Radcliffe