# Wednesday, July 30, 2008

Microsoft Guidance Explorer - An alternative to WikidPad?

I tried Guidance Explorer again after a good while (for the purposes of documenting standards and processes), and concluded that it wasn't going to be up to the job because printing wasn't supported. 

I've been using WikidPad for a while and have been struggling with it's usability so I had another google around the printing capability of Guidance Explorer (my eventual medium is likely to be some other wiki-like product, so this is likely to just be my 'input medium').  I found the (rather obvious) Guidance Explorer Overview on Codeplex that explains that printing is indirectly supported through the export formats. I tried it and - whoa!! I take it all back. 

The export to Word format at different levels and different views is just great.  It gives you an index at the top with links to the actual content.  The formatting's nice too.  The HTML format's just the same as the Word doc visually, but spits out the content to separate files which is a bit nicer for deployment.

I still can't believe how little-known Guidance Explorer is, but much of the Patterns and Practices work is documented, along with many other resources.  I'll certainly be saying Bye bye to WikidPad.


posted on Wednesday, July 30, 2008 10:14:58 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Monday, July 28, 2008

Favicon Browser Support Chart. How different browsers support the Favourites Icon

At last I've found a good reference on which browsers support which types of icons/png's etc for the Favicon.  I guess the news is that you need more than one approach if you're wanting to serve a favicon to the most potential users.

posted on Monday, July 28, 2008 9:16:08 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Wednesday, July 23, 2008

Upgrading SubVersion Server and Repositories to 1.5

After upgrading TortoiseSVN and AnkhSVN to SubVersion 1.5, I set about upgrading the server this morning.  The main reason for this is that I wanted to merge a branch back into the trunk using the integrated merge option, and this simply doesn't work with a 1.4x server and repository.  Everything needs to be 1.5 for that to work. 

We're currently on 1.4.5 ish, from a previous 1-click Windows/Apache setup with some mods for groups authentication

I just downloaded the 1.50 server Windows binary and it actually now does a nice thing of asking whether you want to use SvnServe or Apache. 

I had some issues originally running Apache 2.2 with 1.4, and ended up with Apache 2.0.x but gave the new 1.5 install a go.  The following is how I upgraded my install from 1.4x using the same httpd.conf settings from the previous installation (link above).

Upgrading the Server

  1. Stop existing services (Apache etc)
  2. Back up your httpd.conf (my original install was apache 2.0 and went into a different folder) - but you'll need the settings in any case
  3. Back up mod_auth_sspi.so from your existing Apache installation (/modules folder).  If you were using Apache 2.0 then don't bother 'cos it won't work with 2.2.
  4. NOTE: If you want to upgrade your repository (required to use new 1.5 merging functionality) you need to first dump out the existing repository using the 'old' installation
    1. Backup the existing repository
    2. Run svnadmin /repos/path > dumpfile.txt
    3. You do the rest once you've upgraded the server
  5. Install 1.50 server (choosing Apache and whatever port / repository location you previously had.  You'll replace the repository location later, but make sure your port's right to save confusion and updates to httpd.conf).
  6. Edit the new httpd.conf replacing all '<Location>' sections at the bottom with that from your saved config.
  7. Paste the following line at the bottom of the LoadModule section
    LoadModule sspi_auth_module modules/mod_auth_sspi.so
  8. Copy an Apache 2.2-compatible mod_auth_sspi.so into the /modules folder.  As noted above if you take mod_auth_sspi.so that was compiled against Apache 2.0 you'll get a message along the lines of:
     
    httpd: Syntax error on line 117 of C:/Program Files/CollabNet Subversion Server/httpd/conf/httpd.conf: API module structure 'sspi_auth_module' in file C:/Program Files/CollabNet Subversion Server/httpd/modules/mod_auth_sspi.so is garbled - expected signature 41503232 but saw 41503230 - perhaps this is not an Apache module DSO, or was compiled for a different Apache version?
     
    If you go to the home of mod_auth_sspi you'll only find a module compiled against 2.0 so after hunting around a bit I found a copy compiled against 2.2.  Just copy the .so file into your modules folder)
  9. Start her up, and hopefully hey presto, you'll have 1.50 server.  You can verify this easily by loading up the repository url in your browser and the footer should say something like... Powered by Subversion version 1.5.0 (r31699).

Upgrading the repository

  1. With the new version (1.5) binaries run:
    1. svnadmin create /temp/repos/path
    2. svnadmin load /temp/repos/path < dumpfile.txt
  2. Swap the old and new repository folders (useful to keep the backup)
  3. You're all up to date.
