Tagged: ASP.NET

Crop & Save (Part II)

This is the second (and the last) post discussing cropping images and saving them to the SQL Server database, using C# ASP.NET and jQuery library called Jcrop. In my previous post, I have created a simple database model that can be used for this purpose, so if you haven’t checked it out, please do. Also, I won’t get into details with Jcrop, since its website is very informative and contains a good manual, as well as demos.

I’ve made a simple demo which can be downloaded here, and I’ll quickly guide you through it, so it could be easily understood. The idea is the following: user clicks on ‘Choose file’ button (which opens FileUpload control), picks an image on their computer and clicks on ‘Upload’ button. At that point, the image is uploaded to the app server and saved to a folder in the file system(in this demo, it is TempImages folder). User can then see the uploaded picture and do the cropping, after which ‘Crop’ button should be clicked. Then, a new bitmap will be created, containing only the area of the original photo selected as the crop area. The bitmap will then be saved to the database as the array of bytes.

FIrst of all, here is the aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="JcropDemo._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Jcrop Demo</title>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.2/jquery-ui.min.js"></script>
    <script type="text/javascript" src="js/jquery.Jcrop.min.js"></script>
    <link rel="Stylesheet" type="text/css" href="css/jquery.Jcrop.min.css" />
    <link rel="Stylesheet" type="text/css" href="css/custom.css" />
    
    <script type="text/javascript">
        jQuery(document).ready(function() {
            
            $preview = $('#preview_pane'),
            $pcnt = $('#preview_pane .preview_container'),
            $pimg = $('#preview_pane .preview_container img'),

            xsize = $pcnt.width(),
            ysize = $pcnt.height();
            
            jQuery(window).load(function() {
                jQuery($('img[bid=EditedImage]')).Jcrop({
                    onChange: doPreviewSave,
                    onSelect: doPreviewSave,
                    boxWidth: 600,
                    boxHeight: 400,
                    aspectRatio: xsize / ysize
                }, function() {

                    // Use the API to get the real image size
                    var bounds = this.getBounds();
                    boundx = bounds[0];
                    boundy = bounds[1];
                    // Store the API in the jcrop_api variable
                    jcrop_api = $('img[bid=EditedImage]');          //this;            

                    // Move the preview into the jcrop container for css positioning
                    $preview.appendTo(jcrop_api.ui.holder);

                });
            });

            function doPreviewSave(c) {
                jQuery($('[id$=HIDDENX]')).val(c.x);
                jQuery($('[id$=HIDDENY]')).val(c.y);
                jQuery($('[id$=HIDDENW]')).val(c.w);
                jQuery($('[id$=HIDDENH]')).val(c.h);

                if (parseInt(c.w) > 0) {
                    var rx = xsize / c.w;
                    var ry = ysize / c.h;

                    $pimg.css({
                        width: Math.round(rx * boundx) + 'px',
                        height: Math.round(ry * boundy) + 'px',
                        marginLeft: '-' + Math.round(rx * c.x) + 'px',
                        marginTop: '-' + Math.round(ry * c.y) + 'px'
                    });
                }
            };
        });       
 
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Panel ID="CurrentImagePanel" runat="server">
            <fieldset>
                <legend id="CurrentPhotoLegend" runat="server"></legend>
                    <p>
                    <asp:Image ID="UserImage" runat="server" CssClass="user_photo_large" />
                    <asp:FileUpload ID="ImageUpload" runat="server" />
                </p>
                <p>
                    <asp:Button ID="ImageUploadButton" runat="server" onclick="ImageUploadButton_Click" Text="Upload"/>
                </p>    
                <p>
                    <asp:Label ID="ImageUploadErrorLabel" runat="server"></asp:Label>
                </p>
            </fieldset>            
        </asp:Panel>
        <asp:Panel ID="EditedImagePanel" runat="server">   
            <fieldset>
                <legend><asp:Label ID="EditedPhotoLabel" runat="server"></asp:Label></legend>
                <asp:Image ID="EditedImage" runat="server" bid="EditedImage" />
                <div id="preview_pane">
                    <div class="preview_container">
                        <asp:Image ID="PreviewImage" runat="server" CssClass="jcrop-preview" />
                    </div>
                </div>          
                <p style="clear:both">
                    <asp:Button ID="CropImageButton" runat="server" OnClientClick="return checkCroppedImage();" onclick="CropImageButton_Click" CssClass="custom_button3" Text="Crop"/>
                    <asp:HiddenField ID="HIDDENX" runat="server" />
                    <asp:HiddenField ID="HIDDENY" runat="server" />
                    <asp:HiddenField ID="HIDDENW" runat="server" />
                    <asp:HiddenField ID="HIDDENH" runat="server" />
                </p>  
            </fieldset>              
        </asp:Panel>
    </div>
    </form>
