Uploading file to SQL Server, cannot retrieve it properly

Question

My question is the following: I'm trying to upload an Excel file to database with this method:

using (SqlConnection connection = new SqlConnection(@"Data Source=TESZT1\SQLEXPRESS;Initial Catalog=Alepitmeny;Persist Security Info=True;User ID=sa;Password=*****"))
using (SqlCommand command = connection.CreateCommand())
{
    byte[] file;

    using (var stream = new FileStream(ExcelFilePath, FileMode.Open, FileAccess.Read))
    {
        using (var reader = new BinaryReader(stream))
        {
            file = reader.ReadBytes((int)stream.Length);
        }
    }

    command.CommandText = "INSERT INTO Dokumentacio (Elrendelo_ExcelFile) VALUES (@File) SELECT SCOPE_IDENTITY()";
    command.Parameters.Add("@File", SqlDbType.VarBinary, file.Length).Value = file;

    connection.Open();
    this.dokumentacio_Class.Dokumentacio_ID = Convert.ToInt32(command.ExecuteScalar());
    connection.Close();
}

But when I'm downloading the uploaded files with the method below, I get an error message

Excel found unreadable content in filename.xls. Do you want to recover the contents of this workbook?

from Microsoft Excel, and it can't recover it.

(I'm using SQL Server 2012, Visual Studio 2013, the project is WPF project, my Office version is 2013)

In the database, Elrendelo_ExcelFile column is VARBINARY(MAX)

    public bool ElrendeloExcelFileLetolt(string SavePath)
    {
       using (SqlConnection connection = new SqlConnection(@"Data Source=TESZT1\SQLEXPRESS;Initial Catalog=Alepitmeny;Persist Security Info=True;User ID=sa;Password=*****"))
           try
           {
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandText = @"SELECT d.Elrendelo_ExcelFile FROM Dokumentacio d INNER JOIN Kapcsolotabla k ON k.Dokumentacio_ID=d.Dokumentacio_ID WHERE k.Elrendelo_ID=@id";
                    command.Parameters.AddWithValue("@id", this.dokumentacio_ID);
                    FileStream stream;
                    BinaryWriter writer;

                    int bufferSize = 100;
                    byte[] buffer = new byte[bufferSize];

                    long retval;
                    long startIndex = 0;

                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader(CommandBehavior.Default);

                    while (reader.Read())
                    {
                        stream = new FileStream(SavePath, FileMode.OpenOrCreate, FileAccess.Write);
                        writer = new BinaryWriter(stream);
                        startIndex = 0;

                        retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);

                        while (retval == bufferSize)
                        {
                            writer.Write(buffer);
                            writer.Flush();
                            startIndex += bufferSize;
                            retval = reader.GetBytes(0, startIndex, buffer, 0, bufferSize);
                        }

                        writer.Write(buffer, 0, (int)retval - 1);
                        writer.Flush();
                        writer.Close();

                        stream.Close();
                    }

                    reader.Close();
                    connection.Close();
                }

                return true;
            }
            catch (System.Data.SqlClient.SqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
}

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

This SO answer should help you -> How do I insert/retrieve Excel files to varbinary(max) column in SQL Server 2008?

edited May 23 '17 at 10:33

Community

1
1

answered Jul 31 '14 at 14:07

occasionalDev

84
5

I would like to make a method, which saves an excel file to the database, and one, which can retrieve it properly. – Compoo Jul 31 '14 at 14:17
It seems like you are trying to develop a DMS (Document Management System) - is that correct? – occasionalDev Jul 31 '14 at 14:55
Not really. My input data is in excel file format. I collect the information from it, and work with them in my program, but I would like to save the actual file for later use. So users, who will use my program can download the original input file later. – Compoo Jul 31 '14 at 18:42
Ok I've revised my answer. – occasionalDev Jul 31 '14 at 19:17
Thank you, I couldn't find that thread even though I was searching for this topic a lot. You helped me a lot. – Compoo Aug 01 '14 at 06:30

Uploading file to SQL Server, cannot retrieve it properly

1 Answers1