posted on Wednesday, July 23, 2008 3:10:18 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Tuesday, July 22, 2008

Refactoring the inefficient data loop

OK. Last one for today.  I'm not going to go into too much detail except to say that the offending piece of code loads all items in a 'matrix' database table and builds a business object collection hierarchy.  This table has 4 fields, one or more of which may be NULL - for different meanings.  I really don't particularly like this type of approach but changing it right now would involve quite a bit of work.

The table looks something like

Level1ID
Level2aID
Level2bID
Level2cID

This ultimately translates to a business object hierarchy of:

 

  • Rows with Level1ID (only) - all other fields null - are Level1 objects
  • Rows with Level1ID and Level2a ID (only) are Level2a objects

etc...

 

The matrix table just contains the primary keys for each object so the code loads each one in the style below...

 

Load matrix
for each row in matrix
{
 if row type == Level1
     Add to Level1Collection
 else if row type == Level2a
 {
  Load Level2a object
  Add Level2a object to Level1[Level1ID].Level2aCollection
 }
 else if row type == Level2b
 {
  Load Level2b object
  Add Level2b object to Level1[Level1ID].Level2bCollection
 }
 else if row type == Level2c
 {
  Load Level2c object
  Add Level2c object to Level1[Level1ID].Level2cCollection
 }
}

 

This seems reasonable enough (logical anyway) given the way the data's being retrieved.

This does however load another several hundred rows from more stored proc calls that load each new object into the child collections.  This whole thing consistently takes around 8 seconds.

 

The Refactoring

 

A wise man once told me that if you can be sure the bulk of your operation is data access then lump it all together if you can, and do the loop or other grunt processing on the client. 

  1. I created a new Stored Procedure to return 4 result sets.  These come back as 4 tables in the target DataSet.  Each resultset is qualified by the same master criteria, and just uses joins to get a set of data that can be loaded directly into the collections.  The original stored proc is no longer required and this is now the only data access call.
  2. I changed my collection classes slightly to allow a LoadData method which takes in a dataset, a tablename and a parent key.  This means I can add Level2a objects to the appropriate Level1 collection.  The pseudo code now looks like...

Load multiple results
for each row in matrix
{
 if Level1 Results present 
   LoadData on Level1Collection
    
 if Level2a Results present
  for each Level1 row
   LoadData on Level1[Level1ID].Level2aCollection
   
 if Level2b Results present
  for each Level1 row
   LoadData on Level1[Level1ID].Level2bCollection

 if Level2c Results present
  for each Level1 row
   LoadData on Level1[Level1ID].LevelcbCollection
 
}

As I said at the beginning, there are some definite improvements to be made from changing the data structure, and a lot of this code could look a lot nicer by using Typed Datasets with relationships defined.

 

The new approach actually completes in less than 100ms.  I couldn't actually believe it myself, and made sure I killed connections, cache etc to make sure the database was coming in cold.  Still the same.

 

This basically proves that for data-heavy operations, things really start to hurt when you're making repeated client round-trips, however small the call.  This is basically a 99% saving in load time for this business object. 

 

The resulting page is also really snappy now and I'm sure the customer won't even notice :-)

 

posted on Tuesday, July 22, 2008 5:15:02 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]

Refactoring the blindingly obvious - Enums and Value Names

I know many people have written about this one, but it's cropped up yet again in the app I'm maintaining.  The old chestnut of setting a string based on an enum value - when the enum names are identical to the string values (most often with one word names). 


public enum ItemStatus
{
    Draft 
1,
    Pending 
2,
    Released 
3,
    Recalled 
4,
    Rejected 
5,
}


switch (item.ItemStatus)
{
    
case ItemStatus.Draft:
        {
            statusLabel.Text 
"Draft";
            break;
        
}
    
case ItemStatus.Pending:
        {
            statusLabel.Text 
"Pending";
            break;
        
}
    
case ItemStatus.Released:
        {
            statusLabel.Text 
"Released";
            break;
        
}
    
case ItemStatus.Recalled:
        {
            statusLabel.Text 
"Recalled";
            break;
        
}
    
case ItemStatus.Rejected:
        {
            statusLabel.Text 
"Rejected";
            break;
        
}
}


//or alternatively just take the 'name' as a string and get rid of the switch altogether
statusLabel.Text communicationItem.CommunicationItemStatus.ToString("g");


Colorized by: CarlosAg.CodeColorizer

There's other ways to do this if you want more of a 'description' (e.g. multiple words).  The StringEnum class could do it for you, or you could provide your own implementation with a Description attribute on the values - e.g.  [Description("My extended value name")]