</body>
</html>

So, we have to add references to jQuery and jQueryui, as well as to Jcrop js and css files. There are two panels, CurrentImagePanel, shown before the image has been selected, and EditedImagePanel, where the cropping will take place. Hidden fields HIDDENX, HIDDENY, HIDDENW and HIDDENH are used to store coordinates of the rectangle representing new (cropped) image. On window load, Jcrop’s default behavior is activated. When the selection is made, hidden fields are populated with its values (check doPreviewSave function).

Code behind:

using System;
using System.Linq;
using System.IO;
using System.Web;

namespace JcropDemo
{
    public partial class _Default : System.Web.UI.Page
    {
        private string[] _AllowedExtensions = { ".png", ".jpeg", ".jpg", ".gif" };
        private string _TempImageLocation = "TempImages\\";
        private string _ImagePath = HttpContext.Current.Request.PhysicalApplicationPath + "TempImages\\";
        private int _UserID = 1;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["WorkingImage"] != null)
            {
                EditedImagePanel.Visible = true;
                CurrentImagePanel.Visible = false;

                PreviewImage.ImageUrl = EditedImage.ImageUrl = _TempImageLocation + (string)Session["WorkingImage"];
            }
            else
            {
                EditedImagePanel.Visible = false;
                CurrentImagePanel.Visible = true;

                UserImage.ImageUrl = _TempImageLocation + "noimage.jpg";
            }
        }

        protected void ImageUploadButton_Click(object sender, EventArgs e)
        {
            string imageName = "";
            if (ImageUpload.HasFile)
            {
                string extension = System.IO.Path.GetExtension(ImageUpload.FileName).ToLower();
                if (_AllowedExtensions.Contains(extension))
                {
                    try
                    {
                        imageName = string.Format("{0}_{1}{2}", _UserID, DateTime.Now.ToFileTimeUtc(), extension);
                        ImageUpload.SaveAs(_ImagePath + imageName);

                        EditedImagePanel.Visible = true;
                        CurrentImagePanel.Visible = false;

                        PreviewImage.ImageUrl = EditedImage.ImageUrl = _TempImageLocation + imageName;

                        Session["WorkingImage"] = imageName;
                    }
                    catch (Exception ex)
                    {
                        ImageUploadErrorLabel.Text = "Image could not be uploaded. Details: " + ex.Message;
                    }
                }
                else
                {
                    ImageUploadErrorLabel.Text = "Wrong extension!!!";
                }
            }
        }

        protected void CropImageButton_Click(object sender, EventArgs e)
        {
            if (Session["WorkingImage"] == null)
                return;

            string imageName = Session["WorkingImage"].ToString();

            int w = Convert.ToInt32(Math.Floor(Convert.ToDouble(HIDDENW.Value)));
            int h = Convert.ToInt32(Math.Floor(Convert.ToDouble(HIDDENH.Value)));
            int x = Convert.ToInt32(Math.Floor(Convert.ToDouble(HIDDENX.Value)));
            int y = Convert.ToInt32(Math.Floor(Convert.ToDouble(HIDDENY.Value)));

            byte[] cropImage = Crop(_ImagePath + imageName, w, h, x, y);

            BLL.SaveUserProfilePhoto(_UserID, cropImage);

            File.Delete(_ImagePath + imageName);
            Session["WorkingImage"] = null;

            EditedImagePanel.Visible = false;
            CurrentImagePanel.Visible = true;
        }

        static byte[] Crop(string Img, int Width, int Height, int X, int Y)
        {
            try
            {
                using (System.Drawing.Image originalImage = System.Drawing.Image.FromFile(Img))
                {
                    using (System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(Width, Height))
                    {
                        bmp.SetResolution(originalImage.HorizontalResolution, originalImage.VerticalResolution);
                        using (System.Drawing.Graphics Graphic = System.Drawing.Graphics.FromImage(bmp))
                        {
                            Graphic.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias;
                            Graphic.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.HighQualityBicubic;
                            Graphic.PixelOffsetMode = System.Drawing.Drawing2D.PixelOffsetMode.HighQuality;
                            Graphic.DrawImage(originalImage, new System.Drawing.Rectangle(0, 0, Width, Height), X, Y, Width, Height, System.Drawing.GraphicsUnit.Pixel);
                            MemoryStream ms = new MemoryStream();
                            bmp.Save(ms, originalImage.RawFormat);
                            return ms.GetBuffer();
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
                throw (Ex);
            }
        }
    }
}

In order for this example to be as simple as possible, there is only one user that the image would be added to, hence the variable _UserID is set to 1. In the ImageUploadButton_Click event, the image is saved to a folder on the server. In CropImageButton_Click, the points of the cropped rectangle (represented in the hidden fields) are used to create array of bytes that is later saved to the database. On successful save, the original image is deleted from the file system.

I’ve put the business logic into separate file:

using System;
using System.Configuration;
using System.Linq;
using System.Web;

namespace JcropDemo
{
    public class BLL
    {
        public static Model Model
        {
            get
            {
                if (!HttpContext.Current.Items.Contains("Model"))
                    HttpContext.Current.Items.Add("Model", new Model());

                return (Model)HttpContext.Current.Items["Model"];
            }
        }

        public static void SaveUserProfilePhoto(int userID, byte[] photoContent)
        {
            User usr = Model.Entities.User.Where(x => x.UserId == userID).FirstOrDefault();
            if (usr != null)
            {
                Image img = new Image();
                img.ImageContent = photoContent;
                img.User.Add(usr);

                Model.Entities.AddToImage(img);
                Model.Entities.SaveChanges(true);
            }
        }
    }

    public class Model
    {
        private TestDbEntities _Entities = null;
        public TestDbEntities Entities
        {
            get
            {
                if (_Entities == null)
                {
                    if (ConfigurationManager.ConnectionStrings["TestDbEntities"] == null)
                    {
                        throw new NullReferenceException("Missing connection string!");
                    }
                    _Entities = new TestDbEntities(ConfigurationManager.ConnectionStrings["TestDbEntities"].ConnectionString);
                }
                return _Entities;
            }
        }
    }
}

Static method SaveUserProfilePhoto uses EF model to save the data to the SQL Server database.

So, once again: you can download the source code here, and remember to change your connection settings in Web.config!

Hope this example is useful, and I’ll be glad to get your feedback on it.

Ext.NET Grid Issue

Hello all! Sorry for not posting more often, but I don’t have lots of spare time these days, since I’m working on couple of projects… :( I’ll definitely do my best in writing more!

Today I would like to share an unusual issue I’ve had at work these days, regarding Ext.NET grid control. For you unfamiliar with it, Ext.NET is an ASP.NET component framework integrating the Ext JS library. In my company, we have created an ASP.NET C# application using Ext.NET controls. I have encountered an issue occurring only in one grid, which has both combobox and button in the row. There is some processing on mousedown event, since the row must be selected when the button is clicked. Now, what happened was that, when the combobox was clicked on, it wouldn’t display the values. It looked something like this:

grid on mousedown

But, when navigating through items using keyboard, one could see all the values:

grid on keydown

I’ve been trying to solve the problem, and thought the issue was that the combobox event was somehow overridden. Finally, when I couldn’t solve it, I’ve asked the question on stackoverflow, hoping that someone would help, and created a simplified example in which the behavior could be reproduced. Here it is:

<%@ Page Language="C#" AutoEventWireup="true" %>

<%@ Register Assembly="Ext.Net" Namespace="Ext.Net" TagPrefix="ext" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script type="text/javascript">
    function setGridCurrentRow(grid, e) {
        var parent = Ext.fly(e.target).findParent(grid.getView().rowSelector, grid.getView().rowSelectorDepth);

        if (parent) {        // if no row selected
            grid.currentRow = grid.store.getRange()[parent.rowIndex].data;
        }
        else {               // else - row selected
            grid.currentRow = grid.getRowsValues()[0];
        }
    }
</script>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!X.IsAjaxRequest)
        {
            Ext.Net.ConfigItem cItem = new Ext.Net.ConfigItem();
            cItem.Mode = Ext.Net.ParameterMode.Raw;
            cItem.Name = "listeners";
            cItem.Value += "{'keydown':function(e){ if(e.getKey()==9){setGridCurrentRow(" + TestGrid.ID + ",e);}}";
            cItem.Value += ",'mousedown':function(e){ if(e.target != null) setGridCurrentRow(" + TestGrid.ID + ",e);}";
            cItem.Value += ",'command':function(command,gridRecord){alert('Here I am!');}}";
            TestGrid.CustomConfig.Add(cItem);

            TestStore.DataSource = Data;
            this.TestStore.DataBind();
        }
    }

    private object[] Data
    {
        get
        {
            return new object[]
            {
                new object[] { 1, "f", "First", "" },
                new object[] { 2, "s", "Second", "" },
                new object[] { 3, "f", "First", "" },
                new object[] { 4, "t", "Third", "" },
                new object[] { 5, "f", "Fourth", "" },
            };
        }
    }

