Programming practices question

[Maciek]

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

[Infinity]

Hi Maciek,

I think it's better to return 2 outputs in this case
@status which indicate 0,1,2

if @status=0 then get @subscriberID
else it's an error

Regards,
Infinity
Maciek wrote:

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

[Nick Malik [Microsoft]]

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" <ma****@kolobrzeg.com.plwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

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

[Maciek]

Thank you Nick for your valuable remarks. Now I think I get it.

One more question. I was wondering if you know of any books/articles
focusing on designing multi tier apps with some advices, good practices
and explanations what the layers should or shouldn't be. How to convert
our concepts into well designed layers...
I know the knowledge comes with the experience but there must be some
rules of thumb, some DOs and DON'Ts which would help the beginner to
sail safely through the multi-tier app desing reefs ;-)
Anyway if you could point out something, I would be very greatfull.

Once again thanks!
Regards

Maciek

Nick Malik [Microsoft] napisal(a):

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" <ma****@kolobrzeg.com.plwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

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

[Nick Malik [Microsoft]]

Hello Maciek,

I want to be helpful. I really do. Unfortuately, all I can give you are
patterns books because that's what I learned from myself. Start with Design
Patterns by Gamma, Helm, Johnson, and Vlissides and Design Patterns
Explained by Shalloway. Once you have attempted that, you can pretty much
figure this out all by yourself.

Don't forget to check out the Patterns and Practices site on Microsoft.com

--
--- 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" <ma****@kolobrzeg.com.plwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

Thank you Nick for your valuable remarks. Now I think I get it.

One more question. I was wondering if you know of any books/articles
focusing on designing multi tier apps with some advices, good practices
and explanations what the layers should or shouldn't be. How to convert
our concepts into well designed layers...
I know the knowledge comes with the experience but there must be some
rules of thumb, some DOs and DON'Ts which would help the beginner to
sail safely through the multi-tier app desing reefs ;-)
Anyway if you could point out something, I would be very greatfull.

Once again thanks!
Regards

Maciek

Nick Malik [Microsoft] napisal(a):

>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" <ma****@kolobrzeg.com.plwrote in message
news:11**********************@i42g2000cwa.googleg roups.com...

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