21 Apr 2010

SQL Server 2005 - Snapshot Publishing Fun!

I tried to set up Publishing from my local SQL Server but it reckoned that the server name I was using was an alias. It also suggested that I use it's 'proper' server name, which I recognised as being the machine's name from ages ago before I changed it.

Turns out when I ran SELECT @@SERVERNAME , it was returning the OLD name.

The fix was to run this script (found here):


-- Use the Master database
USE master
GO

-- Declare local variables
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)

-- Get the value returned by the SERVERPROPERTY system function
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))

-- Get the value returned by @@SERVERNAME global variable
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)

-- Drop the server with incorrect name
EXEC sp_dropserver @server=@servername

-- Add the correct server as a local server
EXEC sp_addserver @server=@serverproperty_servername, @local='local'


The server needed a reboot afterwards to make this fix work.

24 Mar 2010

Export / Convert DataTable to Excel (XML format XLS file) in MVC

I see a thousand ASP.NET developers wanting to output an Excel-format document based on a lowly .NET DataTable.

I see people trying to output in CSV and then running into problems with Excel misinterpreting the cell datatype formatting.

I see people wanting to create an Excel Workbook with more than one Worksheet.

So, I present this! A little standalone class that lets you
  • create an Excel document (XMLSS format)
  • add as many Worksheets as you like by simply chucking DataTables at it
  • send it to the browser for download

Usage an an MVC Controller Action:

public void ExportData()
{
    DataTable dtYourData = YourApp.GetYourDataTable();
    ExcelWorkbookGenerator exGen = new ExcelWorkbookGenerator();
    exGen.AddWorksheet("YourWorksheetTitle", dtYourData);
    exGen.SendToBrowser("YourSuggestedFilename");
}

Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Web;

namespace DaddyCode.Utilities
{
    /// <summary>
    /// ExcelWorkbookGenerator : Generate Excel XML - compatible documents from DataTables.
    /// </summary>
    /// <remarks>
    ///      Author: James McCormack, DaddyCode Ltd
    /// </remarks>
    public class ExcelWorkbookGenerator
    {
        private class Worksheet
        {
            public string Title = "";
            public DataTable Data = null;

            public Worksheet(string title, DataTable dataTable)
            {
                this.Title = title;
                this.Data = dataTable;
            }
        }

        private List<Worksheet> Worksheets = new List<Worksheet>();

        /// <summary>
        /// Add a new Worksheet to the Workbook, based on a DataTable that you provide
        /// </summary>
        /// <param name="title"></param>
        /// <param name="dataTable"></param>
        public void AddWorksheet(string title, DataTable dataTable)
        {
            Worksheets.Add(new Worksheet(title, dataTable));
        }

        /// <summary>
        /// Send the current Workbook to the Web Browser to view or save the file
        /// </summary>
        /// <param name="suggestedFileName"></param>
        public void SendToBrowser(string suggestedFileName)
        {
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + suggestedFileName);
            HttpContext.Current.Response.Write(getWorkbookXML());
            HttpContext.Current.Response.End();
        }

        /// <summary>
        /// Generate an Excel-compliant XML Workbook
        /// </summary>
        /// <returns></returns>
        private string getWorkbookXML()
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.AppendChild(xDoc.CreateNode(XmlNodeType.XmlDeclaration, null, null));

            string strCustomNamespace = "urn:schemas-microsoft-com:office:spreadsheet";

            XmlElement root = xDoc.CreateElement("Workbook");
            root.SetAttribute("xmlns", strCustomNamespace);
            xDoc.AppendChild(root);

            XmlElement styles = xDoc.CreateElement("Styles");

                XmlElement styleBold = xDoc.CreateElement("Style");
                    XmlElement font = xDoc.CreateElement("Font");

                    XmlAttribute xStyleBoldID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleBoldID.Value = "dc1";
                    styleBold.Attributes.Append(xStyleBoldID);

                    XmlAttribute xFontWeight = xDoc.CreateAttribute("dc", "Bold", strCustomNamespace);
                    xFontWeight.Value = "1";
                    font.Attributes.Append(xFontWeight);

                    styleBold.AppendChild(font);
                styles.AppendChild(styleBold);

                XmlElement styleDateTime = xDoc.CreateElement("Style");
                    XmlElement numberFormat = xDoc.CreateElement("NumberFormat");

                    XmlAttribute xStyleDateTimeID = xDoc.CreateAttribute("dc", "ID", strCustomNamespace);
                    xStyleDateTimeID.Value = "dcDateTime";
                    styleDateTime.Attributes.Append(xStyleDateTimeID);

                    XmlAttribute xStyleNumberFormat = xDoc.CreateAttribute("dc", "Format", strCustomNamespace);
                    xStyleNumberFormat.Value = "General Date";
                    numberFormat.Attributes.Append(xStyleNumberFormat);

