How to: Store a Workbook in the Database

• Nov 28, 2023
• 2 minutes to read

A workbook can be saved to a byte array in one of the available formats specified by the DocumentFormat enumeration by using the SpreadsheetControl.SaveDocument method. You can store the array of bytes in the varbinary field of the data record in the MS SQL Server database.

After retrieving the content of the varbinary field, use the SpreadsheetControl.LoadDocument method to load a workbook in the SpreadsheetControl.

Form1.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace SpreadsheetToDatabase
{
    public partial class Form1 : Form
    {
        string connectionString = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TestDB.mdf;Integrated Security=True";

        public Form1()
        {
            InitializeComponent();
        }

        private void btnDBSave_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "INSERT INTO WorksheetData(Data) VALUES(@Data)";
                SqlParameter dataParameter = new SqlParameter("@Data", SqlDbType.VarBinary);
                dataParameter.Value = spreadsheetControl1.SaveDocument(DevExpress.Spreadsheet.DocumentFormat.OpenXml);
                command.Parameters.Add(dataParameter);
                command.ExecuteNonQuery();
            }
        }

        private void btnDBLoad_Click(object sender, EventArgs e)
        {
            byte[] receivedBytes;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT Data FROM WorksheetData WHERE ID = (SELECT MAX(ID) FROM WorksheetData)";

                SqlDataReader sqlReader = command.ExecuteReader();
                sqlReader.Read();
                receivedBytes = (byte[])sqlReader[0];
            }
            spreadsheetControl1.Document.LoadDocument(receivedBytes, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
        }
    }
}

Form1.vb

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms

Namespace SpreadsheetToDatabase
    Partial Public Class Form1
        Inherits Form
        Private connectionString As String = "Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TestDB.mdf;Integrated Security=True"

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub btnDBSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDBSave.Click
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim command As SqlCommand = connection.CreateCommand()
                command.CommandText = "INSERT INTO WorksheetData(Data) VALUES(@Data)"
                Dim dataParameter As New SqlParameter("@Data", SqlDbType.VarBinary)
                dataParameter.Value = spreadsheetControl1.SaveDocument(DevExpress.Spreadsheet.DocumentFormat.OpenXml)
                command.Parameters.Add(dataParameter)
                command.ExecuteNonQuery()
            End Using
        End Sub

        Private Sub btnDBLoad_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDBLoad.Click
            Dim receivedBytes() As Byte
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim command As SqlCommand = connection.CreateCommand()
                command.CommandText = "SELECT Data FROM WorksheetData WHERE ID = (SELECT MAX(ID) FROM WorksheetData)"

                Dim sqlReader As SqlDataReader = command.ExecuteReader()
                sqlReader.Read()
                receivedBytes = CType(sqlReader(0), Byte())
            End Using
            spreadsheetControl1.Document.LoadDocument(receivedBytes, DevExpress.Spreadsheet.DocumentFormat.OpenXml)
        End Sub
    End Class
End Namespace