Tuesday, September 11, 2012

AJAX, JSON, C#, and jQuery

So this is going to be a meaty post about the jQuery Ajax POST call with JSON objects and a C# method. As a .NET developer, this was key in developing web applications that were dynamic without page navigation. Many of these concepts can be translated into PHP development rather than C# but I haven't dealt with it much, though it will be something I'm looking into.

To start we need a situation... I want something that sends objects, manipulates objects, and returns objects, just so that we get the whole picture and you can see WHY we might want to do this. 

SITUATION: We have are retrieving a list of statuses per what database type a user selects. 

To start we have a DatabaseType enum in C# that will be loaded via the Ajax call. Here's that class:

 public enum DatabaseType
 {
      DBZero = 0,
      DBOne = 1,
      DBTwo = 2
 }

The nice thing about JSON objects is that they fit like a hand in a glove to many C# types as well as custom objects. So, lets create the Ajax call that will fit out "Database Type" selection into the enum, so we know which database we are pulling from.

Next let's take a look at the C# code. I use a repository to retrieve the statuses, but since that's not the focus of this blog, it's more like sudocode:


[HttpPost] // <-- Designates it's called via a POST method 
 public ActionResult GetStatuses(int databaseType)  
 {  
      SetDatabaseType(databaseType);  
      return Json(new { statuses = repo.GetStatuses().ToArray() });  
 }  

To start we use a function call that sets the database type (as unfortunately the variables do not stay even though the page hasn't changed), we then retrieve the statuses from the Repository, make it an array, and pass it back the page as a JSON.

In this example, I used an int in the parameters for this method, but a enum or special class could be used as well.

We then want to call this method from the page, when desired, so a function would be best:


 function GetStatuses()  
     {  
       $.ajax({  
         type: "POST",  
         url: "@Url.Action("GetStatuses")",  
         dataType: "json",  
         contentType: "application/json; charset=utf-8",  
         data: JSON.stringify( { databaseType: databaseType }),  
         success: function (result){   
           statuses = result.statuses;  
           $('#Statuses').empty();  
           for(var x = 0; x < statuses.length; x++)  
           {  
             $('#Statuses').append($('<option>').text(statuses[x].Name).val(statuses[x].Id));  
           }        
         },  
         error: function (error){  
           alert(error.responseText);  
         }  
       });  
     }  

Here we use jQuery's built in Ajax functionality to "POST" to the C# code. As you can see we specify the type as POST, the url as the Url to our method "GetStatuses", the datatype is JSON, and the data we send needs to be stringified into a JSON object. 

The second part is "success: " or what happens if it succeeds. In this example I empty a multiselect box, append the new options. As you can see, what you return is in the "result" object and that holds whatever you put into it. In this case, we put "statuses" into it, so to get them out we need to do "result.statuses".

The last piece is the error function, or what happens if it fails. For me, I just like a simple alert box, but the response text does contain HTML so some people like to replace the HTML of the body of the page with the information, but I think that's overkill.

Once again, if you have any questions, comments, or concerns, please post here or follow me on twitter @WebDevProblems.


Tuesday, August 28, 2012

EPPlus Pivot Charts?

I have been working extensively on trying to get a pivot chart working but I've come to the conclusion that it isn't possible but I wanted to ask anyone out there if they have gotten them to work. I am not talking about a normal chart, instead pivot charts are able to group via pivot tables. They are super easy in excel once you get the pivot table in, but seems to not be possible through EPPlus.

Any feedback would be appreciated.

Friday, August 24, 2012

SharePoint 2012: Clear/Empty a List

I took a business trip out to Omaha to automate some reporting and SharePoint is what they use so SharePoint is what I need to use. I was able to figure out how to clear or empty a list despite little to no help from any good online sources. Here is the code I used:

public void ClearList(string listName)
{
     List list = client.Web.Lists.GetByTitle(listName);

     ListItemCollection listItemColl = list.GetItems(CamlQuery.CreateAllItemsQuery());

     client.Load(listItemColl);
     client.ExecuteQuery();

     foreach (ListItem item in listItemColl)
     {
         list.GetItemById(item.Id).DeleteObject();
         list.Update();
     }

     client.ExecuteQuery();
}

Basically I have a method that takes in a list name, it then:
  1. Gets the list via "GetByTitle" method
  2. Gets all the items of that list and uses the CreateAllItemsQuery method of the CamlQuery class to get all the items.
  3. Load the items and execute.
  4. Loop through, get the item, delete the item, update the list.
  5. Finally, it executes and deletes everything off the list.
If you found this useful or have any questions please comment or message me, as well, follow me on twitter: @WebDevProblems.

Monday, August 20, 2012

Super Easy Web App Excel Data and Charting (via EPPlus) (.NET)

So for a while I reported only numbers in all the reports that were requested of me, and handed them off to someone else to format and work into charts. Well recently a co-worker was asked to provide a report with a graph daily on top of his normal, busy, workload. I thought this was an injustice, so I set out to find a way I could automate the report and thus I stumbled upon EPPlus, which happens to be a nifty Excel Exporter.

You can get EPPlus from here: http://epplus.codeplex.com/

For some background, this report is a comparison of Opened and Closed Tickets per day. This is how it's started:

ExcelPackage pck = new ExcelPackage();

pck.Workbook.Properties.Title = "Daily *** Closed/Open Tickets";
pck.Workbook.Properties.Company = "***";
pck.Workbook.Properties.Author = "*** Reporting";

var ws = pck.Workbook.Worksheets.Add("Daily *** Closed/Open Tickets");

ws.View.ShowGridLines = false;

ws.Cells[1, 1].Value = "Date";
ws.Cells[1, 2].Value = "Opened";
ws.Cells[1, 3].Value = "Closed";
ws.Cells[1, 4].Value = "Total Open";

We create an Excel Package and specify some other fields and we have thus started creating an Excel workbook! The Cell Values at the bottom of the code is where I set headers for the information. We then go ahead and populate the data. Don't forget that Excel is 1 based while Arrays and Lists are 0 based, plus there are the headers so you need to offset by 2:

for (int row = 2; row <= DateOpenClose.Count() + 1; row++)
            {
                for (int col = 1; col <= 4; col++)
                {
                    if (col == 1)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Date;
                    else if (col == 2)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Opened;
                    else if (col == 3)
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].Closed;
                    else
                        ws.Cells[row, col].Value = DateOpenClose[row - 2].TotalOpen;
                }
            }