</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
     <ext:ResourceManager ID="ResourceManager1" runat="server" />
    <form id="form1" runat="server">
    <div>
        <ext:Store ID="TestStore" runat="server" >
            <Reader>
                <ext:ArrayReader IDProperty="ID" >
                    <Fields>
                        <ext:RecordField Name="ID"></ext:RecordField>
                        <ext:RecordField Name="TestText"></ext:RecordField>
                        <ext:RecordField Name="TestValue"></ext:RecordField>
                        <ext:RecordField Name="ButtonText"></ext:RecordField>
                    </Fields>
                </ext:ArrayReader>
            </Reader>
        </ext:Store>
        <ext:GridPanel ID="TestGrid" runat="server" StoreID="TestStore"  Width="600" Height="350" ClicksToEdit="1" TrackMouseOver="true" Selectable="true" >
            <ColumnModel ID="TestModel">
                <Columns>
                    <ext:Column ColumnID="TestText" Header="TestText" MenuDisabled="true">
                        <Editor>
                            <ext:TextField ID="TestText_Text" runat="server"></ext:TextField>
                        </Editor>
                    </ext:Column>
                    <ext:Column ColumnID="TestValue" Header="Value" MenuDisabled="true">
                        <Editor>
                            <ext:ComboBox ID="TestValue_Combo" runat="server" Editable="false" ForceSelection="true">
                                <Items>
                                    <ext:ListItem Text="First" Value="1" />
                                    <ext:ListItem Text="Second" Value="2" />
                                    <ext:ListItem Text="Third" Value="3" />
                                    <ext:ListItem Text="Fourth" Value="4" />
                                    <ext:ListItem Text="Fifth" Value="5" />
                                </Items>
                            </ext:ComboBox>
                        </Editor>
                    </ext:Column>
                    <ext:Column ColumnID="TestButton" Header="Edit" MenuDisabled="true">
                        <Commands>
                            <ext:ImageCommand Icon="ApplicationEdit" CommandName="Edit" Text="Edit" ></ext:ImageCommand>
                        </Commands>
                    </ext:Column>
                </Columns>
            </ColumnModel>
            <SelectionModel >
                <ext:CheckboxSelectionModel ID="TestSelectionModel" runat="server" ></ext:CheckboxSelectionModel>
            </SelectionModel>
        </ext:GridPanel>
    </div>
    </form>
