Re: Programming practices question
- From: "Nick Malik [Microsoft]" <nickmalik@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 11 Aug 2006 08:06:44 -0700
Step back. Look at the architecture of your data layer. Try to understand
HOW you want your business objects to interact with the database.
There is nothing wrong with combining the 'lookup' and 'insert' operators as
you have. I rather like the idea and have suggested it to other folks many
times. [Aside: Keep in mind that you want to make sure that you have
'scrubbed' that e-mail field (forced to lower case, removed leading and
trailing blanks) to keep your comparison clean. SQL can ignore case on
compare, but scrub the data anyway, so that later on, you can do the compare
at any layer if you need to move it.]
In your model, you have a business layer that 'knows' about a subscriber.
We suspect that they are new but are not sure. From it's standpoint, it
wants to save that data and have the right id to refer to it later. I
assume the BL also wants to know the activation status.
So the interaction goes like this:
BL: Hey, db layer. I have a subscriber.
DL: Cool. Here's his ID and his status
BL: thanks.
There is no reason to have the business layer interpret three different
possibilities.
Simply set up your stored proc to do this:
1) If the subscriber already exists, return the subscriber's ID and status.
Use newly provided data to update old record.
2) If the subscriber doesn't exist, add them with a status of 'unactivated'.
Return the subscribers ID and status.
Now, there are no codes. The business layer knows that the data is
persisted, and it knows the ID to use to find it, and it knows the status of
the subscriber.
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
"Maciek" <maciek@xxxxxxxxxxxxxxxx> wrote in message
news:1155282261.795480.240320@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I've got this question regarding programming and design practices. I'm
designing Newsletter module for my WebApp and I'm greenhorn in
programming.
There's a stored procedure which adds a subscriber to a DB. It outputs
subscriberID (uniqueidetifier) if it succeeds to add them to the
database and return value of "0". When the subscriber already exists
but hasn't activated/confirmed their account it returns null for
subscriberID and return value of "1". If the subscriber exists and is
fully activated it returns null for subscriberID and return value of
"2". Here is the SP code:
create procedure Newsletter_PendingSubscriberAdd
@email varchar(255),
@name varchar(255) = null,
@company varchar(255)= null,
@subscriberID uniqueidentifier output
as
set nocount on
if exists (select email from Newsletter_Subscribers where email =
@email) return 2
if exists (select email from Newsletter_PendingSubscribers where email
= @email) return 1
select @subscriberID = newid()
declare @err int
insert into Newsletter_PendingSubscribers (subscriberid, email, [name],
company) values (@subscriberID, @email, @name, @company)
select @err = @@error if @err <> 0 return @err
return 0
set nocount off
GO
Now in my data layer in my App I'm trying to write method to add the
subscriber to the db. My problem is how and where to react on different
values returned from sql. The method is accessing the procedure and now
I check the Return Value. If it's 0 the method returns subscriberID and
if it's not -- it returns the Return Value.
Public Function Add(ByVal Email As String, ByVal Name As String,
ByVal Company As String)
Dim rowsAffected As Integer
Dim result As Integer
Dim parameters As SqlParameter() = { _
New SqlParameter("@email", SqlDbType.VarChar, 255), _
New SqlParameter("@name", SqlDbType.VarChar, 255), _
New SqlParameter("@company", SqlDbType.VarChar, 255), _
New SqlParameter("@subscriberID", SqlDbType.UniqueIdentifier)}
parameters(0).Value = IIf(Len(Trim(Email)) = 0, DBNull.Value,
Email)
parameters(1).Value = IIf(Len(Trim(Name)) = 0, DBNull.Value,
Name)
parameters(2).Value = IIf(Len(Trim(Company)) = 0, DBNull.Value,
Company)
parameters(3).Direction = ParameterDirection.Output
' I am using this DBObject class for accessing data:
http://www.devx.com/vb2themax/Tip/19480
result = RunProcedure("Newsletter_PendingSubscriberAdd",
parameters, rowsAffected)
If result = 0 Then
Return CStr(parameters(3).Value)
Else
Return CInt(result)
End If
End Function
But this is, I think, bad design. Mainly because this method may return
two different kinds of data types: string for uniqueidetifier
(subscriberID) and Integer for RetVal. So I would need to propagate up
the App layers unspecified (until runtime) data type. This can cause
many problems I think. The other approach I can think of would be
returning subscriberID when subscriber was not in db and throwing and
propagating an Exception if they're already in DB:
If result = 0 Then
Return CStr(parameters(3).Value)
Else
Throw Ex("My custom exception")
End If
But I've read somewhere that we shouldn't fool around with exceptions
if the result (from DB in this case) was EXPECTED. And this is expected
behaviour. The exceptions are for unexpected situations, I think. Hey,
but what do I know, I'm a rookie! Is there the third (right) way of
doing this? Where do I make my design mistakes? Any advice appreciated!
Thanks
Best regards
Maciek
.
- Follow-Ups:
- Re: Programming practices question
- From: Maciek
- Re: Programming practices question
- References:
- Programming practices question
- From: Maciek
- Programming practices question
- Prev by Date: How to: issue a shutdown/restart?
- Next by Date: Re: How to: issue a shutdown/restart?
- Previous by thread: Re: Programming practices question
- Next by thread: Re: Programming practices question
- Index(es):
Relevant Pages
|
