Retrieve files from SQL Server database

Question

I am building a site where I have to use file upload control for attaching support logs / mails etc... I felt that saving files in a database would be the better option.

I am using the code below to upload files. However, I am not able to test it as I don't know how to retrieve files from a database. Can some one please help me on this?

File type can be anything.

Code:

FileUrl = "C:\\Attachments\\"+Path.GetFileName(UploadCtrl.NavigateUrl);
FileStream fs = new FileStream(FileUrl, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
long numBytes = new FileInfo(FileUrl).Length;
buff = br.ReadBytes(Convert.ToInt32(numBytes));
SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString);
conn.Open();
SqlCommand command = conn.CreateCommand();
string InsertQueryText = "insert into Attachments values ('" + Path.GetFileName(FileUrl) + "','" + MIME(Path.GetExtension(Att_Overview_Link.NavigateUrl)) + "','" + buff + "');";
command.CommandText = InsertQueryText;
command.ExecuteNonQuery();

Here, MIME is the user defined function to get the MIME value of the specified file type.

Frontend: C# ASP.NET and SQL Server as backend

Answer 1

If you are using SQL Server 2008 or more recent, you can use FILESTREAM storage for a varbinary(max) datatype. This MSDN article contains some C# example code that should accomplish what you're trying to do. It also shows how to create the table you will use to store your files.

Answer 2

Start by fixing your code to remove the SQL Injection vulnerability:

FileUrl = "C:\\Attachments\\" + Path.GetFileName(UploadCtrl.NavigateUrl);

using (SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString))
using (SqlCommand command = conn.CreateCommand())
{
   command.CommandText = "insert into Attachments values (@FileName, @MimeType, @FileBytes)";
   command.Parameters.AddWithValue("@FileName", Path.GetFileName(FileUrl));
   command.Parameters.AddWithValue("@MimeType", MIME(Path.GetExtension(Att_Overview_Link.NavigateUrl)));
   command.Parameters.AddWithValue("@FileBytes", File.ReadAllBytes(FileUrl));

   conn.Open();
   command.ExecuteNonQuery();
}

NB: I'm not sure what your UploadCtrl is, but most file upload controls provide direct access to the uploaded file as a Stream, not a file name on the server. Depending on how this specific control works, you might need to change how you read the uploaded file.

To retrieve the file, you would select the relevant name, MIME type and bytes, and write them to the response:

using (SqlConnection conn = new SqlConnection(SQLSrc.ConnectionString))
using (SqlCommand command = conn.CreateCommand())
{
   command.CommandText = "SELECT FileName, MimeType, FileBytes FROM Attachments WHERE PK = @PK";
   command.Parameters.AddWithValue("@PK", Request.QueryString["pk"]);

   conn.Open();
   using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.CloseConnection))
   {
      if (reader.Read())
      {
         string name = reader.GetString(reader.GetOrdinal("FileName"));
         Response.AppendHeader("Content-Disposition", "attachment; filename=" + name);
         Response.ContentType = reader.GetString(reader.GetOrdinal("MimeType"));

         int startIndex = 0;
         byte[] buffer = new byte[4096];
         int fieldIndex = reader.GetOrdinal("FileBytes");
         int bytesRead = (int)reader.GetBytes(fieldIndex, startIndex, buffer, 0, buffer.Length);
         while (bytesRead != 0)
         {
            Response.OutputStream.Write(buffer, 0, bytesRead);
            Response.Flush();

            startIndex += bytesRead;
            bytesRead = (int)reader.GetBytes(fieldIndex, startIndex, buffer, 0, buffer.Length);
         }
      }
   }
}