Welcome Guest! You need to login or register to make posts.

Notification

Icon
Error

Options
Go to last post Go to first unread
tvaum  
#1 Posted : Friday, April 4, 2008 3:03:15 AM(UTC)
tvaum

Rank: Newbie

Groups: Member
Joined: 4/4/2008(UTC)
Posts: 1

Does anyone have a chunk of code that they can share that uploads to a database table instead of to a directory? I'd like to include not only filename, filesize, contenttype, and filedata but also DateLastModified - as well as a couple of user completed form items. Any help is greatly appreciated!
Fedor  
#2 Posted : Saturday, April 12, 2008 3:34:01 AM(UTC)
Fedor

Rank: Advanced Member

Groups: Member, Administration, Moderator
Joined: 7/28/2003(UTC)
Posts: 1,660

Thanks: 5 times
Was thanked: 76 time(s) in 74 post(s)
I have attached the sample which uploads and saves files to SQL Server database.

I have used very simple table in this sample:

Code:
CREATE TABLE [dbo].[File](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FileName] [nvarchar](100) NOT NULL,
	[ContentType] [nvarchar](100) NOT NULL,
	[Size] [int] NOT NULL,
	[LastModified] [datetime] NOT NULL,
	[FileData] [image] NOT NULL,
	[ThumbnailData] [image] NOT NULL,
 CONSTRAINT [PK_File] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


You can process uploaded files using the following code:

Code:
<%@ Page Language="C#" AutoEventWireup="true" ValidateRequest="false" Debug="true" %>

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Globalization" %>

<script runat="server">
	private void Page_Load(System.Object sender, System.EventArgs e)
	{
		SqlConnection connection = new SqlConnection(
			System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
			"ImageUploaderConnectionString"].ConnectionString);

		SqlCommand command = new SqlCommand("INSERT INTO [ImageUploader].[dbo].[File]([FileName], "
			+ "[ContentType], [Size], [LastModified], [FileData] ,[ThumbnailData]) " 
			+ "VALUES (@FileName,@ContentType,@Size, @LastModified, @FileData, " 
			+ "@ThumbnailData)", connection);

		SqlParameter fileName = new SqlParameter("@FileName", SqlDbType.NVarChar, 100);
		command.Parameters.Add(fileName);
		
		SqlParameter contentType = new SqlParameter("@ContentType", SqlDbType.NVarChar, 100);
		command.Parameters.Add(contentType);		

		SqlParameter size = new SqlParameter("@Size", SqlDbType.Int);
		command.Parameters.Add(size);

		SqlParameter lastModified = new SqlParameter("@LastModified", SqlDbType.DateTime);
		command.Parameters.Add(lastModified);
		
		SqlParameter fileData = new SqlParameter("@FileData", SqlDbType.Image);
		command.Parameters.Add(fileData);		
				
		SqlParameter thumbnailData = new SqlParameter("@ThumbnailData", SqlDbType.Image);
		command.Parameters.Add(thumbnailData);
		
		connection.Open();

		try
		{
			int fileCount = Int32.Parse(Request.Form["FileCount"]);

			for (int i = 1; i <= fileCount; i++)
			{
				//Get source file and thumbnails and save it to DB.
				HttpPostedFile sourceFile = Request.Files["SourceFile_" + i];
				HttpPostedFile thumbnail1File = Request.Files["Thumbnail1_" + i];

				fileName.Value = Path.GetFileName(sourceFile.FileName);

				//Content-type contains:
				//image/jpeg; charset=utf-8; Content-Transfer-Encoding: binary
				string ct = sourceFile.ContentType;
				int ind = ct.IndexOf(";");
				if (ind > 0)
				{
					ct = ct.Substring(0, ind);
				}
				contentType.Value = ct;

				size.Value = sourceFile.ContentLength;

				//Last modification date of the original Nth file in EXIF-style format:
				//YYYY:MM:DD hh:mm:ss 
				DateTimeFormatInfo info = new DateTimeFormatInfo();
				info.ShortDatePattern = "yyyy:MM:dd HH:mm:ss";
				lastModified.Value = DateTime.ParseExact(Request.Form["SourceFileLastModifiedDateTime_" + i], "d", info);

				byte[] fd = new byte[sourceFile.ContentLength];
				sourceFile.InputStream.Read(fd, 0, sourceFile.ContentLength);
				fileData.Value = fd;

				byte[] td = new byte[thumbnail1File.ContentLength];
				thumbnail1File.InputStream.Read(td, 0, thumbnail1File.ContentLength);
				thumbnailData.Value = td;

				command.ExecuteNonQuery();
			}
		}
		finally
		{
			connection.Close();
		}
	}	
</script>


For displaying files you can use this code:

Code:
<%@ Page Language="C#" AutoEventWireup="true" ValidateRequest="false" Debug="true" %>

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Globalization" %>

<script runat="server">
	private void Page_Load(System.Object sender, System.EventArgs e)
	{
		string sql;

		if (!String.IsNullOrEmpty(Request.QueryString["t"]))
		{
			sql = "SELECT ThumbnailData As [Data], FileName, ContentType FROM [File] WHERE ID = @ID";
		}
		else
		{
			sql = "SELECT FileData As [Data], FileName, ContentType FROM [File] WHERE ID = @ID";
		}

		SqlConnection connection = new SqlConnection(
			System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
			"ImageUploaderConnectionString"].ConnectionString);

		SqlCommand command = new SqlCommand(sql, connection);
		command.Parameters.AddWithValue("@ID", Int32.Parse(Request.QueryString["id"]));

		connection.Open();

		try
		{
			SqlDataReader reader = command.ExecuteReader();

			if (reader.Read())
			{				
				Response.ContentType = reader["ContentType"].ToString();
				Response.AppendHeader("Content-Disposition"
					, String.Format("attachment; filename={0}"
					, HttpUtility.UrlEncode(reader["FileName"].ToString()).Replace("+", "%20")));
				byte[] data = (byte[])reader["Data"];				
				Response.OutputStream.Write(data, 0, data.Length);				
			}

			reader.Close();
		}
		finally
		{
			connection.Close();
		}
	}		
</script>


Please see the entire code in attachment.

Edited by user Wednesday, February 10, 2010 11:44:55 PM(UTC)  | Reason: Not specified

File Attachment(s):
ASPNET_DB.zip (5kb) downloaded 54 time(s).
Best regards,
Fedor Skvortsov
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.