Now for the fun stuff! Creating a clustered column chart, which in my opinion is a stupid chart to go with in this case but I am no business analyist so I do as I am told:

var chart = ws.Drawings.AddChart("Open Vs Closed *** Tickets", OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(600, 400);
chart.Series.Add(ExcelRange.GetAddress(2, 2, DateOpenClose.Count() + 1, 2), ExcelRange.GetAddress(2, 1, DateOpenClose.Count() + 1, 1)).Header = "Opened";
chart.Series.Add(ExcelRange.GetAddress(2, 3, DateOpenClose.Count() + 1, 3), ExcelRange.GetAddress(2, 1, DateOpenClose.Count() + 1, 1)).Header = "Closed";            
chart.Title.Text = "Open Vs Closed *** Tickets";

To break this down, we first create a chart and give it a name and type. Easy stuff so far. Then we set the position (according to cell), and set the size (pixel width by pixel height). Then we add series. This took a little while for me to get the hang of but the first parameter is the y values or the significant data, the second parameter is the x axis which is most of the time a time-frame or grouping of some sort. I also set the header for the data right there and round it out with the title.

This was much simpler than I was expecting and I am currently building a dynamic reporting site that will take advantage of this simplicity and will allow people to build charts into custom reports. Won't lie, it's going to be sexy. Please comment if you have any questions and follow my on twitter @WebDevProblems.

Saturday, August 18, 2012

5 Things About jQuery I Learned the Hard Way

Going Back to Basics


Going back to when I was first learning jQuery, I made some (looking back on it now) stupid mistakes I had terrible ways to fix them. Hopefully, if you're very new to jQuery, this list will help you!

1. jQuery isn't included in the project

This one was just stupid, whether it was caused by a misspelling or maybe thinking it automatically bundled into the site from a scripts folder, there was a time I would start coding and get frustrated that it wouldn't run my test program. If you are having problems with a quick test to show whether or not it's actually included in your project is this script:

$(function() { alert('jQuery is working!') });

This runs a jQuery function that will alert if jQuery is indeed included with the project or else the alert will not trigger.

2. Scripts can be debugged by Dev Tools

I use Chrome for all my development with a supplementary IE window to make sure my changes aren't screwing up IE (which they often do), but Chrome, I believe, has the best debugging tools out there. For a while, my debugging was with alerts. I was completely oblivious to the tools Google provided with Chrome. I would lay an alert, and if it didn't trigger, then there was an error before that point.

I am going to save you that trouble, pressing F12 will load the debugger which is ESSENTIAL. From there you can debug scripts, set breakpoints, see variable values at those breakpoints, run jQuery and javascript commands from the console (image you are looking for element with an id 'Clients' but it's not working in your code, just do $('#Clients') and it will pull up that element and make you realize you originally spelled Clients Cleints or something like that), and see load times.

3. jQuery can run slow on IE

I was building a forecasting site for how many agents our company needed from the historical data of how busy we were, and because they were not clear AT ALL about what they wanted, I had to use jQuery to do most of the grunt work or else redesign the backend which would have put me offschedule so I went ahead leaving the backend in place and let jQuery and Javascript run these equations. As well, at the change of a radio button they wanted to see different values, so that was included. Man did that thing drag in IE. In Chrome it was almost instantaneous, but in IE it took maybe 5 to 10 seconds to populate all the data. Was a little embarrassing when I presented and didn't really test it on IE, I got lucky it worked. This was one of my first projects though.

4. When building a plugin, .find is your friend

I was building a table making, sorting, heat-mapping, plugin that would help me stylize and generate sortable tables quickly (granted I was still really new to jQuery) but the hardest thing to overcome was how to manipulate an element after it was passed to the plugin. I had a "this" variable and I had no idea what to do with it, and it was such a simple concept that no one really knew what I was having a problem with. How I was searching through all the elements was through selectors i.e:
$('.options option:first')

Obviously an example, but it shows how I didn't understand that the find method is basically the same as that second part of the text. This is what it would look like with .find:
$('.options').find('option:first')

This way, if you are stuck with a $(this) object, you can use it and abuse it with .find (I do not advocate violence towards HTML elements).

5. jQuery really need to be run in a jQuery function

Going back to the first problem, that is a jQuery function. You can also use the document.ready one but trust me, that one in the top example is much easier and does the same thing. What it does is it waits for the DOM (HTML elements of the site) to load and then executes. If it doesn't do this then you can be running commands on elements that aren't there. Imaging the following code:
$('#Options').append($('<option>').text('Option').val('1'));

If you execute this before the DOM loads, before the element with the id 'Options' loads, then you are throwing that function away. You won't see any errors, but it won't append your list with that extra option. This can be really frustrating and a huge time waster.

Postmortem

To wrap up, jQuery was a bit to wrap my head around as mainly an application/mobile developer, but it really is a magnificent javascript extension and life is easier with it in the arsenal. If you have any questions don't hesitate to message me, comment, or direct message me on twitter @WebDevProblems.

Wednesday, August 15, 2012

#WebDevProblems

Hello all,

My name is Jonathon, I'm an associate web developer at a help desk company and this is my first job out of college. My college work was mainly in mobile/game/application development, but in this economy we must all adapt to survive.

I'm writing this because I don't have much support and a lot of things I have to learn on my own, and I want to share things I learn that I can't seem to find elsewhere. Sometimes the things I'll say are "no-brainers" but when your background isn't in web development they really aren't and might have been something I struggled with as a new web dev.

Some of the systems I work with and might right about are HTML, CSS, IE (and how it should burn), .NET, C#, jQuery, javascript, Razor, SQL, Informix SQL and ASP.NET MVC.

Stay tuned, and I hope I can provide some insight.

Also, please follow me on twitter: @WebDevProblems