Saturday, 31 January 2009

Testing for Database Nulls in C#

Things I always forget

  1. Mother’s Day (sorry mum)

  2. My mobile phone telephone number

  3. The 7 times table

  4. How to test for Database nulls

There’s not much hope for the first three – however maybe a quick blog might help with the last one

How I always try to test for database nulls


If(drDataRow["FieldName"] == null)

{

//Nope this never works. I won’t hit this

}

How I should test for database nulls


If(drDataRow["FieldName"] is DBNull)

{

//It works! Shirt off and do the victory dance

}

Now if I can just work on those times tables ………

Wednesday, 28 January 2009

Which Building is your Software?

It’s been (excessively) often noted that software is like a building. We do software construction, there’s software architects, we all wear hard hats at work (maybe that’s just my office though) and so on. So, which building exactly is your software most like?




The Pyramids
Software of great age built with ancient technologies (probably COBAL). Everyone admires it but no-one dare touch it and certainly no-one would every dream of extending it. It’s stood for years but for God sake don’t mess around with it.





Humber Bridge
An impressive feat of engineering. Ahead of its time but built for all the wrong reasons. Looks great but is an enormously over engineered solution to a problem that never existed in the first place.


The Gherkin
An impressive feat of architecture that is the top of its class. Has rapidly become iconic and sets the benchmark for all other endeavours in that field. Makes everyone one else feel a tiny bit anxious and inadequate.








The Sears Tower
Built by men, for men for a man’s reason. Huge, thrusting, enormous but pretty much bankrupts any company involved in it. If the project survives and doesn’t bring everyone involved down with it – it will be truly amazing. Brilliant but at great human cost.







Lumiere Tower (Leeds, UK)
Ambitious, stylish and visionary. All the design work done and the foundation laid but then suddenly cancelled. Like the Sears Tower but someone took a brave decision before it bankrupted everyone involved.





The Towering Inferno
A fantastic project. State of the art and impressive. Everyone wants to be associated with it. However fatal construction flaw comes to light too late and the entire thing bursts into flames. Sticking plasters required.






Grove Pub (Leeds, UK)
The tiniest end of terrace pub next to the biggest building in the city. A well established but minor project much loved by a small user base. Operates in same field as a new behemoth who desperately wants to squash it. However against all expectation the minor projects thrives more that ever. The value of user loyalty.

The above pciture shows the Grove Pub while Bridgewater Place (biggest Leeds building) was under construction. Now that Bridgewater Place has been completed it really does look like David and Golaith. Picture was provided by Russell J Smith on Flickr

Saturday, 24 January 2009

Estimating Lines of Code

I’ve always been a bit puzzled on why people would use lines of code to estimate projects. It seems that estimating (more realistically guessing) lines of code (LOC) to estimate effort and schedule is way harder that splitting the project into jobs and undertaking an estimate for each job. However reading Steve McConnell’s Rapid Development book has changed my mind.

In it he has charts linking lines of code to effort over a range of criteria. So if you know the LOC for your projects you can plug them into the tables and see how you projects measure up. Moreover you could get lines of code for a project to build a historic set of data tailored to your team to take some of the guess work out of that blackest of all arts – software estimation.

Which got me thinking of how you would quickly get non-commented, non-whitespace lines of code for a Web Application written it ASP.Net



C#
Count the semicolons C#.

