Dashboard for MVC - How to Load and Save Dashboards from/to a Database

This example shows how to create a custom dashboard storage to load and save dashboards in a database. To create custom dashboard storage, implement IDashboardStorage or IEditableDashboardStorage.

This example also contains an SQL file (SavedDashboards.sql). You can use it to recreate a database on your side. Update the connection string in the Web.config file to make it valid in your environment. The example uses the System.Data.SqlClient members to connect and manage an MS SQL server database.

The following API used in the example:

LoadDashboard
Loads a dashboard with the specified ID in XDocument format from storage.
GetAvailableDashboardsInfo
Returns a list of IDs and Captions of dashboards available in the data storage.
SaveDashboard
Saves the specified dashboard with new settings to the dashboard storage.
AddDashboard
Saves a dashboard definition and its caption to the data storage and returns the ID of the new saved dashboard.

Files to Review

DataBaseEditaleDashboardStorage.cs (VB: DataBaseEditaleDashboardStorage.vb)
Global.asax.cs (VB: Global.asax.vb)

Documentation

More Examples

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

MVCDashboardDesigner/SavedDashboards.sql

SQL
SET NOCOUNT ON
GO

USE master
GO
if exists (select * from sysdatabases where name='SavedDashboards')
        drop database SavedDashboards
GO

DECLARE @device_directory NVARCHAR(520)
SELECT @device_directory = SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysaltfiles WHERE dbid = 1 AND fileid = 1

