Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

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

0
meredithf
Asked:
meredithf
2 Solutions
 
RiteshShahCommented:
you are converting your file while uploading it in wrong format, you can use FileStream to convert your file into byte. have a look at my small article at

http://www.sqlhub.com/2009/03/image-store-in-sql-server-2005-database.html 
0
 
meredithfAuthor Commented:
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RiteshShahCommented:
are you using .NET framework 1.1?
0
 
meredithfAuthor Commented:
Yes.
0
 
RiteshShahCommented:
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/
0
 
meredithfAuthor Commented:
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?
0
 
RiteshShahCommented:
I have uploaded big file as well, what error is it giving you?
0
 
Anurag ThakurCommented:
by default the file upload limit is set to 4 mb
to modify that add this to you web config

 For instance, to expand the upload limit to 20MB, you'd do this:
<system.web>
  <httpRuntime maxRequestLength="20480" />
</system.web>
0
 
meredithfAuthor Commented:
Thanks ragi0017 and RiteshShah!  You guys are awesome.  
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now