Best Practices

Guest · May 26, 2004

Hi. I asked this question on another newsgroup, but with no luck..

I'm doing a .Net conversion project. Before, I was using VB6.0 and Oracle for a 3 tier architecture.
An important part of the conversion is moving a large number of Oracle stored procedure to VB.NET; the reason is that they containt too much business logic. This is an example of how a stored procedure looked like:

BEGIN

define cursor 1
define cursor 2
define cursor 3

validate inputs

open cursor 1(with input arguments)
open cursor 2(with input arguments)
if cursor 1 column(x) <> cursor 2 column(y) raise error
open cursor 3(with input and cursor 2 column(y) arguments)
if cursor 3 column(z) is null then insert into some_table
if cursor 3 column(w) is null then set some output variables

set some output variables

END

What is the best way to write this in a VB.NET function/procedure?
Should I use datasets or should I use datareaders?
Should I execute the select statements (the cursors) at the beginning of the function, or should I execute them in the "middle" of the function?
Do I use the same datareader for all the select statements (eg call ExecuteReader on the same datareader with different command object - or maybe the same command object with a different command string)?
Should I use stored procedures for selecting, inserting and deleting from the database instead of doing it from the function?
How do I deal with nulls?

Maybe I'm not asking all the right questions, so I would appreciate, if someone knows a complex example on the internet that is similar to what I'm asking, to share the link.

Thanks.
TibM

Sahil Malik · May 26, 2004

TibM,

Your question warrants an answer bigger than this newsgroup allows - entire
books could be written about this, and you are right, there is a serious
dearth of Oracle/ADO.NET books, though I have proposed writing one .. hey
maybe one day.

Anyway,

What is the best way to write this in a VB.NET function/procedure?

The bigger picture would be - how do architect your data driven application.
The important facet to understand here is how microsoft recommends you
should architect your application. How you need to abstract database from
the middle tier, and where does the business logic reside (AHA !!!). Ideally
first you should answer the follow question.

"Does my app need to scale?"

If it does, then you need to understand that databases scale using
clustering/failover, but middletier's scale using stateless farms (web
farms/network load balancing etc.). Then if your application should have
neat segregation between database specific everything, versus what may
qualify as a business object. Database could be Select * from Customer, but
a business object representation should be Customer.FirstName. The front
end - whatever it is - would then talk to the business object
representation.

There is a *lot* more to it than above mentioned. The final design depends
on your exact situation. By no means my above 20 words is a complete answer.

Should I use datasets or should I use datareaders?

Combination of both. Datasets are disconnected, and datareaders are not -
depends on what you need and where you need it.

Should I execute the select statements (the cursors) at the beginning of

the function, or should I execute them in the "middle" of the function?

you mean function in oracle? or VB.NET function? Well - if it's within
Oracle, then follow Oracle's guidelines. If it is VB.NET, it doesn't matter
as long as you close database connections soon as possible and open them as
late as you can.

Do I use the same datareader for all the select statements (eg call

ExecuteReader on the same datareader with different command object - or
maybe the same command object with a different command string)?

Try not to design an application that uses one DataReader that is connected
all the time servicing multiple needs. That would prevent ADO.NET from doing
connection pooling effectively. Even otherwise, you need stateless design.
Thats a big no no. DataReaders are apt over dataadapters/datasets where you
need a quick query which you will connect->query->disconnect faster than you
can say YABADABADOO.

Should I use stored procedures for selecting, inserting and deleting from

the database instead of doing it from the function?

Yes. There are multiple reasons to this - speed being not so important
anymore - there are many other advantages by abstracting your d/b specific
stuff in stored procs/packages.

How do I deal with nulls?

IsDBNull() <---

Hope that helped. IMHO, if you are architecting an application, get someone
with experience in this domain to help you guys out. Don't lay the first
brick askew.

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

TIBM said:
Hi. I asked this question on another newsgroup, but with no luck...

I'm doing a .Net conversion project. Before, I was using VB6.0 and Oracle for a 3 tier architecture.
An important part of the conversion is moving a large number of Oracle

stored procedure to VB.NET; the reason is that they containt too much
business logic. This is an example of how a stored procedure looked like:

BEGIN

define cursor 1
define cursor 2
define cursor 3

validate inputs

open cursor 1(with input arguments)
open cursor 2(with input arguments)
if cursor 1 column(x) <> cursor 2 column(y) raise error
open cursor 3(with input and cursor 2 column(y) arguments)
if cursor 3 column(z) is null then insert into some_table
if cursor 3 column(w) is null then set some output variables

set some output variables

END

What is the best way to write this in a VB.NET function/procedure?
Should I use datasets or should I use datareaders?
Should I execute the select statements (the cursors) at the beginning of

