1

I have few thousands attachments saved in a SQL Server database with column datatype varbinary(max). I want to retrieve all these files and save them to a local drive.

What is the best way to achieve that? I am not looking for code specifically but trying to understand all options so I can do this in C#.

Suggestions are appreciated. Thank you

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
challengeAccepted
  • 7,106
  • 20
  • 74
  • 105
  • You can easily read the `varbinary(max)` column into a byte array (`byte[] attachment`) in C# and then save that to a file on disk - question is: do you have the **file name** to use stored in that database table, too?? – marc_s Apr 04 '22 at 16:58
  • Yes I do have all that.. – challengeAccepted Apr 04 '22 at 18:29

2 Answers2

1

You can do any of the followings:

  • Write a SQL script which will read the data from the table and save those to the disk. Here is how that can be done
  • You can write a C# script which will connect to the Database, Read and store the data as a file to a disk

Use the Id of the table as part of the file name to make it unique if you are storing all the files to a single folder.

Rahatur
  • 3,147
  • 3
  • 33
  • 49
0

Here's example code. Note, the namespace System.Data.SqlClient is not referenced by a .NET Core project by default as done by .NET Framework; you have to manually add the System.Data.SqlClient NuGet package to the project.

using System.Data.SqlClient;

var connectionString = "Data Source=localhost;Initial Catalog=MyDatbase;Integrated Security=True;";

var outputFolder = @"C:\temp\";

using var conn = new SqlConnection(connectionString);
conn.Open();

var query = "select DocumentId, Contents from DocumentFile where ID >= 1234";
using var cmd = new SqlCommand(query);
cmd.Connection = conn;

var reader = cmd.ExecuteReader();
if (!reader.HasRows) throw new Exception("No rows!");

while (reader.Read())
{
    var fileName = $"{reader["DocumentId"]}.pdf";
    var data = (byte[])reader["Contents"];
    if (data == null) throw new Exception("Contents is null");

    using var writer = new BinaryWriter(File.OpenWrite(Path.Combine(outputFolder, fileName)));
    writer.Write(data);
}
dr9
  • 51
  • 1
  • 7