</body>
</html>

Fortunately, I was helped by this guy. Seems this was the problem:

grid.getRowsValues()[0];

and should have been changed with this line of code:

grid.getStore().getAt(0).data;

This solved the issue. Thanks Daniil! :)

Google Chart in ASP.NET Application – Quick & Easy (Part 2)

In one of my previous posts, I’ve explained how to easily integrate Google charts with ASP.NET application. Here is one more example related to that subject, having some (for me) very cool data filtering features.
Note: For the sake of simplicity, I’ve removed jQuery and AJAX code, so all the processing and chart drawing happens on page load.

Here is C# code:

using System;
using System.Collections.Generic;
using System.Web.Services;
using System.Web.Script.Serialization;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            List<DataItem> dataList = new List<DataItem>();

            dataList.Add(new DataItem("Column 1", 100, "Male", 25));
            dataList.Add(new DataItem("Column 2", 150, "Male", 37));
            dataList.Add(new DataItem("Column 3", 250, "Female", 25));
            dataList.Add(new DataItem("Column 4", 400, "Female", 35));
            dataList.Add(new DataItem("Column 5", 450, "Male", 35));
            dataList.Add(new DataItem("Column 6", 460, "Female", 26));
            dataList.Add(new DataItem("Column 7", 470, "Female", 30));
            dataList.Add(new DataItem("Column 8", 500, "Male", 31));
            dataList.Add(new DataItem("Column 9", 550, "Male", 30));
            dataList.Add(new DataItem("Column 10", 600, "Female", 33));

            JavaScriptSerializer jss = new JavaScriptSerializer();
                        
            ClientScript.RegisterStartupScript(this.GetType(), "TestInitPageScript",
                string.Format("<script type=\"text/javascript\">drawVisualization({0},'{1}','{2}','{3}');</script>",
                jss.Serialize(dataList),
                "Text Example",
                "Name,Value,Gender,Age",
                "--Choose--"));
        }
    }   
}

