After download/upload files from MS SQL, the files become unreadable

Question

I have ASP MVC app, MS SQL and C#. I download a file to MS SQL, then I upload the file from DB. After this the file become unreadable. I can't understand what happens. Below code, which returns the file to client.

public string CreateFile(HttpPostedFileBase file)
    {
        string stream_id = String.Empty;

        try
        {
            int size = file.ContentLength;
            string name = file.FileName;               
            string contentType = file.ContentType;
            byte[] bytes = new byte[size];
            file.InputStream.Read(bytes, 0, size);

            string constr = ConfigurationManager.ConnectionStrings["PokrovConnectionString"].ConnectionString;

            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    string query = "DECLARE @MyTableVar TABLE (stream_id uniqueidentifier);"
                        + "INSERT INTO Files(name, file_stream) OUTPUT INSERTED.stream_id INTO @MyTableVar VALUES(@name, @file_stream);"
                        + "SELECT TOP (1) @Identity = stream_id FROM @MyTableVar;";
                    using (SqlCommand cmd = new SqlCommand(query))
                    {
                        cmd.Connection = con;
                        cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Path.GetFileName(file.FileName);
                        cmd.Parameters.Add("@file_stream", SqlDbType.VarBinary).Value = bytes;
                        SqlParameter idParam = cmd.Parameters.Add("@Identity", SqlDbType.NVarChar, 1000);
                        idParam.Direction = ParameterDirection.Output;
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                        stream_id = (string)idParam.Value;
                    }
                }
                ts.Complete();
            }
        }
        catch { }

        return stream_id;

    }

    public FileContentResult GetFile(Guid stream_id,  string contentType = "application/octet-stream")
    {
        SqlDataReader rdr;
        byte[] fileContent = null;
        string mimeType = "";
        string fileName = "";
        string connect = onfigurationManager.ConnectionStrings["PokrovConnectionString"].ConnectionString;
        bool success = false;

        using (var conn = new SqlConnection(connect))
        {
            var qry = "SELECT file_stream, name, file_type FROM Files WHERE stream_id = @stream_id";
            var cmd = new SqlCommand(qry, conn);
            cmd.Parameters.AddWithValue("@stream_id", stream_id);
            conn.Open();

            try
            {
                rdr = cmd.ExecuteReader();
                if (rdr.HasRows)
                {
                    rdr.Read();
                    fileContent = (byte[])rdr["file_stream"];
                    mimeType = rdr["file_type"].ToString();
                    fileName = rdr["name"].ToString();
                }
                success = true;
            }
            catch
            {
                return null;
            }
        }

        if (success == true)
        {
            FileContentResult newFile = new FileContentResult(fileContent, contentType);
            newFile.FileDownloadName = fileName;
            return newFile;
        }
        return null;
    }

I wish I would understand even what he thinks he does with the files. — TomTom, Jun 24 '14 at 11:00
For example, if it's an image, I can't open it with Paint or if it's MS World file, when I open it there does not read characters. — Eugene Ilyushin, Jun 24 '14 at 11:06
Do you populate FileContents property for FileContentResult? Can you show pieces of code where you save/load your file binary data? — Alexander, Jun 24 '14 at 13:54

score 1 · Accepted Answer · edited May 23 '17 at 12:05

From looking at the code which stores posted file into database - most probably you forgot to copy actual file content, just initialized empty byte array with length of posted file, and saved it into db.

.Net has a class which can help you save stream into sql server without allocating buffer and reading entire stream first - SqlBytes. Try to replace this line:

byte[] bytes = new byte[file.ContentLength];

With this:

SqlBinary bytes = new SqlBinary(file.InputStream);

You can also take a look at SqlBinary usage in this post.

After download/upload files from MS SQL, the files become unreadable

1 Answers1