A couple of SharePoint 2003 notes

Disadvantages of STPS: You cannot use an stp in a site that is based on a different SharePoint definition. This is because an stp holds the differences between a specific list / site and a definition. Also stps have a size limit.

Accessing an SPListItem field through the object model: Note although your only meant to enumerate on display name of a field and position in array, you can also index array on internal name (seems to take precedent over display name / maybe this is dependant on order.

Various SQL Server Notes

Auto-increment field in SQL Server.
To create an auto increment field in SQL server, create an integer field and within its “Identity Specification” property group, set the following (note you may also want to set this field to be the primary key):
Is Identity – yes
Identity Increment – 1
Identity Seed – 1

UDL Files
An easy way to create a database connection string is to create a blank file with a .udl extension. When you open this file (by double clicking) you will be presented with dialog to help you create a connection string. When done, simple view the contents of the udl file in notepad to see the contents.

Simulating offset and limit with SQL Server 2005
In MySql, you can specify a limit and offset on a select statement. This is incredibly useful for pagination etc. In SQL Server, there is the TOP keyword which is equivalent to LIMIT, however there is no easy way of specifying an offset (well, none until SQL Server 2005).

In SQL Server 2005, a function named ROW_NUMBER has been introduced. This is used to indicate the position of records within a SELECT result. An example is set out below (demonstrating how to use ROW_NUMBER) for pagination. Note that with this example, if there are only 15 records in the overall query, only 5 will be returned.

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY orderByField ASC) AS RowNumber, *
FROM tableName)
SELECT *
FROM Ordered
WHERE RowNumber BETWEEN 10 AND 20

Note that when running this via the server explored in Visual Studio, I got a warning “The OVER SQL construct or statement is not supported.”, however it works anyway (and when executed within code, there are no issues)

There are ways of almost achieving a similar results in SQL Server 2000, although these are based on using the top keyword – the last page still always returns a fixed number of result when used for pagination, and not less as above. See http://josephlindsay.com/archives/2005/05/27/paging-results-in-ms-sql-server/ for details. (note that there’s an interesting post on here by Mathew Waters, although I couldn’t get his idea to work – as I already had an Identity field). Note that you could maybe use cursors to get the desired result.

Some related links…
http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
http://www.extremeexperts.com/SQL/Scripts/SQLRowNum.aspx

Delimiting
Fieldnames are delimited by [] in SQL Server (TSQL), ยด in MySql,
In both SQL Server and MySql, values are quote normally with single quotes. Note that this is irrelevant of field type (eg can still quote if field is an int). It is good practice to delimit fields and quote values to mitigate against SQL injection hacks.

SharePoint Issue Lists

An issue list is a special type of SharePoint list. Each time an issue is update, a historical list item is added to the list. The actual ID of the current issue item therefore never changes. The current item is flagged by an “IsCurrent” boolean field (read only via the SharePoint object model).

 
I initially thought that historical entries would be related via a guid based linked list; however this is not the case. When an issue is created, the issue is given 2 guids – one is the standard listitem “GUID” identifier (possessed by all list items in SharePoint) and the other “Related ID” is used to link related records. The related id stays constant over both the current and historical entry for an issue. The sequence of historical items is simply maintained by the time created or sequence within ids (I’m not sure which, and I guess its not important).

A basic example of how to add / update an issue using the SharePoint object model is available at http://net.bloggix.com/PermaLink,guid,aa4c6b98-2039-432b-af80-b8baf901fdf2.aspx

Using a multiline text box for logging

Just a quick note… if you have a winforms app, you can log what is happening to a multiline textbox (normally set to readonly). To ensure that the last message added is visible, run code as follows everytime you add a log message.

textBoxMessages.Text += string.Format("{0}\r\n", aMessage);
textBoxMessages.SelectionStart = textBoxMessages.Text.Length - 1;
textBoxMessages.ScrollToCaret();