This example demonstrates how to configure the Spreadsheet extension to save/load documents to/from a database.
Follow the steps below to configure the Spreadsheet extension to work with a database:
-
Create a class that models a spreadsheet document. This class should be able to store document identifiers, file formats, and content:
public class SpreadsheetData { public string DocumentId { get; set; } public DocumentFormat DocumentFormat { get; set; } public byte[] Document { get; set; } }
-
Register the database context:
public partial class DocumentsEntities : DbContext { public DocumentsEntities(): base("name=DocumentsEntities") {} protected override void OnModelCreating(DbModelBuilder modelBuilder) { throw new UnintentionalCodeFirstException(); } public virtual DbSet<Doc> Docs { get; set; } } public partial class Doc { public int Id { get; set; } public byte[] DocBytes { get; set; } public string Comments { get; set; } }
-
Add connection strings to the Web.config file:
<connectionStrings> <add name="DocumentsConnectionString" connectionString="Data Source=(local);Initial Catalog = Documents;User Id=sa; Password=dx;Connect Timeout=30" providerName="System.Data.SqlClient" /> <add name="DocumentsEntities" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|Documents.mdf;Integrated Security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" /> </connectionStrings>
-
Create a helper class that works with the database. The class should be able to load a document from a database as an array of bytes and save the byte array back to the database:
public class DataHelper { public static byte[] GetDocument() { DocumentsEntities context = new DocumentsEntities(); return context.Docs.FirstOrDefault().DocBytes.ToArray(); } public static void SaveDocument(byte[] bytes) { DocumentsEntities context = new DocumentsEntities(); context.Docs.FirstOrDefault().DocBytes = bytes; context.SaveChanges(); } }
-
Create the
SpreadsheetSettingsHelper
helper class that configures and returns the SpreadsheetSettings object. Specify the object's Name and CallbackRouteValues properties. Set the Saving property to a function that converts an opened document to a byte array and saves it to the database:public static class SpreadsheetSettingsHelper { public static SpreadsheetSettings GetSpreadsheetSettings() { SpreadsheetSettings settings = new SpreadsheetSettings(); settings.Name = "SpreadsheetName"; settings.CallbackRouteValues = new { Controller = "Home", Action = "SpreadsheetPartial" }; settings.Saving = (s, e) => { byte[] docBytes = SpreadsheetExtension.SaveCopy("SpreadsheetName", DocumentFormat.Xlsx); DataHelper.SaveDocument(docBytes); e.Handled = true; }; // ... return settings; } }
-
In a partial view, pass the settings that the
SpreadsheetSettingsHelper
class configures to the Spreadsheet extension method to create the Spreadsheet. Call the Spreadsheet's Open method to open the document stored in the model:@model DXWebApplication23.Models.SpreadsheetData @Html.DevExpress().Spreadsheet(SpreadsheetSettingsHelper.GetSpreadsheetSettings()).Open( Model.DocumentId, Model.DocumentFormat, () => { return Model.Document; } ).GetHtml()
-
Configure the controller. In the
Index
action method, load a document from the database and save it to a model. In the action method you assigned to theCallbackRouteValues
setting, use theSpreadsheetSettingsHelper
class to configure Spreadsheet settings, then return them back to the client:public class HomeController : Controller { [HttpGet] public ActionResult Index() { var model = new SpreadsheetData() { DocumentId = Guid.NewGuid().ToString(), DocumentFormat = DocumentFormat.Xlsx, Document = DataHelper.GetDocument() }; return View(model); } // The method assigned to the Spreadsheet's CallbackRouteAction property in the helper class public ActionResult SpreadsheetPartial() { return SpreadsheetExtension.GetCallbackResult(SpreadsheetSettingsHelper.GetSpreadsheetSettings()); } }