Smart Database Connector: How to Insert Images into SQL Server

Smart Database Connector: How to Insert Images into SQL Server

Your requirement: I want to receive images in my database.

Solution: Images are transmitted to Smart Database Connector as a Base64 string. This string can be internally converted to an image by SQL Server. Below you’ll find a sample solution:

1. Create two data fields:
   - for a Base64 string (nvarchar(MAX), allows nulls), e.g. ImageBase64
   - for binary data (varbinary(MAX), allows nulls), e.g. ImageBinary
   
   Here is a definition of a sample database “TestTable” in SQL Server 2005+
   
   
2. Use the SQL statement INSERT INTO in Smart Database Connector to send image data from the app (the data field name “Image”) to the field “ImageBase64” of the database “TestTable”:
   
   INSERT INTO TestTable (Data, ImageBase64) VALUES (%Barcode%, %Image%)
   
   
3. Conversion to an image can then be carried out in a TRIGGER stored procedure invoked by an INSERT event. E.g.:
   

   CREATE TRIGGER dbo.ConvertImageOnInsert
      ON  dbo.TestTable
      AFTER INSERT
   AS
   BEGIN
          UPDATE TestTable SET ImageBinary = CAST('' AS XML).value('xs:base64Binary(sql:column("i.ImageBase64"))', 'VARBINARY(MAX)')
            FROM TestTable t
              INNER JOIN inserted i
                  ON t.ID = i.ID
   END
   GO