Get Binary data from a SQL Database

Question

Hi hope some one can help. I have an ASP .Net (3.5) website. I have the following code that uploads a file as a binary to a SQL Database:

Print("
protected void UploadButton_Click(object sender, EventArgs e) {

        //Get the posted file
        Stream fileDataStream = FileUpload.PostedFile.InputStream;

        //Get length of file
        int fileLength = FileUpload.PostedFile.ContentLength;

        //Create a byte array with file length
        byte[] fileData = new byte[fileLength];

        //Read the stream into the byte array
        fileDataStream.Read(fileData, 0, fileLength);

        //get the file type
        string fileType = FileUpload.PostedFile.ContentType;

        //Open Connection
        WebSysDataContext db = new WebSysDataContext(Contexts.WEBSYS_CONN());

        //Create New Record
        BinaryStore NewFile = new BinaryStore();

        NewFile.BinaryID = "1";
        NewFile.Type = fileType;
        NewFile.BinaryFile = fileData;

        //Save Record
        db.BinaryStores.InsertOnSubmit(NewFile);

        try
        {
            db.SubmitChanges();
        }
        catch (Exception)
        {
            throw;
        }
    }");

The files that will be uploaded are PDFs, Can you please help me in writing the code to get the PDF out of the SQL database and display it in the browser. (I am able to get the binary file using a linq query but not sure how to process the bytes)

Answer 1

So are you really just after how to serve a byte array in ASP.NET? It sounds like the database part is irrelevant, given that you've said you are able to get the binary file with a LINQ query.

If so, look at HttpResponse.BinaryWrite. You should also set the content type of the response appropriately, e.g. application/pdf.

Answer 2

How big are the files? Huge buffers (i.e. byte[fileLength]) are usually a bad idea.

Personally, I'd look at things like this and this, which show reading/writing data as streams (the second shows pushing the stream as an http response). But updated to use varchar(max) ;-p

Answer 3

protected void Test_Click(object sender, EventArgs e) {

        WebSysDataContext db = new WebSysDataContext(Contexts.WEBSYS_CONN());

        var GetFile = from x in db.BinaryStores
                      where x.BinaryID == "1"
                      select x.BinaryFile;

        FileStream MyFileStream;
        long FileSize;

        MyFileStream = new FileStream(GetFile, FileMode.Open);
        FileSize = MyFileStream.Length;

        byte[] Buffer = new byte[(int)FileSize];
        MyFileStream.Read(Buffer, 0, (int)FileSize);
        MyFileStream.Close();

        Response.Write("<b>File Contents: </b>");
        Response.BinaryWrite(Buffer);

    }

I tryed this and this did not work. I get a compile error on this line "MyFileStream = new FileStream(GetFile, FileMode.Open);" I not sure where i am going wrong, is it due to the way i have stored it?

Answer 4

can you show me an example?

Answer 5

I am having the same problem in trying to read the file from the database. Did you manage to work out a solution?

Answer 6

Hi

I'm trying to display a document from a database using linq, did anyone get this working.

THanks Adil

Answer 7

Hi,

When you store binary files in SQL Server it adds an OLE Header to the binary-data. So you must strip that header before actually reading the byte[] into file. Here's how you do this.

// First Strip-Out the OLE header
const int OleHeaderLength = 78;

int strippedDataLength = datarow["Field"].Length - OleHeaderLength;

byte[] strippedData = new byte[strippedDataLength];

Array.Copy(datarow["Field"], OleHeaderLength, 
    strippedData , 0, strippedDataLength );

Once you run this code, strippedData will contain the actual file data. You can then use MemoryStream or FileStream to perform I/O on the byte[].

Hope this helps..

Ruchit S.

Answer 8

Hi

this is the code I have got with what you wrote, for some reason, when you click open a File Conversion box pops up in word

The documents in the database is correct, as I can get this working without using linq

help would be much appreciated. Adil

     foreach (View_GetAttachment row in sqlGetJob) {
         const int OleHeaderLength = 78;
         int strippedDataLength = row.BLOB.Length - OleHeaderLength;
         byte[] strippedData = new byte[strippedDataLength];
         Array.Copy(row.BLOB, OleHeaderLength, strippedData, 0, strippedDataLength);

         Response.Buffer = true;
         Response.Charset = "";
         Response.Cache.SetCacheability(HttpCacheability.NoCache);
         Response.ContentType = "application/doc";

         Response.AppendHeader("content-disposition", "attachment; filename=test.doc");
         Response.BinaryWrite(strippedData);
         Response.Flush();
         Response.End(); 
}