Executive Summary:
Data in Microsoft SQL Server 2005 database tables is normally stored unformatted.
Use a C# script and Microsoft Windows Forms to easily store and retrieve Microsoft SQL Server 2005 database data in Rich Text Format (RTF).
To use this solution, you should be familiar with Microsoft Visual Studio 2005 Interactive Development Environment (IDE) and ADO.NET.
|
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, InstantDoc
ID 38572 and "Basics of the .NET Framework: ADO.NET," February 2003, InstantDoc
ID 38254.)
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:
- From the File menu, click New, Project or press Ctrl+Shift+N.
- In the New Project dialog box, select Visual C#, Windows.
- From the Templates list, select Windows Application.
- Name the application; I've called the project WinRichText. Also choose
a location for storing the application files.
- Click OK to finish the process. VS 2005 will create a new 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:
- Two Label controls
- Two RichTextBox controls (one for data entry and other to show data retrieved
from the database)
- Four button controls (Change Color, Change Font, Save to Database, and
Load from Database)
- Two Dialog controls (a FontDialog and a ColorDialog)
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 (http://www.sqlmag.com, InstantDoc ID 95774) 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.
Prev. page
[1]
2
next page 
