Best practise for upload to memory

Sep 6, 2010 at 7:26 PM

Hi,

What would be the best practise for using the uploader to upload to memory only (then out to db) without using file based IO?

Cheers

Ross

Coordinator
Sep 6, 2010 at 9:05 PM

Below I have included a new File Handler called "UploadToDBHandler". It inherits from the BaseUploadHandler as usual but instead of writing to disk, it writes to a db.

NOTE: This is only an example. There are many different databases, database systems and access technologies, so you will have to customize to fit your given environment and circumstances. You could even write the file to disk like the quick start
and then in the UploadComplete method write the new file to the database.

These are situational scenarios and there are no best practices in that regard, it just depends on your situation and current environment.

With that being said, the example assumes MS SqlServer version 2005 or above. There are also several issues not addressed such as locking the file while it's being uploaded as to not allow a new file with the same name/folder combination
and, not letting the file be available for consumption while it's being uploaded etc.

using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using HSS.Interlink.Web;

namespace UploadWithMetadata.Web
{
	public class UploadToDBHandler : HSS.Interlink.Web.BaseUploadHandler
	{
		string connectionStr = "your connection string";

		public UploadToDBHandler()
		{

		}

		#region BaseUploadHandler Members

		public override bool CheckFileExists()
		{
			bool exists = false;

			using (SqlConnection conn = new SqlConnection(this.connectionStr))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = "File_Exists"; // Your stored procedure that returns a value greater than 0 if the file exists
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
					cmd.Parameters.AddWithValue("@FileName", this.FileName);
					cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
					cmd.ExecuteNonQuery();
					int result = (int)cmd.Parameters["@ReturnValue"].Value;
					exists = result > 0; // assumes you have stored procedure that returns a value greater than 0, if the file exists.
				}
			}

			return exists;
		}
		public override Responses CreateNewFile()
		{
                                      // Assumes your sproc handles overwriting existing files. 
			using (SqlConnection conn = new SqlConnection(this.connectionStr))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					//
					// Depending on your situation and existing database schema etc, you could add a column to the file table indicating if the
					// file is available and default it to false or off. Then below in the complete method you turn the flag on. Then in your
					// GetFiles SProc you exclude files that have the bit turned off.
					//
					// See: UploadComplete() below...
					//
					cmd.CommandText = "File_CreateNew"; // Your stored procedure that creates a new, empty file record in the database.
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
					cmd.Parameters.AddWithValue("@FileName", this.FileName);
					cmd.ExecuteNonQuery();
				}
			}

			return HSS.Interlink.Web.Responses.Success;
		}
		public override Responses AppendToFile(byte[] buffer)
		{
			using (SqlConnection conn = new SqlConnection(this.connectionStr))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = "File_AppendChunk"; // Your stored procedure that appends the byte array to the file record in the database.
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
					cmd.Parameters.AddWithValue("@FileName", this.FileName);
					cmd.Parameters.AddWithValue("@Chunk", buffer);
					cmd.ExecuteNonQuery();
				}
			}

			return HSS.Interlink.Web.Responses.Success;
		}
		public override void CancelUpload()
		{
			using (SqlConnection conn = new SqlConnection(this.connectionStr))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = "File_Delete"; // Your stored procedure that deletes a file record in the database.
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
					cmd.Parameters.AddWithValue("@FileName", this.FileName);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public override void UploadComplete()
		{
			//
			// OPTIONALLY:
			//
			// Assuming you have some flag that prevents the file from being made available until it's
			// completely uploaded, you would now turn on the availability bit.
			// OR
			// If you store new files in a temp table, you could now move it to the final File Table.
			//
			// This is really dependant upon your existing schema and applications.
			//
			// You could also store the file local on disk until it's completely uploaded, and then in
			// this call, upload the entire file into the database.
			// 

			using (SqlConnection conn = new SqlConnection(this.connectionStr))
			{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = "File_MakeAvailable"; // Your stored procedure that releases the new file record for use.
					cmd.CommandType = CommandType.StoredProcedure;
					cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
					cmd.Parameters.AddWithValue("@FileName", this.FileName);
					cmd.ExecuteNonQuery();
				}
			}
		}
		public override bool IsAuthorized()
		{
			return true;
		}
		public override void OnError(System.Exception ex)
		{
			Debug.WriteLine(ex.ToString());
		}
		#endregion
	}
}

 

Coordinator
Sep 6, 2010 at 9:31 PM

Also for more info on managing data in the db, here is a good article/discussion for SQL 2005/2008.

http://www.15seconds.com/issue/071025.htm

Jun 4, 2012 at 8:50 PM

Hi hyspdrt,
your code is exactly what I'm looking for but when I try to run your code I'm getting this error: 

'TestClient.Web.UploadToDBHandler.UploadComplete()': return type must be 'string' to match overridden member 'HSS.Interlink.Web.BaseUploadHandler.UploadComplete()' 

