2

Is there an ORM tied to MS SQL 2008+, generating .NET code, which takes advantage of MS SQL specific features?

I'm specifically interested in 2 features:

(1) fetching a group of records by their key, which will not be mapped to "select in" query.
-this can be achieved by using either OPENXML in 2005+ or by using Table-Valued Parameters in 2008+

(2) inserting more than one record with a single request to the DB (this is similar to, yet not the same as Bulk-Insert). For example:

Customer[] customers = CreateSomeCostomers();  
dataManager.Save(customers);

So the SQL related code is prepared on the client, and then sent entirely to the SQL server.

BTW, stored procedures are out of the question.

I'd be happy if you stackoverflowers have any advice for me.

Thanks!

Update:

I'm looking for a complete solution, not an extensible framework/product that I could customize to my needs. So customizing NHibernate/linq2sql/etc. by writing my own code implementation does not suit my needs.

A few words about the accepted answer.

It seems that currently all ORM frameworks try to cover more than a single database/technology. So I guess there's no ready solution for my needs, at least nothing out of the box.

I understand that @Ryk, pointing to Fluent NHibrenate, believes that such solution actually exists, and it's a matter of configuration. I took a look into the source code of both NHibernate and Fluent NHibernate, and I found nothing that supports the features I'm looking for. For instance, I couldn't find the keyword OPENXML or sp_xml_preparedocument in both source codes. So maybe I'm missing something, but I don't think so. Since the bounty is time-limited, I can't take a deeper investigation in FN or NH. I still think these features are not supported there.

So, the accepted answer is, per my best understanding, that there's no such thing. The most generic answer claiming that, is @Cade Roux's answer.

I thank you all for your participation. Cheers!

Ron Klein
  • 9,178
  • 9
  • 55
  • 88
  • It probably wouldn't be much of a stretch to extend NHibernate to support these features if it doesn't already support them. – Michael Maddox Feb 11 '11 at 13:18
  • 3
    @Michael Maddox - Ayende touched on the issue with TVP with NHibernate here: http://ayende.com/Blog/archive/2008/08/26/SQL-Server-2008-Table-Value-Parameters-and-NHibernate.aspx – Phill Feb 14 '11 at 09:34

5 Answers5

1

Unfortunately, no. Microsoft decided to go down the multi-platform lane with Entity Framework. Although it would have been nice if they built something around SQL Server and left other DBs for the respective db vendors or third party OR mappers.

KristoferA
  • 12,287
  • 1
  • 40
  • 62
  • So MS didn't provide such a framework/library, but I'm looking for *any* solution, which may be anything from OSS to some company's product. – Ron Klein Feb 17 '11 at 18:46
0

I realize that this answer comes a bit late (may be helpful to others seeing it though). For the reasons given here plus also for getting proper DB source code management in the process we have our own mapper, which is available as open-source project: bsn ModuleStore

This toolset does not go all the way that ORMs such as nHibernate or Entity Framework do and it does require some more coding. However, it is following the "good old" approach with SPs for all data access (no debate please - both dynamic and static DB code have their pros and cons). The consequence is that queries have to be hand-coded in SQL, giving you full control over the code executed on the DB server.

As for some benefits, you get full bidirectional mapping of entities (yes, using TVPs, with an efficient custom data reader that "streams" the entities instead of building an in-memory DataTable as most examples with TVP do). It also supports SPs returning multiple result sets and last but not least it provides completely integrated and quite extensible support for database modularization, versioning (at the source code level for proper integration with your SCM) and integrity checking (comparing the objects present in the DB against the source files).

Lucero
  • 59,176
  • 9
  • 122
  • 152
0

If you want to use SQL Server features, then a black box ORM isn't going to cater to that.

If you want to get the most out of your chosen database (any database), you are going to be more tightly coupled and so you are better off with code-generation for (generating both stored procs and) your mapping layer instead of a drop-in ORM.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • I didn't check it, but I think I can create a (temp) table type, with a unique name, use it with TVP, and then delete the table type. All in the same request, and no stored procedure involved. Seems reasonable to me. I'll try to create a PoC project and I'll update the question. – Ron Klein Feb 15 '11 at 04:42
  • @Ron Klein, You're right, you can CREATE TYPE and DROP TYPE in a batch without them being the first statement in a batch, maybe even combined with a parameterized statement. – Cade Roux Feb 15 '11 at 08:21
0

Yes, I believe that Fluent NHibernate will do what you want. Takes a bit setting up, not too hard, but once it is working, it is a treat.

