Saving blobs from database to files-CodePudding

I am trying to extract blobs from my database and save them to a folder with the correct them and file extension, I had been trying to follow along from a similar question here

So I have the following method:

    public static void extractFiles(string connectionString, List<Guid>ItemDetailIds, List<string>Filenames, string fileLocation)
    {
        Directory.CreateDirectory(fileLocation);

        using var cnn = new SqlConnection(connectionString);
        int bufferSize = 100;
        byte[] outbyte = new byte[bufferSize];
        long retval;
        long startIndex = 0;

        for (var i = 0; i <Filenames.Count; i  )
        {
           var cleanedFile = CleanFileName(Filenames[i]);
            Filenames[i] = cleanedFile;
        }


        string sql = "select did.FileExtension, did.Blob From ItemView v inner join DocumentItemDetail did on did.ItemDetailID"  
            "= v.ItemDetailId Where did.ItemDetailID = @ItemDetailId AND v.Type='Document' AND did.Blob IS NOT NULL";
        SqlCommand sqlCommand = new SqlCommand(sql, cnn);

        for (var i = 0; i < ItemDetailIds.Count; i  )
        {
            cnn.Open();
            sqlCommand.Parameters.AddWithValue("@ItemDetailId", ItemDetailIds[i].ToString());
            SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess);
            while (reader.Read())
            {

               FileStream fs = new FileStream(fileLocation   Filenames[i]   reader["FileExtension"], FileMode.OpenOrCreate, FileAccess.Write);
               BinaryWriter bw = new BinaryWriter(fs);

                retval = reader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                while (retval == bufferSize)
                {
                    bw.Write(outbyte);
                    bw.Flush();

                    startIndex  = bufferSize;
                    retval = reader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
                }

                bw.Write(outbyte, 0, (int)retval - 1);
                bw.Flush();

                bw.Close();
                fs.Close();
            }

            sqlCommand.Parameters.Clear();
             reader.Close();
            cnn.Close();
        }

    }

But I am getting the error:

Message: Offset and length were out of bounds for the array or count are greater than the number of elements from the index to the end of the source collection. (Parameter 'count')
Source: System.Private.CoreLib
Stack  :   at System.IO.BinaryWriter.Write(Byte[] buffer, Int32 index, Int32 count)

I can't see where it is that I have gone wrong, I'm assuming it's something with how I'm managing the blob but I am not able to see my mistake

CodePudding user response：

Why not use System.IO.File.WriteAllBytes()

while (reader.Read())
{
   File.WriteAllBytes(fileLocation   Filenames[i]   reader["FileExtension"], (byte[])reader["Blob"]);
}

CodePudding user response：

You should be able to use the built in stream copy:

reader.GetStream(1).CopyTo(fs)