Friday, November 19, 2010

Finally Developing For iOS

Here's a little post I made a while ago (which languished in draft for some time):
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...
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...

Friday, October 22, 2010

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));
}

Thursday, October 21, 2010

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
--------------------

Tuesday, October 12, 2010

Writing Windows Services

Just found a really neat (as in compact) post on writing Windows Services in C#:
And here's some Windows Services resources for doing different things in a service:

Friday, October 01, 2010

Monday, September 27, 2010

Monday, September 20, 2010

Wednesday, September 15, 2010

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:
Here's a post about writing your own:

Friday, September 10, 2010

Wednesday, September 08, 2010

Tuesday, September 07, 2010

The Register: iPad Apps Galore

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

Monday, September 06, 2010

LINQ2SQL Transaction

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

Wednesday, September 01, 2010

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:

Tuesday, August 31, 2010

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.

Friday, August 20, 2010

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);
}

Wednesday, August 18, 2010

Friday, August 13, 2010

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!

JQuery

A few JQuery links:

Wednesday, August 11, 2010

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:

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:
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...

Wednesday, July 21, 2010

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:
The company Frontier, started by David Braben which now continues the Elite legacy:
Ian Bell's Elite FAQ:

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:

Thursday, July 15, 2010

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:

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.

Wednesday, July 14, 2010

Google

How does Google work?...

Thursday, July 08, 2010

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:

ElementIndexParentElementFieldNameNodeTypeFieldValue
1mTimeelement10:22
1mAskelement1943.25
1mOpenelement1940.00
1mcodeattributeAL
2mTimeelement14:42
2mAskelement143.05
2mOpenelement230.50
2mcodeattributeAA

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:

Tuesday, July 06, 2010

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')
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:

IndexField NameField Value
1Type3M
1CodeAL
1Time1900-01-01T10:22:00
2Type3M
2CodeAA
2Time1900-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)
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

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:

I got the XML to paste properly by using this link to encode the XML into HTML:
  • http://centricle.com/tools/html-entities/

Friday, July 02, 2010

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:

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:

Thursday, July 01, 2010

Splitting Strings in T-SQL Using XML

Having this post:
And later finding this forum entry:
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);

Monday, June 28, 2010

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:
  • 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());


Friday, June 25, 2010

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:
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'')



Wednesday, June 23, 2010

Monday, June 21, 2010

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:
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...

Sunday, June 20, 2010

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 20th 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/

Friday, June 18, 2010

Calculate XML Element Depth

In XPath (from the excellent D.Pawson site):
In LINQ:
  • int depth = element.Ancestors().Count();

Thursday, June 17, 2010

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:
Please see other links on my blog for how to do more specific things; this is tech I use occasionally, but sometimes in detail.

Wednesday, June 16, 2010

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

Tuesday, June 15, 2010

My Favourite Chrome Extensions

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

Thursday, June 10, 2010

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:

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.