How to insert binary stream into varbinary?

Question

I have been reading here and here about how to insert a binary array into a varbinary, but what if I don't have a byte array ready? What if I am serializing an object instead? So if I have

var Formatter = new BinaryFormatter();
Formatter.Serialize(SerializeStream, NewEmployeeTree);

when I want to serialize into a file, I pass SerializeStream as

 var SerializeStream = new FileStream(SelectedPath, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);

but this time I want to serialize into the database. how can I do it?

[Creating a Byte Array from a Stream](https://stackoverflow.com/questions/221925/creating-a-byte-array-from-a-stream) — Robert Harvey, Nov 11 '19 at 05:53
Your question was "What if I don't have a byte array ready?" The linked post explains how to convert the stream into a byte array. Once you do that, you will have a byte array ready. — Robert Harvey, Nov 14 '19 at 19:38
@RobertHarvey But from where do I get the stream? and what type of stream? — avivgood2, Nov 14 '19 at 19:49

score 0 · Accepted Answer · answered Nov 11 '19 at 08:34

you can use

bulk insert

like this:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleApp1
{
   class Program
   {
       static void Main(string[] args)
       {
           //toturial obj = new toturial();
           //obj.ID = 1;
           //obj.name = "MrLotfi";
           //IFormatter formatter = new BinaryFormatter();
           //Stream stream = new FileStream("E:\\example.txt", FileMode.Create, FileAccess.ReadWrite);
           //formatter.Serialize(stream,obj);
           //stream.Close();
           Tutorial obj = new Tutorial();
           obj.ID = 1;
           obj.Name = ".Net";
           IFormatter formatter = new BinaryFormatter();
           Stream stream = new FileStream(@"E:\ExampleNew.txt", FileMode.Create, FileAccess.Write);
           formatter.Serialize(stream, obj);
           stream.Close();
           SqlConnection conn = new SqlConnection("Your SQL Connection String");
           conn.Open();
           StringBuilder str = new StringBuilder();
           str.AppendLine("DECLARE @tImage AS VARBINARY(MAX)");
           str.AppendLine(@"SELECT @tImage = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK 'E:\ExampleNew.txt', SINGLE_BLOB ) AS x");
           str.AppendLine(@"INSERT INTO TblPhotos (ID, Name, Images) SELECT NEWID(), 'E:\ExampleNew.txt',@tImage");
           SqlCommand cmd = new SqlCommand(str.ToString(), conn);
           cmd.ExecuteNonQuery();
           Console.WriteLine("Image Saved Successfully...");
           stream = new FileStream(@"E:\ExampleNew.txt", FileMode.Open, FileAccess.Read);
           Tutorial objnew = (Tutorial)formatter.Deserialize(stream);
           Console.WriteLine(objnew.ID);
           Console.WriteLine(objnew.Name);
           Console.ReadKey();
       }
   }
   [Serializable]
   public class Tutorial
   {
       public int ID;
       public string Name;
   }
}

How to insert binary stream into varbinary?

1 Answers1