public class DataItem
{
    #region Internal Members

    private string _ColumnName = "";
    private double _Value1 = 0;
    private string _Value2 = null;
    private int _Value3 = 0;     

    #endregion

    #region Public Properties

    public string ColumnName
    {
        get { return _ColumnName; }
        set { _ColumnName = value; }
    }
    public double Value1
    {
        get { return _Value1; }
        set { _Value1 = value; }
    }
    public string Value2
    {
        get { return _Value2; }
        set { _Value2 = value; }
    }
    public int Value3
    {
        get { return _Value3; }
        set { _Value3 = value; }
    }

    #endregion

    #region Constructors

    public DataItem(string columnName, double value1, string value2, int value3)
    {
        _ColumnName = columnName;
        _Value1 = value1;
        _Value2 = value2;
        _Value3 = value3;
    }

    #endregion
}

ColumnName is the name of a specific item, for example person’s name, or name of the product our company makes. Values(Value1, Value2, Value3) closely represent the item, e.g. person’s age, salary, number of products sold etc. In a project I’m currently working on, I use Google chart to show which products are popular among different age/sex groups.

On page load, the data is compiled into JSON string using JavaScriptSerializer(line 29), and then the script is registered using ClientScript.RegisterStartupScript. This way, the script will be executed when loaded on client. Some additional information is also provided, including chart title(line 30), data column names(line 31), neutral value text in the category dropdown list(line 32).

