Generating Letters on the Web Using ASP.NET, XSLT and WordML Architectural Background & Major Enhancements in Versi

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

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

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 ------------------------------ UserId PropertyNames PropertyValuesString ASPNET_Users -------------------- UserId ApplicationId ASPNET_UsersInRoles ----------------------------- UserId RoleId ASPNET_Roles -------------------- RoleId RoleName Description ASPNET_Membership ------------------------------ UserId Password Email Is Approved IsLockedOut LastLockoutDate 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 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.

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.0Architectural 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);

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 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 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 Berezovsky’s 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; • It’s amazing that despite its pros and cons, it’s 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 letter’s 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.0Foreign 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;

28. 4. New Features in Version 2.0Foreign 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.0Foreign 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

30. 5. Questions and Answers

31. Contact Information • Emails: • president@sql.la • ben@pointercorp.com • Websites: • www.sql.la • www.pointercorp.com • www.vipletters.com • www.takeatest.net

32. Thank You!