How to put large text data (~20mb) into sql cs 3.5 database?

Anindya Chatterjee

I am using following query to insert some large text data :

internal static string InsertStorageItem =
"insert into Storage(FolderName, MessageId, MessageDate, StorageData) values ('{0}', '{1}', '{2}', @StorageData)";

and the code I am using to execute this query is as follows :

string content = "very very large data";
string query = string.Format(InsertStorageItem, "Inbox", "AXOGTRR1445/DSDS587444WEE", "4/19/2010 11:11:03 AM");
var command = new SqlCeCommand(query, _sqlConnection);
var paramData = command.Parameters.Add("@StorageData", System.Data.SqlDbType.NText);
paramData.Value = content;
paramData.SourceColumn = "StorageData";
command.ExecuteNonQuery();

But at the last line I am getting this following error : System.Data.SqlServerCe.SqlCeException was unhandled by user code Message=The data was truncated while converting from one data type to another. [ Name of function(if known) = ] Source=SQL Server Compact ADO.NET Data Provider HResult=-2147467259 NativeError=25920 StackTrace: at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at Chithi.Client.Exchange.ExchangeClient.SaveItem(Item item, Folder parentFolder) at Chithi.Client.Exchange.ExchangeClient.DownloadNewMails(Folder folder) at Chithi.Client.Exchange.ExchangeClient.SynchronizeParentChildFolder(WellKnownFolder wellknownFolder, Folder parentFolder) at Chithi.Client.Exchange.ExchangeClient.SynchronizeFolders() at Chithi.Client.Exchange.ExchangeClient.WorkerThreadDoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument) InnerException: Now my question is how am I supposed to insert such large data to sqlce db?

Regards, Anindya Chatterjee[^]

Ashfield

You need to split it over several records as it is too large for the data type. Use a sequence number on each record to allow you to stitch it back together in the right order. As an alternative, would it be possible to put the data in a file and store the file details in the database?

Bob Ashfield Consultants Ltd

Anindya Chatterjee

Hi Bob, Thanks for your reply. Let me tell you one thing that I am going to create a desktop mail storage. Still I am not sure how to store the messages in an elegant way. This was my first try to store the content of the message in sqlce db. Is there any other better way to do this?

Regards, Anindya Chatterjee[^]

Ashfield

There is no nice solution. Each datatype of any database, be it sqlce, oracle, sybase or whatever has a size limit and there is no way around this. In the past when storing large data items I have had to split the data over several records and join them back together for display etc.

Bob Ashfield Consultants Ltd

Md Marufuzzaman

Anindya Chatterjee wrote:

I am going to create a desktop mail storage

Let me know what are the step(s) you following currently....For example read email one after another and store it to the database....

Thanks Md. Marufuzzaman

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Anindya Chatterjee

What I am following are a very simple steps as follows: Start application search for accounts in settings if nothing found show creation dialog create folder and storage tables save the settings if found check for folder structure if not synchronized create folders at folder table add its parent folder if any check for mails in each mail if not uptodate download mails for each folder and save to storage table if uptodate start the listeners

Regards, Anindya Chatterjee[^]