Saving ParameterValue Object To SQL Table

Greetings,
I am trying to store/retrieve given RS report's ParameterValue object to a
SQL table (ntext?)â?¦Iâ've tried MemoryStream w/ BinaryFormatter and am getting
â'Not marked as serializableâ' errors. Any suggestions on a viable way to do
this would be appreciated.
Thank you for your time!
JohnI solved my problem using the XmlSerializer object as followingsâ?¦
Public Shared Function Deserialize(ByVal bytes() As Byte) As Object
Dim xs As New XmlSerializer(GetType(ParameterValue()))
Dim ms As New MemoryStream(bytes)
Dim obj As Object
Try
obj = xs.Deserialize(ms)
Catch ex As Exception
Throw ex
End Try
Return obj
End Function
Public Shared Function Serialize(ByVal Obj() As Object) As Byte()
Dim xs As New XmlSerializer(GetType(ParameterValue()))
Dim Ms As New MemoryStream
Try
xs.Serialize(Ms, Obj)
Catch ex As Exception
Throw ex
End Try
Return Ms.ToArray
End Function
Dim Rp() as ParameterValue
â'Store parameter object
DataRow(â'MyBinaryFieldâ') = Serialize(Rp())
â'Get Stored Parameter object
Rp = Deserialize(DataRow(â'MyBinaryFieldâ'))
"JB" wrote:
> Greetings,
> I am trying to store/retrieve given RS report's ParameterValue object to a
> SQL table (ntext?)â?¦Iâ've tried MemoryStream w/ BinaryFormatter and am getting
> â'Not marked as serializableâ' errors. Any suggestions on a viable way to do
> this would be appreciated.
> Thank you for your time!
> John
>sql

Saving Images in SQL Servers Image Datatype

I would like to store images in and retrieve images from SQL Server. And the images are to be stored and retrieved from ASP. How do I do it?Hi..

Check out in this web site

http://www.sqlteam.com/item.asp?ItemID=986

Regards,
Selva Balaji B.sql

Saving files to SQL Server 2000

Someone:
I have the need to upload a file via a webpage and then save that file into the database. I would also like to retrieve it and show it to the user.

Can someone show me an example of how this should be done. Also I am concerned of the pros and cons of saving files in the database. Is there a performance hit on the server? Will it make the database unstable? Has anyone had problems doing this?

Any suggestions, samples, and/or help are welcome.What kind of files are you attempting to save to the database, images?
Yes, there is a performance hit by placing the file into the database, and you are also increasing the size lots. Remember, if you have 100 rows of data in the table that stores the image in a column and you only have actually 2 images it is saving space for all 100 rows to have an image in there. I really don't think it will make the database unstable, but storing files in the physical folders of the application just seem like a better idea to me. Perhaps in SQL server Yukon this will change since I hear you can store objects in it.|||A database is not a file store. While SQL Server can store binary data, this is not as efficient as storing files in a file system.

File system = designed for storing files
Database = NOT designed for storing files.

Don't do it.|||Pierre:
Thanks for your comments. I have to say that I agree with you. However there are instances that storing a file in the database is more compelling than the file system and you have to weigh both options. Hence my post.

-SosaWISE

save and retrieve RTF TEXT FROM SQL DATABASE

Hi,
i have a richtextbox control in my application, and want to know how to save and retrieve formatted text in a MS SQL DB.

I found this example in the forum, but I don't know how to use it. Can you please help me solve the problem? Where should I write the code below?

I've just tried it and able to do this successfully.

I basically did this:

load the RTF into the RTB control created a SQL command, simple basic insert statement:

SqlCommand theSQLCommand = new SqlCommand("INSERT INTO [TableName] (Field) VALUES (@.p1)");
SqlParameter theSQLParameter = new SqlParameter("@.p1", SqlDbType.Text);
theSQLParameter.Value = this.theRichTextBox.RTF;
theSQLCommand.Parameters.Add(theSQLParameter);
theSQLCommand.Connection = new SqlConnection(ConnectionString);
theSQLCommand.Connection.Open();
theSQLCommand.ExecuteNonQuery();
theSQLCommand.Connection.Close();

then to retrieve it, I did this, again, specifically for this example

this.theRichTextBox.Text = String.Empty;
SqlCommand theSQLCommand = new SqlCommand("SELECT [Field] FROM [TableName] WHERE [ID] = 1");
theSQLCommand.Connection = new SqlConnection(ConnectionString);
theSQLCommand.Connection.Open();
SqlDataReader theReader = theSQLCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (theReader.Read())
{
this.theRichTextBox.RTF = theReader.GetValue(0).ToString();
}
theSQLCommand.Connection.Close();

And was able to do this fine and got all the formatting etc... correctly.

The two block codes are from a Windows Forms .NET application with a richtextbox control called theRichTextBox. You would just need to create a table with a column of type TEXT and replace the appropriate [Field] and [TableName] values with the table and column as appropriate. Also, (hopefully obviously) you would need to replace ConnectionString with the parameters to connect this to your specific database.

Save and Retrieve PDF Files in SQL

Using ASP.net I need to be able to save and retrieve PDF Files in SQL.

I believe the best way to do this is through a BLOB datatype.

I have been searching (without luck) for a tutorial/code sample explaining how to do this.

Any help would be greatly appreciated.

Chris

p.s. I know many prefer to store the files on the server and simply store pointers to the files in SQL, but I need to store the actual files in SQL.

Search for storing images inside SQL Server, the idea is the same. A blob is a blob.

The answer has been given at least 3 times in this forum alone once you know that.

|||

