Store RTF Data in a SQL Server Database

How do you prefer your email message-text format? Plain and simple or jazzy and HTML coded? If you're like me, you prefer messages with colors and fonts used to highlight the content. SQL Server 2005 offers a way to store Rich Text Format (RTF) data—for example, a notification form that's emailed to customers—directly in a SQL Server database, so that you can retrieve the formatted data (including both text and graphics) later and use it in various applications. Just as you can store "plain" data in a varchar or nvarchar data type column in a SQL Server database table, by using coding techniques you can store the same data with its attributes, such as color and font.

I'll show you how to collect RTF data from users by using the Windows Forms RichTextBox control and save it to a SQL Server 2005 database. To use this method, you should be comfortable with writing code in C# and have a basic understanding of the Microsoft Visual Studio (VS) 2005 IDE, ADO.NET, and SQL statements. (To learn more about ADO.NET and Windows Forms, see the Windows IT Pro Web-exclusive articles "Basics of the .NET Framework: Windows Forms," March 2003 and "Basics of the .NET Framework: ADO.NET," February 2003.)

Getting Started

To demonstrate my method, I'll use a simple example: a standard Windows form that has a RichTextBox control on it. The RichTextBox control is similar to the Windows TextBox control but also enables a text box to display and allow entry and manipulation of formatted text (e.g., fonts, colors, links, images). I'll change the font and font color of the sentence, "The quick brown fox jumps over the lazy dog" (the well-known sentence for practicing keyboarding, which contains all the letters in the English alphabet), so it looks like the text in Figure 1.

Step 1: Create a Windows Application Project

If VS 2005 IDE isn't already started, you can launch the IDE in Windows (e.g., click Start, Programs, Microsoft Visual Studio 2005 and click the Microsoft Visual Studio 2005 icon). Follow these steps to create a Windows application project:

You should now see a blank form called Form1.

Step 2: Set the Form's Properties and Add Controls

Set the properties of Form1 by using the values that Table 1 shows. If the Properties window isn't visible, press F4 to display it. Make sure that you select Form1 before applying changes to properties via the Properties window.

Next, you'll add controls to the newly created Form1. As you can see in Figure 1, you'll use one RichTextBox control to enter and format text. You have two buttons, Font and Color, for formatting the text and two buttons, Save and Load, for interacting with database. You'll need to add the following controls to Form1:

You can add controls to Form1 in two ways. You can double-click the control, or you can drag and drop the needed control onto the form. I prefer the drag-and-drop method because it lets you drop the control exactly on your chosen location. Your Form1 should look similar to the example that Figure 2 shows.

Next, change the Form1 control properties' default settings by using the values listed in Table 2. You can change properties by selecting the control and using the Properties window. For example, the button1 text property should be Font instead of the default button1.

Step 3: Add Database Access to the Form

You don't have to be a database guru to add database access to a Windows form. All you need to do is create a SQL Server table that has an nvarchar column. For this example, I'll add a new table called tblRichTextData to the AdventureWorks sample database. To do so, run the C# code in Web Listing 1 to create the table and add a dummy row, which you'll update later when you run the sample project. (To download the listing, click the Download the Code button at the beginning of the online article.)

The rationale behind using nvarchar and varchar data types to store RTF data is the variable length. As you can see, you'd need to store 43 characters if you store text with no formatting. Storing the same text as RTF data requires 274 characters—almost six times more space than normal text. If you expect that you'll store a large amount of RTF data, it's best to use nvarchar(max) or varchar(max) to avoid running short on space.

The code in the button1_Click event uses the Font Dialog control and the richTextBox1 control to change the font of selected text in the text box. The button2_ Click event performs a similar task to the button1_click event: It uses the ColorDialog control to change the color of the text in the text box.

The button3_Click event performs a simple databasesave routine. This code uses the RichTextBox control's Rtf property to convert the unformatted text to RTF text, then passes it to the ADO.NET command object for update into the database. Because the table has only one row, the UPDATE statement doesn't need a WHERE clause.

The button4_Click event loads the RTF data that was saved to the database (after the color and font were changed) into the text box. This code again uses the Rtf property, this time associated with richTextBox2, to display the retrieved data with formatting intact. The code uses a SELECT TOP 1 statement to load the first row from the table.

Step 4: build and run the Project

Your final step is to build and then run the project. You can build a project either by clicking the green Play button on the main toolbox or pressing F5 to start the application in runtime mode. After you've built the application, you should see a dialog box similar to that in Figure 1 containing the unformatted sentence "The quick brown

fox jumps over the lazy dog" in the Enter Rich Format Text box. Let's test the application by formatting the text, saving it to the database, and retrieving the formatted text from the database. To do so, perform these steps:

As you can see, when you run the application for first time, the retrieved data isn't formatted and the Text from Database text box is empty. After you perform the previous steps, the result in both textboxes will look same.

A Handy Tool

Collecting RTF data from a user and storing it in a SQL Server 2005 database table is a simple process, with the help of my C# script. The ability to store and retrieve formatted data will save you and your end users time, for example, by avoiding the need to reformat database data that's merged into a form.