                    styleDateTime.AppendChild(numberFormat);
                styles.AppendChild(styleDateTime);

            root.AppendChild(styles);

            // Populate worksheets

            foreach (Worksheet wSheet in Worksheets)
            {
                XmlElement worksheet = xDoc.CreateElement("Worksheet");

                XmlAttribute xSheetTitle = xDoc.CreateAttribute("dc", "Name", strCustomNamespace);
                xSheetTitle.Value = System.Text.RegularExpressions.Regex.Replace(wSheet.Title, "[^a-z0-9 -]", "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                worksheet.Attributes.Append(xSheetTitle);

                XmlElement table = xDoc.CreateElement("Table");

                // Populate header row

                XmlElement header = xDoc.CreateElement("Row");
                XmlAttribute xHeaderStyle = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                xHeaderStyle.Value = "dc1";
                header.Attributes.Append(xHeaderStyle);

                foreach (DataColumn col in wSheet.Data.Columns)
                {
                    XmlElement headerCell = xDoc.CreateElement("Cell");
                    XmlElement headerData = xDoc.CreateElement("Data");
                    headerData.InnerText = col.ColumnName;

                    XmlAttribute xHeaderDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);
                    xHeaderDataType.Value = "String";
                    headerData.Attributes.Append(xHeaderDataType);

                    headerCell.AppendChild(headerData);
                    header.AppendChild(headerCell);
                }
                table.AppendChild(header);

                // Populate data rows

                foreach (DataRow drData in wSheet.Data.Rows)
                {
                    XmlElement row = xDoc.CreateElement("Row");

                    foreach (DataColumn col in wSheet.Data.Columns)
                    {
                        XmlElement cell = xDoc.CreateElement("Cell");
                        XmlElement cellData = xDoc.CreateElement("Data");
                        XmlAttribute xCellDataType = xDoc.CreateAttribute("dc", "Type", strCustomNamespace);

                        if (drData[col.ColumnName] == DBNull.Value)
                        {
                            cellData.InnerText = "";
                            xCellDataType.Value = "String";
                        }
                        else
                        {
                            switch (col.DataType.Name)
                            {
                                case "Single":
                                case "Double":
                                case "Decimal":
                                case "Int16":
                                case "Int32":
                                case "Int64":

                                    cellData.InnerText = drData[col.ColumnName].ToString();
                                    xCellDataType.Value = "Number";
                                    break;

                                case "DateTime":

                                    XmlAttribute xCellStyleID = xDoc.CreateAttribute("dc", "StyleID", strCustomNamespace);
                                    xCellStyleID.Value = "dcDateTime";
                                    cell.Attributes.Append(xCellStyleID);
                                    if (drData[col.ColumnName] != null 
                                            && drData[col.ColumnName] != DBNull.Value
                                            && (DateTime)drData[col.ColumnName] != DateTime.MinValue)
                                    {
                                        cellData.InnerText = ((DateTime)drData[col.ColumnName]).ToString("o");  // ISO 8601 DateTime String Format
                                    }
                                    xCellDataType.Value = "DateTime";
                                    break;

                                case "Boolean":

                                    cellData.InnerText = (bool)drData[col.ColumnName] ? "1" : "0";
                                    xCellDataType.Value = "Boolean";
                                    break;

                                default:

                                    cellData.InnerText = drData[col.ColumnName].ToString(); // XmlElement.InnerText escapes reserved XML characters automatically
                                    xCellDataType.Value = "String";
                                    break;
                            }
                        }

                        cellData.Attributes.Append(xCellDataType);
                        cell.AppendChild(cellData);
                        row.AppendChild(cell);
                    }

                    table.AppendChild(row);
                }

                worksheet.AppendChild(table);
                root.AppendChild(worksheet);
            }

            StringWriter swOut = new StringWriter();
            xDoc.Save(swOut);

            return swOut.ToString();
        }
    }
}

If you're reading this and know a better way to do this sort of thing - PLEASE LET ME KNOW. I was driven to this because of the crap documentation on the web. I only achieved this limited success by reverse-engineering an existing Excel doc and latterly discovering the MS XML Spreadsheet Reference. Why they don't tell you that the XMLSS DateTime format is ISO 8601, I don't know...

21 Jan 2010

ASP.NET GridRow - Get Cell when you don't know the index - using HeaderText, DataField, SortExpression etc.

Sometimes you want to reference a cell in a gridview row, but you don't know its ordinal index, so Row.Cells[x] is no good for you. In this circumstance it would be nice to say something like "just get me the cell from the column with the HeaderText value 'Price'".

Voila:


public static int GetCellIndexByFieldHandle(this GridView grid, string fieldHandle)
{
int iCellIndex = -1;

for (int iColIndex = 0; iColIndex < grid.Columns.Count; iColIndex++)
{
if (grid.Columns[iColIndex] is DataControlField)
{
DataControlField col = (DataControlField)grid.Columns[iColIndex];
if ((col is BoundField && string.Compare(((BoundField)col).DataField, fieldHandle, true) == 0)
|| string.Compare(col.SortExpression, fieldHandle, true) == 0
|| col.HeaderText.Contains(fieldHandle))
{
iCellIndex = iColIndex;
break;
}
}
}
return iCellIndex;
}


Usage:


void myGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
TableCell cellPrice = e.Row.Cells[e.Row.GetCellIndexByFieldHandle('Price')];
}
}


The method works for column HeaderText, DataField and SortExpression, so you should always have a way to grab hold of that cell. One caveat - it can't reference AutoGenerated columns. They have to be defined in the GridView template a la:


<Columns>
<asp:BoundField DataField="Price" />
</Columns>


or


<Columns>
<asp:TemplateField HeaderText="Price">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>

A JQuery function to set the value of all input controls which have an ID containing a given string


function setAll(idFilter, value) {

$('input[id*=' + idFilter + ']').each(function() {
$(this).val(value);
});
}


JQuery has some superduper wildcard attribute selectors like *= (contains), ^= (begins with) and $= (ends with). More here.

19 Jan 2010

iPhone on Orange - Cellular Data Network problem

I have an iPhone 3GS on the Orange UK network. It stopped being able to access the internet by any means other than wifi. 3G, Edge, GPRS - none of it worked.

I phoned Orange support - they said that a Carrier Settings update had screwed up the configuration. Normally, you can manually configure your phone in the Settings > General > Network > Cellular Data Network menu, but the Orange carrier settings update restricts your access to this.

To sort the problem, I had to do a full restore, allow the iPhone to reconnect with Orange via iTunes, but then deny the requests to update the Carrier Settings. By doing this, the Cellular Data Network menu was restored and I was able to set the APN correctly to orangeinternet (no user/pass required). Now internet could be accessed by the cellular data network again.

After a few days I did another sync with iTunes and this time when the carrier settings prompt appeared, I allowed it to update. Again, the Cellular Data Network menu was removed and although the phone continued to be able to access the internet without WIFI, I was annoyed to discover that the phone seemed to have had its MMS settings removed - it had even removed the option to send a photo as an MMS.

Before I resorted to doing another restore, I tried turning the phone off and on again - hey presto, MMS came back and all was okay again. Phew!

3 Jan 2010

Addictive Drums + Reaper + Roland TD3 VDrums + MIDI UM-1G USB

Maybe you're a daft sod like me that doesn't read the instructions, but I had a few hurdles setting up this combo. So here's some tips.

1. Attach the UM-1G unit MIDI OUT cable to the MIDI OUT socket on the TD-3.
2. Install the UM-1G drivers in Windows and then attach the UM-1G USB cable.
3. Download ASIO4ALL v2 and install.
4. Install Addictive Drums.
5. Fire up Reaper.
6. In Preferences > Audio > MIDI Devices, Enable the UM-1G.
7. In Preferences > Audio > Device, Select Audio System : ASIO and choose Asio4All as the Driver.
8. Create a new track and arm it. Turn record monitoring on. Select MIDI > UM-1G > All Channels as the Input source. Enter FX, add the Addictive Drums VSTi.

At this point you should be getting sound out of your drums.

If you can't find the Addictive Drums instrument, maybe you should locate the vst file and place in the Reaper VST path?

If the MIDI map is wrong for your kit, go into the AD VST and click the "?" button - open the Map Window. Under Map Preset, choose Roland > TD-3. You can do further custom mapping by creating a Reaper JS file in the Program Files / Reaper / Effects folder. I found a couple of useful ready-made ones here.

I now realise that ASIO4ALL grabs the sound device and stops other apps like Windows Media Player from using it - annoying when you want to jam. I don't know a way round it, can anyone shed some light? At the moment it's okay cos I've hooked up my GuitarPort and am using it's ASIO driver instead of ASIO4ALL.

Comments / Questions appreciated!

LogiTech S530 Keyboard + Mouse For Mac - On Windows

Quick one - if like me you're using a Mac with Bootcamp and wondered if your Logitech S530 keyboard/mouse combo would work okay with Windows (and Win 7 in my case) - the answer is yes. It works fine out of the box.

Even better news is it works with the S510 drivers (SetPoint suite) if you download them off the Logitech site here.

When you do that all the cool shortcut buttons work as intended - volume control, quicklaunch etc.

Hope that helps someone!