posted on Tuesday, July 22, 2008 10:52:28 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]

Refactoring below the surface. Things aren't always as they seem

Continuing the refactoring from last week, I spotted another great example this morning that I 'almost' didn't improve at all.

My 'entry point' into this refactoring was trying to eradicate dynamic SQL and also the dreaded SELECT *, so this snippet popped up in my search.
My immediate reaction was to refactor the dynamic SQL to a Stored Procedure, but then I took a step back and had a further look at the code.

The following has been changed slightly (naming) to protect the innocent. 

string listOfIDs "";
foreach 
(Organisation organisation in dataObject.DistributionList.Organisations.Values)
{
    listOfIDs +
"'" + organisation.OrganisationID + "',";
}
string sql "";
if 
(listOfIDs.Length > 0)
{
    listOfIDs 
listOfIDs.Substring(0, listOfIDs.Length - 1);
    
sql "SELECT * FROM tblOrganisations WHERE OrganisationID IN (" + listOfIDs + ") ORDER BY [Name]";
}
else
{
    sql 
"SELECT * FROM tblOrganisations WHERE OrganisationID = '' ORDER BY [Name]";
}
Database database 
Database.GetDefaultConnection();
DataSet dsOrganisations database.Execute(sql, Database.CommandExecutionType.TSQLDS);
dgrConfirmDistributionList.DataSource dsOrganisations;
dgrConfirmDistributionList.DataBind();

int 
rowCount 0;
foreach 
(Organisation organisation in dataObject.DistributionList.Organisations.Values)
{
    
// Get the datagrid row we are working on

    
DataGridItem dataGridItem dgrConfirmDistributionList.Items[rowCount];

    
// Bind Target Divisions & Select Targeted Divisions
    
Divisions divisions = new Divisions();
    
divisions.Load(dataGridItem.Cells[0].Text);
    
DataGrid dgrConfirmTargetDivisions =
        
(DataGrid) dataGridItem.Cells[2].FindControl("dgrDivisions");
//more.....

 


What this code is doing is actually using an 'already populated' collection of 'Organisation's and performing a nasty dynamic SQL call (using an constructed IN clause) back to the database to retrieve the same data again so it can bind against a datagrid. 

It then goes back to using the original collection further on and does some more binding through the object hierarchy by linking to child controls in the grid.

It's clear here that we've got more going on than the need to just refactor the SQL code (dodgy table names etc).  It turns out that I've touched this code quite recently by refactoring what was a 'pseudo-not-quite' collection into a generic dictionary.  The original poor collection implementation was probably confusing enough to put the developer off trying to use that for data binding.  The collection now can of course be bound against the original collection with no need to go back to the database.

We end up with the following:

dgrConfirmDistributionList.DataSource dataObject.DistributionList.Organisations.Values;
dgrConfirmDistributionList.DataBind();

int 
rowCount 0;
foreach 
(Organisation organisation in dataObject.DistributionList.Organisations.Values)
{
    
// Get the datagrid row we are working on

    
DataGridItem dataGridItem dgrConfirmDistributionList.Items[rowCount];

    
// Bind Target Divisions & Select Targeted Divisions
    
Divisions divisions = new Divisions();
    
divisions.Load(dataGridItem.Cells[0].Text);
    
DataGrid dgrConfirmTargetDivisions =
        
(DataGrid) dataGridItem.Cells[2].FindControl("dgrDivisions");
        
//more.....    

This not only removed bad and unnecessary code that could have hurt performance, it also made me focus on 'real' refactoring opportunities.  It turned out that 90% of the remaining dynamic sql calls we actually duplicates of this code in other ASP.NET pages, and so that could be removed too. 

Refactoring can be quite like peeling an onion.  I'd been in this code a few days ago, and due to a small improvement made then (with the collection), I was able to come back and do some more.  The moral here is 'patience'.  If you're serious about improving all code you touch then you learn to make small, manageable changes that you can test before moving on to the next.  There's still work to do on this application but the code smells are starting to fade.

posted on Tuesday, July 22, 2008 10:14:16 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Thursday, July 17, 2008

Getting into IT? - Get into Usability

I've been tailed by a graduate this week (who's very switched on), and in talking to him it became apparent that as part of his rotation activities he'd been sitting in on some usability testing and interviews with users.

It struck me that this sort of experience will be incredibly valuable to him in years to come as he's forming many of his norms and opinions about the IT world right now and to start off thinking about 'what the user needs' is not a bad thing!  I know I was first subjected to the partisan stereotype of users before making up my own mind that the reason many projects fail is because many programmers don't understand basic usability concepts.  I felt I needed to understand more.

Part of the problem is that all too often companies shortsightedly exploit their new and junior starters.   If you take the example of a kitchen hand in a 19th century mansion, you'd walk in through the door, and be ushered past many rooms filled with things you're not allowed to experience - past the ladies doing embroidery, the gents in the den smoking cigars and drinking brandy.  Before you could ask any questions (some of which may be quite insightful) you're locked in the kitchen washing dishes!

IT and programming is still mistakenly viewed as a 'back room' function in many places, and thankfully my current organisation has the forsight to expose new people to things that are ultimately going to make them more valuable to the company.  Learning about usability is fundamental to understanding what makes systems work well, and also being able to address those that fall short, so the earlier you start, the better.

posted on Thursday, July 17, 2008 11:34:15 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Wednesday, July 16, 2008

NAnt Solution Task bug - Error checking whether 'xxx' is an enterprise template project.

Just spent a little while looking into this.  I thought I'd be clever and add my build script to the solution it builds so I can get some intellisense from Resharper (although it's somewhat limited).  This added the file into 'Solution Items' which is a virtual folder.

