ansaurus

Question

Save HTML (as is) to SQL 2005 table

Answer 1

A:

Don't get caught out using the "text" data type for this data. It's going to be removed from SQL server and you don't want to cause yourself a headache later on.

Store the data in a "varchar(max)" data type.

As far as making it as small as possible, stripping out unnecessary white space may help to reduce the size of the data.

Sohnee 2009-06-30 07:07:57

I use nvarchar(3000), is there a difference in the saving output by using varchar ?

pixel3cs 2009-06-30 07:18:00

Using nvarchar is great if you require double-byte characters (so if you are going to internationalise yourself!) It does take up twice the space (and this will affect your indexes, joins etc. If you will never need to use internationalised characters use varchar.

Sohnee 2009-07-01 06:54:36

Answer 2

A:

Hi,

You can do the Server.HtmlEncode and save it in a varchar field in the database (SQL 2005). When you extarct it you can do a Server.HtmlDecode and load it into a label/literal and it will rendered normally.

Bhaskar 2009-06-30 07:24:15

Mandatory, no HtmlEncode. No >

pixel3cs 2009-06-30 07:28:44

Answer 3

+1 A:

A direct insert of an arbitrary varchar string should always work:

create table MarkupTable(
  id int identity(1,1) /*please don't berate me for using identity!*/
, markup varchar(max) /* this will use only the space taken by your markup string */
)

insert MarkupTable select '<html><b>demo</b></html>'

select * from MarkupTable

Now - the question is: what piece of code is encoding your markup???

Some starting places:

Are you using C# objects properly: SqlParameter(SqlDbType.VarChar).Value = yourMarkupString;
Are you using FOR XML anywhere?
Are you using anything like Security.Escape(markup)?
Is your string being returned as part of an XmlDocument or SOAP envelope? (it WILL be escaped by the serializer)
Are you getting a doubly encoded string?

Try:

Server.HtmlDecode(Server.HtmlDecode(markupString));

For space requirements:

You could run the string through a whitespace stripping algorithm (anything becomes a single space or tab.
You could compress the text and not even store varchar(max), but varbinary(max)

Jeff Meatball Yang 2009-06-30 07:30:40

Good answer. Given your example, I think you are right, my HTML is encoded by something else, not SQL itselfs. Debugging my code I found that HTML is sended encoded to my StoredProcedure. Server.HtmlDecode won't entirely decode my HTML.

pixel3cs 2009-06-30 07:34:53

try calling it twice.

Jeff Meatball Yang 2009-06-30 07:38:55

ansaurus

tags:

views:

answers:

Save HTML (as is) to SQL 2005 table

related questions