How to save a combination of text and image in SQL Server database

Question

I need to save data which is q combination of text and images. For example: "some text" 'image' "some text", and the point is, there is no particular format for this data. The image can be at any part of the sequence with the text before and after it. For example:

I need to save it in the same sequence as it's defined and retrieve and display in the same sequence and preserve the format; Text as text and Image as image.

I know that we can save image in byte[] format but how to save text and image together in single column in database?

Kindly help.

Answer 1

Regardless of the application and storage technology you need the following pieces in your solution:

• Content Model
• Content Editing
• Content Storage
• Content Rendering
• Content Processing and Indexing

Here I will try to explain more about those building blocks:

• Content Model - It's a model of the content. To decide about the content mode based on your requirement, consider these options

  • Maximum flexibility: A combination of text and images is considered as a rich content. You can use a markup language like HTML or rich text formats like RTF as format of the content.
  • Limited structure: To have a limited structure, you still can rely on rich content (like HTML) and easily validate the content to see if the content is in your desired structure. Also as another option, you can rely on a specific class as a template, having a few properties like Text1, Image, Text2 and serialize the class as XML or Binary value.

  Essentially, whatever model type that you choose, you need to convert it to byte[] or string to store in storage. For example you may have a C# class containing some image and text properties and convert it to XML to store in database. Or you may use HTML as content type. In this case DOM is equivalent to C# model and HTML is like XML for you as representation of Document Object Model.

• Content Editing - Depending the to content type which you need, you can create a UI as content editor to allow users to create the content.

  • You can use any Rich Editor like an HTML Editor or RichTextBox.
  • Or you may create just a custom UI using PictureBox, TextBox and other controls for your custom C# model.

• Content Storage - You can store the content as byte[] in any blob storage. In SQL Server, it could be VARBINARY(MAX) or NVARCHAR(MAX) depending to your requirement.

  For example, if using HTML for content type, you can easily save it in both VARBINARY(MAX) and NVARCHAR(MAX). Also if you used any custom C# model, you can easily convert it into binary or XML and save it in VARBINARY(MAX) and NVARCHAR(MAX).

• Content Rendering - Whatever content model that you choose, the content renderer is responsible to render the content to the UI of your application. To render the content, in case of using HTML as content type, you can easily use a WebBrowser control. In case of using RTF, you can use RichTextBox, in case of a custom class, you can use GDI+ or easily use some PictureBox, label and TextBox controls.

• Content Processing and Indexing - After adding the contents to the storage, you may want to have another thread/process to process the content and extract indexing information. This way you can search in the contents easier and faster. For example for the HTML contents while you can easily use string search operation, but for advanced search scenarios and data analytics, you can process the html content to extract more information.

Content Example - HTML

Here is an example of an HTML content. I've created this content using a simple HTML editor which I've shared its code in this answer:

<h3>Lorem ipsum dolor sit amet?</h3>
<img src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAEUAAAAyCAYAAAAKqhZQAAAABHNCSVQICAgIfAhkiAAAAAFzUkdCAK7OHOkAAAAEZ0FNQQAAsY8L/GEFAAAACXBIWXMAAA7EAAAOxAGVKw4bAAAFAUlEQVRoQ+2bWyhlXxzHNw8ajXhBuZSoIYWURpJLrsmlJCnihZRbSGaKvLhkatwvT3ghL6R4UZ7EiyI8kHs0Csm1yKWU9V/r+LF+a519OIcz/rM5n/o267e+v986e62x1744rBRFIVQWENbwrwWEZVFUEE4fQj7vmWRlxZbiActPigqWRVHhwy3K5uamkpeXp1hbW+tOCaagoCClr68PMoyDbSQ6aZ3MzMynuRjS2toaZItIeTzQMn5+fnhSz2pxcRGqOFIOD7RKUVERnhCJiooi6+vr4BLS2toq+Ewykm84UQvc3d3hyZDGxkZwRK6vr4W8kpIScB7AHhUPtEhbW9vT8X/79g161Zmfn3/KleeL+6nUk7RCUlLS0/EPDg5Cr2HwfC8uLqBX7Nf8JXl3dxdaiuLv7w8twwQGBkJLUXZ2dqAlovlFcXJygpaiHBwcQMswdAOGlqI4OjpCS0TzixISEgItRens7ISWOjc3N8rt7S1EiuLm5gYtfZ7OJS3y588fYT+YnZ0FRx98LxMdHQ29D+AxqHigVUJDQ/GESEdHBzgPrKysELqXCDn7+/vgPoA9Kh5oGTwPJjs7O+Lu7k5cXV31vObmZqjiSDk80DJ0vyB048QTU1V9fT1UiEh5PPgbZGdnE3pVgOjv09TURDw8PPAEyZcvX3T3M8vLy5ClD86n4oG5ycjIeBp7fHwcet8HtgFvbm6SjY0Ncnl5Cb2GwetAxQNzkpWVhT9EJ0PPJf8C0rHywFykpKTgDxAUGxsLWf8W0nHywBykpqbiwUlNTQ1JTk4W+mxsbMjR0RFUmI+5uTlomQ4+PioevJX09HQ8sO4UeqS0tFTwmEZGRsB9O2FhYboxt7e3occ0pGPjwVuIi4vDg5L8/HxwOGNjY0IOk/xew1TY+xR6uy6MSR8SwTUeXE/Fg9eSmJiIByTl5eXg6HNyckLoQ5yQHxwcTO7v7yHDePb29oRxsE5PTyHLOKR6HrwGeb/Izc0F53kiIiKEOltbW7K1tQXuy0xOTgr1j2Pg+OrqCrJfBtdR8cBUIiMj8UCkrKwMHOOora0V6pmGhobANUxvb69e3cLCAjk8PNTrZ3e6xiDV8cAUYmJi8CCkqqoKHNOYnp4WxmFiL6IN8fPnTyGX3a0eHx+DS8jq6qrgMxmDVGNaMUPeVIuLi8F5Hefn58TZ2VkY09vbG1xOWlqakOPi4gKOCLs047yvX7+CYxicT8UDYwgJCcHFpLq6Gpy3o3bTt7S0pPN8fHyEfnYJfo6pqSkh397eHhx1cC4VD14iPDwcF5K6ujpwzMfv37+Fz2Dy9PQU4oKCAsh+nomJCaHOwcEBHH1wHhUPnkNekMrKSnDMz8zMjPBZWKb+RwwPDwv1Xl5e4IjgHCoeGCIgIAAX6K4a74H8gmh0dBQc0+jv7xfGUduvsE/FAzW+f/+Ok9/9Sffxl+Y7OzvQ8zq6urqEebD3tRjsUfFARl4Q9nD3f3B2dgattyHvV+x3zo/gfioeYNi5h71fv36Bo23kG0Z2v8XAfVQ8eET+WoPai14tU1FRIczvx48fQkzFA4avry82SUNDg67/o1FYWCjMUxIPEhISsEHa29thiI9JTk6OMF8k1U7S3d0NpR8b+dEBpNdBWlpaoORzEB8fL6+BEJCenh5I/VxI68CDgYEBSPl84HWwfA0dwF9Dt/xpiwqWr6GrYFkUPRTlPzq+MgkshdSAAAAAAElFTkSuQmCC"/>
<ol>
  <li>Consectetur adipiscing elit</li>
  <li>Sed do eiusmod tempor</li>
  <li>Incididunt ut labore et dolore</li>
  <li>magna aliqua.</li>
