Field Name Data Type Description
id Int The unique identifier for this record.
filename varchar(300) The question ID to which this response applies. fromContactID Int The ID of the contact record that sent the file. toContactID Int The ID of the contact record that received the file. message Varchar(1000) The message that the sender provided with the file
being uploaded.
datesent Datetime The datetime stamp at the time the file is uploaded. In addition to these three tables, a number of stored procedures are in use. They follow a consistent nam- ing pattern with the other chapters, as shown here:
❑ sprocTableNameSelectList ❑ sprocTableNameSelectSingleItem ❑ sprocTableNameInsertUpdateItem
In such fashion, the following stored procedures are used in the application:
❑ sprocEmailInsertUpdateItem ❑ sprocEmailSelectSingleItem ❑ sprocResourceInsertUpdateItem ❑ sprocResourceSelectSingleItem
The naming convention allows you to easily and quickly find the stored procedures that apply to a spe- cific table, and whether they are selects, inserts, updates, or deletes.
There are a few stored procedures that you need to walk through. The first stored procedure, sproc ResourceSelectSingleItem, is a basic SELECTstatement based on the IDparameter, which selects a single resource record from the database and returns it to the caller:
ALTER PROCEDURE dbo.sprocResourceSelectSingleItem
/* ‘=============================================================== ‘ NAME: sprocResourceSelectSingleItem
‘ DATE CREATED: October 19, 2005
‘ CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ‘ CREATED FOR: ASP.NET 2.0 - Instant Results
‘ FUNCTION: Gets a specific resource from the DB ‘===============================================================
*/ (@id int)
as
select * from Resource where id = @id
The preceding stored procedure is called from the ResourceDB.vb data layer, in the GetResourceFileNamefunction.
In similar fashion, the next stored procedure, sprocEmailSelectSingleItem, is used to select a single record from the Email table. There is no IDparameter in this one, because it assumes you will be storing only one record in this table for now. If you choose to add different e-mail versions or types in the system at a later time, this is the place to manage that information:
ALTER PROCEDURE dbo.sprocEmailSelectSingleItem
/* ‘=============================================================== ‘ NAME: sprocEmailSelectSingleItem
‘ DATE CREATED: October 19, 2005
‘ CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ‘ CREATED FOR: ASP.NET 2.0 - Instant Results
‘ FUNCTION: Gets the html and text message body from the DB ‘=============================================================== */
as
select top 1 * from Email
Moving into the other two stored procedures, the level of complexity increases slightly. The following is the next stored procedure, sprocEmailInsertUpdateItem, which is used to update the e-mail text in the Email table’s one record. It accepts one parameter, @text, which is simply the text content of the template e-mail that is used to send e-mails to recipients:
ALTER PROCEDURE dbo.sprocEmailInsertUpdateItem
/* ‘=============================================================== ‘ NAME: sprocEmailInsertUpdateItem
‘ DATE CREATED: October 21, 2005
‘ CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ‘ CREATED FOR: ASP.NET 2.0 - Instant Results
‘ FUNCTION: Inserts or Updates the email content to the DB ‘=============================================================== */ (@text varchar(MAX)) AS UPDATE Email SET [text] = @text
The final stored procedure, sprocResourceInsertUpdateItem, is by far the most complex one, but not to worry. The basic idea of it is actually quite simple:
ALTER PROCEDURE dbo.sprocResourceInsertUpdateItem
/* ‘=============================================================== ‘ NAME: sprocResourceInsertUpdateItem
‘ DATE CREATED: October 19, 2005
‘ CREATED BY: Shawn Livermore (shawnlivermore.blogspot.com) ‘ CREATED FOR: ASP.NET 2.0 - Instant Results
‘ FUNCTION: Inserts or Updates a resource into the DB ‘=============================================================== */ (@id int, @filename varchar(300), @fromContactEmail varchar(300), @toContactEmail varchar(300), @message varchar(1000)) AS
DECLARE @returnValue int Declare @fromContactID int Declare @toContactID int
/*
--- fromContactID --- */
--insert the contact records if they do not already exist... if((select count(*) from contact where email = @fromContactEmail)=0)
begin
insert into contact (email) values (@fromContactEmail)
SET @fromContactID = SCOPE_IDENTITY() --extract the contact id from the insert
end else
begin
--extract the contact id from the insert
SET @fromContactID = (select id from contact where email = @fromContactEmail) end
/*
--- toContactID --- */
if((select count(*) from contact where email = @toContactEmail)=0) begin
insert into contact (email) values (@toContactEmail)
SET @toContactID = SCOPE_IDENTITY() --extract the contact id from the insert end
else begin
--extract the contact id from the insert
SET @toContactID = (select id from contact where email = @toContactEmail) end
IF (@id IS NULL) BEGIN INSERT INTO Resource ( filename, fromContactID, toContactID, message ) VALUES ( @filename, @fromContactID, @toContactID, @message )
SET @returnValue = SCOPE_IDENTITY() END ELSE BEGIN UPDATE Resource SET filename = @filename, fromContactID = @fromContactID, toContactID = @toContactID, message = @message WHERE Id = @id
SET @returnValue = @id END
select @returnValue
This procedure is used to insert the resource information into the database, add new contacts to the Contact table, and resources to the Resource table. It uses the upsert methodology, wherein it will pro- vide an update if the record already exists, or an insert if it does not.
sprocResourceInsertUpdateItemfollows these specific steps:
1.
Checks to see if the e-mail address of the sender (@fromContactEmail) is not already in the system:/*
--- fromContactID --- */
--insert the contact records if they do not already exist... if((select count(*) from contact where email = @fromContactEmail)=0)
2.
If not, the stored procedure adds the e-mail address as new contact record, extracting the unique ID value to set the the @fromContactIDlocally declared variable for later insertion into the Resource table. If the record does exist, it performs a selectstatement to populate @fromContactID:begin
insert into contact (email) values (@fromContactEmail)
SET @fromContactID = SCOPE_IDENTITY() --we extracted the contact id from the insert end
else begin
--extract the contact id from the insert SET @fromContactID =
(select id from contact
where email = @fromContactEmail) end
* The next section of the stored procedure does the exact same thing, except this time it is with the @toContactEmailparameter, populating the @toContactIDvariable.
3.
After you have valid ContactIDs, you can focus on the insertion of the resource record into the database. The following section is used to insert a new resource record into the Resource table, returning the new ID of the resource into the @resourceIDvariable:-- Insert a new resource record IF (@id IS NULL) BEGIN INSERT INTO Resource ( filename, fromContactID, toContactID, message ) VALUES ( @filename, @fromContactID, @toContactID, @message )
SET @returnValue = SCOPE_IDENTITY() END
4.
The following elsestatement immediately follows this ifclause, with the case in which the @idparameter is not null. This would be the case if the application passed an ID to the stored procedure, indicating that a resource record already existed, and the stored procedure is expected to perform an update, instead of an insert:ELSE BEGIN UPDATE Resource SET filename = @filename, fromContactID = @fromContactID,
toContactID = @toContactID, message = @message
WHERE Id = @id
SET @returnValue = @id END
select @returnValue
❑ The preceding code performs the UPDATEquery, and returns the resulting @resourceID variable. Once the @resourceIDvariable is sent back to the caller (the data layer), the process for inserting a resource into the system is complete.
These are the stored procedures used within the Wrox File Share, and are entirely common for this type of application.