How to Best store this data

Question

How to Best store this data

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
August 20, 2012 at 6:06 pm

#280032

Hello Everyone
I am working on one of my own .NET websites that stores data in SQL 2008 database.
I would like to store the data to be able keep the paragraphs that are typed in from the front-end.
For example:
blah, blah, blah, this really has nothing to do with the data itself, but more of the formatting.
Now to view this data on a website, to keep all the original formatting, one would do something like this. Or one could do this........
I want to keep that paragraph correct when I store that data in a column with a Varchar(max) data type.
Thank you in advance for all your help.
Andrew SQLDBA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Have you checked on the many posting here on SSC for keeping XML data ? Suggest that it might be worth your while to check those and then make a decision as to how you are going to store your data and retain its original format.

Scroll up to the top of this page and in the right hand corner in the box with the word "search" simply type in XML format and click on the GO button.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 2

.NET has the ability to capture and pass the HTML to a sproc and you can store it as varchar(max) like you said. I did this on a few customer web pages I built for users that wanted text to appear exactly as they typed it, including < br > and < p > tags.

Mark

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

And let me suggest you one tip:

Store duplicate of your data without any formatting... You should get unformatted text when you save the data from UI, as it's easy in .NET.

It does happen quite often, that very soon you will be asked if this data can be included into report or some thing else, removing HTML or other formatting tags from saved text - will be much harder task...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sandeep rawat Ten Centuries Points: 1317 More actions · Answer 4

Plz read

http://www.sqlmag.com/article/c3/store-rtf-data-in-a-sql-server-database

For SQL Server 2005 and up, use VARCHAR(MAX) / NVARCHAR(MAX) if you're dealing with pure text files (like source code or CSV files), or VARBINARY(MAX) if you're dealing with binary files.

Those allow up to 2 GB of storage for each single file, and you can use all the usual T-SQL string functions on them to manipulate them (the (N)VARCHAR(MAX) fields, that is).

If you're using SQL Server 2008, there's also an additional option - the FILESTREAM attribute on VARBINARY(MAX) columns. This allows you to store the files in the SQL Server machine's file system (instead of the database tables) while preserving transactional and data integrity.

FILESTREAM is recommended for files that are typically and usually larger than 1 MB in size, or if you ever need more than 2 GB (since you can't store more than 2 GB in a regular VARBINARY(MAX)

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 5

Is there anything Special that I need to do within my .NET code to force the issue of keeping the tags in place? Or by default, will they be there? I am using C# as my OOP language.

I could probably use a smaller varchar(2000) data type, I doubt if there will ever be anything large stored. It is only the steps of a recipe.

Thanks

Andrew SQLDBA

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

AndrewSQLDBA (8/21/2012)
Is there anything Special that I need to do within my .NET code to force the issue of keeping the tags in place? Or by default, will they be there? I am using C# as my OOP language.
I could probably use a smaller varchar(2000) data type, I doubt if there will ever be anything large stored. It is only the steps of a recipe.
Thanks
Andrew SQLDBA

It's depends of what control you are using.

All RTF controls should be able to return formatted and unformatted text.

You may consider to use NVARCHAR datatype if your text will contain some unicode characters...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 7

You can get by storing the data once and removing the formatting for reporting purposes if needed.

I used this free tool to build a wysiwyg kind of editor within a web page:

http://www.freetextbox.com/

Look up htmlencode and htmlencode and that should help.

Mark