Re: Best Practices
From: Sahil Malik (contactmethrumyblog_at_nospam.com)
Date: 05/26/04
- Next message: JJKane: "Search a DataTable on a byte[] field"
- Previous message: TIBM: "Best Practices"
- In reply to: TIBM: "Best Practices"
- Next in thread: David Browne: "Re: Best Practices"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 26 May 2004 14:00:18 -0400
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" <anonymous@discussions.microsoft.com> wrote in message
news:7F80E544-6519-433B-BB29-CFB3B691565F@microsoft.com...
> 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
- Next message: JJKane: "Search a DataTable on a byte[] field"
- Previous message: TIBM: "Best Practices"
- In reply to: TIBM: "Best Practices"
- Next in thread: David Browne: "Re: Best Practices"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