ASPX & javaScript:


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Google Charts Example</title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js" type="text/javascript"></script>
    <script type="text/javascript" src="//www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load('visualization', '1.1', { packages: ['controls'] });
    </script>
    <script type="text/javascript">  

        function drawVisualization(dataValues, chartTitle, columnNames, categoryCaption) {
            if (dataValues.length < 1)
                return;

            var data = new google.visualization.DataTable();
            data.addColumn('string', columnNames.split(',')[0]);
            data.addColumn('number', columnNames.split(',')[1]);
            data.addColumn('string', columnNames.split(',')[2]);
            data.addColumn('number', columnNames.split(',')[3]);

            for (var i = 0; i < dataValues.length; i++) {
                data.addRow([dataValues[i].ColumnName, dataValues[i].Value1, dataValues[i].Value2, dataValues[i].Value3]);
            }

            // Define a category picker control for the Gender column
            var categoryPicker = new google.visualization.ControlWrapper({
                'controlType': 'CategoryFilter',
                'containerId': 'CategoryPickerContainer',
                'options': {
                    'filterColumnLabel': columnNames.split(',')[2],
                    'ui': {
                        'labelStacking': 'horizontal',
                        'allowTyping': false,
                        'allowMultiple': false,
                        'caption': categoryCaption,
                        'label': columnNames.split(',')[2]
                    }
                }
            });

            // Define a Pie chart
            var pie = new google.visualization.ChartWrapper({
                'chartType': 'PieChart',
                'containerId': 'PieChartContainer',
                'options': {
                    'width': 600,
                    'height': 350,
                    'legend': 'right',
                    'title': chartTitle,
                    'chartArea': { 'left': 50, 'top': 15, 'right': 0, 'bottom': 0 },
                    'pieSliceText': 'label',
                    'tooltip': { 'text': 'percentage' }
                },
                'view': { 'columns': [0, 1] }
            });

            // Define a table
            var table = new google.visualization.ChartWrapper({
                'chartType': 'Table',
                'containerId': 'TableContainer',
                'options': {
                    'width': '300px'
                }
            });

            // Define a slider control for the Age column.
            var slider = new google.visualization.ControlWrapper({
                'controlType': 'NumberRangeFilter',
                'containerId': 'SliderContainer',
                'options': {
                    'filterColumnLabel': columnNames.split(',')[3],
                    'ui': { 'labelStacking': 'horizontal' }
                }
            });

            new google.visualization.Dashboard(document.getElementById('PieChartExample')).bind([categoryPicker,slider], [pie, table]).draw(data);
        }
        
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="PieChartExample">
            <table>
                <tr style='vertical-align: top'>
                    <td>
                        <div id="CategoryPickerContainer"></div>
                        <div id="SliderContainer"></div>
                    </td>
                </tr>
                <tr>
                    <td >
                        <div style="float: left;" id="PieChartContainer"></div>            
                        <div style="float: left;" id="TableContainer"></div>    
                    </td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

Notice that controls package is used here, instead of corechart, that was used in previous example.
drawVisualization is the function where all the magic happens. :)
Two classes are important in this example: ControlWrapper and ChartWrapper. ControlWrapper is a wrapper around a JSON representation of a configured control instance – in this case, the “filtering” controls: CategoryFilter (for choosing sex) and NumberRangeFilter (slider, for defining age range).
Finally, new instance od Dashboard class is created, and its bind method invoked.
(I strongly recommend experimenting in Code Playground, it’s very useful!)

As a result, we get something like this:

GoogleChartsExample

When an item is selected in gender drop down list, or the age range is changed, both pie chart and table values are filtered. It all happens on client and it’s very fast. Also, the design is lite and very Google-like, so users really like it!

Google Charts, ASP.NET, jQuery, AJAX – Quick & Easy

What to do when there is a requirement in your application to make a graphical representation of some statistical data? Well, there are a lot of controls you can use, depending on your needs.

In this post, I’ll show you how to quickly and easily introduce Google charts to your ASP.NET application, getting something like this as a result:

GoogleCharts

Also, I’ll be using jQuery and AJAX for fetching data, although they are not necessary, of course.

First of all, let’s define the data we want to show in the chart. This is really basic example, so my class looks something like this:

public class Data
{
    public string ColumnName = "";
    public int Value = 0;

    public Data(string columnName, int value)
    {
        ColumnName = columnName;
        Value = value;
    }
}

This is really all we need to display the chart: data caption and value.

