data access layer function call

[Guest]

Hi. I have the following requirement and I don't know which way I should approach it
In a 3 tier application, the business layer calls a function in the data access layer; the data access layer function opens an Oracle connection, execute an "ExecuteScalar" instruction, closes the connection and returns the result to the business layer

I would like to know which is the best way for handling both database errors and invalid data
Right now, I wrapped connection.open and command.executescalar in a try/catch block with connection.close in the finally statment. Should I not do that? Should I move the try/catch block in my business layer wrapped around the data access layer function call? Btw, what is the difference between connection.close and connection.dispose - some MS examples have connection.close while others have both
Where should I do my data validation? For example, should I throw an exception if the executescalar returns a null, that I should catch in the business layer, or should I have an IIf(retVal.IsDBNull, -1, retVal) statement? Is the data access layer supposed to return clean data

Thank
TM

 

[David Browne]

Hi. I have the following requirement and I don't know which way I should approach it:
In a 3 tier application, the business layer calls a function in the data

access layer; the data access layer function opens an Oracle connection,
execute an "ExecuteScalar" instruction, closes the connection and returns
the result to the business layer.

I would like to know which is the best way for handling both database errors and invalid data.
Right now, I wrapped connection.open and command.executescalar in a

try/catch block with connection.close in > the finally statment. Should I
not do that?

Not quite. Shoule be:
Connection.Open
try
command.executeScalar
finally
connection.Dispose
end

Should I move the try/catch block in my business layer wrapped around the

data access layer function call?
No. Try block in your DAL layer is primarily there to grarantee that the
connection is properly closed.

<BTW, what is the difference between connection.close and
connection.dispose - some MS examples have >connection.close while others
have both?

Very little. On objects which have a .close method, it often just invokes
Dispose, or vice versa. The reason it has both is that connections, and
sockets and files traditionally "opened" are cleaned up by being "closed".
To keep this familiar pattern, MS put a .close method. However these are
all also "types requiring cleanup", and MS added IDisposable as a generic
interface for any type requiring manual cleanup, so these types also
implement IDisposable and have a .Dispose method.

Where should I do my data validation? For example, should I throw an

exception if the executescalar returns a >null, that I should catch in the
business layer, or should I have an IIf(retVal.IsDBNull, -1, retVal)
statement?

It depends on the meaning of the NULL result, but if the NULL indicates that
there is bad data or a bad request then the DAL should throw an exception
explaining that, and perhaps encorporating the bad input parameters into the
message. Something like:

if rv is DBNull.Value then
throw new Exception(String.Format("No data found for param1='{0}'
param2='{1}'",param1,param2))
end if

You shouldn't catch the exception in your business layer except if you want
to log it, or add additional context to it before rethrowing it.


David

 

[David Browne]

Thanks for your reply.
I would like to know why I shouldn't put connection.open inside the try

statment.

It shouldn't go inside that try statement since the finally closes the
connection. If the connection fails to open, you shouldn't try to close it.

How am I going to deal with unexpected errors?

Unexpected errors should propagate up the stack until they hit a method
which has the proper context to handle them. In your DAL, what on earth do
you propose doing if Connection.open fails? There's nothing you can do.
That error should be handled way up-stack by a method with the proper
context to decide how to handle a database conneciton failure. For instance
a UI screen might show the user a popup and ask the user what to do. A
console app might show an error and exit, and a middle-tier app might log an
error and propagate the exception back to a client.

- someone else (another .NET beginner) suggested that I wrap the whole function (EVERYTHING, even
Dim statements) inside a try/catch block, thus providing the same "global"

error handling functionality as VB6's >"OnError GoTo errorhandler". That
doesn't look good to me, but shouldn't there be a techinque in .NET that

achieves that?

VB6 error handling sucked. The worst thing about it is that it has posioned
the minds of many VB programmers who got used to it. They insist on putting
error handling code in _every_ method. ARRG.

In VB6 I followed a simple way of calling functions: if the function

failed because of some unexpected error (eg >div/0) then return -1; for
everything else return 0; the return argument(s) would be passed by
reference and would >be used only if the function returned 0.

That's a habit from C. Error handling in C also sucked. There were no
exceptions, no messages, no stack traces, only return values. Structured
exceptions are better, cleaner, easier, and more useful. They clean up your
source code, and free up your methods' return values for useful purposes.

What is the best way (or at least a proven/good way) of doing this in .NET?

Don't catch exceptions except:
--in the outermost calling code, where you log it and then decide what to do
--where the exception will be rethrown as a more informative Exception
or
--you can anticipate the exception and you can do something usefull instead
of giving up (like a retry).

David

Right now, in a simple authentication process I have a bunch of function
calls to the data access layer(DAL), eg:

if getpassword()
if validatepassword()
if getmembershipID()
return new user
end if
end if
end if

I don't think I can afford to throw new exceptions in each DAL function,

and then catch them in the business >layer, because apparently they are very
expensive. But how expensive are they?

Not that expensive. Negligible if a human is involved. Exceptions are for
exceptional cases. They are there to allow you to _assume_ that your method
calls are succeeding. So either:

try
getpassword()
validatepassword()
getmembershipID()
return new user
catch (ex as Exception)
throw new Exception(String.Format("User Login Failed for user
{0}",UserName),ex))
end try

or

getpassword()
validatepassword()
getmembershipID()
return new user

Also, a small question... if executescalar returns an integer, how can I

check if it is null (Nothing)? I guess one >way is to return a string,
compare to Nothing and convert to integer, but that doesn't sound like an
elegant >solution.

ExecuteScalar is a convenience method. But because of issues like this it's
not very convenient. Use ExecuteReader, grab the first row,column and close
the reader. That's all ExecuteScalar does. Better yet, use output
parameters. Result sets are too expensive for returning simple scalar
values. For Oracle something like:

OracleCommand cmd = new OracleCommand("select description into :description
from my_table where id = :id",con)

Then bind on output parameter for description, and an input parameter for
ID, and run ExecuteNonQuery.

David

 

[Guest]

Thanks again. Great answers
TM