Link to home
Start Free TrialLog in
Avatar of meredithf
meredithf

asked on

Uploading and downloading files from SQL server using C#.Net and SQL Server

Based on the code below I was able to upload and download files to and from the server with no error.  However, I can't open the file I uploaded then downloaded.  For Excel files it will say "The file is not in recognizable format."  For MSWord docs, it will just display lots of squares.   For jpeg files, it will display nothing.  I'm not sure where did I go wrong since I can see the record on my sql server database table  and I didn't get any error.

Any help will be greatly appreciated.
private void btnUpload_Click(object sender, System.EventArgs e)
{
	int i;
	string strFileName = File1.PostedFile.FileName.Trim();
	string strContentType = File1.PostedFile.ContentType;
	DateTime dateNow = System.DateTime.Now;			
	int fileSize = Convert.ToInt32(File1.PostedFile.InputStream.Length);
 
	if (fileSize == 0)
	       return;
			
	byte[] bytContent = new byte[fileSize];
 
	strFileName = GetFileName(strFileName);
 
	string strConnection = ConfigurationSettings.AppSettings["MyConnection"];
	string strSQL = "INSERT INTO tblUploads (FileName, FileSize, FileData, UploadDate , ContentType) VALUES (@FileName, @FileSize, @FileData, @UploadDate, @ContentType)";
				
	SqlConnection myConnection=new SqlConnection(strConnection);
	SqlCommand  myCommand = new SqlCommand(strSQL, myConnection);
	myConnection.Open();
	try
	{				
		SqlParameter FileName;
		SqlParameter FileSize;
		SqlParameter FileData;
		SqlParameter UploadDate;
		SqlParameter ContentType;	
    
		FileName = new SqlParameter("@FileName", SqlDbType.VarChar);
		FileName.Value = strFileName;
 
		FileSize = new SqlParameter("@FileSize", SqlDbType.Int);
		FileSize.Value = fileSize;
 
		FileData = new SqlParameter("@FileData", SqlDbType.Image);
		FileData.Value = bytContent;
 
		UploadDate = new SqlParameter("@UploadDate", SqlDbType.DateTime);
		UploadDate.Value = dateNow;
 
		ContentType = new SqlParameter("@ContentType", SqlDbType.NVarChar);
		ContentType.Value = strContentType;
 
		myCommand.Parameters.Add(FileName);
		myCommand.Parameters.Add(FileSize);
		myCommand.Parameters.Add(FileData);
		myCommand.Parameters.Add(UploadDate);
		myCommand.Parameters.Add(ContentType);								
		myCommand.ExecuteNonQuery();
		myConnection.Close();
		LoadGrid();  
	}
	catch (Exception ex)
	{
	           myConnection.Close();
	}
	finally
	{
	           Response.Redirect(Request.Url.ToString());
	}
}
 
//Files uploaded are displayed on a grid.  FIlenames are on a hyperlink format.  Once user clicks on a hyperlink it will go to a ViewPage.aspx which has the following code:
 
private void Page_Load(object sender, System.EventArgs e)
{
	// Put user code to initialize the page here
	byte[] result;
	string strConnection = ConfigurationSettings.AppSettings["MyConnection"];
	string strSQL = "Select * from tblUploads where (FileID = @FileID)";
	SqlConnection myConnection = new SqlConnection(strConnection);
	myConnection.Open();
 
	SqlCommand cmdGetFile = new SqlCommand(strSQL, myConnection);
	SqlParameter FileID = new SqlParameter("@FileID", SqlDbType.Int);
	FileID.Value = Request.QueryString["FileID"];
	cmdGetFile.Parameters.Add(FileID);			
	SqlDataReader myDataReader;
	myDataReader = cmdGetFile.ExecuteReader();
	if (myDataReader.Read())
	{		
		result = (byte[]) myDataReader["FileData"];
		Response.ContentType = myDataReader["ContentType"].ToString();
		Response.OutputStream.Write(result, 0, Convert.ToInt32(myDataReader["FileSize"]));
		Response.AddHeader("Content-Disposition", "attachment;filename=" + myDataReader["FileName"].ToString());
	}
	else
		Response.Write("File Not Found.");
	}
}

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of meredithf

ASKER

When I try RiteshSha's link I got an error on  
        "Bitmap bNewImage = new Bitmap(strImageName);"    invalid parameter used.

When I try HarryNS' link I got an error on
        "binary = File1.FileBytes;"     no definition  for filebytes

I'm using VS2003.  What's the equivalent of these in VS2003?
are you using .NET framework 1.1?
Yes.
have a look, it may help. I don't version 1.1 installed, otherwise, I would have created one for you.

http://www.aspfree.com/c/a/ASP.NET/Uploading-Images-to-a-Database--C---Part-I/
Thanks RiteshShah!

Guess what?  It seems the you did not use the bitmap that you defined so I just comment them out and it works!  Yahoo!  The only problem now is if I upload a 5mb file, it won't work.  Any idea how can I make it possible to upload a 5mb file?
I have uploaded big file as well, what error is it giving you?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks ragi0017 and RiteshShah!  You guys are awesome.