Reading SQL Varbinary Blob from Database

Question

I am working on saving files to sql blob to a varbinary(max) column, and have got the save side of things working now (I believe).

What I can't figure out is how to read the data out, given that I'm retrieving my DB values using a stored procedure I should be able to access the column data like ds.Tables[0].Rows[0]["blobData"]; so is it necessary that I have an SQLCommand etc like I've seen in examples such as the one below:

private void OpenFile(string selectedValue)
{
    String connStr = "...connStr";
    fileName = ddlFiles.GetItemText(ddlFiles.SelectedItem);

    using (SqlConnection conn = new SqlConnection(connStr))
    {
        conn.Open();
        using (SqlCommand cmd = conn.CreateCommand())
        {
            cmd.CommandText = "SELECT BLOBData FROM BLOBTest WHERE testid = " + selectedValue;

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    int size = 1024 * 1024;
                    byte[] buffer = new byte[size];
                    int readBytes = 0;
                    int index = 0;

                    using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write, FileShare.None))
                    {
                        while ((readBytes = (int)dr.GetBytes(0, index, buffer, 0, size)) > 0)
                        {
                            fs.Write(buffer, 0, readBytes);
                            index += readBytes;
                        }
                    }
                }
            }
        }
    }

Is there a simpler way to do this when I can access the column that I need without the sqlcommand?

Hope I was clear enough in my question, if not then ask and I will elaborate!

UPDATE:

The situation is now this- I have the value of the blobData column returned by my stored procedure, and can pass this into a memory stream and call 'LoadDocument(memStream); however this results in jibberish text instead of my actual file displaying.

My question now is is there a way to get the full path including file extension of a file stored in an SQL Blob? I am currently looking into using a Filetable for this in the hopes that I will be able to get the full path.

UPDATE 2:

I tried creating a temp file and reading this to no avail (still gibberish)

                string fileName = System.IO.Path.GetTempFileName().ToString().Replace(".tmp", fileExt);

            using (MemoryStream myMemoryStream = new MemoryStream(blobData, 0, (int)blobData.Length, false, true))
            {
                using (FileStream myFileStream1 = File.Create(fileName))
                {
                    myMemoryStream.WriteTo(myFileStream1);

                    myMemoryStream.Flush();
                    myMemoryStream.Close();

                    myFileStream1.Flush();
                    myFileStream1.Close();

                    FileInfo fi = new FileInfo(fileName);

                    Process prc = new Process();
                    prc.StartInfo.FileName = fi.FullName;
                    prc.Start();
                }
            }

Cheers, H

Answer 1

You are making it more difficult than it needs to be. This is using MySQL just because it is handy - the providers all work pretty much the same. Some things will need to be tweaked to handle very large data items (more of a server thing than DB Provider).

Saving image

string sql = "INSERT INTO BlobDemo (filename, fileType, fileData) VALUES (@name, @type, @data)";
byte[] imgBytes;

using (MySqlConnection dbCon = new MySqlConnection(MySQLConnStr))
using (MySqlCommand cmd = new MySqlCommand(sql, dbCon))
{  
    string ext = Path.GetExtension(filename);

    dbCon.Open();
    cmd.Parameters.Add("@name", MySqlDbType.String).Value = "ziggy";
    cmd.Parameters.Add("@data", MySqlDbType.Blob).Value = File.ReadAllBytes(filename);
    cmd.Parameters.Add("@tyoe", MySqlDbType.String).Value = ext;
    int rows = cmd.ExecuteNonQuery();
}

The file data is fed directly to the DB Provider

is there a way to get the full path including file extension of a file stored in an SQL Blob?

No. Your code and the code above is saving the bytes which make up an image or any file.

Read Img Data back

This will read the data back, save it to file and start the associated app:

string SQL = "SELECT itemName, itemData, itemtype FROM BlobDemo WHERE Id = @id";

string ext = "";
string tempFile = Path.Combine(@"C:\Temp\Blobs\", 
    Path.GetFileNameWithoutExtension(Path.GetTempFileName())); 

using (MySqlConnection dbCon = new MySqlConnection(MySQLConnStr))
using (MySqlCommand cmd = new MySqlCommand(SQL, dbCon))
{
    cmd.Parameters.Add("@id", MySqlDbType.Int32).Value = 14;
    dbCon.Open();

    using (MySqlDataReader rdr =  cmd.ExecuteReader())
    {
        if (rdr.Read())
        {
            ext = rdr.GetString(2);
            File.WriteAllBytes(tempFile + ext, (byte[])rdr["itemData"]);
        }
    }
   
    // OS run test
    Process prc = new Process();
    prc.StartInfo.FileName = tempFile + ext;
    prc.Start();
}

• The number of bytes read back matched
• The associated app launched just fine with the image
• The image showed in the picturebox

In both cases, File.ReadAllBytes() and File.WriteAllBytes() will do most of the work for you, no matter the file type.

There is no need to scoop out the data 1k at a time. If the blob was something like an image you wished to use in the app:

using (MySqlDataReader rdr = cmd.ExecuteReader())
{
    if (rdr.Read())
    {
        ext = rdr.GetString(2);
        using (MemoryStream ms = new MemoryStream((byte[])rdr["imgData"]))
        {
            picBox.Image = Image.FromStream(ms);
        }
    }
}

The blob bytes can be fed to the memstream, and even a temp Image need not be created unless you don't need to show it.

In all, Ceiling Cat made it back just fine (image was 1.4 MB, zoomed; another test with a 15.4 MB image also worked - both are larger than I would care to store in a DB).:

Depending on how this is used, consider archiving the images to somewhere on the file system and just saving the filename - perhaps with the Id added to assure the names are unique and help visually link them to the record. Not only will large blobs of data bloat the DB, but there is obviously some overhead involved in converting to and from bytes which can be avoided.

---

If you want/need to delete these at some point after the associated app is done with them (not really a component of the question), then use a tempfile in a specific directory so you can delete everything in it (conditionally¹) when the app ends, or at start up:

private string baseAppPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData),
                    "Company Name", "Product Name", "Temp Files");

Append a Temp Filename and the actual extension for individual files. Alternatively, you could maintain a List<string> trashCan to store the name of each file you create to be deleted later.

¹ Whenever you do delete them, do allow that files could still be open in the app associated with the extension.