Best Practices

From: TIBM (anonymous_at_discussions.microsoft.com)
Date: 05/26/04 

Date: Wed, 26 May 2004 10:26:09 -0700

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

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Error adding command properties for stored procedure call
    ... I see you are using the SQL Native Client for SQL Server ... A server cursor is not allowed on a remote stored procedure or stored ... The stored procedure called is a test one that simply SELECTs the name field ... int InitializeAndConnect; ...
    (microsoft.public.data.oledb)
  • Re: SQL stored procedure not working.
    ... If I do not use that, how can I get the total record count? ... >> I create a stored procedure and a asp page. ... Even if you could specify the cursor type there, ... > interested in the RETURN value, you do not need to use a Command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Error adding command properties for stored procedure call
    ... Microsoft OLE DB Provider for SQL Server ... properties to the command the execute call attempts to create a cursor. ... A server cursor is not allowed on a remote stored procedure or stored ...
    (microsoft.public.data.oledb)
  • DBI Stored Procedures and Cursors
    ... I have a stored procedure that belongs within a package in Oracle. ... This is my first exposure to DBI, so I'm sure i'm doing something ... The stored procedure is supposed to initialize the cursor and all I ... I can execute without any problems, ...
    (perl.dbi.users)