Generating Letters on the Web Using ASP.NET, XSLT and WordML Architectural Background

1
Generating Letters on the WebUsing ASP.NET,
XSLT and WordMLArchitectural 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

2
Agenda

• 1. Introduction and Background
• 2. Architectural Overview
• 3. From Hello Word to a Production System
• 4. New Features in Version 2.0
• Managers Corner
• Four Storage Options for Generated Letters
• Multi-lingual Letters in Foreign Languages
• 5. Questions and Answers

3
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

4
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
5
2. Architectural OverviewHello 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)

6
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

7
3. From Hello Word to a Production SystemGoals
Objectives

• The Architects 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

8
3. From Hello Word to a Production System The
Wheel
9
3. From Hello Word to a Production System The
Wheel

• ASP.NET Membership

ASPNET_Profile ------------------------------ User
Id PropertyNames PropertyValuesString
ASPNET_Users -------------------- UserId Applicati
onId
ASPNET_UsersInRoles -----------------------------
UserId RoleId
ASPNET_Roles -------------------- RoleId RoleName
Description
ASPNET_Membership ------------------------------ U
serId Password Email Is Approved IsLockedOut LastL
ockoutDate
ASPNET_Applications ------------------------------
ApplicationId ApplicationName Description
10
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

11
3. From Hello Word to a Production System
Developing a New Letter

• Developing a New Letter From A to Z
• Receive a Sample Letter and Verify / Create User
  Data Entry Tags enclosed in (.DOC or .DOCX)
• 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?
• Add New Letter to VIP System Menu (Web.SiteMap)
• Enter Authorized ASP.NET Membership Roles allow
  / deny (Letters\web.config)

12
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. 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.

13
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

14
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 Id 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.

15
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

16
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
• Theres no physical deletion deleted records are
  only marked for deletion (with isdeleted1)

17
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

18
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 arent 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 File-System
  as our document repository? What are the Pros and
  Cons of such approach?

19
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 Berezovskys blog
  http//www.harborobjects.com/AllenBerezovsky/post/
  2009/03/04/FILESTREAM-in-SQL-Server-2008.aspx

20
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

21
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
• Its amazing that despite its pros and cons, its
  still offered as a viable option!

22
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?

23
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 Re-generate
  the whole document
• PROS and CONS

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
• 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)

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
• 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

26
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 letters archival status as of the
  time it was originally generated.
• A change in DocumentConfig table is NOT
  RETROACTIVE!

27
4. New Features in Version 2.0 Foreign Language
Multi-lingual Letters

• Lets 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

28
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?

29
4. New Features in Version 2.0 Foreign Language
Multi-lingual Letters

• Based on answers to design questions, well 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

30
5. Questions and Answers
31
Contact Information

• Emails
• president_at_sql.la
• ben_at_pointercorp.com
• Websites
• www.sql.la
• www.pointercorp.com
• www.vipletters.com
• www.takeatest.net

32
Thank You!