On a performance note, it is very good, but if you are planning to do a transactional system with it that does thousands per minute, I would probably go down the old stored proc way for tuning.

Basic idea is that you create entity classes and mapper classes that will map your database objects to the c# objects. From there you simply populate and object or list of objects, and do Object.Update() or Object.Delete() or Object.Get() etc. Already built in, you dont have to go and wire it up. You can always override it if you want. It is very flexible.

Ryk
  • 3,072
  • 5
  • 27
  • 32
  • I took a look at this project, per your suggestion. It looks like this project is mostly into easing the pain of schema mapping for NHibernate. If so, this reduces the question to: "does NHibernate support the features described above?" – Ron Klein Feb 17 '11 at 18:41
  • (1) fetching a group of records by their key, which will not be mapped to "select in" query. - If I understand you correctly, - YES -this can be achieved by using either OPENXML in 2005+ or by using Table-Valued Parameters in 2008+ (2) inserting more than one record with a single request to the DB (this is similar to, yet not the same as Bulk-Insert). - YES – Ryk Feb 17 '11 at 21:12
  • @Ryk, so basically your point is that NHibernate supports the features above. Does NHibernate support these features out of the box? – Ron Klein Feb 18 '11 at 04:15
  • @Ron: As with all these things, yes it is supported out of the box, but the box have to be wired up. And this is Fluent NHibernate, not just nhibernate. – Ryk Feb 18 '11 at 05:14
  • @Ryk, I'd greatly appreciate if you could provide a link for such implementation. – Ron Klein Feb 18 '11 at 07:23
  • @Ron: I already did, in my Answer post. There is a fully working example there. – Ryk Feb 18 '11 at 07:44
  • @Ryk, I looked into the source code of FN, I found nothing there regarding the issue at question. No `OPENXML`, no `sp_xml_preparedocument`. I still think FN is pretty much into easing the pain of wiring up NH. I took a deeper look into the link you provided, and saw nothing about a specific advantage of MS SQL Server. – Ron Klein Feb 20 '11 at 20:16
  • @Ron - Hint: Look for XDocument. But this is available in normal C# too, and it fluently converts between SQL XML and C# XML. – Ryk Feb 20 '11 at 21:13
  • @Ryk, I'm looking for multiple inserts, implemented by `OPENXML` at the TSQL level. I'm not looking for XML mappings, that's a totally different story. If you could point me to a specific file in the source code, which actually uses a single TSQL statement to implement multiple inserts into a single table, by using `OPEN XML` - I'd be more than happy. Or, as an alternative: If you can create a sample project against MSSQL, do multiple inserts to a single table, profile it and see if it's all reduced to a TSQL code with `OPENXML` in it - I'd be really happy, and you'll get a bounty. – Ron Klein Feb 20 '11 at 21:35
  • @Ron, I cannot see how you want to bring ORM auto generate code and OPENXML together. The idea of using ORM code <-> object mappers is so that you *don't* care what the app uses. The moment you want to force it to use a particular function or method, is where you would override the method to force it to do what you want. I don't see why you would care how records are updated or selected when using ORM, as long as the correct records are selected or updated. OPENXML is a SQL 2000 function, and whether your app uses it or not to update records should not be an issue, or am I missing something? – Ryk Feb 20 '11 at 23:18
  • @Ron, and if you want to use a stored proc to do multiple records at once, just use a TVP where one of your input parameters is a TVP with an XML field and then inside the proc you can use OPENXML to do the updates. – Ryk Feb 20 '11 at 23:25
  • @Ryk, I *do* care about the ORM's implementation, that's the whole point. I want multiple inserts to be combined to a single request. This can be achieved by using `sp_xml_preparedocument`, `OPENXML` etc. – Ron Klein Feb 25 '11 at 04:34
  • @Ron - Since you accepted Cade's answer, it is clear that you have an architecture *and/or* a technology misunderstanding. You are trying to implement something that the whole ORM architecture is trying to make transparent for you. – Ryk Feb 25 '11 at 04:58
  • @Ryk, perhaps we don't share the same ideas about what ORM is and what it should or may be. Software architecture, in general, is a very fluid place, with lots of opinions. Having said that, your claim about my so called misunderstanding is not friendly, to say the least. – Ron Klein Feb 25 '11 at 20:45
0

Nhibernate is the only your option except writing your own data acccess layer, you can easily extend Nhibernate to use new|custom|specific SQL syntax.

Alex Burtsev
  • 12,418
  • 8
  • 60
  • 87