Transcript Architectural Background & Major

Generating Letters on the Web
Using ASP.NET, XSLT and WordML
Architectural Background & Major Enhancements in
Version 2.0
By: Ben Aminnia
President, L.A. SQL Server Professionals Group www.sql.la
Database Architect, Pointer Corporation www.pointercorp.com
Agenda
Introduction and Background
Architectural Overview
From Hello Word to a Production System
New Features in Version 2.0
 Managers Corner
 Four Storage Options for Generated Letters
 Multi-lingual Letters in Foreign Languages
5. Questions and Answers
1.
2.
3.
4.
2
1. Introduction & Background
Where it all started:

My Presentation at SQL Server 2005 Launch
Event – December 2005

Missing Piece of the Puzzle
Opportunity Knocks!

In Office 2003, Word becomes WordML

Web-based System to Generate, Archive, and
Retrieve Template Letters

Store Generated Letters in an XML Column of a
SQL Server Table
3
2. Architectural Overview
The Old Method
The New Method
Letter Template
Development
Word Document with
Bookmarks
XML Document
with Tags
Composition
OLE Automation to Create a
Word Document and Replace
Bookmarks with Runtime
Values
XML Document and
Replace Tags with
Runtime Values
Storage
Word Document on the File
System
XML Column in a
SQL Server Table
Content Search
Windows Search through the
File System
Full-text Index in
SQL Server
Attribute Search
Not Available (unless a
loosely connected table
points to the file system)
SQL Server WHERE
Clause
Bookmark / Tag
Search
Not Available
XML Search
4
2. Architectural Overview
Hello World or Hello Word!



Step 1 – Just the XML Tags: HelloWorld1.xml
Step 2 – Open it with Word: HelloWorld2.xml
Step 3 – Make it Updatable: HelloWorld3.xslt
(Programming Shell for the WordML Document)
5
3. From Hello Word to a
Production System







The Site: www.vipletters.com
Login
Role-Based View / Scope
Site-Based View / Scope
Generate a Letter
Reports
Others pieces
6
3. From Hello Word to a Production System
Goals & Objectives
The Architect’s Perspective:

Web-based Intranet (fewer than 1,000 users)

Role-based (not everyone can see everything)

A General Web Site for All Public Users vs.
A Specific Web Site for Each Client (with
specific requirement that may not be applicable
to the general site)

And the most important of all …
7
3. From Hello Word to a Production System
The Wheel
8
3. From Hello Word to a Production System
The Wheel
ASP.NET Membership:
ASPNET_Profile
-----------------------------UserId
PropertyNames
PropertyValuesString
ASPNET_Membership
-----------------------------UserId
Password
Email
Is Approved
IsLockedOut
LastLockoutDate
ASPNET_Users
-------------------UserId
ApplicationId
ASPNET_UsersInRoles
----------------------------UserId
RoleId
ASPNET_Roles
-------------------RoleId
RoleName
Description
ASPNET_Applications
----------------------------ApplicationId
ApplicationName
Description
9
3. From Hello Word to a Production System
Other Features and
Requirements





Allow DEV to see more stuff (e.g. connection
strings)
Show / Hide Certain Objects to / from One
Client vs. the Public Site
Connect to DEV / Public / Client DBs
Updateable WEB.CONFIG
Setup requirements by the Web Host
10
3. From Hello Word to a Production System
Developing a New Letter
Developing a New Letter – From A to Z
A.
Receive a Sample Letter and Verify / Create
User Data Entry Tags – enclosed in [] (.DOC or
.DOCX)
B.
C.
Create VIP Template Files (.XML and .XSLT)
Create Data Entry Form and the [Generate]
Button on the Form (.ASPX and .ASPX.CS)
How about the Replicate function?
What if there are 125 data entry fields?
D.
Add New Letter to VIP System Menu
E.
Enter Authorized ASP.NET Membership Roles –
allow / deny (Letters\web.config)
(Web.SiteMap)
11
3. From Hello Word to a Production System
Developing a New Letter
Developing a New Letter – From A to Z (Continued)
If a new membership role needs to be created:
F.
G.
Create the new role in ASP.NET Membership
Add menu access for the new role – allow /
deny (web.config and MembershipAdmin\web.config)
And Finally …
H.
Add the XML tags of the new letter to the
DocumentMetadata table, which populates two
DDList controls on the Letters Report page.
12
3. From Hello Word to a Production System
Developing a New Letter
Developing a New Letter – From A to Z (Continued)
Some Additional Tricks:
•
•
•
Letters with / without a Company Logo
Letters with / without a Signature
Adding a Unique ID to the bottom of each
generated letter
13
3. From Hello Word to a Production System
Developing Reports and Charts
•
•
•
•
Many detailed areas can be covered here, which
are applicable to developing reports and charts
FOR ANY APPLICATION (not just VIP Letters)
I have created separate presentations for
Developing Reports and Charts
One thing that I’d like to emphasize here …
Since we are storing letters and their metadata
in XML columns, you need to be fairly familiar
with XML column retrieval techniques.
14
4. New Features in Version 2.0
Managers Corner
Similar to Reports and Charts, Managers Corner
is also part of enhancements for managing the
website, which is applicable to administration of
ANY APPLICATION (not just VIP Letters)
The Challenge: Coordinating creation of new
users among three people:
•
•
•
•
•
•
Manager: Identify new users and their system roles
New User: Specify Password , Security Question and
Answer
Administrator: Help them out as needed
Solution: A simple form which is email enabled
15
4. New Features in Version 2.0
Architectural Challenge for the DBA