Thanks for the response.

Unfortunately, my searching skills must not be that good.

I'm having trouble finding an answer to my question (I did find someone else asking the same questio, but you responded to him suggesting he search as well).

In any case, attempting to search the web I have found conflicting advice. In particular people are recommending different datatypes for storage (BLOB, Image, and VarBinary).

Specifically I need to store about 1,000 PDF's in my table (each pdf will be under 100k in size).

Any suggestions on the best datatype to use? That should help me with my searching.

Thanks a lot,

Chris

|||

The create table statement below is from AdventureWorks modify it for your use and try the thread below take your pick of code sample. If you know the files will be 100k you can use the thumbnail but run some tests. Hope this helps.

CREATE TABLE [ProductPhoto] (
[ProductPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductPhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_ProductPhoto_ProductPhotoID] PRIMARY KEY CLUSTERED
(
[ProductPhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

http://forums.asp.net/thread/1401879.aspx

|||

Caddre,

Thanks for trying to help. I'm not sure I understand your reply though. I know how to create a table and records, my questions are:

1. Should I be using an Image, Blob, or VarBinary datatype to store pdf's?

2. Once I know what type I should be using is there some sample code for storing and retrieving that data?

Thanks though,

Chris

|||Varbinary is like Varchar meaning variable length PDF is IMAGE because the file will never change and there are several codes to store you need ExecuteNonQuery, to retrieve you need ExecuteReader or ExecuteScalar and those code are in Programming .NET by Jeff Prosise or in the link I posted. Hope this helps.|||

Image.

SQL Server does not have a "BLOB" data type.

Varbinary is limited to 8000 characters (in SQL Server 2000), so unless every PDF you want to store is less than 8k, this is a poor choice. There are other reasons as well, but the size limit should kill the idea by itself.

If you are using SQL Server 2005 (or later), you can use varbinary(max), but I see little reason to switch from image which works in both.

|||

Here's how to save:

ProtectedSub btnSave_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnSave.Click

If FileUpload1.HasFileThen

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdInsertAsNew SqlCommand("INSERT INTO Attachments(MimeType,Filename,Data) VALUES (@.MimeType,@.Filename,@.Data) SELECT SCOPE_IDENTITY()", conn)

cmdInsert.Parameters.Add(New SqlParameter("@.MimeType", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@.Filename", SqlDbType.VarChar))

cmdInsert.Parameters.Add(New SqlParameter("@.Data", SqlDbType.Image))

Dim bArray(FileUpload1.PostedFile.ContentLength - 1)AsByte

FileUpload1.PostedFile.InputStream.Read(bArray, 0, FileUpload1.PostedFile.ContentLength)

cmdInsert.Parameters("@.MimeType").Value = FileUpload1.PostedFile.ContentType

cmdInsert.Parameters("@.Filename").Value = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName).ToLower

cmdInsert.Parameters("@.Data").Value = bArray

EndIf

Dim xAsInteger = cmdInsert.ExecuteScalar

conn.Close()

EndIf

EndSub

Here's how to retrieve:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim connAsNew SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

conn.Open()

Dim cmdAsNew SqlCommand("SELECT MimeType,Filename,Data FROM Attachments WHERE ID=@.ID", conn)

cmd.Parameters.Add("@.ID", SqlDbType.Int).Value = Request.QueryString("ID")

Dim drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader("Content-type", dr("MimeType"))

Response.AddHeader("Content-Disposition","inline; filename=""" & dr("Filename") &"""")

Dim buffer()AsByte = dr("Data")

Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

EndSub

Save & Retrieve news article in SQl Server

Hi there,

I hope I post this in the right section!!

I want to save an article in a sql server database. When the article is retrieved from the database, I want the formatting to be exactly the same as when it was saved ie. line breaks must be there and bold text must be shown, bulletpoints, etc.

Currently I save the full article in a single column defined as varchar(max). When I read the data in the column and display it on the webpage, all the line breaks that was in the article is not there anymore and the artcle is one long conitneous piece of text.

If I can use this post as an example. How would this post be stored in a sql database and when viewed, the formatting is still kept - line breaks intact and other formatting where it should be?

Hope I defined it properly and that someone would be able to help!!

Many thanks

Have a look at what you are sending to the database (in profiler), what is saved and what is returned.

I suspect you may need a varbinary rather than text.

It could also be being formatted by your app.

|||

I have looked at Profiler. I am not really fimiliar with profiler, have not worked in it before.

What I can see though is that the data sent to the stored procedure does contain all the line breaks as required. I don't know how to check if the line breaks are still there when reading the data as profiler only shows the query that I used to retrieve the data or the SP name. Any help on how to check what the data looks like after its returned?

At the moment there is no formatting applied to the text as I use a textbox to enter it. All I want to achieve at the moment is to enter text and then return the text from the database with the line breaks that was entered using the textbox...

|||

The varchar(max) should preserve your data from the text box.

Have you stopped your program prior to the insert to confirm that what is being inserted into SQL Server is in the proper format?

And if so, then query that data using SSMS to confirm that what is in the database is the same as what was entered.

That should help isolate where the formatting changes are occcuring.

|||The formatting is not being stripped, it probably does not exist the way you are pulling it.

Look at how you got the data. Most downloads do NOT include formatting because they have no idea how you are going to present it.

|||

I think the problem might be this...

The line breaks are there in the database (as you entered them from your textbox) but you are rendering the text from the database on a web browser that pays no attention to your linebreaks.

Try something like this on your data returned from your database.

dbDataString.Replace(Environment.NewLine,"<br />")