EXECUTE (N'CREATE DATABASE SavedDashboards
  ON PRIMARY (NAME = N''SavedDashboards'', FILENAME = N''' + @device_directory + N'SavedDashboards.mdf'')
  LOG ON (NAME = N''SavedDashboards_log'',  FILENAME = N''' + @device_directory + N'SavedDashboards.ldf'')
  COLLATE SQL_Latin1_General_CP1_CI_AI')
GO

USE [SavedDashboards]
GO

/****** Object:  Table [dbo].[Dashboards]    Script Date: 07/06/2016 15:51:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Dashboards](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Dashboard] [varbinary](max) NULL,
    [Caption] [nvarchar](255) NULL,
 CONSTRAINT [PK_Dashboards] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

MVCDashboardDesigner/DataBaseEditaleDashboardStorage.cs(vb)

C#
VB.NET

C#
using DevExpress.DashboardWeb;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Linq;

namespace MVCDashboardDesigner {
    public class DataBaseEditaleDashboardStorage : IEditableDashboardStorage {
        private string connectionString;

        public DataBaseEditaleDashboardStorage(string connectionString) {
            this.connectionString = connectionString;
        }

        public string AddDashboard(XDocument document, string dashboardName) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();
                MemoryStream stream = new MemoryStream();
                document.Save(stream);
                stream.Position = 0;

                SqlCommand InsertCommand = new SqlCommand(
                    "INSERT INTO Dashboards (Dashboard, Caption) " +
                    "output INSERTED.ID " +
                    "VALUES (@Dashboard, @Caption)");
                InsertCommand.Parameters.Add("Caption", SqlDbType.NVarChar).Value = dashboardName;
                InsertCommand.Parameters.Add("Dashboard", SqlDbType.VarBinary).Value = stream.ToArray();
                InsertCommand.Connection = connection;
                string ID = InsertCommand.ExecuteScalar().ToString();
                connection.Close();
                return ID;
            }
        }

        public XDocument LoadDashboard(string dashboardID) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();
                SqlCommand GetCommand = new SqlCommand("SELECT  Dashboard FROM Dashboards WHERE ID=@ID");
                GetCommand.Parameters.Add("ID", SqlDbType.Int).Value = Convert.ToInt32(dashboardID);
                GetCommand.Connection = connection;
                SqlDataReader reader = GetCommand.ExecuteReader();
                reader.Read();
                byte[] data = reader.GetValue(0) as byte[];
                MemoryStream stream = new MemoryStream(data);
                connection.Close();
                return XDocument.Load(stream);
            }
        }

        public IEnumerable<DashboardInfo> GetAvailableDashboardsInfo() {
            List<DashboardInfo> list = new List<DashboardInfo>();
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();
                SqlCommand GetCommand = new SqlCommand("SELECT ID, Caption FROM Dashboards");
                GetCommand.Connection = connection;
                SqlDataReader reader = GetCommand.ExecuteReader();
                while (reader.Read()) {
                    string ID = reader.GetInt32(0).ToString();
                    string Caption = reader.GetString(1);
                    list.Add(new DashboardInfo() { ID = ID, Name = Caption });
                }
                connection.Close();
            }
            return list;
        }

        public void SaveDashboard(string dashboardID, XDocument document) {
            using (SqlConnection connection = new SqlConnection(connectionString)) {
                connection.Open();
                MemoryStream stream = new MemoryStream();
                document.Save(stream);
                stream.Position = 0;

                SqlCommand InsertCommand = new SqlCommand(
                    "UPDATE Dashboards Set Dashboard = @Dashboard " +
                    "WHERE ID = @ID");
                InsertCommand.Parameters.Add("ID", SqlDbType.Int).Value = Convert.ToInt32(dashboardID);
                InsertCommand.Parameters.Add("Dashboard", SqlDbType.VarBinary).Value = stream.ToArray();
                InsertCommand.Connection = connection;
                InsertCommand.ExecuteNonQuery();

                connection.Close();
            }
        }
    }
}

Visual Basic
Imports DevExpress.DashboardWeb
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Xml.Linq

Namespace MVCDashboardDesigner
    Public Class DataBaseEditaleDashboardStorage
        Implements IEditableDashboardStorage

        Private connectionString As String

        Public Sub New(ByVal connectionString As String)
            Me.connectionString = connectionString
        End Sub

        Public Function AddDashboard(ByVal document As XDocument, ByVal dashboardName As String) As String Implements IEditableDashboardStorage.AddDashboard
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim stream As New MemoryStream()
                document.Save(stream)
                stream.Position = 0

                Dim InsertCommand As New SqlCommand("INSERT INTO Dashboards (Dashboard, Caption) " & "output INSERTED.ID " & "VALUES (@Dashboard, @Caption)")
                InsertCommand.Parameters.Add("Caption", SqlDbType.NVarChar).Value = dashboardName
                InsertCommand.Parameters.Add("Dashboard", SqlDbType.VarBinary).Value = stream.ToArray()
                InsertCommand.Connection = connection
                Dim ID As String = InsertCommand.ExecuteScalar().ToString()
                connection.Close()
                Return ID
            End Using
        End Function

        Public Function LoadDashboard(ByVal dashboardID As String) As XDocument Implements DevExpress.DashboardWeb.IDashboardStorage.LoadDashboard
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim GetCommand As New SqlCommand("SELECT  Dashboard FROM Dashboards WHERE ID=@ID")
                GetCommand.Parameters.Add("ID", SqlDbType.Int).Value = Convert.ToInt32(dashboardID)
                GetCommand.Connection = connection
                Dim reader As SqlDataReader = GetCommand.ExecuteReader()
                reader.Read()
                Dim data() As Byte = TryCast(reader.GetValue(0), Byte())
                Dim stream As New MemoryStream(data)
                connection.Close()
                Return XDocument.Load(stream)
            End Using
        End Function

        Public Function GetAvailableDashboardsInfo() As IEnumerable(Of DashboardInfo) Implements DevExpress.DashboardWeb.IDashboardStorage.GetAvailableDashboardsInfo
            Dim list As New List(Of DashboardInfo)()
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim GetCommand As New SqlCommand("SELECT ID, Caption FROM Dashboards")
                GetCommand.Connection = connection
                Dim reader As SqlDataReader = GetCommand.ExecuteReader()
                Do While reader.Read()
                    Dim ID As String = reader.GetInt32(0).ToString()
                    Dim Caption As String = reader.GetString(1)
                    list.Add(New DashboardInfo() With {.ID = ID, .Name = Caption})
                Loop
                connection.Close()
            End Using
            Return list
        End Function

        Public Sub SaveDashboard(ByVal dashboardID As String, ByVal document As XDocument) Implements DevExpress.DashboardWeb.IDashboardStorage.SaveDashboard
            Using connection As New SqlConnection(connectionString)
                connection.Open()
                Dim stream As New MemoryStream()
                document.Save(stream)
                stream.Position = 0

                Dim InsertCommand As New SqlCommand("UPDATE Dashboards Set Dashboard = @Dashboard " & "WHERE ID = @ID")
                InsertCommand.Parameters.Add("ID", SqlDbType.Int).Value = Convert.ToInt32(dashboardID)
                InsertCommand.Parameters.Add("Dashboard", SqlDbType.VarBinary).Value = stream.ToArray()
                InsertCommand.Connection = connection
                InsertCommand.ExecuteNonQuery()

                connection.Close()
            End Using
        End Sub
    End Class
End Namespace

MVCDashboardDesigner/Global.asax.cs(vb)

C#
VB.NET

C#
using System;
using System.Configuration;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Routing;
using DevExpress.DashboardCommon;
using DevExpress.DashboardWeb;
using MVCDashboardDesigner.App_Start;

namespace MVCDashboardDesigner {
    // Note: For instructions on enabling IIS6 or IIS7 classic mode, 
    // visit http://go.microsoft.com/?LinkId=9394801

    public class MvcApplication : System.Web.HttpApplication {
        protected void Application_Start() {
            DashboardConfig.RegisterService(RouteTable.Routes);
            AreaRegistration.RegisterAllAreas();
            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);

            ModelBinders.Binders.DefaultBinder = new DevExpress.Web.Mvc.DevExpressEditorsBinder();

            DevExpress.Web.ASPxWebControl.CallbackError += Application_Error;
        }

        protected void Application_Error(object sender, EventArgs e) {
            Exception exception = System.Web.HttpContext.Current.Server.GetLastError();
            //TODO: Handle Exception
        }
    }
}

Visual Basic
Imports System
Imports System.Configuration
Imports System.Web.Http
Imports System.Web.Mvc
Imports System.Web.Routing
Imports DevExpress.DashboardCommon
Imports DevExpress.DashboardWeb

Namespace MVCDashboardDesigner
    ' Note: For instructions on enabling IIS6 or IIS7 classic mode, 
    ' visit http://go.microsoft.com/?LinkId=9394801

    Public Class MvcApplication
        Inherits System.Web.HttpApplication

        Protected Sub Application_Start()
            AreaRegistration.RegisterAllAreas()

            WebApiConfig.Register(GlobalConfiguration.Configuration)
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters)
            RouteConfig.RegisterRoutes(RouteTable.Routes)

            ModelBinders.Binders.DefaultBinder = New DevExpress.Web.Mvc.DevExpressEditorsBinder()

            AddHandler DevExpress.Web.ASPxWebControl.CallbackError, AddressOf Application_Error

            Dim dataBaseDashboardStorage = New DataBaseEditaleDashboardStorage(ConfigurationManager.ConnectionStrings("DashboardStorageConnection").ConnectionString)

            DashboardConfigurator.Default.SetDashboardStorage(dataBaseDashboardStorage)

            Dim dataSourceStorage As New DataSourceInMemoryStorage()
            Dim objDataSource As New DashboardObjectDataSource("Object Data Source", GetType(SalesPersonData))

            objDataSource.DataMember = "GetSalesData"

            dataSourceStorage.RegisterDataSource("objectDataSource", objDataSource.SaveToXml())

            DashboardConfigurator.Default.SetDataSourceStorage(dataSourceStorage)
        End Sub

        Protected Sub Application_Error(ByVal sender As Object, ByVal e As EventArgs)
            Dim exception As Exception = System.Web.HttpContext.Current.Server.GetLastError()
            'TODO: Handle Exception
        End Sub
    End Class
End Namespace

DevExpress Support Team

last modified 6 years ago

Modified: January 29, 2019 7:48 AM

Platform: Business Intelligence

Product: Dashboard for MVC

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.