Running SQL scripts in PowerShell

I had the requirement to create a power shell script that would query a SQL database.  It turned out to be very easy indeed…

# Create SqlConnection object, define connection string, and open connection
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=Livesqlserver; Database=WebCDB; Integrated Security=true"
$con.Open()

First create the connection…

$cmdSelect = "SELECT DATEDIFF(day, update_date, getdate()) as datedifference, DATENAME(dw, update_date) as theday , count(*)as TotalMails FROM  mail_tbl where sent=1 and mail_type='XTRANET' and  DATEDIFF(day, update_date, getdate()) < 7 group by DATEDIFF(day, update_date, getdate()), DATENAME(dw, update_date) order by datedifference desc"
$da = New-Object System.Data.SqlClient.SqlDataAdapter($cmdSelect, $con)

Create the SQL you want to return values on..

$dt = New-Object System.Data.DataTable
$da.Fill($dt) | Out-Null

Fill a dataset with the results

Foreach ($row in $dt.rows)
{  Write-Host $row.theday $row.TotalMails  }

Print out the results… easy

Workflow work-around made easy

I had a typical User request today, you know the type of thing “We have a rules that says ‘Reporting is always to Managers’, but we’ve these two people people who are ‘Manager’, but report to other ‘Managers’”.  “Simple”, I said", “Just don’t call them Managers”.. “Ahh yeah..but we can’t do that!” was the reply. You know yourself usual things we get thrown every now and again.  So I figured I could do something that was a little but better than just the usual big “If” statement, so I’ve implemented the Strategy Pattern.

Before the good stuff

The code used to look like this a big if statement which did a recursive call to itself, if the person was a Manager, Divisional Manager, Director or CEO then that was fine, if not try the next person.

/// <summary>
/// Finds the next in line by post ID.
/// </summary>
/// <param name="Id">The id.</param>
/// <returns></returns>
public ReportingStructure FindNextInLineByPostID(string Id)
{
IList<ReportingStructure> records = ReportingStructure.FindAll(Expression.Eq("PostId", Id));
if (records.Count > 0)
{   // OK now we have to check if this is a department or overseas Manager by the title
    if (records[0].JobTitleDescription.Contains("Department Manager") ||
        records[0].JobTitleDescription.Contains("Overseas Manager") ||
        records[0].JobTitleDescription.Contains("Divisional") ||
        records[0].JobTitleDescription.Contains("Director") ||
        records[0].JobTitleDescription.Contains("Chief Executive Officer")
        )
        return records[0];
    // not an exec so move up the line
    return NextInLine(records[0].ReportsToPostId);
}
return null;
}

So I figured, hey I’ll just hard code in the two exceptions; but then it occurred to me that this could (and probably would) change over time.  More exceptions would be added, more code and next year it might all change again.

Getting down with Strategy

This is really easy once you get your head around it.  First we create an abstract class with an abstract method.  This will be the blue-print for the class we’ll use for our reporting strategy.

abstract class ReportingStrategy
{
    public abstract ReportingStructure NextInLine(string Id);
}

Next we create a concrete class that contains the code we want to implement.

internal class Reporting2010 : ReportingStrategy
{
    public override ReportingStructure NextInLine(string Id)
    {
        IList<ReportingStructure> records = ReportingStructure.FindAll(Expression.Eq("PostId", Id));
        if (records.Count > 0)
        {   // OK now we have to check if this is a department Manager or overseas
            if (records[0].JobTitleDescription.Contains("Department Manager") ||
                records[0].JobTitleDescription.Contains("Department Manager") ||
                records[0].JobTitleDescription.Contains("Divisional") ||
                records[0].JobTitleDescription.Contains("Director") ||
                records[0].JobTitleDescription.Contains("Chief Executive Officer")
                )
                return records[0];
            // not an exec so move up the line
            return NextInLine(records[0].ReportsToPostId);
        }
        return null;
    }
}

As you will see this is the same workflow logic as before but now it’s out on it’s own in its own class.

Finally we update the context class to make use of this new object. I’ve clipped out all the other code to make it easier to read.

    public class ReportingStructure 
    {

        #region Private Members
        ……….

        private ReportingStrategy _reportingStrategy = new Reporting2010();
        #endregion

public ReportingStructure FindNextInLineByPostID(string Id)
{
    return _reportingStrategy.NextInLine(Id);

}

   }

So what does that give us?