Next, here is the method for getting the data from the server:

[WebMethod]
public static List<Data> GetData()
{
    List<Data> dataList = new List<Data>();

    dataList.Add(new Data("Column 1", 100));
    dataList.Add(new Data("Column 2", 200));
    dataList.Add(new Data("Column 3", 300));
    dataList.Add(new Data("Column 4", 400));

    return dataList;       
}

This is a web method, since I’ll make AJAX call from the client to get JSON object containing data to be displayed. Bear in mind that there is no need for serializing to JSON string – just make the method return the object, or in this case – list of objects.

As for the client script, let’s take a look at the AJAX method for fetching the data from the server:

  
$(document).ready(function() {
    $.ajax({
        type: 'POST',
        dataType: 'json',
        contentType: 'application/json',
        url: 'Default.aspx/GetData',
        data: '{}',
        success:
            function(response) {
                drawVisualization(response.d);
            }
        });
    })
})

Nothing new here – just defining which server method to invoke and what to do when the data is received.

We need to define where the chart will be drawn:

  
<div id="visualization" style="width: 600px; height: 400px;"></div>

To display the Google chart, we must load three libraries: the Google JSAPI API, the Google Visualization library and the library for the chart itself.

First of all, Google JSAPI API:

  
<script type="text/javascript" src="//www.google.com/jsapi"></script>

Next, load the Google Visualization and chart libraries:

  
<script type="text/javascript">
    google.load('visualization', '1', { packages: ['corechart'] });
</script>

Note:
- ‘visualization’ – loads the google.visualization library, which defines all the core utility classes and functions.
- ’1′ – visualization version to load (’1′ is current production version).
- { packages: ['corechart'] } – here we can define the list of all Google chart libraries needed. The ‘corechart’ library contains most basic charts, including pie chart, which we use in this example.

Finally, here is the function that will actually draw the chart:

  
function drawVisualization(dataValues) {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Column Name');
    data.addColumn('number', 'Column Value');

    for (var i = 0; i < dataValues.length; i++) {
        data.addRow([dataValues[i].ColumnName, dataValues[i].Value]);
    }

    new google.visualization.PieChart(document.getElementById('visualization')).
        draw(data, { title: "Google Charts Example" });          
}

The code is self-explanatory: the DataTable is initialized, the columns are added to it, and then the data is entered. Finally, new PieChart is initialized and its draw method is invoked.

The whole example:
cs:

 
using System;
using System.Collections.Generic;
using System.Web.Services;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }

    [WebMethod]
    public static List<Data> GetData()
    {
        List<Data> dataList = new List<Data>();

        dataList.Add(new Data("Column 1", 100));
        dataList.Add(new Data("Column 2", 200));
        dataList.Add(new Data("Column 3", 300));
        dataList.Add(new Data("Column 4", 400));

        return dataList;       
    }
}

public class Data
{
    public string ColumnName = "";
    public int Value = 0;

    public Data(string columnName, int value)
    {
        ColumnName = columnName;
        Value = value;
    }
}

aspx:

 
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.0/jquery.min.js" type="text/javascript"></script>
    <script type="text/javascript" src="//www.google.com/jsapi"></script>
    <script type="text/javascript">
        google.load('visualization', '1', { packages: ['corechart'] });
    </script>
    <script type="text/javascript">
        $(document).ready(function() {
            $.ajax({
                type: 'POST',
                dataType: 'json',
                contentType: 'application/json',
                url: 'Default.aspx/GetData',
                data: '{}',
                success:
                    function(response) {
                        drawVisualization(response.d);
                    }

            });
        })

        function drawVisualization(dataValues) {
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Column Name');
            data.addColumn('number', 'Column Value');

            for (var i = 0; i < dataValues.length; i++) {
                data.addRow([dataValues[i].ColumnName, dataValues[i].Value]);
            }

            new google.visualization.PieChart(document.getElementById('visualization')).
                draw(data, { title: "Google Charts Example" });          
        }

        
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div id="visualization" style="width: 600px; height: 400px;"></div>
    </form>
