Writing a binary xls in SQL Server

Question

Writing a binary xls in SQL Server

Navigation

#1 by (0 votes)

2

I have an xls file that I load and transform into binary as follows:

FileStream fs = new FileStream(physicalPath + "/cadastros/documentos/" + "AWS-Estudantes_" + Convert.ToString(System.DateTime.Now.Year) + "-" + Convert.ToString(System.DateTime.Now.Month) + "-" + Convert.ToString(System.DateTime.Now.Day) + ".xls", FileMode.Open, FileAccess.Read);                
BinaryReader br = new BinaryReader(fs);
_planilha = br.ReadBytes((Int32)fs.Length);
fs.Close();
br.Close();

Then I call my method to write to the database by passing filename and _planilha which is byte[] _planilha :

string binary = string.Empty;

for (int i = 0; i < _planilha.Length; i++)
{
  binary += _planilha[i];
}

Here I get the data from within _planilha :

providerFactory.ClearParameters();
providerFactory.SqlStat.Append("INSERT INTO BinaryFiles(Titulo,Arquivo) VALUES ('" + _fileName + "', CAST(CAST('" + binary + "' AS VARCHAR(MAX)) AS BINARY(8000))) ");

And here I write, in the bank my field is a binary(8000) only that happens that it transforms the binary I created from xls into a binary. But I need to record just what I gave it to him. That is not happening because without the Cast(Cast it of the conversion error.

sql-server c#

asked by anonymous 21.01.2016 / 16:09

1 answer

Send Display Text of an Enum by Json Syntax error in VBA [closed]

score 0 · Answer 1

I usually save the documents in the database in GZIP format converted to Base64 to avoid encoding problem.

            using (var db = IMBase.getNewDBContext())
        {
            var anexo = db.AtendimentoAnexo.Create();
            anexo.TarefaAnexoId = id;
            anexo.TarefaId = tarefa;
            anexo.NomeDoArquivo = Path.GetFileName(filename);
            anexo.UsuarioId = usuario;
            anexo.Data = DateTime.Now;
            anexo.BinarioZipado = Compress.GZipCompress(filename);

            return Save<AtendimentoAnexo>(anexo, w => w.TarefaId.Equals(id));
        }

Compress.GZipCompress Method:

        public static string GZipCompress(string fileName)
    {
        byte[] file = File.ReadAllBytes(fileName);

        // Compress
        byte[] data = GzipCompress(file);
        if (data == null)
            throw new ArgumentNullException("data");

        return Convert.ToBase64String(data);
    }

To recover the file simply use Decompress.

        private static byte[] base64_decode(string encodedData)
    {
        byte[] encodedDataAsBytes = Convert.FromBase64String(encodedData);
        return encodedDataAsBytes;
    }       

    private static byte[] Decompress(byte[] data)
    {
        using (var compressedStream = new MemoryStream(data))
        using (var zipStream = new GZipStream(compressedStream, CompressionMode.Decompress))
        using (var resultStream = new MemoryStream())
        {
            var buffer = new byte[4096];
            int read;

            while ((read = zipStream.Read(buffer, 0, buffer.Length)) > 0)
            {
                resultStream.Write(buffer, 0, read);
            }

            return resultStream.ToArray();
        }
    }