Saving a binary xls to SQL Server

Asked

Viewed 191 times

2

I have an xls file that I upload 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 record in the bank passing the file name and the _planilha that is byte[] _planilha:

string binary = string.Empty;

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

Here I take the data from inside the _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 make the recording, on the bench my field is a binary(8000) but it turns out that it turns the binary I created from the xls into a binary. But I only need to record what I passed to it. That it’s not happening because without the Cast(Cast he of conversion error.

c# sql-server

  • Vc uses varbinary(8000) in the bank?

  • Last time I had to write the entire file - pdf - I couldn’t do a direct SQL, I had to use a feature of the q framework I have to add a parameter; this feature allows me to add n parameters; I passed the file as vector of bytes. Another difference is that I used varbinary(MAX) in the bank.

  • Use Binary(8000) in the bank and Marco, that’s what I did there, _spreadsheet is a byte[] that saves the bytes of all text, so I read all indices and concateno for Inary, and then Inary has a line with all bytes of the file and it’s what I write in the bank... But he does not accept to record like this in a Inary or varbinary

1 answer

0

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 just 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();
        }
    }

Browser other questions tagged c# sql-server

You are not signed in. Login or sign up in order to post.