Do you have an idea of what I've done wrong ?

Coordinator
Jun 4, 2012 at 11:33 PM

In the latest version the UploadComplete method must return a String. This is an optional return string that will be returned to your client. If you don't need it, just return an empty string.

Jun 5, 2012 at 4:02 AM

Thanks for the quick reply, it did solve my problem. I've run the project but the images are not going into the database. Instead they continue to go into the folder.
Do I need to change something into the web.config file ?

Coordinator
Jun 5, 2012 at 5:59 AM

Sounds like it's not executing your code, maybe you have it pointing to a different file handler.

Jun 5, 2012 at 2:28 PM

Sorry if I'm asking a lot of question,  i'm new to .net. I don't think I understand what I need to change to point to the correct file handler.

here's what i have in the webconfig, is it here that I need to change something ?

	<appSettings>
		<add key="UploadHandler" value="TestClient.Web.UploadHandler, TestClient.Web"/>
		<add key="DownloadHandler" value="TestClient.Web.DownloadHandler, TestClient.Web"/>
	</appSettings>
	<system.web>
		<compilation debug="true" optimizeCompilations="true" targetFramework="4.0"/>
		<httpHandlers>
			<add verb="GET,POST" path="FileDownload.ashx" type="HSS.Interlink.Web.FileDownload, HSS.Interlink.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=9907eb1ad38d3903"/>
			<add verb="GET,POST" path="FileUpload.ashx" type="HSS.Interlink.Web.FileUpload, HSS.Interlink.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=9907eb1ad38d3903"/>
		</httpHandlers>
	</system.web>
Coordinator
Jun 5, 2012 at 2:30 PM

No worries, glad to help. Assuming your Project is named TestClient.Web and contains the UploadHandler.cs file, then whatever code is in that file should be executed. Maybe put a break point on AppendBytes and see what's happening. That's where you would be writing to the database.

Jun 5, 2012 at 2:36 PM

I was creating a new file named  UploadToDbHandler.cs,  instead I need to put your code in the UploadHandler.cs file ?

Coordinator
Jun 5, 2012 at 2:39 PM

If you created your own (UploadToDbHandler), then you need to update the config to point to that file handler.

Jun 5, 2012 at 9:53 PM

Hi hyspdrt,
Thanks for the previous help, I'm now having problem with this section 'CheckFileExists()'

                    cmd.CommandText = "File_Exists"; // Your stored procedure that returns a value greater than 0 if the file exists
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Folder", this.Metadata); // Assumes you've passed the folder name as the upload Metadata.
                    cmd.Parameters.AddWithValue("@FileName", this.FileName);
                    cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    cmd.ExecuteNonQuery();
                    int result = (int)cmd.Parameters["@ReturnValue"].Value;
                    exists = result > 0; // assumes you have stored procedure that returns a value greater than 0, if the file exists.

i've create this storedproc, but for some reason I'm getting an error when the code hit's the 'cm.ExecuteNonQuery()'
Here's my storedProc,

ALTER PROCEDURE [File_Exists] (@Folder NVARCHAR(200), @FileName NVARCHAR(300), @ReturnValue int = 0 output)
AS 
BEGIN

	SELECT @ReturnValue = COUNT(DRPFileName)
	FROM dbo.DocumentRepository
	WHERE DRPFileName = @FileName
	
	RETURN @ReturnValue
END
Coordinator
Jun 5, 2012 at 9:56 PM

Return value is what you return from SQL, not an OUT parameter.

declare @count int
select @count = Count(filename)
from table
where ???

return @count;

Jun 5, 2012 at 10:25 PM

Thank you,  I've change the stored proc as follow but have the same problem in my project.

ALTER PROCEDURE [File_Exists] (@Folder NVARCHAR(200), @FileName NVARCHAR(300))
AS 
BEGIN
	declare @ReturnValue int
	
	SELECT @ReturnValue = COUNT(DRPFileName)
	FROM dbo.DocumentRepository
	WHERE DRPFileName = @FileName
	
	RETURN @ReturnValue	

END
Coordinator
Jun 5, 2012 at 11:08 PM

Sounds like you're having issues with SQL not Interlink.

I would try changing the return parameters to some other name like @count or @fileCount etc.

Jun 6, 2012 at 3:02 AM

your comment helped, I had to add these lines:

conn.Open();
cmd.Connection = conn;

My next step is to create the code for 'File_AppendChunk' stored proc, can you give me a hint or do you have some example that would help me to get in the good direction.

Coordinator
Jun 6, 2012 at 3:10 AM
Edited Jul 23, 2012 at 8:17 PM

Assuming you're storing the bytes in a varbinary(max) column, you should be able to use the SQL .Write function.

NOTE: this is to APPEND data only.

ALTER PROCEDURE [YOUR_SPROC_NAME_Append]
	@SomeId	UNIQUEIDENTIFIER, 
	@Chunk	VARBINARY (MAX)
