|
Hello !
Is there a way to save a large text as compressed in a sql server field ?
Thank you !
|
|
|
|
|
Compress it (zip), save it to a file (or binary stream), read file into a binary stream.
Write the data into a varbinary(MAX) field in the database.
You should understand about FILESTREAM as an alternative to storing the data in the database. You may also want to consider storing the file on a file server and storing only the file name in the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello !
I have a field on sql server database , Varchar(300)
From my vb.net program I want to insert in this field this text :
abc cde ght
1234 5678
567
The text should be saved on database as I wrote ( so in different lines ).
How should I format this text before saving to database ?
Thank you !
|
|
|
|
|
By using a SqlParameter[^] you can easily add multiline data in your DML statement. Use for example AddWithValue[^] method to add the parameter to your command.
Just a side note, your example data looks like you're going to add data into a single column which should be stored in separate columns/rows. But I hope it's just the example data...
|
|
|
|
|
Sorry , maybe you misunderstand.
I want to put all the 3 lines in a single field ( column )
for example :
myobject.mytext= " all the 3 lines that I wrote before"
|
|
|
|
|
satc wrote: I want to put all the 3 lines in a single field ( column )
Yes, and for that you create a single parameter which contains the value.
Consider the following (pseudo-code, not to be compiled)
command AS SqlCommand = new SqlCommand("INSERT INTO MyTable (Col1) VALUES (@colvalue)", connection)
command.Parameters.AddWithValue("@colvalue", "abc cde ght" & vbCrLf & _
"1234 5678" & vbCrLf & _
"567")
command.ExecuteNonQuery()
In the example above, a single row is created and a single value is inserted into column Col1 and it contains multiple lines.
|
|
|
|
|
Sorry , but I don't use pure SQL to send values to sql server.
I'm using Entity Framework , so I need to format those 3 lines in order to do something like this :
Myobject.Mytext=" ......"
I've tried to put vbcrlf at the end of ach line but doesn't work.
|
|
|
|
|
Entity Framework works in exactly the same way. If you've set your property correctly:
Myobject.Mytext = "abc cde ght" & vbCrLf & "1234 5678" & vbCrLf & "567"
then the value in SQL will include the line breaks. If you're not seeing the line breaks, then that's a problem with how the data is displayed.
For example, if you're looking at the value in the grid of query results in SQL Management Studio, the line breaks will be shown as two spaces. If you select the value, copy it, and paste it into a text editor, the line breaks will reappear.
If you're displaying the value in a markup language such as HTML, then you'll need to properly encode the output to see the line breaks. HTML ignores carriage returns, and requires a <br> tag instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I've used exactly the same line as you.
But after if I read this value from database , and put into a bound RichTextEdit the line breaks are missing.
|
|
|
|
|
And which "RichTextEdit" control are you using? If it's an ASP.NET control, then it's almost certainly working with HTML, and you'll need to replace the line breaks with <br> tags.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Yes, but which control???
The built-in control is called RichTextBox. You specifically said your control is a RichTextEdit, which isn't the same thing.
You need to specify which control you're using.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
And what is the Multiline property[^] set to? If you've changed it to False, then you won't see any line breaks.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Two things:
- have you checked from the database? If you query using SSMS, is the value in DB correct?
- if not, what is the data type of your column?
|
|
|
|
|
|
And what about the first question? If you query the value from SSMS, is it correct?
NOTE: you can't see the linefeed in SSMS output so copy the value from the column and paste it for example into notepad to see if it contains linefeed or not.
|
|
|
|
|
does not contain linefeed
|
|
|
|
|
Then I believe the next step is to check the calling side.
After you have set to myobject.text value, using debugger, see if the property myobject.text contains the linefeeds or not. If it does, then they are removed one way or another during the save and if not then the assignment is somehow wrong.
|
|
|
|
|
After setting the myobject.mytext value , using the debugger I see that it contains the linefeeds.
So I think that SQl server doesn't know the vbcrlf maybe ????? !!!
|
|
|
|
|
vbCrLf is a carriage return (ASCII code 13) followed by a line-feed (ASCII code 10). SQL is perfectly capable of storing those characters in a varchar field.
Given what you've told us, there is no reason why the line breaks would disappear. Therefore, either something you've told us is wrong, or there's something else going on that you haven't told us about.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
To rectify; that is not "thinking", that is "guessing" - and it is not helping.
Saving text in a database is a very (!) common practice. As far as SQL Server is concerned in respect to data, the CRLF combinination is "just" as set of characters in a specified encoding.
Write a small console-app that reproduces your problem; that way it should be fairly simple to point out the problem.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Something doesn't match here. Everything should be just fine. So probably there is something you have missed so I would advice going through all the things we have asked once more and re-checking the behaviour.
Also if possible, create as simple test case as possible with minimal amount of code. For example create a new table, just one column, create EF part for it and run a simple insert with a fixed text from the code side and see what happens. I believe this should clarify the situation.
|
|
|
|
|
Have you tried this before speaking ?
Or you are just speaking in theory ?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
