Monday, September 18, 2006

Why Oracle .Net Dataset development needs stored procedures

If you use OR mapping, then no need to use stored procedures.
Also, if you use SQL Server, even if you use dataset, it is not that bad not using stored procedures.

However, if you use Oracle, and if you use dataset, then, you need to pay extra for not using stored procedures (the upside: you learn a lot to manipulate the dataset mechanism – but it is pain though).

1. Stored procedure adds another indirection, therefore, level the play ground of SQL server and Oracle. So, if you use dataset, you’d better use stored procedures, to leverage the features that SQL server enjoys. For example, insert with a refresh and with sequence id.

2. Oracle ODP supports arrays as input parameters. Combined with stored procedure, this can be easily used in batch processing. Note that because of the extra installation, for two tier clients, it is possible that you choose not using ODP. Even then, you can pass a string, XML or plain old comma/bar formatted, for batch processing.

Note that I feel OR mapping is the way to go, so, in the future, it is likely stored procedures are only used in rare cases; however, we live in the “present”, not in the “future”. Let's use stored procedures.

However, let's keep it as thin as possoble, i.e. no “real” business logics.


Anonymous Anonymous said...

9/18/2006 06:52:00 PM  
Blogger survic said...

Great minds are alike ;-)

The essay makes me feel standing on a solid ground!

9/21/2006 09:15:00 PM  
Blogger Vikas said...

Hi Survic ,

I found a very nice link explaining a very interesting study

9/24/2006 05:21:00 PM  
Blogger survic said...

This one is good (perhaps you told me before? it looks familiar):

There is less literature on custom class than dataset; however, there is even lesser literature on a clean dataset approach than custom class one – perhaps that is because that is impossible (a clean dataset approach); however, we can have some rules to have a not-too-messy dataset approach.

9/26/2006 05:15:00 PM  
Blogger survic said...

Also, an architect who asks people to select custom class approach, but not providing the framework, she/he has only done 10% of the job.

Providing a framework, but if it has tough learning curve, especially if it has some peculiar characteristics, he/she has only done 50%.

Providing a framework, finding ways to make it easy to adopt; further, also providing some lightweight utilities for custom class approach (for example, for winform, you got to have an easy solution for the sorting/filtering!). That is 100% job.

I will find a way to get the custom BindingSource done. "Framework" is good; but a small utility/library like this makes you feel much more confident: it enables you to say that I can compete with dataset anywhere anytime.

9/26/2006 05:28:00 PM  
Blogger Vikas said...

Jeremy D. Miller writes in his comments

"Some Agile teams will delay the introduction of a database until relatively late in the project to focus more on the behavior of the application without getting bogged down in database details."

No comments from me.

10/02/2006 05:10:00 PM  
Blogger survic said...

The interesting thing is, dataset is actually an option also. How in the world you can using dataset but postpone database until later, it is out of my wildest imagination. I do not like it at all. If it is database application, then, the metaphor is a so powerful that it is almost “accurate”. No ground, no house. Build a house on quick sand, that is crazy.

I worked in data warehouse before. The best I learnt was that it is a total myth that users do not know database. It is so obvious that human thinking is indeed “coincidentally” parallel with database! As a result, spreadsheet, grid, dimensional – all those are a subset of database. Design UI without database for a heavy data application is almost criminally wrong.

Note that I have strong non-VB background -- although I used VB from VB4 -- and then a lot of Vb3 after(!) that -- I also use the series of C/C++, perl, and Java, and before using C#, I always think I was a Java programmer, instead of VB programmer (probably when use VB, I do not think ;-). I also certainly believe OO is "natural", and also I believe custom class is superior to dataset (actually, before my recent dataset adventure, I always believed dataset was just dirty hacks -- if not beginner’s dirty hacks!). Also, I even believe domain entity modeling is acceptable. However, I believe – I believe you also agreed – separation of domain entity modeling and database modeling is almost criminally wrong (waste time and resource, create huge headache and problems between teams etc.). Whoever creates that kind of organizational problem should be brought to the parking lot and …

10/06/2006 10:51:00 PM  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home