none
Saving Image from SQL Server 2008 DB to File RRS feed

  • Question

  • I've hunted high and low to try and find something to suit my needs and have found stuff to assist but I have not been successful.  I have a database that contains 1.1 million records of BMP, and another couple hundred thousand of JPG.  I need to export them to file from the database.

    This is what I have thus far but I am not getting a usable file when the code is executed.  I'm obviously missing a key piece here, I just don't know what.

    The datatype of the FILE_ field is image and is the field from which I am trying to export.  The data in the field shows as 0x07FD300C6 ...  I am guessing it is stored as hex.

    Thank you in advanced for your assistance.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using System.IO;
    
    namespace Fix_Signatures
    {
        class Program
        {
            static void Main(string[] args)
            {
                // Create the connection string for the database using Windows
                // Integrated Security.
                string connectionString =
                    "Data Source=DBSERVER;Initial Catalog=DBNAME;"
                    + "Integrated Security=true";
    
                // Provide the query string with a parameter place holder.
                string queryString =
                    "SELECT FileName, FileType, File_, RecId FROM DocuValue "
                    + "WHERE RecId=@recId;";
    
                // Specify the parameter value.
                long recId = 5555555555;
    
                FileStream fs;                          // Writes the BLOB to a file.
                BinaryWriter bw;                        // Streams the BLOB to the FileStream object.
                int bufferSize = 100;                   // Size of the BLOB buffer.
                byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
                long retval;                            // The bytes returned from GetBytes.
                long startIndex = 0;                    // The starting position in the BLOB output.
                string FilePath = "C:\\Temp";
    
                // Create and open the connection in a using block.  This ensures
                // that all resources will be closed and disposed
                // when the code exits.
                using (SqlConnection connection =
                    new SqlConnection(connectionString))
                {
                    // Create the Command and Parameter objects.
                    SqlCommand command = new SqlCommand(queryString, connection);
                    command.Parameters.AddWithValue("@recId", recId);
                    
                    // Open the connection in a try/catch block.
                    // Create and execute the DataReader, writing the result
                    // set to the console window.
                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                        while (reader.Read())
                        {
                            //Console.WriteLine("\t{0}\t{1}\t{2}\t{3}", reader["FileName"], reader["FileType"], reader["File_"], reader["RecId"]);
                            
                            // Create a file to hold the output.
                            fs = new FileStream(FilePath + "\\" + reader["FileName"] + "." + reader["FileType"], FileMode.OpenOrCreate, FileAccess.Write);
                            bw = new BinaryWriter(fs);
    
                            // Reset the starting byte for the new BLOB.
                            startIndex = 1;
    
                            // Read the bytes into outbyte[] and retain the number of bytes returned.
                            retval = reader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
    
                            // Continue reading and writing while there are bytes beyond the size of the buffer.
                            while (retval == bufferSize)
                            {
                                bw.Write(outbyte);
                                bw.Flush();
    
                                // Reposition the start index to the end of the last buffer and fill the buffer.
                                startIndex += bufferSize;
                                retval = reader.GetBytes(2, startIndex, outbyte, 0, bufferSize);
                            }
    
                            // Write the remaining buffer.
                            bw.Write(outbyte, 0, (int)retval);
                            bw.Flush();
    
                            // Close the output file.
                            bw.Close();
                            fs.Close();
                        }
                        reader.Close();
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    Console.ReadLine();
                }
            }
        }
    }


    Wednesday, June 13, 2012 8:42 PM

Answers