Generating Letters on the Web Using ASP NET

Generating Letters on the Web Using ASP. NET, XSLT and Word. ML 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 « 1. « 2. « 3. « 4. 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 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 Word. ML « 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 SQL Server WHERE loosely connected table points Clause to the file system) Bookmark / Tag Search Not Available XML Search 4

2. Architectural Overview Hello World or Hello Word! « « « Step 1 – Just the XML Tags: Hello. World 1. xml Step 2 – Open it with Word: Hello. World 2. xml Step 3 – Make it Updatable: Hello. World 3. xslt (Programming Shell for the Word. ML 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 ---------------User. Id Property. Names Property. Values. String ASPNET_Membership ---------------User. Id Password Email Is Approved Is. Locked. Out Last. Lockout. Date ASPNET_Users ----------User. Id Application. Id ASPNET_Users. In. Roles --------------User. Id Role. Id ASPNET_Roles ----------Role. Id Role. Name Description ASPNET_Applications --------------Application. Id Application. Name 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 – 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 (Lettersweb. config) (Web. Site. Map) 11

3. From Hello Word to a Production System 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 Membership. Adminweb. config) And Finally … H. Add the XML tags of the new letter to the Document. Metadata table, which populates two DDList controls on the Letters Report page. 12

3. From Hello Word to a Production System 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 File. System 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) n Other Documents (using Filestream or other methods) n See also Allen Berezovsky’s blog: n http: //www. harborobjects. com/Allen. Berezovsky/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 n In my RKCM table, over 40, 000 letters have been archived since 2002; n 20

4. New Features in Version 2. 0 Document Storage & Archival Options 2 b. Save documents on file system – without a link on a database table Write your own search / retrieval mechanism n For example, using No. SQL as described in IEEE article; n It’s amazing that despite its pros and cons, it’s still offered as a viable option! n 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; n What are the pros and cons? n 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 n On the file system n Both on the database and the file system n Neither on the database nor on the file system (just re-generate the letter when user is trying to retrieve the archived document) n 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 n Each letter template has a record in Document. Config table n There are two places where Document. Config record is utilized: n • 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 Document. Config table is subsequently changed? n Document. Archive table keeps track of each individual letter’s archival status as of the time it was originally generated. n A change in Document. Config table is NOT RETROACTIVE! n 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; n A multi-lingual letter has more than one language in the same letter; n The VIP system supports both foreign language and multi-lingual letters; n 27

4. New Features in Version 2. 0 Foreign Language & Multi-lingual Letters Some preliminary design questions: How about the user data entry form? n How about values that are entered on the data entry form? n Does the user have a foreign language keyboard? n Are the values going to be copied / pasted from another system which is already in the foreign language? n 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. n Creation of a separate xslt file per language n Data entry form(s) n User selection of desired language n A foreign language keyboard n Code-behind to select the language-specific xslt file accordingly n 29

5. Questions and Answers 30

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

Thank You! 32