The error appears because NAnt expects to find valid project files in the solution folders.  I had the same error previously with Web Site Projects (as these are also 'special folders'). 

I'm using NAnt 0.86 (Build 0.86.2898.0; beta1; 8/12/2007), and from the bug report it seems that it's fixed in 0.86 beta 2 (not yet out) so you'll have to get a nightly build if you're desperate.

My workaround for the moment is to use the NAntContrib MSBuild task to build my Web Deployment project (which in turn builds all the dependencies), as follows:

<target name="build.webdeploy" description="Compiles a Web Deployment Project using MSBuild">
  <msbuild project="${project.dir}\mydeploydir\mydeployproj.wdproj" verbosity="Detailed" >
     <arg value="/p:Configuration=${build.configuration}" />
  </msbuild>
</target>


You can fill in the blanks yourself with the parameters.

posted on Wednesday, July 16, 2008 1:00:04 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Friday, July 11, 2008

Feelgood Friday - Refactoring with .NET Generics

As it's Friday I thought - let's do some cleaning up.  The following exercise is refactoring a class from an app I'm maintaining at the moment.  The app's fine (functional etc), and was originally a .NET 1.1 build.  A combination of this, a previous, straight conversion to .NET 2.0 (with the minimum of mods) and a variety of historical developers (mostly junior) means that the code has a certain amount of 'odour' in numerous places. 

Lack of consistency isn't the main problem, it's more about doing things smartly rather than taking the path of least resistance with your current knowledge.  The latter generally means you don't learn much, get much satisfaction or pride in your work, and you may end up a tad confused as to why you always end up with the 'less glamourous' projects.

Here's the original code.  A simple collection class containing custom MenuItem objects. 

    /// <summary>
    /// Represents a collection of MenuItems.
    /// </summary>
    
public class MenuItems
    {
        
private readonly ArrayList palMenuItems = new ArrayList();


        
// Add a MenuItem to the Collection
        
public void Add(MenuItem objMenuItem)
        {
            palMenuItems.Add(objMenuItem)
;
        
}

        
// Retrieve a MenuItem from the Collection
        
public MenuItem Item(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        
return objMenuItem;
                    
}
                }
            }
            
return new MenuItem();
        
}

        
// Returns True if the specified Key exists in the collection
        
public bool Exists(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        
return true;
                    
}
                }
            }
            
return false;
        
}

        
// Remove a MenuItem from the Collection
        
public void Remove(string strTitle)
        {
            
if (palMenuItems.Count > 0)
            {
                
foreach (MenuItem objMenuItem in palMenuItems)
                {
                    
if (objMenuItem.Title.ToLower() == strTitle.ToLower())
                    {
                        palMenuItems.Remove(objMenuItem)
;
                        break;
                    
}
                }
            }
        }

        
// Clear the collections contents
        
public void Clear()
        {
            palMenuItems.Clear()
;
        
}

        
// Return the Number of MenuItems in the Collection
        
public int Count()
        {
            
return palMenuItems.Count;
        
}

        
// Access to the enumerator
        
public MenuItemEnumerator GetEnumerator()
        {
            
return new MenuItemEnumerator(this);
        
}

        
#region MenuItem Enumaration Class

        
//=================================================================================================
        // Inner Enumeration Class:
        //=================================================================================================
        
public class MenuItemEnumerator
        {
            
private readonly MenuItems pMenuItems;
            private int 
position -1;

            public 
MenuItemEnumerator(MenuItems objMenuItems)
            {
                pMenuItems 
objMenuItems;
            
}

            
public MenuItem Current
            {
                
get return (MenuItem) pMenuItems.palMenuItems[position]}
            }

            