CSS
I would count the number of {

HTML
LOC are more difficult here. Possibility to count open angled brackets (<) and maybe divide by 5 – making the assumption that there is 5 tags per line JavaScript and VB.Net
Semicolons doesn’t work so well here as they are optional in the code. You’d need to be a bit cleverer and count the number of carriage returns without comments and with a length of more then one when been trimmed

OR

Quick and dirty: arbitrarily estimate the number of characters per line then whip through all the files and could the characters and divide by the per line estimate.

SQL stored procedures and functions
Like JavaScript you’d be looking at using carriage returns or using the quick and dirty method

Other elements.
For example SQL reports, Data extract components etc…
Can’t do anything with these so get the back of the envelope out and estimate what fraction of the project was spend on these activities. Multiple the LOC by this ratio.



In the best of all worlds we would all use function points or some kind of automated formula based approach but I moved out of the best of all worlds sometime ago and sadly my software development occurs in the dirty old real world. In this world one frequently doesn’t have time to spend half a day calculating historical effort but you’ll always have the opportunity to spend a sneaky 15 minutes seeing how many LOC were wrote and how long it took. It’s always good to marvel at how much work you’ve done and it may even make software estimation a slightly less black art in the future.

Wednesday, 21 January 2009

It’s a BUG


The other day I was merrily exporting a SQL Report to Excel when I came across this cheeky little message where some tables should have been

"Data Regions within table/matrix cells are ignored"


A quick search on forums reveals the following solution


This is a known issue in the reporting services. It is caused by the limitation of the Excel rendering. So you may redesign the report or export to other formats.


Well that’s a relief. It’s not a bug it’s a known issue and limitation, entirely different from a bug. If it was a bug I’d be stuffed and would have to go back to my project manager and tell him that we’re starting the export again from scratch so the deadline is receding rapidly into the distance. Thankfully because it’s only a limitation I only need to go back to my project manager and tell him that we’re starting the export again from scratch and etc………..

IT’S A BUG. It’s not a limitation, known issue, user error, unforeseen circumstance or convergence of several highly unlikely events. Come on Microsoft admit to it. My software’s got bug’s, my colleagues software’s got bug’s and Microsoft’s software’s got bugs. It’s nothing to be ashamed of. The first step to solving a problem is admitting it’s there. Mr Bill Gates – you’ve got a bug.

Saturday, 17 January 2009

Facts and Fallacies of Software Development


It’s been said by people a lot cleverer that me (DeMarco and Lister 1999 – Peopleware) that most developers don’t read books. So with a typical level of humdrum insight I’ve worked out that if I read a few programming books a year then it will lift me a bit above the norm – keep a positive career calculus.

Facts and Fallacies of Software Development by Robert Glass is the latest book to have taken up residence on my bedside table. It’s a collection of little known “facts” with a smaller number of falsehoods about software engineering – unsurprisingly. How though can there be a “fact” in a profession that seems to owe more to superstition and religion than it does to science and engineering. The conceit it that the facts are items of research and less often the authors personal experience. So at it heart it is a book popularising academic research in a series of short (1 -5 page) articles.

So does it work? I have enormous sympathy with the project of academic popularisation and God knows – computer science needs it more than most. It’s very engaging, very readable and gives food for thought. For me though the nature of the book makes it a bit disjointed. I appreciate IT books are novels – but that said I do like them when they have more of a beginning, middle and an end. That said – for dipping into its perfect. So buy it when you’ve a long journey – sitting on a train, sipping an overpriced coffee and reading this book – that’s the way to travel.

And my favourite fact – well it’s “[Software] Maintenance is not a problem it’s a solution”. When you’ve completed your project and your client comes back with 4 pages of desired enhancements it isn’t because your product is rubbish – it’s because your client has total engaged with it and can now see the potential. And if they pay for it – then everyone’s a winner.

Wednesday, 14 January 2009

Gordon Brown Loves Developers. It’s Official


It appears that Mr Gordon Brown our beloved prime minister is proposing that we reinflate the UK economy by a massive digital infrastructure programme. Mr Brown has clearly been reading recent Tech Splurge posts and is quite rightly basing his economic policies on my idle technology musings. With this blog as his guiding light we can expect the recession to be a mere blip.

While Mr Brown is in the technology investment zone here’s a five point tech investment plan that can only assist the UK ailing economy.


  1. Two monitors for all programmers and designers.

  2. Comfy chairs and foot stools for anyone who knows what a complier is.

  3. Free StarBucks/Café Nero coffee for anyone even obliquely involved in software development.

  4. Dry cleaning service for anyone that can write a bit of HTML

  5. Free taxis service for anyone who can tell the difference between a computer and a microwave.
With this forward thinking policy I believe that the UK will be at the forefront of technological innovation and the five point plan is in now way motivated by greed, self-interest and a desperate attempt to get something for nothing.

Saturday, 10 January 2009

T-SQL Random Data Generator

I always think that creating a component to generate random data for a table in SQL Server should be easy but it never is. I’ve written a few windows components in the past to do this but I always leave them in random folders and lose them in my sock draw. So I’ve spend a bit of time writing a stored procedure that will do the job – so I can keep it associated with the database du jour and not lose it. The proc isn’t perfect but then neither am I.

What it does
  1. Will output an insert T-SQL statement for any named table in the database.
  2. Only tested on SQL Server 2005. The INFORMATION_SCHEMA use should make it forwardly compatible but doesn’t do backwards compatibly any favours.
  3. Detects foreign keys and inserts and makes a random but valid link to the child table (this was the hard part)
  4. Detect different data types and do the necessary SQL formatting
  5. Accounts for max character length in string type fields
  6. Accounts for identity columns
  7. Detects nullable columns and leaves them null

What it doesn’t do
  1. Doesn’t account for unique indexes
  2. There are omitted datatypes (cursor, timestamp, uniqueidentifier, binary, varbinary, image)

Horrible hacks
Where the foreign key links where linked to child table via a unique index rather than a primary key it assumes a foreign key of 1. The INFORMATION_SCHEMA views don’t include index information. So this would necessitate delving into the sys.index table. Not impossible but the proc was complicated enough as it was. I didn’t need it so I didn’t do it.

Assumptions
  1. Foreign keys are of a numeric type i.e. int, float et. al.
  2. Foreign key constraints exist on the database. It isn’t magic – it can’t work out the relationships if they don’t exist.


Create Procedure usp_RandomInsertGenerator
(
@TableName nvarchar(128)
)
As

Begin

Set NoCount On

Declare @ColumnName nvarchar(128)
Declare @FK_TableName nvarchar(128)
Declare @FK_ColumnName nvarchar(128)
Declare @ConstraintName nvarchar(128)
Declare @DataType nvarchar(128)
Declare @CharacterMaximumLength int
Declare @Sql nvarchar(max)
Declare @MaxValue int
Declare @InsertValue nvarchar(200)
Declare @SqlOutputFields nvarchar(max)
Declare @SqlOutputValues nvarchar(max)
Declare @FirstLoop bit
Declare @IsIdentity bit
Declare @StringInsert bit
Declare @DummyText varchar(48)

Set @FirstLoop = 1
Set @SqlOutputFields = 'INSERT INTO ' + @TableName + ' ('
Set @SqlOutputValues = ' VALUES ('
Set @DummyText = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'

-- cursor loops through every column for named table
Declare procCursor CURSOR FORWARD_ONLY
For
Select
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
From
INFORMATION_SCHEMA.COLUMNS col
Where
TABLE_NAME = @TableName
And IS_NULLABLE = 'NO'
Order By
ORDINAL_POSITION

Open procCursor
Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength

while @@fetch_status <> -1
Begin

-- datatypes i haven't bothered implementing
if @DataType = 'cursor' OR @DataType = 'timestamp'
Or @DataType = 'uniqueidentifier' Or @DataType = 'binary'
Or @DataType = 'varbinary' Or @DataType = 'image'
Begin
Raiserror('Unsupported Data Type', 1, 16)
End

--reset variables
Set @FK_TableName = ''
Set @FK_ColumnName = ''
Set @StringInsert = 0
Set @ConstraintName = ''

-- Don't add in an insert value if the loop is an identity
Select @IsIdentity = COLUMNPROPERTY(OBJECT_ID(@TableName),@ColumnName,'IsIdentity')
if @IsIdentity = 1
Begin
Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength
continue
End

-- getting the value to be inserted for this data type
if @DataType = 'varchar' Or @DataType = 'nvarchar'
Or @DataType = 'char' Or @DataType = 'nchar'
Or @DataType = 'text' Or @DataType = 'ntext'
Begin
Set @StringInsert = 1
Set @InsertValue = SubString(@DummyText, 1, @CharacterMaximumLength)
End
Else if @DataType = 'datetime' Or @DataType = 'smalldatetime'
Begin
Set @StringInsert = 1
Set @InsertValue = Cast(GetDate() as varchar(20))
End
Else -- it is some key of numeric type
Begin

-- getting the child table indexes
Set @Sql = '
Select
@FK_TableName = pkconst.TABLE_NAME,
@FK_ColumnName = pkconst.COLUMN_NAME,
@ConstraintName = coluse.CONSTRAINT_NAME
From
INFORMATION_SCHEMA.KEY_COLUMN_USAGE coluse
Join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fkconst
On fkconst.CONSTRAINT_NAME = coluse.CONSTRAINT_NAME
Left Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkconst
On pkconst.CONSTRAINT_NAME = fkconst.UNIQUE_CONSTRAINT_NAME
Where
coluse.TABLE_NAME = @TableName
And coluse.COLUMN_NAME = @ColumnName'


Execute sp_executesql @Sql, N'@TableName nvarchar(128),
@ColumnName nvarchar(128),
@FK_TableName nvarchar(128) OUTPUT,
@FK_ColumnName nvarchar(128) OUTPUT,
@ConstraintName nvarchar(128) OUTPUT',
@TableName=@TableName,
@ColumnName=@ColumnName,
@FK_TableName=@FK_TableName OUTPUT,
@FK_ColumnName=@FK_ColumnName OUTPUT,
@ConstraintName=@ConstraintName OUTPUT

if Len(@FK_TableName) > 0 And Len(@FK_ColumnName) > 0
Begin

/* have found foreign key and the lookup table
so pick a random primary key from the lookup table */
Set @Sql = 'Select top 1 @InsertValue = Cast(' + @FK_ColumnName +
' as varchar(200)) From '
+ @FK_TableName + ' Order By newid()'
Execute sp_executesql @Sql, N'@InsertValue nvarchar(128) OUTPUT', @InsertValue=@InsertValue OUTPUT

End
Else if(Len(@ConstraintName) > 0)
Begin

/* OK we've found the foreign key constraint but have no idea what the
lookup table is. This is because we are joining on a unique index to the
lookup table not a primary key. Make a MASSIVE assumption in this instance
- that the lookup table has a link value of 1 */
Set @InsertValue = '1'

End
Else
Begin
-- no foreign key so the max that can be inserted is based on the datatype
-- don't bother with any thing greater than thirty thousand - big enough
if @DataType = 'bit'
Set @MaxValue = 1
else if @DataType = 'tinyint'
Set @MaxValue = 255
else if @DataType = 'smallint'
Set @MaxValue = 32767
else
Set @MaxValue = 32767

-- randomly generate a number to insert up to maximum
Set @InsertValue = Cast(ROUND(((@MaxValue - 1) * RAND() + 1), 0) as varchar(200))

End
End -- end of numeric processing

-- building up output string
Declare @Delimiter char(1)
if @FirstLoop = 1
Begin
Set @FirstLoop = 0
Set @Delimiter = ''
End
Else
Begin
Set @Delimiter = ','
End

Set @SqlOutputFields = @SqlOutputFields + @Delimiter + @ColumnName

if @StringInsert = 1
Begin
Set @SqlOutputValues = @SqlOutputValues + @Delimiter + '''' + @InsertValue + ''''
End
Else
Begin
Set @SqlOutputValues = @SqlOutputValues + @Delimiter + @InsertValue
End

Fetch Next From procCursor Into @ColumnName, @DataType, @CharacterMaximumLength
End -- finished this column = go to next

close procCursor
deallocate procCursor

-- outputting the sql string
Set @SqlOutputFields = @SqlOutputFields + ')'
Set @SqlOutputValues = @SqlOutputValues + ')'

select @SqlOutputFields + ' ' + @SqlOutputValues

End
Go


It’s very easy to create a wrapper sproc to get multiple statements.
One row of random data isn’t that much use – 10,000 might be.



Create Procedure usp_RandomInsertGeneratorWrapper
(
@TableName nvarchar(128),
@NumberOfInserts int
)
As
Begin

Set NoCount On

Declare @StatementCount int
Set @StatementCount = 0

While @StatementCount < @NumberOfInserts
Begin

exec usp_RandomInsertGenerator @TableName
Set @StatementCount = @StatementCount + 1
End

End

Room for improvement
Here’s some extensions that my be useful for individual projects
  1. Implement missing datatypes. Things like uniqueidenifier would be very easy indeed to implement. It’s just that I didn’t need it.
  2. Create custom inserts for know field names to give more realistic data for your application
  3. Randomise the character data properly.
So that’s it. Live it, love it and fiddle about with it a bit.