Storing and retrieving files from Sql server 2005 with C#

Question

I am trying to store whole files in the SQL Server 2005.I am storing them as image files as this seems to stroing the file as binary data

Created 

 

Spreadsheet class

 

private byte[] _rawFile = new byte[0];

public Guid Id = Guid.Empty;

public string FileName;

public string ModifyBy;

public Spreadsheet(string fileName)

{

FileName = fileName;

}

public byte[] RawFile

{

get

{

return _rawFile;

}

set

{

_rawFile = value;

}

}

public byte[] UnCompressedFile

{

get

{

try

{

return Compressor.UnCompress(_rawFile);

}

catch (ICSharpCode.SharpZipLib.SharpZipBaseException ex)

{

Logger.Current.Error(ex);

throw;

}

}

set

{

_rawFile = Compressor.Compress(value);

}

}

 

 

I am storing the file in database through a stored procedure as follows

 

 

oConn.Open();

using (SqlCommand oCommand = new SqlCommand("proc_star_ins_upd_file", oConn))

{

oCommand.CommandType = System.Data.CommandType.StoredProcedure;

oCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = ScrfFile.Id;

 

oCommand.Parameters.Add("@file_name", SqlDbType.NVarChar, 50).Value = ScrfFile.FileName;

oCommand.Parameters.Add("@contents", SqlDbType.Image, ScrfFile.RawFile.Length).Value = ScrfFile.RawFile;

oCommand.Parameters.Add("@modify_by", SqlDbType.NVarChar, 150).Value = ScrfFile.ModifyBy;

//oCommand.Parameters.Add("@modify_date", SqlDbType.DateTime, 150).Value = DateTime.Now;

oCommand.ExecuteNonQuery();

return true;

 

 

 

PROBLEM

I am getting back bytes[] of information when I retrieve the file.How do I reconstruct the original file back?

Answer 1

Write the byte[] with FileStream.Write().

Answer 2

 check the example below


private static string connection = your connection string;
        static void Main(string[] args)
        {
            
            FileInfo fileInfo = new FileInfo("test.txt");
            long fileLength = fileInfo.Length;
            FileStream fs = new FileStream("trial.txt", FileMode.Open, FileAccess.Read, FileShare.Read);

            Byte[] array = new byte[Convert.ToInt32(fileLength)];
            int iBytesRead = fs.Read(array, 0,
                             Convert.ToInt32(fileLength));
            fs.Close();

            SqlConnection sqlConnection = new SqlConnection(connection);
            string query = "INSERT INTO Files" +
                         "(FileContent)" +
                        "VALUES(@FileContent)";

            SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
            sqlCommand.Parameters.AddWithValue("@FileContent", array);

            sqlConnection.Open();

            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            Console.WriteLine("Inserted");

            Retrieve();

        }

        private static void Retrieve()
        {
            SqlConnection sqlConnection = new SqlConnection(connection);
            string query = "SELECT FileContent FROM Files " +
                         "WHERE ID = 1";
            
            
            SqlCommand cmd = new SqlCommand(query,sqlConnection);
            
            sqlConnection.Open();

            Byte[] array = (byte[])cmd.ExecuteScalar();

            FileStream fs = new FileStream("trial2.txt",
                         FileMode.CreateNew, FileAccess.Write);
            fs.Write(array, 0, array.Length);
            fs.Flush();
            fs.Close();
            Console.WriteLine("created");
        }
    }