Carl Sagan: Pale Blue Dot

Finally Developing For iOS

Here's a little post I made a while ago (which languished in draft for some time):

• http://springboardpillow.blogspot.com/2010/06/developing-for-iphone.html

And here's some links I've found recently, now that I've chosen a development platform for iOS - having decided that, essentially, re-learning ANSI C++ with a new IDE (that I can't find any straightforward videos for [the latest version of XCode works slightly - but importantly - differently to that demonstrated in all the online tutorials]) is not my cup of tea...

• GameSalad - no-code game builder:
  http://gamesalad.com/
• Appcelerator - javascript/HTML based apps for utilities, not games:
  http://www.appcelerator.com/
• Corona - 'lua' based 2D development, for 2D games and utilities. My weapon of choice:
  http://anscamobile.com/
• Cocos2D - Objective-C games framework for 2D games:
  http://www.cocos2d-iphone.org/about
• Unity3D - full 3D gaming development system, producing some awesome results:
  http://unity3d.com/
• Rhodes - for a mix of things, closest to Corona, I think. Ruby on Rails:
  http://rhomobile.com/products/rhodes/
• Sparrow - open source development for those who know a little Objective-C:
  http://www.sparrow-framework.org/
• Some very useful links if you're looking for a broad range of programming info:
  http://iphonedevdepot.com/tiki-index.php

I've found that for ease of learning the new language and a solid output, Ansca Mobile's Corona platform is performing well, has plenty of good tutorials and material and after the Apple iPhone Developer 1 year license ($99 in the USA, £59 in the UK), the Corona or Corona game license isn't too pricey. Though that might change soon...

Javascript Everyone Should Know - Iterating URL Args

Here's some javascript I'm often looking for, because I can't remember it off the cuff after 3 months of LINQ coding and I've started writing something where JQuery isn't available...

It iterates through all the parameters and their arguments passed in the URL...

var url = window.location.toString();
var query_string = url.split('?')[1];
var params = query_string.split("&");
for (var i = 0; i < params.length; i++)
{
var name = params[i].split("=")[0];
var arg = params[i].split("=")[1];
alert(i+': '+name+' = '+unescape(arg));
}

Simple Category Tree Walking In T-SQL

I needed, as many have done before me, to create a snippet of SQL which was short and fast and returned a flattened table of tree nodes. In short, if you have a tree of nodes, get all the nodes below a specified node. The result is surprisingly simple and straightforward. Here it is, both as a snippet and a stored procedure. Assume the tree is stored in a table called Category, with CategoryID being the primary key and ParentCategoryID being the foreign key, to the same table, but nullable...

--------------------

declare @collect table ( catid int, tlevel int )

insert into @collect values (3,1)

declare @count int

set @count = 2

declare @maxlevel int

set @maxlevel = 2

while ((select count(1) from @collect where tlevel = @count-1) > 0 and @count <= @maxlevel)

begin

insert into @collect

select c.categoryid, @count

from category c inner join @collect b on c.parentcategoryid = b.catid and b.tlevel = @count-1

set @count = @count + 1

end

select * from @collect order by tlevel

--------------------

--------------------

CREATE PROCEDURE [dbo].[GetCategoryTree]

-- Add the parameters for the stored procedure here

@ParentCategoryID int

AS

BEGIN

-- the category id's to be output

declare @collect table ( catid int, tlevel int )

insert into @collect values (3,1)

-- a loop counter used to limit the depth we go to

declare @count int

set @count = 2

-- the deepest level to go to

declare @maxlevel int

set @maxlevel = 2

-- the loop to collect category id's

while

(

(select count(1) from @collect where tlevel = @count-1) > 0 -- keep going while we have categories

and @count <= @maxlevel -- keep going until the max level depth is reached

)

begin

-- insert the categories where the previous level contains the parent ids of the current level

insert into @collect

select c.categoryid, @count

from category c inner join @collect b on c.parentcategoryid = b.catid and b.tlevel = @count-1

-- increment the level being collected

set @count = @count + 1

end

-- return the found category id's

select * from @collect order by tlevel

END

--------------------

Writing Windows Services

Just found a really neat (as in compact) post on writing Windows Services in C#:

• http://industriousone.com/blog/writing-windows-services-c

And here's some Windows Services resources for doing different things in a service:

• http://msdn.microsoft.com/en-us/vcsharp/aa336739.aspx

Present!

Get Rid.

Wisdom.

DateDiff

Recently, I had to work out the number of months between two arbitrary dates. In C# this is a bit of a challenge, simply because DateTime.Subtract(DateTime) returns a TimeSpan, which does not have a Months property.

This is because TimeSpan doesn't know which months it would be referring to. Specifically, to know how many months there are in a date range you need to count each month index from date A to date B. Problem being, if you don't know those arbitrary dates - you only have a count of days - how many months does that range cover?

Example: How many months does 30 days represent? Answer: 1.

Wrong.

Why?... Because if we are talking about February, it's two months. If we're talking about August it's not a whole month.

The solution is to take into account the specific dates being used in the subtraction and count their months, unfortunately. Yeah, I don't want to write that code either.

However, for the C#'ers among us, there is a solution. VB has a DateDiff class which can do this for you. Just use the Microsoft.VisualBasic namespace and the static DateDiff method, as I found out here:

• http://valcsgal.blogspot.com/2005/01/datediff-in-c.html

Here's a post about writing your own:

• http://aspcode.net/c-datediff

DataView, DataTable, Unique, Select And Filters

Found this awesome little cheat/info page on DataView et al:

• http://www.csharp-examples.net/dataview-rowfilter/

You Had Me At Brains And Squeegees

The Register: iPad Apps Galore

The Register has pulled together a collection of iPad apps for geeks, techies and web pros...

• SysAdmins
• Training and Editors
• Web Tools

LINQ2SQL Transaction

A good thread to read on LINQ to SQL transactions, centred on commits and rollbacks:

• http://stackoverflow.com/questions/542525/transactionscope-vs-transaction-in-linq2sql

Your Anonymous Survey Said...

You Don't Trust... Oh.

Why Not? "Wave-Enabled" Google Email Server

I was thinking that it's a shame that Google didn’t take the Wave, build it into their email server and have just an option that let you “email in real time”, ie: any email on their system could be seen to be edited in real time (as wave showed) but in the gmail editor.

The major work would be in the server, so that it is still an email system, exchange compatible and all, but also makes this new function available. Hopefully, people would use it more and more as rapid email repliers discover they are responding quickly.

Perhaps Google could then market their own email server as “Wave-enabled, MS Exchange-compatible”.

So simple really – read this:

• http://www.theregister.co.uk/2010/08/20/google_wave_lessons_learned/

ImageButton Without A SRC Value Causes GET Request!

Situation:

Click submit on a submit button.

Page posts back with IsPostBack == true.

Browser shows page render.

Problem:

Server (in debug mode) shows another request perform, this time a GET, so IsPostBack == false.

Evidence:

An ImageButton was on the page with no SRC attribute at all. This caused the browser (so far happening in (Windows...) Chrome, Firefox and (MAC...) Safari to resolve the URL of the image, for the image button, to be the same as the page the button is sitting on. The request for this then causes a standard GET request to the page and IsPostBack becomes false again.

Solution:

Remove the image button or give it a valid src attribute url.

Result:

Happy developer.

Javascript: Finding All The Properties Of A JScript Object

Just a little function to dump out into an alert all the properties, methods, etc of any javascript object...

function listout(obj) {

var keys;

for (var key in obj) {

keys += key + ' , ';

}

alert(keys);

}

I Found The Google

LINQ2SQL Exception: The query contains references to items defined on a different data context

When getting the exception

The query contains references to items defined on a different data context.

Check that the context object being used for each 'from' and 'join', etc, within LINQ statements is not a new object but the same one being re-used!

• http://stackoverflow.com/questions/3478148/two-linq-data-contexts-proving-a-problem

JQuery

A few JQuery links:

• JQuery site:
  http://jquery.com/
• JQuery UI building:
  http://jqueryui.com/
• 20 Advanced JQuery effects:
  http://www.webdesignerdepot.com/2010/07/20-demos-showing-advanced-jquery-effects/
• Setting cookies with JQuery:
  http://www.electrictoolbox.com/jquery-cookies/

Reset The Primary Key Of A Table

Lets say you've got 28 rows in your table but because you've been doing a lot of inserts and rollbacks the next key to be inserted is much higher than your highest primary key. This would be a problem if you've done a lot of inserts and have maybe reached the maximum value of the int type being used as the primary key field type.

You would want to reset the index (as all primary keys are special indices) like this:

DBCC CHECKIDENT ({tablename}, reseed, {number})

Don't make my initial mistake and think that the 'reseed' in the middle there is a place holder for a value - it isn't. Just leave it as is; it's a command. The operation is called reseeding, not resetting - but that will be all academic to most of us.

References:

• Insert:
  http://msdn.microsoft.com/en-us/library/ms174335.aspx
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=t-sql+insert
• Rollback:
  http://msdn.microsoft.com/en-us/library/ms181299.aspx
  http://www.google.co.uk/search?hl=en&q=t-sql+rollback&aq=f&aqi=g2g-c2g1&aql=&oq=&gs_rfai=
• DBCC CHECKIDENT:
  http://msdn.microsoft.com/en-us/library/ms176057.aspx
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=DBCC+CHECKIDENT
• Data type maximum values:
  http://www.tsqltutorials.com/datatypes.php
  http://www.google.co.uk/search?sourceid=chrome&ie=UTF-8&q=t-sql+int+maximum+value
• Pinal Dave:
  http://blog.sqlauthority.com/2007/03/15/sql-server-dbcc-reseed-table-identity-value-reset-table-identity/

LINQ: NOT IN / NOT EXISTS

So, while trying to get a fairly straight forward (I thought) select ... from ... where ... not in (...) I found that there is not really an equivalent in LINQ, or so I thought...

Read the solution here:

• http://programminglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx

A quick sample from me:

var orphans = (from c in context.Instances

orderby c.Title

where !(from o in context.CategoryInstances

select o.InstanceID)

.Contains(c.InstanceID)

select c).Skip(PageIndex * PageSize).Take(PageSize);

Essentially, the query does the same thing in C# - that is, to get the list of IDs to check within and then check within it for the ID which is hoping to be not found. Because the whole thing is compiled into SQL before before executed, LINQ generates a NOT EXISTS statement in the WHERE clause, rather than the slower NOT IN.

Concept Car Designs

Found this the other day and thought these were all really cool designs, possibly inspired by the forth-coming Tron movie...

• Interesting gallery of personal designs - http://www.mr-shojaie.com/
• Concept gallery - http://www.carbodydesign.com/gallery/2009/04/30-iran-car-design-exhibition/
• Favourite 1 - http://www.carbodydesign.com/gallery/2009/04/30-iran-car-design-exhibition/14/
• Favourite 2 - http://www.carbodydesign.com/gallery/2009/04/30-iran-car-design-exhibition/16/

Elite For The iPhone And iPad

Unfortunately, I cannot post a link to the game here - I do want to bung down as much as I can and hopefully one day link to it...

I would love to see the Archimedes version of Elite - in my opinion the best version, because it had solid 3D graphics and very smooth controls - ported to the iPhone and iPad, possibly with the iPhone being able to act as a remote control for the iPad running the game.

However, first, a link to nostalgia:

• http://www.iconbar.com/forums/viewthread.php?newsid=879

The company Frontier, started by David Braben which now continues the Elite legacy:

• http://elite.frontier.co.uk/

Ian Bell's Elite FAQ:

• http://www.iancgbell.clara.net/elite/faq.htm

For emulating the Acorn:

• http://arcem.sourceforge.net/

Server Error With Virtual Directory

Trying to put a virtual directory under a web site in WinSrv2003, I hit this problem:

Server Error in '/TestSubSite' Application.

---

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly 'SControls' or one of its dependencies. The system cannot find the file specified. (D:\web\testsite\web.config line 211)

Source Error:

Line 209: Line 210: Line 211: Line 212: Line 213:

Source File: D:\web\testsite\web.config Line: 211

---

Fortunately, the answer is to wrap the system.web element in a location element, with the attributes path="." and inheritInChildApplications="false".

I've documented this fun on stackoverflow.com:

• http://stackoverflow.com/questions/3297190/iis6-virtual-directory-not-accessible-as-an-app/3297257#3297257

Useful links:

http://forums.asp.net/t/1164283.aspx

http://dotnetslackers.com/Security/re-55457_Stopping_ASP_NET_web_config_inheritance.aspx

http://msdn.microsoft.com/en-us/library/ms178685.aspx

How To Get DateTime In XSLT

It's actually quite easy, though definately not obvious, to perform a fairly standard =DateTime.Now operation in XSLT, even when using C# (the default Microsoft XSL parser is only on version 1.)

Here's the XSLT:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:my="urn:sample" extension-element-prefixes="msxml">

<msxsl:script language="JScript" implements-prefix="my">
function today()
{
return new Date();
}
</msxsl:script>
<xsl:template match="/">

Today = <xsl:value-of select="my:today()"/>

</xsl:template>
</xsl:stylesheet>

Here's the C#:

/// <summary>
/// Takes a string containing XML to be parsed and a string containing the the XSLT to do the parsing.
/// </summary>
public static string XSLTransformation(string xml, string xsl)
{
// create the readers for the xml and xsl
XmlReader reader = XmlReader.Create(new StringReader(xsl));
XmlReader input = XmlReader.Create(new StringReader(xml));

// create the xsl transformer
XslCompiledTransform t = new XslCompiledTransform(true);
XsltSettings settings = new XsltSettings(false, true);
t.Load(reader, settings, null);

// create the writer which will output the transformed xml
StringBuilder sb = new StringBuilder();
XmlWriterSettings tt = new XmlWriterSettings();
//tt.Encoding = new UTF8Encoding(false);
XmlWriter results = XmlWriter.Create(new StringWriter(sb)); //, tt);

// write the transformed xml out to a stringbuilder
t.Transform(input, null, results);

// return the transformed xml
return sb.ToString();
}

References:

• http://stackoverflow.com/questions/1575111/can-an-xslt-insert-the-current-date
• http://jira.public.thoughtworks.org/secure/attachment/11154/CC.Net-XslTransformer.cs.patch

Every Project Fails

Every project fails - you and I both know it - in two places: the Beginning and the End. Both of these, as Douglas Adams put it, are due to sloppy thinking. The first is because the full scope of the thing is not realised. The second is because the full scope of the thing is realised. The are known, in order, as "not writing the spec properly" and "feature creep". Often, the spec is not written properly because it is perceived to have been written properly or that it is someone else's job to write it out more, read: "flesh it out", also read: "passing the buck". Further, the feature creep happens not because due diligence is not done, but because discipline is not employed in keeping the extra features out. Even further, those features only come about because it's easier to think of new things when you've had time to think about the existing things. Give a project 3 months to progress and I guarantee that everyone on that project will have thought about some feature that wasn't in the original (even aged-) concept, simply because they've had more time to think.

What can be done about these? Some would say agile development is one (of many) approach, providing the time to define a concept but with development time allotted for later to deliberately increase the feature set as time, or units of the project, get completed. This is fine, as long as the wider scope of the project is realised at the start.

Google

How does Google work?...

• How Google works: http://www.ppcblog.com/how-google-works/
• How Google ranks search results: http://www.google.com/technology/pigeonrank.html

SQL Knowledge Base

A collection of links to useful and oft-forgotten SQL info:

• Getting the last inserted key (identity):
  http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
• T-SQL FOR XML Basics:
  http://blah.winsmarts.com/2007-2-Bak2Basics__Learn_T-SQL_-_FOR_XML.aspx
• T-SQL XML Crib-sheet:
  http://www.simple-talk.com/sql/learn-sql-server/sql-server-xml-cribsheet/#fourth
• Robyn Page's SQL Articles:
  http://www.simple-talk.com/author/robyn-page/
• Never do this- Validating a URL in SQL:
  http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/validate-a-url-from-sql-server.aspx
• Reading XML files into table fields:
• http://stackoverflow.com/questions/3245301/using-t-sql-to-read-an-xml-feed-directly-into-an-xml-field-in-a-table
• SSIS XML Import tutorial video:
  http://msdn.microsoft.com/en-us/library/cc952927(SQL.100).aspx
• Processing XSLT in SQL Server:
  http://blogs.msdn.com/b/mrorke/archive/2005/06/28/433471.aspx
• Numbering Rows:
  http://blogs.x2line.com/al/archive/2005/11/18/1323.aspx
• Rollbacks:
  http://www.geekinterview.com/question_details/33247
  http://msdn.microsoft.com/en-us/library/aa175920(SQL.80).aspx

Parsing XML Into T-SQL Tables

Ok, some heavy SQLXML work here...

The aim was to make a query which would take any XML and list, in one table, all the elements, their attributes and their element and attribute values of the immediate child nodes of the root node. On top of that, each immediate child of the root had to be enumerated so it could be referenced properly when being inserted into another table/s.

Example input:

<root>
<date>07/07/10</date>
<start>07/02/10</start>
<m code="AL">
<Time>10:22</Time>
<Ask>1943.25</Ask>
<Open>1940.00</Open>
</m>
<m code="AA">
<Time>14:42</Time>
<Ask>143.05</Ask>
<Open>230.50</Open>
</m>
</root>

Example output:

ElementIndex	ParentElement	FieldName	NodeType	FieldValue
1	m	Time	element	10:22
1	m	Ask	element	1943.25
1	m	Open	element	1940.00
1	m	code	attribute	AL
2	m	Time	element	14:42
2	m	Ask	element	143.05
2	m	Open	element	230.50
2	m	code	attribute	AA

And finally, the SQL...

declare @xml XML
set @xml = 'YOUR XML HERE'

create table #t
(
rownum int,
xmlextract xml
)

-- populate the table with the child nodes of the root, if they have their own sub nodes
insert into #t
SELECT
row_number() over(order by getdate()) as 'RowNum',
pref.query('.') as XmlExtract
FROM
@xml.nodes('/*/*') AS extract(pref)
WHERE
pref.value('./*[1]', 'nvarchar(50)') IS NOT NULL

SELECT distinct rownum 'ElementIndex', ParentName 'ParentElement', FieldName, NodeType, FieldValue FROM
(
-- selects the attributes and their values from the xml
SELECT DISTINCT
rownum,
SubEl.SubElem.value('local-name(..)', 'nvarchar(50)') AS ParentName,
SubEl.SubElem.value('local-name(.)', 'nvarchar(50)') AS FieldName,
'attribute' as NodeType,
SubEl.SubElem.value('.', 'nvarchar(150)') AS FieldValue
FROM
#t
CROSS APPLY
xmlextract.nodes('//@*') AS SubEl(SubElem)
UNION
-- selects the inner element and their values from the xml
SELECT
rownum,
SubEl.SubElem.value('local-name(..)', 'nvarchar(50)') AS ParentName,
SubEl.SubElem.value('local-name(.)', 'nvarchar(50)') AS FieldName,
'element' as NodeType,
SubEl.SubElem.value('.', 'nvarchar(150)') AS FieldValue
FROM
#t
CROSS APPLY
xmlextract.nodes('/*/*') AS SubEl(SubElem)
) as tmp
ORDER BY ElementIndex, ParentElement, FieldName

drop table #t

However, for performance and tidy-up reasons, when using this code in a stored procedure, you may want to use a table variable. Here's the modified version:

declare @xml XML
set @xml = 'YOUR XML HERE'

declare @t table
(
rownum int,
xmlextract xml
)

-- populate the table with the child nodes of the root, if they have their own sub nodes
insert into @t
SELECT
row_number() over(order by getdate()) as 'RowNum',
pref.query('.') as XmlExtract
FROM
@xml.nodes('/*/*') AS extract(pref)
WHERE
pref.value('./*[1]', 'nvarchar(50)') IS NOT NULL

SELECT distinct rownum 'ElementIndex', ParentName 'ParentElement', FieldName, NodeType, FieldValue FROM
(
-- selects the attributes and their values from the xml
SELECT DISTINCT
rownum,
SubEl.SubElem.value('local-name(..)', 'nvarchar(50)') AS ParentName,
SubEl.SubElem.value('local-name(.)', 'nvarchar(50)') AS FieldName,
'attribute' as NodeType,
SubEl.SubElem.value('.', 'nvarchar(150)') AS FieldValue
FROM
@t
CROSS APPLY
xmlextract.nodes('//@*') AS SubEl(SubElem)
UNION
-- selects the inner element and their values from the xml
SELECT
rownum,
SubEl.SubElem.value('local-name(..)', 'nvarchar(50)') AS ParentName,
SubEl.SubElem.value('local-name(.)', 'nvarchar(50)') AS FieldName,
'element' as NodeType,
SubEl.SubElem.value('.', 'nvarchar(150)') AS FieldValue
FROM
@t
CROSS APPLY
xmlextract.nodes('/*/*') AS SubEl(SubElem)
) as tmp
ORDER BY ElementIndex, ParentElement, FieldName

References:

• Flattening XML:
  http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx
• Table variables:
  http://odetocode.com/code/365.aspx

N00BS

SQLXML Gymnastics

Producing well-formed XML from SQL:

SELECT

ID,

Name as 'Names/FirstName',

Surname as 'Names/LastName'

FROM

Names

FOR XML PATH('person'), ROOT('people')

• http://stackoverflow.com/questions/3184346/neat-xml-from-t-sql
• http://blah.winsmarts.com/2007-2-Bak2Basics__Learn_T-SQL_-_FOR_XML.aspx

Converting XML into a table:

Input:

<Elements>

<Element>

<Index>1</Index>

<Type>3M</Type>

<Code>AL</Code>

<Time>1900-01-01T10:22:00</Time>

</Element>

<Element>

<Index>2</Index>

<Type>3M</Type>

<Code>AA</Code>

<Time>1900-01-01T19:00:00</Time>

</Element>

</Elements>

SQL:

SELECT

r.value('Index[1]', 'int') [Index],

r.value('Type[1]', 'nvarchar(50)') [Type],
r.value('Code[1]', 'nvarchar(50)') [Code],
r.value('Time[1]', 'datetime') [Time]

FROM

@content.nodes('/Elements/*') AS records(r)

Converting XML into a pivot table:

Input:

<Elements>

<Element>

<Index>1</Index>

<Type>3M</Type>

<Code>AL</Code>

<Time>1900-01-01T10:22:00</Time>

</Element>

<Element>

<Index>2</Index>

<Type>3M</Type>

<Code>AA</Code>

<Time>1900-01-01T19:00:00</Time>

</Element>

</Elements>

SQL:

SELECT

El.Elem.value('(Index)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'varchar(100)') AS 'Field Name',

SubEl.SubElem.value('.', 'varchar(100)') AS 'Field Value'

FROM

@content.nodes('/Elements/Element') AS El(elem)

CROSS APPLY

El.Elem.nodes('*') AS SubEl(SubElem)

WHERE

SubEl.SubElem.value('local-name(.)', 'varchar(100)') <> 'Index'

Produces:

Index	Field Name	Field Value
1	Type	3M
1	Code	AL
1	Time	1900-01-01T10:22:00
2	Type	3M
2	Code	AA
2	Time	1900-01-01T19:00:00

Extracting attribute names (plus parent element name of each attribute):

SELECT

elem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

elem.value('local-name(.)', 'nvarchar(10)') AS 'Attribute Name',

elem.value('.', 'nvarchar(10)') AS 'Attribute Value'

FROM

@content.nodes('//@*') AS El(elem)

• http://stackoverflow.com/questions/3193956/how-to-get-every-attributes-name-value-and-parent-element-name-in-sqlxml

Extracting field names:

SELECT DISTINCT r.value('fn:local-name(.)', 'nvarchar(50)') FieldName

FROM @xml.nodes('/*/*') AS records(r)

Extracting nodes as XML:

SELECT

pref.query('.') as SomeXml,

FROM

@xml.nodes('/*/*') AS Content(pref)

Extracting nodes as XML with indices only if they have child nodes:

SELECT

row_number() over(order by cast(pref.query('.') as nvarchar(max))) as 'RowNum',

pref.query('.') as XmlExtract

FROM

@xml.nodes('/*/*') AS extract(pref)

WHERE

pref.value('./*[1]', 'nvarchar(10)') IS NOT NULL

• http://stackoverflow.com/questions/3195193/select-xml-nodes-as-xml-in-t-sql
• http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

Various extractions:

Given:

DECLARE @content XML

SET @content =

'<people>

<person id="1" bimble="1">

<firstname bobble="gomble">John</firstname>

<surname>Doe</surname>

</person>

<person id="2" bimble="11">

<firstname bobble="zoom">Mary</firstname>

<surname>Jane</surname>

</person>

<person id="4" bimble="10">

<firstname bobble="womble">Matt</firstname>

<surname>Spanner</surname>

</person>

</people>'

Return:

-- All attributes with parent element name

SELECT

elem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

elem.value('local-name(.)', 'nvarchar(10)') AS 'Attribute Name',

elem.value('.', 'nvarchar(10)') AS 'Attribute Value'

FROM

@content.nodes('//@*') AS El(elem)

-- Inner element values (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('*') AS SubEl(SubElem)

-- Second level element attributes (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('@*') AS SubEl(SubElem)

-- Third level element attributes (with index attribute)

SELECT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('*/@*') AS SubEl(SubElem)

-- All element attributes and parent element name (with index attribute)

SELECT DISTINCT

El.Elem.value('(@*)[1]', 'int') [Index],

SubEl.SubElem.value('local-name(..)', 'nvarchar(10)') AS 'Parent Name',

SubEl.SubElem.value('local-name(.)', 'nvarchar(10)') AS 'Field Name',

SubEl.SubElem.value('.', 'nvarchar(10)') AS 'Field Value'

FROM

@content.nodes('/*/*') AS El(elem)

CROSS APPLY

El.Elem.nodes('//@*') AS SubEl(SubElem)

ORDER BY [Index]

External references:

• http://beyondrelational.com/blogs/jacob/archive/2010/05/30/select-from-xml.aspx
• http://stackoverflow.com/questions/3180834/retrieving-xml-element-name-using-t-sql/3180868#3180868
• http://stackoverflow.com/questions/2266132/how-can-i-get-a-list-of-element-names-from-an-xml-value-in-sql-server
• http://www.stylusstudio.com/sqlxml_tutorial.html
• http://blogs.msdn.com/b/simonince/archive/2009/04/24/flattening-xml-data-in-sql-server.aspx

One last thing...

I got the XML to paste properly by using this link to encode the XML into HTML:

• http://centricle.com/tools/html-entities/

Some Tech Stuff I Really Must Read

Just links found today:

• http://www.dotnetcurry.com/ShowArticle.aspx?ID=515&AspxAutoDetectCookieSupport=1
• http://www.highoncoding.com/Articles/697_Persisting_CheckBox_State_While_Paging_in_GridView_Control.aspx
• http://csharpbits.notaclue.net/2010/06/securing-dynamic-data-4-replay.html
• http://dotnetslackers.com/articles/aspnet/ASP-NET-MVC-2-0-Areas.aspx
• http://www.c-sharpcorner.com/UploadFile/vendettamit/733/Default.aspx
• http://geekswithblogs.net/WinAZ/archive/2010/04/07/using-linq-distinct-with-an-example-on-asp.net-mvc-selectlistitem.aspx

Server Application Unavailable

I'm hoping that this post will one day come under the tag heading "problem solved" but for now it's going to be "problems."

This damn thing seems to crop up whenever you least expect it. There's multiple solutions, none guaranteed.

Essentially, the initial problem with be a message in big red letter which says "Server Application Unavailable"

This is basically a message from IIS saying you don't have permissions to see the proper error.

In my particular case, giving the directory hosting the web app full security permissions to the ASPNET user identity and dropping the IIS Virtual Directory (tab) Application Protection to Low allowed the true error to be seen.

This was: Could not load file or assembly 'System.Web.Extensions, Version=2.0.....' etc.

At the same time, the Event Viewer also started showing: Failed to execute the request because the ASP.NET process identity does not have read permissions to the global assembly cache

I have also tried referencing the correct DLLs in the project references as I had tried to correct these links to the up-to-date DLLs, however they should have been pointing at a very specific location, brought in by SVN.

Ok, so the secret to this particular mess seems to have been "Make sure you're referencing the right DLLs."

Now the only problem is to solve the code issues in the controls!

Resources looked at so far include:

• http://social.msdn.microsoft.com/forums/en-US/asmxandxml/thread/bf530e90-5d42-4909-a858-6be0ae28717d/
• http://www.velocityreviews.com/forums/t121570-asp-net-process-identity-does-not-have-read-permissions-to-the-global-assembly-cache.html

Adding And Subtracting Dates In T-SQL

This code will add 10 days to the current date:

@declare myDate datetime

set @myDate = (select dateadd(dd,10,getdate()))

select @myDate

This code will subtract one month from the passed in date:

@declare myDate datetime

set @myDate = (select dateadd(mm,-1,@dateArg))

select @myDate

Found at:

• http://www.java2s.com/Code/SQLServer/Date-Timezone/DATEADDaddorsubtractanumberofdaysmonthsoryearsfromaspecificdate.htm

Splitting Strings in T-SQL Using XML

Having this post:

• http://springboardpillow.blogspot.com/2009/04/splitting-strings-in-t-sql.html

And later finding this forum entry:

• http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic64815.aspx

I have come up with my own SQL which does not require a function in order to take a series of pairs of integers and split them into a two-field result set:

Which when run on it's own, will output this:

The effect here is that a string passed in as a parameter can contain pairs (rows) of integers; each column in the rows being separated by ':' and each row being separated by ','.

This is aggregated into a single XML element, which can then be parsed into specific types in a result set (table) format.

Note: Sorry for the images, but blogger.com's online editor would not let me paste the XML source.

Event Firing Delegate Handlers For Basic Use Controls

Sometimes it's nice and tidy and simply easy to create a small user control which can listen fire events when it does something. This requires the use of delegates, the format of which escapes me every single time. Argh!

Anyway, here's an example:

public delegate void ChartSelected(int chartId);

public ChartSelected onChartSelected = null;

So the parent control or page would assign a method to the onChartSelected delegate to be fired when, in this case, a chart is selected:

selector.onChartSelected = MyEventMethod;

And the method fired when called would look like the delegate identifier:

private void ChartSelected(int chartId)

{

// do something

}

Of course, the control declaring the delegate has to fire the event at some point:

if (onChartSelected != null)

onChartSelected(someSelectedValue);

More SQL XML, Making It Easier...

Well, I've previously used stored procedures to execute SQLXML queries, pulling the XML out using a standard SqlDataAdapter and concatenate the returned rows via a StringBuilder.

There is, however, a different mechanism to use:

• Microsoft.Data.SqlXml.SqlXmlCommand:
  http://www.microsoft.com/downloads/details.aspx?familyid=51d4a154-8e23-47d2-a033-764259cfb53b&displaylang=en

Some concerns:

• It does throw a large number of exceptions, so your code needs to be clean.
• You will need to remove System.Data.SqlClient because the new DLL replaces the namespaces found in the original DLL.

Example code:

using Microsoft.Data.SqlXml;

string connectionString = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;

SqlXmlCommand queryCommand = new SqlXmlCommand(connectionString);

queryCommand.CommandText = @"SQL * FROM something FOR XML";

SqlXmlParameter queryParameter = queryCommand.CreateParameter();

queryParameter.Value = 2860;

queryCommand.RootTag = "root";

queryCommand.XslPath = Server.MapPath("~/XSLT/formatter.xslt");

queryCommand.ClientSideXml = true;

XmlDocument xmlChartData = new XmlDocument();

xmlChartData.Load(queryCommand.ExecuteXmlReader());

Listing Table Column Names On One Line

I wanted to list out all the names of a table on one line, separated by tab characters, so that I could do a simple copy-paste from the SQL Server Management Studio into an Excel sheet - having each column name place itself conveniently into the next Excel column.

The code I came up with is a slight modification of a previous post:

• http://springboardpillow.blogspot.com/2010/05/concatenating-string-fields-in-t-sql.html

select STUFF(

(SELECT char(9) + name FROM

(SELECT c.name

FROM syscolumns c inner join sysobjects o on c.id = o.id

where o.name = 'your-table-name') AS Y

--ORDER BY names -- optional sorting of the column names

FOR XML PATH('')),

1, 1, N'')

Developing For iPhone

There's plenty of info out there about writing for iOS (as it's now called) the operating system which runs on iPhone, iPod Touch and iPad, so here's my list of stuff for writing Objective-C and non-Objective-C code for iOS:

Objective-C:

• http://itunes.stanford.edu/
• http://developer.apple.com/videos/

Non-Objective-C:

• http://www.saurik.com/id/5
• http://phonegap.pbworks.com/
• http://building-iphone-apps.labs.oreilly.com/ch07.html

Useful blog pages et al:

• http://dotnetaddict.dotnetdevelopersjournal.com/
• http://www.lostechies.com/blogs/scottcreynolds/archive/2009/02/05/beginning-objective-c-for-the-c-guy.aspx

Relevant Stackoverflow.com posts:

• http://stackoverflow.com/questions/tagged/objective-c+C%23
• http://stackoverflow.com/questions/1369078/how-does-objective-c-compare-to-c

Prince Of Persia For iPhone And iPad Is Awesomely 8 Bit

Having run across the re-release of the original Prince Of Persia on the iPhone App Store (under Featured) I found that it can be sync'd across to the iPad and then uses a whole other set of higher resolution graphics - making it perfect for full size retro gaming.

One post I found, after search for the age old "can't pick up sword!!!" problem was this:

• http://www.theiphoneguru.net/2010/05/28/prince-of-persia-retro-so-good/

However, click the "Controls" option on the main menu shows that the "action" button is in fact anywhere on the screen, that isn't already one of the four Up, Down, Left or Right buttons.

Here's some visual goodness for you to rest your eyes on - and remember, the one PoP app works on both iPhone and iPad, with improved graphics on the iPad!...

Prince Of Persia on iPhone...

Prince Of Persia on iPad...

L2B 2010 - It's Over!

It was long and it was kinda cold, actually, but it warmed up once we got to Brighton - yeah, afterwards!

Pictures from the day...

Here's the route we eventually took...

View L2B 2010 in a larger map

London To Brighton 2010

This Father’s Day, Sunday 20^th June, I am doing (again) the BHF London to Brighton 58 mile bike ride in a Morph Suit! It’s a grueling challenge filled with blood, sweat, tears, burgers, beer, steep hills and numb-bum-syndrome.

It really is a worthy cause – and I will look a complete prat - so please, if you can, follow this link and donate just a little towards my target for the British Heart Foundation…

http://original.justgiving.com/matthewwebster

Click here to see the estimated route!

View L2B2010 in a larger map

Footnote: Yes, numb-bum-syndrome is real: http://nutritionfitnesslife.com/numb-bum-syndrome/

Calculate XML Element Depth

In XPath (from the excellent D.Pawson site):

• select="count(ancestor::*)"

• http://www.dpawson.co.uk/xsl/sect2/N2193.html

In LINQ:

• int depth = element.Ancestors().Count();

Direct SQLXML Access And XSLt Using ADO.NET

If you want a quick and easy way to directly access a SQL 2005 DB, read the content in XML and render it from your website, here's how:

• ADO.NET access to the SQL:
  http://www.sharpdeveloper.net/content/archive/2007/06/09/how-to-use-sqldatareader-plus-source-code.aspx
• XSL Transformation:
  http://msdn.microsoft.com/en-us/library/ms345117(SQL.90).aspx
• Filling DataSets:
  http://msdn.microsoft.com/en-us/library/bh8kx08z.aspx
• The 'using' statement used appropriately with SqlConnection and SqlCommand:
  http://davidhayden.com/blog/dave/archive/2005/01/13/773.aspx

Please see other links on my blog for how to do more specific things; this is tech I use occasionally, but sometimes in detail.

Performing Queries With T-SQL DBs With Illegal Characters In Their Name

I've been trying to connect to a DB on a completely different server, using Microsoft SQL Server Management Studio, and discovering a big problem. The SQL Server I'm trying to query has a '-' character in it's name.

This is overcome using two methods:

1. Add the SQL Server to the local (on my development machine) SQL Server linked servers list.
   
   This is done by:
   
   Using the Object Explorer and opening "Server Objects -> Linked Servers", right clicking and selecting "New Linked Server".
   
   The important next step is that the "Linked server" field is filled out with the SQL Server name only, eg: SOMELIVEBOX-SQL5 and then the "SQL Server" radio button is selected.
   
   Next, on the left, select "Security" and choose "Be made using this security context:" and fill out the SQL Server login details.
   
   
2. Then the query, and this is only relevant if the SQL Server name has an illegal character (like '-') in it.
   
   Lets say the table is called "MyTable", is in a database called "MyDB", is in the server mentioned in point 1 and we want all the records from it...
   
   Open a new query window from inside the local database and type:
   
   SELECT * FROM [SOMELIVEBOX-SQL5].MyDB.dbo.MyTable

My Favourite Chrome Extensions

Yep, cos I keep forgetting, each time set up a new PC....

• Speed Dial
  https://chrome.google.com/extensions/detail/dgpdioedihjhncjafcpgbbjdpbbkikmi?hl=en-gb
  https://chrome.google.com/webstore/detail/speed-dial-3web/lceefillmbhhileboicaeakgcikdocmm?hl=en-US
• InstaPaper
  https://chrome.google.com/extensions/detail/acgdjjilmhiofacmdnmmlndeokamkkcl?hl=en-gb
• Tube Service Status Updates
  https://chrome.google.com/extensions/detail/dcbnpmjhmjhpaegikhpjlaemjiilnkgb?hl=en-gb
• CoolIris
  https://chrome.google.com/extensions/detail/noocneohefmdhonidldnlhaainpiomkp?hl=en-gb
• Fastest Chrome
  https://chrome.google.com/extensions/detail/mmffncokckfccddfenhkhnllmlobdahm?hl=en-gb
• Downloads
  https://chrome.google.com/extensions/detail/jfchnphgogjhineanplmfkofljiagjfb?hl=en-gb
• Quick QR
  https://chrome.google.com/extensions/detail/bcfddoencoiedfjgepnlhcpfikgaogdg?hl=en-gb
• GMail Checker Plus
  https://chrome.google.com/extensions/detail/gffjhibehnempbkeheiccaincokdjbfe?hl=en-gb
• Join Tabs
  https://chrome.google.com/webstore/detail/binjiceocgbfooocmheaenmmcominbpe
• XMarks Bookmark Sync
  https://chrome.google.com/webstore/detail/ajpgkpeckebdhofmmjfgcjjiiejpodla
• Page Snooze
  https://chrome.google.com/webstore/detail/bmhnhpfchoeofnjoobbhgihdkhllnfbc
• Print Friendly
  https://chrome.google.com/webstore/detail/jmplllfmgpkogegnjnkecmkdbeaeheop
• youR Here
  https://chrome.google.com/webstore/detail/fkandkgadbpobooanbmiiinlelnhbabm
• Edit This Cookie
  https://chrome.google.com/webstore/detail/edit-this-cookie/fngmhnnpilhplaeedifhccceomclgfbg

Can't Connect To Local SQLEXPRESS

This started occurring the other day and I was confused as to why the local database would not let me connect via standard Windows Authentication.

The answer turned out to be simply that the database had not been started as a Windows Service. This was because the Log On account it was trying to use did not have appropriate permissions. Not having another machine to check the correct settings, I decided that in the Services snap-in (Start -> Run -> services.msc -> SQL Server (MSSQLSERVER) & SQL Server (SQLEXPRESS)) I went to the Log On tab, selected "Local System account", hit "Apply" and then "Start" under the General tab.

For those two services, on my machine at least, this got everything working again. I was only able to discover this solution after googling and ending up here:

• http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/4d2ed764-e196-404c-a610-e10322b002cb/

In case the post with the answer disappears, here it is in full:

I encountered the exact same error. My work around to get the server back up and running is as follows. This applies to both the engine and the agent. The bug in this patch didn't affect the other SQL related services.

I changed the service account from the normal one to one that is local administrator to the server (me).

I was then able to start the services.

I went into the SQL Server 2005 Surface Area Configuration tool and changed Database Engine -> Remote Connections to use Local connections only.

I restarted the engine service.

I then changed it back to using Local and remote connections Using TCP/IP only.

I restarted the engine service.

I then changed both services back to their normal service account and all is well.

I could not get the patch to apply under any circumstances or configuration that I tried and have given up on it in hopes that MS releases a new one for this real quick.