Need to save a 2 GB of data in the database message collumn

Topics: Logging Application Block
Mar 27, 2012 at 6:19 PM

Hi,

I have a requirement like....I need to save a message of max 2GB size on the Log table.  Sometimes, it might be a pdf, image, etc.,.  I used Varbinary(Max) datatype on database side.  But, I dont understand what kind of equal datatype we have on C# side.  And also, I know we need to change the datatype on tracelistener also.

 

1)  How to read a such kind of large file in C#?

2)  What is the datatype to be placed on tracelistener that can match with varbinary (max)?

 

How can I achieve this requirement?

Mar 27, 2012 at 11:20 PM
Edited Mar 28, 2012 at 6:39 PM

At 2GB you are getting close to the limit for array size: http://blogs.msdn.com/b/joshwil/archive/2005/08/10/450202.aspx

The naive approach is to read the file into a byte[] (assuming you don't get an OutOfMemoryException).  Then add the byte[] to your command as SqlDbType.Varbinary.     

If you run into size/performance problems then you will want to consider inserting the data using chunks.  This is a good example: http://stackoverflow.com/questions/1942609/sql-sequentially-doing-update-write-on-varbinary-column

You may also want to investigate using SQL Server's FILESTREAM.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Mar 28, 2012 at 12:03 AM

Can you exactly give me the code.  Please give me the code which uses byte[] process.  And also, in the tracelistener.....i need to change the datatype right.  There, dbType is given by default.  When I tried to change into sqlDBType...it is throwing an error.

 

Thanks

Pavan

Mar 28, 2012 at 12:40 AM

You should be able to use DbType.Binary.  See Saving and Retrieving Images From SQL Server Using DAAB and ADO.NET 2.0.  The article is old but I think it should still apply.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Mar 28, 2012 at 1:32 AM

 

My DB side

BEGIN
CREATE TABLE [dbo].[Log](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	
	[LogEntryID] [nvarchar](256) NOT NULL,
	[Title] [nvarchar](256) NULL,
	[Timestamp] [datetime] NOT NULL,
	[Message] [nvarchar](1500) NULL,
	[LogMessage] [varbinary](max) NULL,
	[Source] [nvarchar](256) NULL,
	[Type] [nvarchar](256) NULL,
	[SeverityValue] [nvarchar](256) NULL,
	[FormattedMessage] [ntext] NULL,
 CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END


Stored procedure

CREATE PROCEDURE [dbo].[WriteLog]
(
	@EventID int, 
	@Priority int, 
	@Severity nvarchar(32), 
	@Title nvarchar(256), 
	@Timestamp datetime,
	@MachineName nvarchar(32), 
	@AppDomainName nvarchar(512),
	@ProcessID nvarchar(256),
	@ProcessName nvarchar(512),
	@ThreadName nvarchar(512),
	@Win32ThreadId nvarchar(128),
	@Message nvarchar(1500),
	@FormattedMessage ntext,
	@logentryID nvarchar (256),
	@logmessage varbinary (max),
	@sourceValue nvarchar (256),
	@severityvalue nvarchar (256),
	@typeValue nvarchar (256),
	@LogId int OUTPUT
)
AS 

	INSERT INTO [Log] (
		
		Title,
		[Timestamp],
		Message,
		FormattedMessage,
		LogEntryID,
		Source,
		SeverityValue,
		Type,
		LogMessage
	)
	VALUES (
		
		@Title, 
		@Timestamp,
		@Message,
		@FormattedMessage,
		@logentryID,
		@sourceValue,
		@severityvalue,
		@typeValue,
		@logmessage
	)

	SET @LogID = @@IDENTITY
	RETURN @LogID

On CustomTraceListener side

db.AddInParameter(cmd, "logmessage", DbType.Binary, logEntry.LogMessage);

logEntry.LogMessage is a byte[] datatype

My target is to save a 2GB file.  I am trying to read a 64MB file only.  Still it's not saving anything.

My c# code looks like this

 public static byte[] GetErrorMessage(string filePath)
        {
            byte[] fileData;            

            using (FileStream fs = new FileStream(filePath, FileMode.Open))
            {
                BinaryReader reader = new BinaryReader(fs);
                fileData = reader.ReadBytes((int)fs.Length);
                fs.Close();
            }

            return fileData;
}

please help me where I am doing wrong.  It's not throwing any exception but not saving any data in the database.

Thanks
Pavan
Mar 28, 2012 at 2:58 AM

I'm not sure why the stored procedure accepts so many parameters when the table can only take half the number of values.  Also, some of the parameters seem duplicated (severity vs. severityValue) so I'm not sure what the point of those are.

You didn't post your full code (problem could be there) but this code works:

db.AddParameter(cmd, "logmessage", DbType.Binary, fileData.Length, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, fileData);

A complete example using hardcoded values that works for me is:

byte[] fileData = GetErrorMessage(fileName);

var db = DatabaseFactory.CreateDatabase();

DbCommand cmd = db.GetStoredProcCommand("WriteLogTest");

db.AddInParameter(cmd, "eventID", DbType.Int32, 100);
db.AddInParameter(cmd, "priority", DbType.Int32, 0);
db.AddParameter(cmd, "severity", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "Error");
db.AddParameter(cmd, "title", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "Title");
db.AddInParameter(cmd, "timestamp", DbType.DateTime, DateTime.Now);
db.AddParameter(cmd, "machineName", DbType.String, 32, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "MachineName");
db.AddParameter(cmd, "AppDomainName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "AppDomainName");
db.AddParameter(cmd, "ProcessID", DbType.String, 256, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "ProcessId");
db.AddParameter(cmd, "ProcessName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "ProcessName");
db.AddParameter(cmd, "ThreadName", DbType.String, 512, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "ManagedThreadName");
db.AddParameter(cmd, "Win32ThreadId", DbType.String, 128, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "Win32ThreadId");
db.AddParameter(cmd, "message", DbType.String, 1500, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, "Message");

if (Formatter != null)
    db.AddInParameter(cmd, "formattedmessage", DbType.String, "Formatter.Format(logEntry)");
else
    db.AddInParameter(cmd, "formattedmessage", DbType.String, "Message");

// custom stuff
db.AddParameter(cmd, "logentryID", DbType.String, 256, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, "logEntryID");
db.AddParameter(cmd, "logmessage", DbType.Binary, fileData.Length, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, fileData);
db.AddParameter(cmd, "sourcevalue", DbType.String, 256, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, "sourcevalue");
db.AddParameter(cmd, "severityvalue", DbType.String, 256, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, "severityvalue");
db.AddParameter(cmd, "typevalue", DbType.String, 256, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, "typevalue");

db.AddOutParameter(cmd, "LogId", DbType.Int32, 4);

db.ExecuteNonQuery(cmd);
            
int logId = Convert.ToInt32(cmd.Parameters[cmd.Parameters.Count - 1].Value, CultureInfo.InvariantCulture);

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Mar 28, 2012 at 5:57 PM

Hi Randy,

The command that you mentioned above worked really well.  But, that is able to upload only a file of upto 128 MB.  I am not able to upload more than that.  Is there any reason for it?  This is the critical issue going on the project.  

 

Thanks

Pavan

Mar 28, 2012 at 6:54 PM

Continution to the above question.......Is it because of firing a thread to do this job asynchronously?  In the logging dll we are firing a thread to connect to database and update it.  Is it because the thread is not capable of holding such a large object?

Mar 29, 2012 at 2:16 PM

I have no problem saving a 700MB file but that size is dependent on many factors.

Just a note you may want to try changing the code to not use the fileData.Length and use -1 (for varbinary) instead:

db.AddParameter(cmd, "logmessage", DbType.Binary, -1, ParameterDirection.Input, true, 0, 0, null, DataRowVersion.Default, fileData);

 

I'm assuming you are getting an OutOfMemoryException.  If so, then you will need to read the file in chunks and update the database in chunks.  This article looks to be good: DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC.

I think the issue is a general purpose .NET programming problem; I would recommend posting on a general purpose programming question site such as Stack Overflow.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com