Mitch Wheat - Treat the cause, not the symptoms!

DECLARE @Year smallint

DECLARE @Month smallint

DECLARE @Day smallint

SET @Year  = 2007

SET @Month = 5

SET @Day   = 24

SELECT DATEADD(month, 12 * @Year - 22801 + @Month, @Day - 1)

// Create a FileStore object

DBFileStore dbf = new DBFileStore(connectionString, CompressMode.Compressed);

// Add file to DB

Stored storeID = dbf.Store(filename);

// …OR since returned Stored object is never null (but Id might be zero)

int fileID = dbf.Store(filename).Id;

//...

// Restore file from db to file system with original filename

string localfilename = dbf.Restore(fileID, @"C:\temp");

private Stored Store(byte[] bytearray, int origLength, 

                     string name, int? parentID, int? refID, 

                     CompressMode compress)

{

    int fileID = 0;

    Guid guidID = new Guid();

    Stored retID = new Stored(fileID, guidID);

    // Execute stored proc to add data to repository

    using (SqlConnection conn = new SqlConnection(this.connectionString))

    {

        using (SqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandText = "AddToRepository";

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@filename", Path.GetFileName(name));

            cmd.Parameters.AddWithValue("@length", origLength);

            cmd.Parameters.AddWithValue("@compressedlength", bytearray.Length);

            cmd.Parameters.AddWithValue("@binarydata", bytearray);

            cmd.Parameters.AddWithValue("@refID", refID);

            cmd.Parameters.AddWithValue("@parentID", parentID);

            cmd.Parameters.AddWithValue("@iscompressed",

                (compress == CompressMode.Compressed) ? true : false);

            // Add output parameters

            SqlParameter p = cmd.Parameters.Add("@ID", SqlDbType.Int);

            p.Direction = ParameterDirection.Output;

            p = cmd.Parameters.Add("@GUID", SqlDbType.UniqueIdentifier);

            p.Direction = ParameterDirection.Output;

            // No retry logic; any Exceptions bubble up to caller...

            conn.Open();

            cmd.ExecuteNonQuery();

            conn.Close();

            // Retrieve output values

            fileID = (int)cmd.Parameters["@ID"].Value;

            guidID = (Guid)cmd.Parameters["@GUID"].Value;

            retID = new Stored(fileID, guidID);

        }

    }

    return retID;

}

/// <summary>

/// Restores a saved file from Repository to file system.

/// </summary>

/// <param name="fileID">Integer ID of the file to restore</param>

/// <param name="filepath">Either the folder which the original file 

/// is to be restored to, OR the "full file path" + "\" + "new name"</param>

/// <returns>The full filename of the restored file/data</returns>

public string Restore(int fileID, string folderfilepath)

{

    string filename = String.Empty;

    // Execute stored proc to read data from repository

    using (SqlConnection conn = new SqlConnection(this.connectionString))

    {

        using (SqlCommand cmd = conn.CreateCommand())

        {

            cmd.CommandText = "LoadFromRepository";

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@ID", fileID);

            conn.Open();

            using (SqlDataReader rdr =

                       cmd.ExecuteReader(CommandBehavior.CloseConnection))

            {

                if (rdr.Read())

                {

                    filename = SaveToFileSystem(rdr, folderfilepath);

                }

            }

        }

    }

    return filename;

}

private string SaveToFileSystem(SqlDataReader rdr, string folderfilepath)

{

    byte[] binaryData = (byte[])rdr["binarydata"];

    string dbFilename = rdr["filename"].ToString();

    int length = (int)rdr["length"];

    bool isCompressed = (bool)rdr["iscompressed"];

    DateTime CreationTime;

    DateTime LastWriteTime;

    DateTime.TryParse(rdr["FileCreatedDate"].ToString(), out CreationTime);

    DateTime.TryParse(rdr["FileModifiedDate"].ToString(), out LastWriteTime);

    string filename;

    if (Directory.Exists(folderfilepath))

    {

        // Folder only passed: restore as folder + original name...

        filename = Path.Combine(folderfilepath, dbFilename);

    }

    else

    {

        // Caller has specified exact folder and filename we should restore as...

        filename = folderfilepath;

    }

    using (FileStream fs = new FileStream(filename, FileMode.CreateNew, FileAccess.Write))

    {

        if (isCompressed)

        {

            MemoryStream ms = new MemoryStream(binaryData);

            byte[] decompressedData = new byte[length];

            using (GZipStream cgz = new GZipStream(ms, CompressionMode.Decompress))

            {

                cgz.Read(decompressedData, 0, length);

            }

            fs.Write(decompressedData, 0, length);

        }

        else

        {

            fs.Write(binaryData, 0, length);

        }

    }

    // Set file creation/modified date and time if these exist...

    FileInfo fi = new FileInfo(filename);

    if (CreationTime != DateTime.MinValue)

    {

        fi.CreationTime = CreationTime;

    }

    if (LastWriteTime != DateTime.MinValue)

    {

        fi.LastWriteTime = LastWriteTime;

    }

    return filename;

}