the function, or should I execute them in the "middle" of the function?

Do I use the same datareader for all the select statements (eg call

ExecuteReader on the same datareader with different command object - or
maybe the same command object with a different command string)?

Should I use stored procedures for selecting, inserting and deleting from

the database instead of doing it from the function?

How do I deal with nulls?

Maybe I'm not asking all the right questions, so I would appreciate, if

someone knows a complex example on the internet that is similar to what I'm
asking, to share the link.

David Browne · May 26, 2004

TIBM said:
Hi. I asked this question on another newsgroup, but with no luck...

I'm doing a .Net conversion project. Before, I was using VB6.0 and Oracle for a 3 tier architecture.
An important part of the conversion is moving a large number of Oracle

stored procedure to VB.NET; the reason is that they containt too much
business logic. This is an example of how a stored procedure looked like:Why would you want to do that? That's what stored procedures are for.
PL/SQL is a better language for expressing business logic that VB.NET
ADO.NET. If you can come up with a throughly object oriented design for
your application, you may be able to express the business logic better in VB
than you can in PL/SQL. Even then it would be slower.

Rewriting oracle stored procedures in VB.NET will just result in slower,
more complicated and harder to maintain code.

Should I use datasets or should I use datareaders?

You should use strongly-typed datasets for all your tables. This gives you
intellisense for your columns, automatic type conversions, compile-time type
safety and more compact and efficient expressions.

Should I execute the select statements (the cursors) at the beginning of

the >function, or should I execute them in the "middle" of the function?

Get what you need when you need it.

Do I use the same datareader for all the select statements (eg call
ExecuteReader on the same datareader with different command object - or
maybe the same command object with a different command string)?

No. Create new ones.

Should I use stored procedures for selecting, inserting and deleting from

the >database instead of doing it from the function?

No. You should use stored procedures to encapsulate business logic, not to
wrapper trivial SQL statements.

How do I deal with nulls?

System.DbNull

David

Guest · May 26, 2004

Hi. Thanks a lot to both of you. I think I got more feedback than I expected for
To me, the conversion seemed simple: create objects which encapsulate business logic and create a data access class composed of static/shared procedures (which are basically the converted plsql procedures that I had before)
I did not know that plsql stored procedure are considerate as appropriate "containers" for business logic in the OO world, nor did I know that writing the business logic in VB.NET will be difficult to maintain

I have a last question. In our old 3-tier design, each user would get an instance of a db connection upon authentication which he would use thoroughout the session for all database calls. I was told this design was preferred since connection setup takes a long time (in VB6.0). Is this true for VB.NET? Is this considered a bad design

Thank
TibM

Sahil Malik · May 26, 2004

I have a last question. In our old 3-tier design, each user would get an
instance of a db connection upon authentication which he would use
thoroughout the session for all database calls. I was told this design was
preferred since connection setup takes a long >time (in VB6.0). Is this true
for VB.NET? Is this considered a bad design?

............... If you intend on keeping the connection open all the time,
then it is considered bad design. Per user connection that is kept open all
the time is highly not scalable. Ideally you shouldn't even maintain a
connection object.

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

TIBM said:
Hi. Thanks a lot to both of you. I think I got more feedback than I expected for.
To me, the conversion seemed simple: create objects which encapsulate

business logic and create a data access class composed of static/shared
procedures (which are basically the converted plsql procedures that I had
before).

I did not know that plsql stored procedure are considerate as appropriate

"containers" for business logic in the OO world, nor did I know that writing
the business logic in VB.NET will be difficult to maintain.

I have a last question. In our old 3-tier design, each user would get an

instance of a db connection upon authentication which he would use
thoroughout the session for all database calls. I was told this design was
preferred since connection setup takes a long time (in VB6.0). Is this true
for VB.NET? Is this considered a bad design?

David Browne · May 26, 2004

Sahil Malik said:
instance of a db connection upon authentication which he would use
thoroughout the session for all database calls. I was told this design was
preferred since connection setup takes a long >time (in VB6.0). Is this true
for VB.NET? Is this considered a bad design?

It is no longer bad design to open and close connections frequently.
ADO.NET pools the connections behind the scenes, and so when you "open" a
connection you are usually just grabbing an open connection from the pool.

.............. If you intend on keeping the connection open all the time,
then it is considered bad design. Per user connection that is kept open all
the time is highly not scalable.

Not true. No Oracle server will blink at a couple of hundred connections,
and if the number of connections becomes a problem you can always switch
your TNS listener to MTS mode. So one connection per end user is just fine
for many apps.

David

Best Practices

Guest

Sahil Malik

David Browne

Guest

Sahil Malik

David Browne