</ol>
<p><i><u>Ut enim</u>: Ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.</i></p>

Content Editor Example - HTML Editor

In this example, I'll show how easily you can create a simple but useful html editor in C# or HTML, however you can use 3rd party HTML editors as well. The HTML example which you see above, is created by my simple HTML editor:

A basic HTML editor could be a <div contenteditable="true"></div>. It supposrt common keyboard shortcuts like Ctrl+B to make the selection bold, Ctrl+I to make the selection italic, Ctrl+U to make the selection underlined, Ctrl+A to select all text, Ctrl+C to copy selection, Ctrl+X to cut selection, Ctrl+V to paste the selection, Ctrl+L to insert a link.

To have a better user experience for the HTML editor For a Web Application, you can use the document object and use its execCommand method and easily run commands like Bold, Italic, Underline, InsertOrderedList, InsertUnorderedList, InsertImage, FormatBlock, ForeColor, BackColor, and etc.

Here I will share an example for a C# application and will show you easily you can implement an HTML editor:

public class HtmlEditor
{
    WebBrowser webBrowser;
    private dynamic doc;
    private dynamic contentDiv;
    public HtmlEditor(WebBrowser webBrowserControl, string htmlContent)
    {
        webBrowser = webBrowserControl;
        webBrowser.DocumentText = @"<div contenteditable=""true""></div>";
        webBrowser.DocumentCompleted += (s, e) =>
        {
            doc = webBrowser.Document.DomDocument;
            contentDiv = doc.getElementsByTagName("div")[0];
            contentDiv.innerHtml = htmlContent;
        };
    }
    public string HtmlContent => contentDiv.InnerHtml;
    public void Bold() { doc.execCommand("bold", false, null); }
    public void Italic() { doc.execCommand("italic", false, null); }
    public void Underline() { doc.execCommand("underline", false, null); }
    public void OrderedList() { doc.execCommand("insertOrderedList", false, null); }
    public void UnorderedList() { doc.execCommand("insertUnOrderedList", false, null); }
    public void ForeColor(Color color)
    {
        doc.execCommand("foreColor", false, ColorTranslator.ToHtml(color));
    }
    public void BackColor(Color color)
    {
        doc.execCommand("backColor", false, ColorTranslator.ToHtml(color));
    }
    public void InsertImage(Image image)
    {
        var bytes = (byte[])new ImageConverter().ConvertTo(image, typeof(byte[]));
        var src = $"data:image/png;base64,{Convert.ToBase64String(bytes)}";
        doc.execCommand("insertImage", false, src);
    }
    public void Heading(Headings heading)
    {
        doc.execCommand("formatBlock", false, $"<{heading}>");
    }
    public enum Headings { H1, H2, H3, H4, H5, H6 }
}

To use this HTML Editor class, it's enough to have a WebBrowser control on a Form and initialize the editor this way:

HtmlEditor editor;
private void Form1_Load(object sender, EventArgs e)
{
    var html = @"Some html content";
    editor = new HtmlEditor(webBrowser1, html);
}

Then you can use a ToolStrip to show available commands and run the commands. For example:

private void OrderedListButton_Click(object sender, EventArgs e)
{
    editor.OrderedList();
}

private void ImageButton_Click(object sender, EventArgs e)
{
    using (var ofd = new OpenFileDialog())
    {
        ofd.Filter = "Image files|*.png;*.jpg;*.gif;*.jpeg;*.bmp";
        if (ofd.ShowDialog() == DialogResult.OK)
        {
            using (var image = Image.FromFile(ofd.FileName))
            {
                editor.InsertImage(image);
            }
        }
    }
}