Well, now if I want to make a change to the 2010 workflow I can simply update a small specific class, which can also be use in other places if needed.  More importantly however I can create any number of specific new workflows and rules and just swap out the private member to point to the right one.  I can even make it public or build it into the constructer to allow dependency injection.

e.g.   ReportingStructure reporting = new ReportingStructure (new Reporting2011());

or     ReportingStructure reprting = new ReportingStructure();
        reporting.Workflow = new (ReportingWhatEverIWant();

Charting in ASP.NET and Visual Studio 2008

I was doing some VS2010 migration research over the weekend and found that the Charting options available are really good, we could finally rid ourselves of our old Dundus Charting software.  My heart sank when I came back to the other problems in moving from VS2008 to VS2010, but after a bit of searching I found that all the charting options are backwardly compatible.

Things to install

You need to install two items onto your desktop development PC, both are very simple self extractors.  It would be best to close Visual Studio before doing this, but I don’t think it would make a lot of difference.

• Microsoft Chart Controls for Microsoft .NET Framework 3.5
• Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008

Following the installation you should now have a new Chart option available in your Data tool bar.

Using the new graph facilities

To create a simple graph I just created a new ASP.NET application with a single ASPX page called default. Dragging a chart from the toolbar to the design surface and switch to the source view will give you the code below.

<asp:Chart ID="Chart1" runat="server">
    <Series>
        <asp:Series Name="Series1">
        </asp:Series>
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1">
        </asp:ChartArea>
    </ChartAreas>
</asp:Chart>

First thing we need to do is add soem values.  I could do this a code behind, but for this demo I’ll just use the page code.

<asp:Series Name="Column" BorderColor="180, 26, 59, 105" YValuesPerPoint="2">
    <points>
        <asp:DataPoint YValues="45,0" AxisLabel="Jan" />
        <asp:DataPoint YValues="34,0" AxisLabel="Feb" />
        <asp:DataPoint YValues="67,0" AxisLabel="Mar" />
        <asp:DataPoint YValues="31,0" AxisLabel="Apr" />
        <asp:DataPoint YValues="27,0" AxisLabel="May" />
        <asp:DataPoint YValues="87,0" AxisLabel="Jun" />
        <asp:DataPoint YValues="45,0" AxisLabel="Jul" />
        <asp:DataPoint YValues="32,0" AxisLabel="Aug" />
    </points>
</asp:Series>

Here I’ve added 8 random numbers and given them a label for each month.  Next we add in the chart area code.

<asp:ChartArea Name="ChartArea1" BorderColor="64, 64, 64, 64" BorderDashStyle="Solid" BackSecondaryColor="White" BackColor="64, 165, 191, 228" ShadowColor="Transparent" BackGradientStyle="TopBottom">
    <area3dstyle Rotation="10" perspective="10" Inclination="15" IsRightAngleAxes="False" wallwidth="0" IsClustered="False"></area3dstyle>
    <axisy linecolor="64, 64, 64, 64">
        <labelstyle font="Trebuchet MS, 8.25pt, style=Bold" />
        <majorgrid linecolor="64, 64, 64, 64" />
    </axisy>
    <axisx linecolor="64, 64, 64, 64">
        <labelstyle font="Trebuchet MS, 8.25pt, style=Bold" />
        <majorgrid linecolor="64, 64, 64, 64" />
    </axisx>
</asp:ChartArea>

Doing a preview will give you the following error: “Error executing child request for ChartImg.axd”

So what went wrong?  Well, as all the charts are generated on the fly, we need to make a few changes in the Web.Config.  Add the following  lines and all should be well; 

Within <system.web><httpHandlers>, add the following: <add path="ChartImg.axd" verb="GET,HEAD" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false" />

Within <system.webServer><handlers>, add the following:

<add name="ChartImageHandler" preCondition="integratedMode" verb="GET,HEAD,POST" path="ChartImg.axd" type="System.Web.UI.DataVisualization.Charting.ChartHttpHandler, System.Web.DataVisualization, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />

Now when we run the programme, we get the following:

Putting on a bit more flash on our creation

Ok that graph looks a bit dull, so we add a legend and border by adding the following code to the page;

<legends>
    <asp:Legend IsTextAutoFit="False" Name="Default" BackColor="Transparent" Font="Trebuchet MS, 8.25pt, style=Bold"></asp:Legend>
</legends>
<borderskin skinstyle="Emboss"></borderskin>

Then we change the type to “Area” by modifying the series properties;

<asp:Series Name="Column" BorderColor="180, 26, 59, 105" YValuesPerPoint="2" ChartType="Area">

Now it looks like this:

Its simple easy to use and very powerful and should be compatible with SharePoint.  It may not be Sliverlight, but it work…

Getting Jiggy with Ajax and fields

I was asked to do something that I’ve done a million times before the other day, link a drop down field to a radio button so depending on what is selected the list of option should reduce. I decided to use Ajax and JQuery to do all the hard work and also because its really good at what it does.

The first requirement was to have a number of radio buttons on screen each showing the name of a department. These were very simple at first, not even taken from the database. So I created a very simple bit of HTML…

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Ajax Demo</title>
</head>
<body>
Corporate <input type="radio" id="radio1" name="BU" value="Corporate" /><br />
Food <input type="radio" id="radio2" name="BU" value="Food" /><br />
Investment <input type="radio" id="radio3" name="BU" value="Investment" /><br />
<select id="department">
<option value="0">-- No business unit selected --</option>
</select>
</body>
</html>

Nothing crazy here, just your basic setup, with three radio buttons and a select box with no values. It would look like this.

Adding the Ajax bit

Now we need to implement JQuery onto the page, and we do this by including the latest script file. You can get this on the internet, via a content delivery network or just download it. I’ve taken a copy as its quicker.

<script type="text/javascript" src="template/scripts/jquery-1.4.2.min.js"></script>

The line above adds the JQuery script into the page your are working with.

Below is the script to do all the wor, I’ve highlighted the interesting bit.

<script type="text/javascript" language="javascript">
$(document).ready(function() {
$("input[name*='BU']").click(function() {
callAjax($(this).val());
});
});

function callAjax(val) {

var selectedValue = val;
var servletUrl = '/OrganisationChartDataService.svc/Departments/'">http://<server>/OrganisationChartDataService.svc/Departments/' + val + '/JSon';

$.getJSON(servletUrl, function(options) {
var department = $('#department');
$('>option', department).remove(); // Clean old options first.
$.each(options, function(index) {
department.append($('<option/>').val(options[index].Id).text(options[index].Name));
});
});

}

</script>

The first section binds a “click” function to any element on the page with the name “BU”. This is handy because we don’t have to write a separate function for each element and if we add more we don’t have to do anything, it will just work.

The second highlighted bit is teh call to a WCF web service that takes in the name of a business Unit as being part of the URL, what’s returned can then be added to the select list.

The results can be see below, its fast simple and very powerful.

clicking another item shows this:

Old school CSV without creating a temp file

I had a request to export a report out to Excel today, which is something I’ve done a bunch of times before but always by producing a temporary file.  I figured I’d try something different, find a way to give me the same functions without having to worry about permissions on the file system, or deleting the files afterwards.

It turned out to be very easy… use a TextStream that writes out  a HTML header so Explorer does all the hard work.

On the ASPX page…

On the click event for the export button we turn off the usual ASP.NET view state stuff and make a new header with the content type of "application/vnd.ms-excel", this tells IE to start Excel regardless of the details sent.  Another interesting thing to see here is the Response.End call, you’ll need this to prevent ASP.NET sending the page refresh information along with your data.

protected void ExportButton_Click(object sender, EventArgs e)
{
      this.EnableViewState = false;
        Response.Clear();
        Response.Buffer = true;
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", "inline;filename=TeamExport.csv");
        team.ExportToExcel(Response.Output, true);
        Response.Charset = "";
        Response.End();
}

On the Business Object its a simple matter of building up an array of results, making sure to wrap them in “” for each field.  Then added this to the HTML TextStream …..

/// <summary>
/// Export Global Team lists to Excel
/// </summary>
/// <param name="httpStream">The HTTP stream.</param>
public void ExportToExcel(TextWriter httpStream)
{
     // find all associated applications
     IList<Application> applications =  (from application in Application.FindAllByProperty("GlobalTeam", Id)
             select application).ToList<Application>();

     foreach(Application app in applications)
     { 
         string[] dataArr = new string[]
             {
                 WriteableValue(app.Id),
                 WriteableValue(app.GlobalTeamName),
                 WriteableValue(app.Title),
                 WriteableValue(app.SupportManager),
                 WriteableValue(app.ProjectManager),
                 WriteableValue(app.StartDate.ToShortDateString()),
                 WriteableValue(app.EndDate.ToShortDateString())
             };
         httpStream.WriteLine(string.Join(",", dataArr));
     }
}

public static string WriteableValue(object o)
{
    if (o == null || o == null)
        return "";
    else
        return "\"" + o.ToString() + "\"";
}

Its good to rediscover something old, simple but yet works so well ….

More pain with Windows x64 migration

You have no idea how hard it was to get this to work!.  following on from my last posting I moved over from Windows XP to Windows 7 on x64 for development.  Although there was some pain in setting up the VB COMs it was nothing to the suffering when it comes to Registry settings.

Some applications were continually giving Generic 500 errors with little or nothing in the Event Logs, so I presumed it was a security problem.  Following many hours of messing about, it turns out I was on the wrong track altogether.  The specific COMs were failing because they were looking for Registry settings…

All the settings had been imported into their usual position under HKEY_LOCAL_MACHINE\SOFTWARE\SomeApplication, but no matter what I did the values always came back null. 

The solution

The answer was hidden away in a MS support article (http://support.microsoft.com/kb/896459)

32-bit programs and 64-bit programs that are running on an x64-based version of Windows operate in different modes and use the following sections in the registry:

• Native mode 64-bit programs run in Native mode and access keys and values that are stored in the following registry sub key:
  HKEY_LOCAL_MACHINE\Software
• 32-bit programs run in WOW64 mode and access keys and values that are stored in the following registry sub key:
  HKEY_LOCAL_MACHINE\Software\WOW6432node

So adding a copy of the REG settings to this second location made everything work again.

Setting up Windows 7 for Classic ASP

I’ve decided to move over from using the slow VPN to local development so decided to setup some old classic ASP applications on my local machine which is running IIS 7.5.  Unfortunately this process was far from easy.

Setting up IIS

First you need to be able to activate all the required roles.  You do this by selecting Control Panel/Programs/Turn Windows features on or off.  from the list you select the following:

Doing some configuration

Next you should create an Admin Console for you to work with.  Type  “MMC” into the start menu and select File/Add Remove Snap in from the menu.  Select IIS Manager and IIS Manager Ver.6, Also choose Event Log for the current machine.

File Save this to your desktop for later.

Getting it working

When I got a local copy of the application all the .NET code worked first time without any issue, however when I tried to run any Class ASP code I got a standard Error: This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying.

After some checking about on the net it turns out that you can turn on messages for ASP code using a configuration setting in IIS Manager,

Within IIS Manager Browse to the Virtual Directory you need and Double click click on the ASP icon and expand the “Debugging Properties” tree.

Turn on “Log Errors to NT Log” and “Send Errors to Browser”.

Unfortunately after doing this I still did not get an error messages displayed so had to do some more hunting down of the error.

Tracing requests

You also need to turn on Tracing which can be done by reading the instructions on the following link: http://learn.iis.net/page.aspx/565/using-failed-request-tracing-to-troubleshoot-classic-asp-errors/

Following this you actually get to see a correct error!

LineNumber:21
ErrorCode:800a01ad
Description: ActiveX component can't create object

Checking the global.asa gave me the following line:
Set GetDirectory = Server.CreateObject("EnterpriseIreland.BusinessObjects.HumanResources.Directory")

Checking Permissions

This error is usually down to permissions or the worker process can’t find the DLL.  Setting “Everyone” with Full permissions on the D:\Applications\ folder did not work, neither did giving “Everyone” access to the D:\Development folder.

Next I checked to make sure the DLLs were correctly registered by re-running  the “register_assembly.bat” command file located with in the D:\Applications\Common folder.

Next open Regedit.exe and browse to the DLL that was failing:
HKEY_CLASSES_ROOT\EnterpriseIreland.BusinessObjects.HumanResources.Directory open the CLSID key and you find a GUID.  In my case it was “{C940B037-A429-303E-8B2E-162E4E19AC91}” search the registry for this GUID.

You should find it somewhere in the HKEY_CLASSES_ROOT\Wow6432Node\CLSID\ key group; in my case it was here: HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{C940B037-A429-303E-8B2E-162E4E19AC91} click into the “InprocServer32” key and look for the value key “Codebase” this should show the file location of the DLL.  If it’s not pointing to the correct location change it.

Moving to App Pool

None of these fancy changes seemed to make any difference so I decided to look into the AppPool configuration.  The DefaultAppPool was working fine with .Net, so I created a second one based on this called “ASP”.

Next in the Advanced settings you need to make one minor but VERY IMPORTANT change;

You must set “Enable 32-bit Applications” to “True”

Finally assign your website to use this application pool by selecting it in IIS Manager and click Basic Settings in the Action Menu.

Click Select and choose “ASP” or whatever your AppPool name is called.

Success At last!!

After all this, we finally have a working legacy ASP site working on Windows 7.