Attention: We are retiring the ASP.NET Community Blogs. Learn more >

TechEd - DeKlarit 4.0 and SQLCLR

DeKlarit has a booth in TechEd (the 4th year in a row, booth #850 this time), and we are showcasing a DeKlarit 4.0 CTP that runs in Visual Studio 2005.

It still does not take advantage of most of the new .NET 2.0 feature, but it generates partial classes, uses the new .NET DataSet serialization features, and it includes one new feature that is causing a lot of impact, which is SQLCLR stored procedures.

Let's say you want to save an Invoice, and before saving it you need to check the Customer's balance, the Product's inventory, and if everything is OK, save the Invoice header, update the Customer Balance, update the Item inventory, and save the Invoice line.

Doing this with a single roundtrip to the database is not possible using dynamic SQL. Doing it with T-SQL stored procedures is also difficult because there's no way to send the whole Invoice to the stored proc. If you write T-SQL stored procedures with business logic, you could have one stored procedure to save the header and check the customer balance, and  another to check the item inventory, decrease it, and save the lines. If you have '1-sql-sentence' T-SQL stored procedures, then you will need one to check the customer balance, another to save the header, other to check the inventory, another to update it, and another to insert the invoice line. This goes in the same direction as the 'databases as services' approach.

With CLR stored procedures, you can send the whole Invoice as a single data structure and do everything in the server. This is what we are enabling with DeKlarit 4.0. In the current version we are sending a diffgram, but we are testing other alternatives. This makes me less worried about not having support for batching outside of the SqlDataAdapters ;)

Some people will like it, other people won't, but I think it's very important to have this option.

 

1 Comment

  • Neat! :)



    I'm still puzzling with the idea of having some sort of uber procedure which hosts the Dynamic Query engine (which spits out SQL code on meta-imput), but I've to see if it's really useful.



    One disadvantage of your approach is of course that you tie your BL to SqlServer 2005. But as you said: some will like it, others won't.



    I've yet to see how much the SQLCLR feature will be used in practise. On Oracle, Java procedures are rare, at least that's my experience, while these could do the same thing as you're describing.

Comments have been disabled for this content.