Compoo
Compoo

Reputation: 45

Uploading file to SQL Server, cannot retrieve it properly

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

Upvotes: 0

Views: 483

Answers (1)

Related Questions