</body>
</html>

Google charts are easy to implement and highly customizable. I will be writing more about different types of charts and how to customize them, in my future posts.

Entity Framework and Setting Primary Keys on Views

When using Entity Framework, it’s important to set primary keys on table and view objects in SQL Server. If you skip this step, EF will define composite primary key made of all non-nullable columns belonging to the object. That way you can end up with some strange results and spend hours finding out what went wrong (trust me, I’ve been there). So, first of all: set primary keys in the database!

OK, with tables, that’s just it – set the primary keys and you’re good to go. Now, what about views? In case of views having primary keys defined in underlying tables, that should also be enough. But what to do if there are simply no columns to use as PK? Well, this can be achieved by using ROW_NUMBER() function. Here is the actual example from one of my projects:

CREATE VIEW [dbo].[vw_Action]
AS

SELECT *, ROW_NUMBER() OVER(ORDER BY ActionDate DESC) AS RowID
FROM
(
   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 1
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate

   UNION

   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 2
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, ActionDate

   UNION

   SELECT User1ID AS UserID, NULL AS FriendID, Object1ID, Object2ID, ActionType, ActionDate
   FROM vw_ActionBase
   WHERE ActionType = 3
   GROUP BY User1ID, Object1ID, Object2ID, ActionType, Object1ID, ActionDate

)x

Now, here’s something that made me confused: I’ve expected RowID column to be defined as NOT NULL. I thought that if there was a row, it should have a number. But I was wrong, as you can see:

vw_Action

So, the column I wanted to use as primary key (RowID) was nullable, and the other (ActionType) that shouldn’t be primary key was in fact the only PK candidate for Entity Framework. As expected, when I performed Update From Database option, the result in edmx file was:

<EntityType Name="vw_Action">
    <Key>
        <PropertyRef Name="ActionType" />
    </Key>
    <Property Name="UserID" Type="int" />
    <Property Name="FriendID" Type="int" />
    <Property Name="Object1ID" Type="int" />
    <Property Name="Object2ID" Type="int" />
    <Property Name="ActionType" Type="int" Nullable="false" />
    <Property Name="ActionDate" Type="datetime" />
    <Property Name="RowID" Type="bigint" />
</EntityType>

If you get tempted to set keys manually in EF, don’t! You will run into errors similar to these:

EFModel.Model.msl(266,10) : error 3002: Problem in Mapping Fragment starting at line 266: Potential runtime violation of table vw_Action’s keys (vw_Action.ActionType): Columns (vw_Action.ActionType) are mapped to EntitySet vw_Action’s properties (vw_Action.ActionType) on the conceptual side but they do not form the EntitySet’s key properties (vw_Action.RowID).\r\n\r\nEFModel.Model.msl(266,10) : error 3003: Problem in Mapping Fragment starting at line 266: All the key properties (vw_Action.RowID) of the EntitySet vw_Action must be mapped to all the key properties (vw_Action.ActionType) of table vw_Action.\r\n

Also, changes will be lost on next update from database.

So, what’s the solution?

The solution is to explicitly define columns you want for PK in SQL server as non-nullable using ISNULL function. Similary, if there is an actual non-nullable column that you want excluded from the PK, use NULLIF, like this:

CREATE VIEW [dbo].[vw_Action]
AS

SELECT UserID, FriendID, Object1ID, Object2ID, NULLIF(ActionType, -1) AS ActionType,
ActionDate, ISNULL(ROW_NUMBER() OVER(ORDER BY ActionDate DESC), -1) AS RowID
FROM
(
    ...
)x

What will be the result of this code? SQL Server will understand that RowID could never be NULL – it will be set to -1 in case of null value (although this will never happen in my case anyway). On the other hand, ActionType column, that can actually never be NULL since it has values 1, 2 and 3, will be interpreted as nullable.

Here is how it all looks in the end:

vw_Action_1

After updating model from the database, the right column will be set as entity primary key in edmx file.

——— EDIT ———
Related post: A Quick Note on COALESCE and ISNULL