AS
BEGIN

	UPDATE	YOUR_TABLE_NAME
		SET Contents .WRITE(@Chunk, NULL, NULL)
	WHERE	SomeId = @SomeId

END
Also, if you don't mind giving Interlink a good review and/or a donation would be appreciated :)
Jun 6, 2012 at 10:18 PM
Edited Jun 6, 2012 at 10:19 PM

Hi hyspdrt,
you lost me with the last stored proc. I've tried to implement it like you said but I was unsuccessful.
Should I do something else in the SQL stored proc ?

Here's what I have.
Code Behind

cmd.CommandText = "File_AppendChunk"; // Your stored procedure that appends the byte array to the file record in the database.
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FileName", this.FileName);
cmd.Parameters.AddWithValue("@Chunk", buffer);

conn.Open();
cmd.Connection = conn;

cmd.ExecuteNonQuery();


 

SQL

ALTER PROCEDURE [dbo].[File_AppendChunk] (@FileName NVARCHAR(300), @Chunk VARBINARY (MAX))
AS
BEGIN

UPDATE DocumentRepository
SET DRPFile .WRITE(@Chunk, NULL, NULL)
WHERE DRPFileName = @FileName

END
Coordinator
Jun 6, 2012 at 10:29 PM

Here is the code that I've used in the past.

NOTE:

1. This snippet leverages the DataAccess Layer from the HSS Core Framework, but it's just a wrapper so shouldn't change the concept.
2. The .Write method in SQL, when used for appending, assumes you've already ceated a record. (see the CreateNew Method from the sample)
3. I recommend you study varbinary(max) in SQL and the .Write method

using (var da = new DataAccess(DataAccessContext.DefaultConnection))
{
	var returnValue = 1;
	da.AddWithValue("FileId", ?your_file_id?);
	da.AddWithValue("Chunk", buffer);
	da.ExecNonQuery("File_Append", ref returnValue);
	if (returnValue == 0)
		return Responses.Success;
	return Responses.UnknownException;
}

 

Jun 6, 2012 at 10:46 PM

Sounds good,
I will study the SQL .Write method and varbinary(max).

Does the buffer variable contains all the binary data for the uploaded document ?
ex:  0xFFD8FFE00........

Coordinator
Jun 6, 2012 at 10:47 PM

No it's just the chunk (partial content) of the file. Which is why it's appending...

Jun 7, 2012 at 3:08 AM
hyspdrt wrote:

Assuming you're storing the bytes in a varbinary(max) column, you should be able to use the SQL .Write function.

NOTE: this is to APPEND data only.

ALTER PROCEDURE [YOUR_SPROC_NAME_Append]
	@SomeId	UNIQUEIDENTIFIER, 
	@Chunk	VARBINARY (MAX)
AS
BEGIN

	UPDATE	YOUR_TABLE_NAME
		SET Contents .WRITE(@Chunk, NULL, NULL)
	WHERE	SomeId = @SomeId

END
Also, if you don't mind giving Interlink a good review and/or a donation would be appreciated :)


Hi hyspdrt,
I've finally been able to insert document into the database using your stored proc. The thing I was missing is a 'Default Value' for the Contents field. If i'm correct, you need a value of 0x.

Jun 7, 2012 at 3:37 AM

hyspdrt,
the online demo is displaying the uploaded file from a specific folder under the 'File Explorer'.  Since i'm uploading document to the database, is there a way to display the uploaded file in the 'File Explorer' instead ?

Coordinator
Jun 7, 2012 at 9:47 AM

Yes, there is, but is beyond the scope of the tech support provided for the product. If you're interested, you can hire HSS for consulting services and/or development services.

Jun 7, 2012 at 12:36 PM

Thanks for the info,
what would be the cost for consulting or development services for something like that ?

Coordinator
Jun 7, 2012 at 12:54 PM

Please contact us via email at support@highspeed-solutions.net and we'd be happy to help.

Jun 7, 2012 at 2:37 PM

Hi hyspdrt,
I have another question, is there a way that I can use the 'metadata' to pass the file type and file size so I can upload that info with the document to the database

thank you


Coordinator
Jun 7, 2012 at 3:18 PM

Yes, that is what it's there for, just for that purpose. You can pass any string you want into the Metadata from the client and it will be delivered to the server.

Towards the bottom of the documentation page we explain how to use this feature. 

Jul 22, 2012 at 7:51 PM
hyspdrt wrote:

 

Also, if you don't mind giving Interlink a good review and/or a [donation] would be appreciated :)

note that the donation link posted at one of the replies above is broken

also, regarding reviews, I guess you mean at http://interlink.codeplex.com/releases/view/89573#ReviewsAnchor (different for each release)

Coordinator
Jul 23, 2012 at 8:18 PM

Thanks, the donation link has been updated. And for reviews, you should provide a review for the release version you're using.