// Declare the MoveNext method required by IEnumerator:
            
public bool MoveNext()
            {
                
if (position < pMenuItems.palMenuItems.Count - 1)
                {
                    position++
;
                    return true;
                
}
                
else
                
{
                    
return false;
                
}
            }

            
// Declare the Reset method required by IEnumerator:
            
public void Reset()
            {
                position 
-1;
            
}

            
// Declare the Current property required by IEnumerator:
        
}

        
#endregion
    
}


Colorized by: CarlosAg.CodeColorizer

OK - Here's the issues I see with the code.

  1. You can spot some room for improvement immediately with variable naming (a weird variation of hungarian notation) - palMenuItems, objMenuItems etc.
  2. This is a simple wrapper implementation.  In conjunction with the naming you may conclude this is an ex-VB programmer who hasn't quite grasped object-oriented concepts yet (an observation - not a criticism).  If the developer knew about inheritance then they would have hopefully just inherited from one of the many applicable classes in System.Collections.  This would have also removed the need for implementing a custom Enumerator class.  Note that neither class actually inherits from any useful base class.  This is copy/paste pseudo-inheritance, and is rarely a wise idea.
  3. The Exists and Item methods use a simple loop mechanism to find the item they're looking for.  This is potentially wasteful with many items, and goes back to the 'path of least resistance' thought.  It's also using the inefficient 'ToLower' implementation to do comparison of what should be a key.  In practice the match doesn't need to be case-insensitive, and if it did then a String.Compare should be used with the appropriate 'ignorecase' argument).  The developer clearly isn't familiar (or maybe just not comfortable) with dictionaries or hashtables where access to items via a key is supported and far more efficient.
  4. The Count implementation is a method rather than a property (aargh!)
  5. It could be argued that the menuitem class itself (not shown here) is redundant as the ASP.NET framework does define a similar MenuItem class.  In reality the whole implementation could be replaced with a sitemap, a menu control, and some custom code, but I'll leave that out of scope for now.
That gives some pointers as to what 'could' have been done in .NET 1.1.  .NET 2.0 of course introduced generics and so let's follow that path.

The MenuItems class is used in a few places:

  • A menu control (that renders menu items)
  • Many pages that load items into the control (I already refactored some of this into inherited menu controls, where all pages shared the same 'dynamic' menu), but there's plenty more improvements to be made.

The first thing is to say...

I don't need a collections class...

The following is now in the Menu Control:

        //private MenuItems menuItems = new MenuItems(); - Out with the Old
        
private Dictionary<string, MenuItem> menuItems = new Dictionary<string, MenuItem>()//In with the new

        
public Dictionary<string, MenuItem> MenuItems
        {
            
get return menuItems}
            
set { menuItems = value; }
        }


Colorized by: CarlosAg.CodeColorizer

This simply uses a generic Dictionary object with a string key (as we find items by title).  Anywhere that references this dictionary will now need to add a 'key' in certain places (e.g. Add method), and the Item method will no longer work as that's gone.  That needs to change to a class indexer.

Fortunately Visual Studio's find and replace can cope with all of this as the code is largely consistent...

A quick compile should show all the patterns (from the errors) if there's any inconsistencies.  There were probably about 200 references to patch here with about 4 different variations of naming and scope - but it took about 5 minutes to patch (including writing this text :-))

We then have to patch all the 'Item' references.  These are almost always setting a 'selected' property (Stopwatch on...)

As there's only a few references here (about 20), I could patch them manually, but this could be done entirely by using regular expressions to do the find and replace.  http://msdn.microsoft.com/en-us/library/2k3te2cs.aspx shows all the options available.  I tend to only use regexp for large volume stuff where I get a return on my time investment to work out the expression! 

I'm just doing it in two steps without regexp as follows:

The code

            Menu1.MenuItems.Item(UserMenu.MyCommunicationItems).Selected = true;

becomes

            Menu1.MenuItems[UserMenu.MyCommunicationItems].Selected = true;

For extra points you then use Resharper to rename all of the obj and pal references to something reasonable, e,g, objMenuItem becomes menuItem.

Oh - and use Resharper's 'Find Usages' on the MenuItems class to confirm it can be deleted.

And relax.....


In hindsight I would have used Resharper to 'change signature' on the methods in MenuItems (to patch all the references first), then converted to the generic Dictionary.  Could have been a bit quicker.

Hope this gives someone some courage to get in there and improve their code.  I firmly believe you've just got to 'want' to do it and then you're biggest problem will be stopping yourself!