22 Dec 2009

HttpHandler EnableEventValidation Error

I wrote a HttpHandler to do some crazy thing or other. Put it in web.config's <httpHandlers> like this:


<add path="*/Pages/Review/*/*.aspx" verb="*" type="Freda.Classes.v3ReviewScreenHandler" />


Had to handle it in IIS7 too (or integrated pipeline mode on my workstation), so had to stick it in the <system.webServer>/<handlers> section like this:


<add name="v3ReviewScreenHandler1" path="*/Pages/Review/*/*.aspx" verb="*" type="Freda.Classes.v3ReviewScreenHandler" resourceType="Unspecified" preCondition="integratedMode" />


And to make both play nicely together, under <system.webServer> I had to also add:


<validation validateIntegratedModeConfiguration="false" />


The handler in this case is basically just a subclass of System.Web.UI.Page that dynamically loads a UserControl based on the requested URL path, or otherwise processes an ASPX page, which I did like so:


public override void ProcessRequest(System.Web.HttpContext context)
{
string candidateControlFilePath = context.Request.Path.Replace(".aspx", ".ascx");

if (System.IO.File.Exists(context.Request.MapPath(candidateControlFilePath)))
{
// Load Screen Control if available (the groovy new way)
base.ProcessRequest(context);
}
else
{
// Revert to aspx mode (the non-bulk-printable way)
Page page = (Page)System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(context.Request.Path, typeof(System.Web.UI.Page));
page.AppRelativeVirtualPath = context.Request.AppRelativeCurrentExecutionFilePath;
page.ProcessRequest(context);
}
}


And the "Load User Control" bit was like so:


protected override void OnInit(EventArgs e)
{
base.OnInit(e);

// Load ReviewScreen control
string candidateControlFilePath = Request.Path.Replace(".aspx", ".ascx");
v3ReviewScreen uctl = (v3ReviewScreen)LoadControl(candidateControlFilePath);
this.Master.MainContentPlaceHolder.Controls.Add(uctl);
uctl.Setup();
}


And to make the Handler play nice with Sessions it was declared like so:


public class ReviewScreenHandler : System.Web.UI.Page, System.Web.SessionState.IRequiresSessionState


And all was dandy! Except for some funky GridView Row-Rendering stuff that was working in normal ASPX pages but not in my HttpHandler-based pages, producing this error:


RegisterForEventValidation can only be called during Render();


Which was weird, because the code to supress this sort of thing was already in web.config:


<pages enableEventValidation="false" validateRequest="false">


And I tried setting EnableEventValidation programatically in the HttpHandler's OnInit method, but got:


The 'EnableEventValidation' property can only be set in the page directive or in the configuration section.


And in the end the only solution was:


protected void Page_PreInit(object sender, EventArgs e)
{
Page.EnableEventValidation = false;
}


Which, called by magic and with no override or event handler to obviously hang off, is stupid. But works.

Mac OSX Bootcamp XP funtime

I got a Mac. I always said I wouldn't. And now I have. A little Mac Mini 2.53Ghz Intel Core 2 Duo 4Gb. Just for research, you understand ;) And cos I fell in love with my iPhone 3GS. Damn it.

What I found out so far:

- Snow Leopard is teh sexeh.
- Objective-C sucks monkey balls.

Anyway, I wanted to set up a little XP installation so I could see how well Windows runs on a Mac, and to do stuff that the mac just won't do, like Visual Studio 2008 (soooo much better than XCode).

I thought I would be smart and use the OSX Disk Utility to partition the drive 3 ways so I could have a main mac partition, a FAT32 partition to share my music on, and a NTFS partition for XP. But all my manual blunderings and attempts with Bootcamp just shagged it up, and ended up with "Disk error" on boot-up. Thank goodness for holding down the Option key on reboot to choose the boot drive. I had to use Disk Utility to create one big contiguous Mac partition again.

In essence then, to get XP running happily:

1. In OSX run Boot Camp Assistant and create a Windows Partition (I chose 32GB)
2. Put the XP disk in the drive when it tells you and start the Windows installation process.
3. When the XP installer asks what partition to put it on, choose your new Bootcamp partition and SELECT NTFS FORMAT. *Not* Quick Format, Fat32 or "Preserve Existing". It must do it the long winded way.

Anyways, it works. I was very impressed by the XP drivers Apple included on the main OSX disc. XP runs fabbo on the Mac Mini, and it picked up my Logitech wireless keyboard/mouse combo without a hitch, even though the box for them says OSX only.

Oh, almost forgot. After installing the Apple XP drivers I could read files off the main OSX drive from XP with no problems (Read Only). I thought that wasn't possible! Well chuffed.

Toodles.
If I helped you out today, you can buy me a beer below. Cheers!