How to: Store a Workbook in the Database

  • 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.

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);
        }
    }
}