posted on Friday, July 11, 2008 10:06:52 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [2]
# Wednesday, July 09, 2008

Test out String.Format options online

Love this little tool.  It's making me think about writing some stuff for Silverlight!

I just plain can't remember the options for String.Format and standard/custom strings so being able to try them out is great.  Nice work!

I'd normally use John Sheehan's excellent Cheat Sheet, but I'll probably stick to the tool in future.

posted on Wednesday, July 09, 2008 5:13:49 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [1]

Who needs Internet Explorer when you can use it in Firefox?

I love Firefox and its addons, although I still habitually use Internet Explorer.  IE6 at work (which always provides some nice CSS challenges!) and IE7 at home.

I'm a bit slow off the mark with using firefox, but the latest addon that someone sent me yesterday may have tipped the balance.

The IE Tab addon actually uses the IE rendering engine to render in a new tab in Firefox.  It's quite amusing when you right-click and get the IE context menu.

The really great thing about this is that you can then use the power of the other Firefox addons to develop for IE.  Here's my list of 'must haves'.

  • Firebug.  This is quite simply the best addon.  Hit F12 and you can instantly see (and manipulate) all sorts of things in the comfort of your own browser.
  • YSlow.  Great Javascript profiler
  • FireCookie. See and change your cookies (for development purposes of course! - Good to test the security of your apps)
  • Web Developer.  Bit of overlap with Firebug but some cool features.

The IE Developer Toolbar is still a pretty good tool, but Firebug just gives you more info when you're debugging CSS (which is inevitable with IE).

 

posted on Wednesday, July 09, 2008 12:24:40 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Tuesday, July 08, 2008

Search SQL Server Stored Procedures for any text string

Here's the SQL script I probably use most when I'm trying to work out dependencies and scope of development changes.  Query Analyzer allows you to search objects (hit F4 - it's cool!) and find objects by 'name', but there's no obvious way to search stored procs for a particular string.

SQL Server hides Stored Procedure code in the sysComments table (in each database), and so it's possible to search.  This is another script I found from not sure where (so can't really take credit).  It does a great job of scanning and even gives you line numbers.  It would be great to be able to just shove this into the master database, but you need to be in the context of the database you're searching in (or it would have to be amended somewhat to run some more 'dynamic' SQL). 

I have previously front-ended this for development databases with an ASP.NET page that simply runs the query substituting a parameter for the search text and connecting to the correct database.  The results show nicely in a grid.

Here's the code - just paste into a new query in your chosen database, change @SearchTerm and away you go...

 

SET NOCOUNT ON
GO

declare 
@dbname sysname
,@SearchTerm    varchar(100)
,@BlankSpaceAdded   
int
,@BasePos       int
,@CurrentPos    int
,@TextLength    int
,@LineId        int
,@AddOnLen      int
,@LFCR          int --lengths of line feed carriage return
,@DefinedLength int
,@obj_name    varchar(255)
,@prev_obj_name 
varchar(255)


/* NOTE: Length of @SyscomText is 4000 to replace the length of
** text column in syscomments.
** lengths on @Line, #CommentText Text column and
** value for @DefinedLength are all 255. These need to all have
** the same values. 255 was selected in order for the max length
** display using down level clients
*/
,@SyscomText    nvarchar(4000)
,@Line          
nvarchar(255)

set @SearchTerm 'MyTable'
set @dbname db_name()


set @SearchTerm '%' + @SearchTerm + '%'

Select @DefinedLength 255
SELECT @BlankSpaceAdded /*Keeps track of blank spaces at end of lines. Note Len function ignores
                             trailing blank spaces*/
