May 2008 Blog Posts
CompiledTemplateBuilder and Event Catch-up

Federik Kalseth has a great easy-to-understand post on using template controls programmatically.  Serves as a great review of templated controls since I needed to refresh my memory on how they worked. cool0003.gif

 

Here's another post on Template Control Catch-up in ASP.NET:

http://weblogs.asp.net/leftslipper/archive/2007/01/18/How-to-instantiate-templates-properly.aspx

http://weblogs.asp.net/vga/archive/2003/08/11/23498.aspx

TRUNCATE TABLE and DBCC CHECKIDENT

During development, it's nice to reset the database tables and load the database up to a known state for testing purposes without having to reload the schema.

Well, I was running into a problem where calling DBCC CHECKIDENT() on a newly built database would actually reseed the identity column to the value passed into CHECKIDENT().  For example, after creating a new database (without any previous INSERTs), and calling "DBCC CHECKIDENT('table', reseed, 0)", the very next INSERT on 'table' would cause the identity column on the newly inserted row to be 0 on 'table' when it should be 1.

Now, if you had existing rows in 'table', deleted the existing rows, then reseeded using "DBCC CHECKIDENT('table', reseed, 0)", the very next INSERT on 'table' would cause the identity column on the newly inserted row to be 1 (as expected).

Whack.  So, calling DBCC on a new database (without any previous inserts) next INSERT identity would be 0, and calling DBCC on a existing database (with previous inserts) next INSERT identity would be 1.  This kind of inconsistent behavior totally defeated my DB tests.  Fortunately, there's a way to differentiate between newly created tables (with no prior rows) and tables that had have had rows even after a delete.  Here's a script that I made that completely resets tables and identity columns apart of your build process:(!BE VERY CAREFUL!)

Yes it's a bit hacky, using undocumented stored procedures and system tables, but it's the best I could do.  MS doesn't make this easy...

image WARNING THIS WILL COMPLETELY DELETE ANY DATA IN YOUR DATABASE image

 

SET NOCOUNT ON

Exec sp_MSForEachTable 'Alter Table ? NoCheck Constraint All'

Exec sp_MSForEachTable
'
If ObjectProperty(Object_ID('
'?''), ''TableHasForeignRef'')=1
Begin
    -- Just to know what all table used delete syntax.
    Print '
'Delete from '' + ''?''
    Delete From ?
END
ELSE BEGIN
    -- Just to know what all table used Truncate syntax.
    Print '
'Truncate Table '' + ''?''
    Truncate Table ?
END
'


Exec sp_MSForEachTable '
DECLARE @lastIdentVal sql_variant

SELECT @lastIdentVal = LAST_VALUE
    FROM     SYS.IDENTITY_COLUMNS
    WHERE OBJECT_ID=Object_ID('
'?'')

    If ObjectProperty(Object_ID('
'?''), ''TableHasIdentity'')=1 AND @lastIdentVal NOT NULL
    BEGIN
        DBCC CHECKIDENT ('
'?'', reseed, 0)
        DBCC CHECKIDENT ('
'?'', reseed)
    END
'


Exec sp_MSForEachTable 'Alter Table ? Check Constraint All'

Hope that helps,

cowboy