Each record is about 100 KB large;
So it takes ONLY about ten thousand records to
reach one GB in DB size;
There’s no physical deletion; deleted records
are only marked for deletion (with
[isdeleted]=1);
16
4. New Features in Version 2.0
Reasons for Multiple Archival Options





Increasing cost of storage / hardware
Performance Degradation / Response Time
Legal and Regulatory Requirements
Search and Retrieval Techniques
Managers’ Concerns
17
4. New Features in Version 2.0
What is the management asking now?





So where are you storing the generated letters?
INSIDE THE DATABASE?
Is it a good idea to have SQL Server as our
document repository medium?
Some decision makers aren’t used to this, so
they may ask: “Can we have a file-system
repository instead?”
Is the answer yes or no?
How about using BOTH SQL Server and FileSystem as our document repository? What are
the Pros and Cons of such approach?
18
4. New Features in Version 2.0
Document Storage & Archival Options
1. Save documents in a database table



Word Documents (VIP System utilizing XML
/ XSLT)
Other Documents (using Filestream or other
methods)
See also Allen Berezovsky’s blog:
http://www.harborobjects.com/AllenBerezovsky/post/2009
/03/04/FILESTREAM-in-SQL-Server-2008.aspx
19
4. New Features in Version 2.0
Document Storage & Archival Options
2. Save documents on file system and
create a link in a database table


Historically, this used to be the
recommended way to archive documents
In my RKCM table, over 40,000 letters have
been archived since 2002;
20
4. New Features in Version 2.0
Document Storage & Archival Options
2b. Save documents on file system –
without a link on a database table



Write your own search / retrieval
mechanism
For example, using NoSQL as described in
IEEE article;
It’s amazing that despite its pros and cons,
it’s still offered as a viable option!
21
4. New Features in Version 2.0
Document Storage & Archival Options
3. Save documents both on file system
and in a database table


One prospect has actually asked about this
for an upcoming project;
What are the pros and cons?
22
4. New Features in Version 2.0
Document Storage & Archival Options
4. Do NOT save documents anywhere
(neither on the file system nor on the
database)
• Think about it: What does this mean?
• Just save the original parameters which
were used to generate the document
• When “retrieval” is needed, just “Regenerate” the whole document
• PROS and CONS
23
4. New Features in Version 2.0
Document Storage & Archival Options
In the VIP System – Version 2.0, we now
have all four archival / storage options:




On the database
On the file system
Both on the database and the file system
Neither on the database nor on the file
system (just re-generate the letter when
user is trying to retrieve the archived
document)
24
4. New Features in Version 2.0
Document Storage & Archival Options
In the VIP System – Version 2.0, we now
have all four archival / storage options:



Configuration of archival option is per letter
template
Each letter template has a record in
DocumentConfig table
There are two places where DocumentConfig
record is utilized:
• When [Generate] is clicked to create a letter
• When Select is clicked to retrieve a letter
25
4. New Features in Version 2.0
Document Storage & Archival Options
In the VIP System – Version 2.0, we now
have all four archival / storage options:



BUT WAIT! What if the record in
DocumentConfig table is subsequently
changed?
DocumentArchive table keeps track of each
individual letter’s archival status as of the
time it was originally generated.
A change in DocumentConfig table is NOT
RETROACTIVE!
26
4. New Features in Version 2.0
Foreign Language & Multi-lingual Letters
Let’s clarify the terminology here:



A letter in a foreign language is entirely in
one (non-English) language;
A multi-lingual letter has more than one
language in the same letter;
The VIP system supports both foreign
language and multi-lingual letters;
27
4. New Features in Version 2.0
Foreign Language & Multi-lingual Letters
Some preliminary design questions:




How about the user data entry form?
How about values that are entered on the
data entry form?
Does the user have a foreign language
keyboard?
Are the values going to be copied / pasted
from another system which is already in the
foreign language?
28
4. New Features in Version 2.0
Foreign Language & Multi-lingual Letters
Based on answers to design questions,
we’ll need one or more of the following:






Letter template in Word, saved as XML, etc.
Creation of a separate xslt file per language
Data entry form(s)
User selection of desired language
A foreign language keyboard
Code-behind to select the language-specific
xslt file accordingly
29
5. Questions and Answers
30
Contact Information
•
Emails:


•
[email protected]
[email protected]
Websites:

www.sql.la

www.pointercorp.com

www.vipletters.com

www.takeatest.net
31
Thank You!
32