CREATE TABLE #CommentText
(obj_name 
varchar(255)
 ,LineId    
int
 
,Text  nvarchar(255collate database_default)

/*
**  Make sure the @objname is local to the current database.
*/
if @dbname is not null and @dbname <> db_name()
        
begin
                raiserror
(15250,-1,-1)
        
end

    begin

        DECLARE 
ms_crs_syscom  CURSOR LOCAL
        FOR SELECT 
so.name, sc.text FROM syscomments sc
    
inner join sysobjects so
    
on so.id = sc.id
    where 
so.type 'P'
    
AND sc.encrypted 0
                
ORDER BY number, colid

        
FOR READ ONLY
    
end

/*
**  Get the text.
*/
SELECT @LFCR 2
SELECT @LineId 1


OPEN ms_crs_syscom

FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText

WHILE @@fetch_status >0
BEGIN

    SELECT  
@BasePos    1
    
SELECT  @CurrentPos 1
    
SELECT  @TextLength = LEN(@SyscomText)

    
IF @prev_obj_name <> @obj_name
        
--start again
        
SELECT @LineId 1
    
    
WHILE @CurrentPos  !0
    
BEGIN
        
--Looking for end of line followed by carriage return
        
SELECT @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

        
--If carriage return found
        
IF @CurrentPos !0
        
BEGIN
            
/*If new value for @Lines length will be > then the
            **set length then insert current contents of @line
            **and proceed.
            */
            
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
            
BEGIN
                SELECT 
@AddOnLen @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
                
INSERT #CommentText VALUES
                
( @obj_name,
          @LineId,
                  
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                
SELECT @Line = NULL, @LineId @LineId + 1,
                       @BasePos 
@BasePos + @AddOnLen, @BlankSpaceAdded 0
            
END
            SELECT 
@Line    = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
            
SELECT @BasePos @CurrentPos+2
            
INSERT #CommentText VALUES( @obj_name, @LineId, @Line )
            
SELECT @LineId @LineId + 1
            
SELECT @Line = NULL
        END
        ELSE
        
--else carriage return not found
        
BEGIN
            IF 
@BasePos <@TextLength
            
BEGIN
                
/*If new value for @Lines length will be > then the
                **defined length
                */
                
While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
                
BEGIN
                    SELECT 
@AddOnLen @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
                    
INSERT #CommentText VALUES
                    
( @obj_name,
              @LineId,
                      
isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
                    
SELECT @Line = NULL, @LineId @LineId + 1,
                        @BasePos 
@BasePos + @AddOnLen, @BlankSpaceAdded 0
                
END
                SELECT 
@Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
                
if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
                
BEGIN
                    SELECT 
@Line @Line + ' ', @BlankSpaceAdded 1
                
END
            END
        END
    END
    
--Save previous name
    
SET @prev_obj_name @obj_name
    
FETCH NEXT FROM ms_crs_syscom into @obj_name, @SyscomText
END

IF 
@Line is NOT NULL
    INSERT #
CommentText VALUES( @obj_name, @LineId, @Line )

select from #CommentText 
where  patindex(@SearchTerm, text) <> 0

CLOSE  ms_crs_syscom
DEALLOCATE     ms_crs_syscom

DROP TABLE     #CommentText


Colorized by: CarlosAg.CodeColorizer
posted on Tuesday, July 08, 2008 9:38:31 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]

Generate SQL Inserts from existing data

This is one from the archives that obviously got away.  I had need for this today so thought I'd post so I don't have to look so hard next time!

It's pretty much a case of create the SP in your chosen database and run with a tablename as the parameter.  I originally found this on one of the SQL sites (probably SQLServerCentral), and amended it slightly to add some basic filtering in the @where parameter.

There's a few limitations from memory - with blob, image and text fields, but for your average tables this will probably do the trick just fine.

I've also used a different method that basically builds up a single INSERT with SELECT 'literal values' UNION SELECT 'literal values'.  That works fine and is potentially easier to control transaction-wise as everthing's in a single statement.  Anyway here's the code....

if exists (select from dbo.sysobjects where id = object_id(N'[dbo].[usp_InsertGenerator]'and OBJECTPROPERTY(idN'IsProcedure'1)
drop procedure [dbo].[usp_InsertGenerator]
GO


CREATE PROC 
dbo.usp_InsertGenerator
(@tableName 
varchar(100), @where varchar(1000= NULLas
 
--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT 
column_name,data_type FROM information_schema.columns WHERE table_name @tableName
OPEN cursCol
DECLARE @string nvarchar(3000--for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000--for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000--data types returned for respective columns
SET @string='INSERT '+@tableName+'('
SET @stringData=''
 
DECLARE @colName nvarchar(50)
 
FETCH NEXT FROM cursCol INTO @colName,@dataType
 
IF @@fetch_status<>0
 
begin
 print 
'Table '+@tableName+' not found, processing skipped.'
 
close curscol
 
deallocate curscol
 
return
END
 
WHILE 
@@FETCH_STATUS=0
BEGIN
IF 
@dataType in ('varchar','char','nchar','nvarchar')
BEGIN
 SET 
@stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if 
@dataType in ('text','ntext'--if the datatype is text or something else 
BEGIN
 SET 
@stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF 
@dataType 'money' --because money doesn't get converted from varchar implicitly
BEGIN
 SET 
@stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE 
IF 
@dataType='datetime'
BEGIN
 SET 
@stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
END
ELSE 
IF 
@dataType='image' 
BEGIN
 SET 
@stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE 
--presuming the data type is int,bit,numeric,decimal 
BEGIN
 SET 
@stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END
 
SET 
@string=@string+@colName+','
 
FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE 
@Query nvarchar(4000)
 
SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
IF @where IS NOT NULL
 SET 
@query @query + ' where ' + @where
 
exec sp_executesql @query
 
CLOSE cursCol
DEALLOCATE cursCol
GO

Colorized by: CarlosAg.CodeColorizer
posted on Tuesday, July 08, 2008 9:18:47 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Monday, July 07, 2008

Upgrading TortoiseSVN, Subversion 1.5 and AnkhSVN.

I thought I'd upgrade to v1.5 of TortoiseSVN this morning (leaving the server on V1.4.x) - of course completely forgetting that I use AnkhSVN in Visual Studio. 

New versions of TortoiseSVN and Subversion tend to upgrade the format for Working Copies.  This means AnkhSVN (1.02) was no longer compatible with my 'new' Working Copy and I got the following error...

This client is too old to work with Working copy 'xxx'. Please get a newer Subversion client.

Thankfully AnkhSVN 2.0 is on the way and I downloaded a nightly build (2.0.4523.91) and installed (without uninstalling 1.0.2).  I usually do this to 'test' whether people write good installers.  Most people do the right thing with AutoUpgrade and 1.0.2 dutifully disappeared.

I loaded up Visual Studio with the same solution and - 'Where's my AnkhSVN menus gone?'.  As V2.0 is not even Alpha yet there's not much in the way of obvious help on the Collabnet site.  The community area may have more, but I luckily didn't need it.

I noticed in the application folder that there's now an Ankh.Scc.dll.  Well blow me if they haven't gone and made it a 'standard' Visual Studio SCC Provider!  Tools --> Options --> Source Control --> Plug-in Selection.  There it is.

Once you've got over that, then you might find a few little errors here and there, but the main difference is that instead of an ANKH menu on Tools and in the Solution Explorer context menu - you get a Subversion context menu. 

I'm liking it so far.  I think it's probably stable enough to put your faith in and make the jump to SVN 1.5.

Update (08/07/2008) 
I submitted a bug report yesterday on an error I encountered (from the nice 'send error details' link) and got a reply saying 'fixed in latest build' the next day.  This bodes well!!!  Reasons to pay money for VisualSVN now disappearing fast...

Update (23/07/2008)

AnkhSVN v2.0 now officially out (that was a quick alpha/beta phase!)

posted on Monday, July 07, 2008 10:37:45 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [1]
# Friday, July 04, 2008

Invalid Postback or Callback Argument - ASP.NET. One cause

Just been getting this error.  It's quite common, especially if you're using Ajax.  I got the error for an altogether more 'schoolboy error' reason.

I had a textbox control e.g. called 'customerName', and two radio buttons controlling whether to show the customer name.  These had a 'GroupName' of customerName, and this caused the error.  You need the GroupName to associate the radio buttons together, but the postback mechanism obviously sees that as the control identifier.  Silly boy. 

The naming was pretty bad in any case and actually prompted me to take more of a long hard look at the UI to tidy it up so no such conflict would arise.

posted on Friday, July 04, 2008 4:21:22 PM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]
# Wednesday, July 02, 2008

ASP.NET - The name xxx does not exist in the current context

I recently converted a Web Site to a Web Application project, and as it had been a while I tripped up on a few things. 

First of all I made the mistake of creating a new Web Application and pasting all the web files in there (I was intending to split some of the site into another Class Library), and I wanted to port to another file structure.  I mistakenly thought it would be easier to do this 'before' changing to the WAP.

I then of course got a gazillion compiler errors (well - 1436 to be exact) on the new project.  The vast majority of which were 'The name xxx does not exist in the current context'.  Yes it does! (I protested).  Well.  That showed mistake number 2.

If you want to convert then use the 'Convert to Web Application' option on the Web Site properties (or on the individual pages if necessary).  This does a number of things:
  1. Sorts out your project structure
  2. Changes 'CodeFile' to 'CodeBehind' and other directives
  3. Adds a designer file for pages and controls
  4. Other stuff that you shouldn't bother yourself with etc...
The compiler errors were due to the fact that the WAP model expects pages to be structured in a certain way, and although the 'code' looks fine (and Resharper was green all the way) it won't compile 'cos the designer files are missing and hence so are the definitions the compiler's looking for.

See Steven Smith's post on some other tips for converting projects.

posted on Wednesday, July 02, 2008 11:07:48 AM (AUS Eastern Standard Time, UTC+10:00)  #    Comments [0]