Developer forum

Hi Fabio

You can create your own version of the SQL widget - but instead of inheriting the GridWidget you can inherit the ChartWidget:

using Dynamicweb.Core;
using Dynamicweb.Core.Helpers;
using Dynamicweb.Core.UI;
using Dynamicweb.Data;
using Dynamicweb.Extensibility.AddIns;
using Dynamicweb.Extensibility.Editors;
using Dynamicweb.UI.Elements.Displays;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;

namespace Dynamicweb.Dashboards.Widgets
{
    /// <summary>
    /// The class ScalarSqlCountWidget provides SQL Query grid widget
    /// </summary>
    [AddInName("SQL Query Table")]
    [AddInDescription("SQL Query Table")]
    [AddInIcon(Core.UI.Icons.KnownIcon.Straighten)]
    public class SqlGridWidget : GridWidget
    {
        /// <summary>
        /// The class ScalarSqlCountWidget constructor
        /// </summary>
        public SqlGridWidget() : base()
        {
            base.Title = "SQL Query";
            Query = "select * from ";
        }

        /// <summary>
        /// Gets or sets sql query
        /// </summary>
        [AddInLabel("Sql Query"), AddInParameter("SqlQuery"), AddInParameterGroup(" "), AddInParameterEditor(typeof(TextParameterEditor), "NewGUI=true;TextArea=true;")]
        public string Query { get; set; }

        /// <summary>
        /// Gets or sets sql query
        /// </summary>
        [AddInLabel("Max records"), AddInParameter("MaxRecords"), AddInParameterGroup(" "), AddInParameterEditor(typeof(IntegerNumberParameterEditor), "allowNegativeValues=false;maxValue=500;")]
        public int MaxRecords { get; set; } = 500;

        public override IEnumerable<GridColumn> GetColumns()
        {
            var columns = new List<GridColumn>();
            if (!string.IsNullOrWhiteSpace(Query))
            {
                try
                {
                    DataTable schema = null;
                    using (var connection = Database.CreateConnection())
                    {
                        var queryBuilder = ScalarSqlCountWidget.SubstituteQueryParameters(Query);
                        using (var command = queryBuilder.CreateCommand(connection))
                        {
                            using (var reader = Database.CreateDataReader(command, CommandBehavior.SchemaOnly))
                            {
                                schema = reader.GetSchemaTable();
                            }
                        }
                    }

                    if (schema != null && schema.Columns != null)
                    {
                        var noNameCol = 1;
                        foreach(DataRow colInfo in schema.Rows)
                        {
                            var colName = Converter.ToString(colInfo[0]);
                            if (string.IsNullOrEmpty(colName))
                            {
                                colName = $"Column{noNameCol}";
                            }
                            var gridColumn = new GridColumn()
                            {
                                Id = colName,
                                EnableTooltip = true,
                                Title = colName,
                                TruncateText = true,
                            };
                            columns.Add(gridColumn);
                        }
                    }
                }
                catch (Exception) { }
            }
            return columns;
        }

        public override GridViewResponse GetItems(IDashboard dashboard, string path)
        {
            var rows = new List<ExpandoObject>();
            var rowsCount = 0;
            if (!string.IsNullOrWhiteSpace(Query))
            {
                try
                {
                    var queryBuilder = ScalarSqlCountWidget.SubstituteQueryParameters(Query);
                    using (var dataReader = Database.CreateDataReader(queryBuilder))
                    {
                        while (rowsCount < MaxRecords && dataReader.Read())
                        {
                            var row = ExtractRow(dataReader);
                            rows.Add(row);
                            rowsCount++;
                        }
                    }
                }
                catch (Exception) { }
            }

            var data = new GridViewResponse()
            {
                Total = rowsCount,
                Rows = rows
            };
            return data;
        }

        private ExpandoObject ExtractRow(IDataReader dataReader)
        {
            IDictionary<string, object> row = new ExpandoObject();
            var count = dataReader.FieldCount;
            for (var i = 0; i < count; i++)
            {
                var colName = dataReader.GetName(i);
                if (string.IsNullOrEmpty(colName))
                {
                    colName = $"Column{i + 1}";
                }
                var colVal = StringHelper.StripHtml(Converter.ToString(dataReader.GetValue(i)));
                row.Add(colName, colVal);
            }
            return (ExpandoObject)row;
        }
    }
}

E.g. like this:

[AddInName("My Test chart")]
[AddInDescription("Show test chart")]
[AddInIcon(Core.UI.Icons.KnownIcon.PieChart)]
public sealed class MyTestChart : ChartWidget
{
    public MyTestChart()
    {
        Title = "Total cities population";
        ChartType = ChartType.Pie;
        InstantFetch = true;
    }
    public override ChartData GetData(IDashboard dashboard, string path)
    {
        var data = new ChartData
        {
            Labels = new[] { "London", "New York", "Moscow", "Copenhagen" },
            Series = new[] { 500d, 200d, 150d, 300d }
        };

        return data;
    }
}