If you need to remove a column from a Telerik RadGrid at runtime, subscribe to the grid's DataBound event, find the column by its unique name, and set its display property to false.  This is the correct way to do this without breaking the filter functionality.  For a long time I was subscribing to the grid's ItemDataBound event and hiding each dataitem for the column- only now did I realize that the filter was broken by doing it that way.

 

Example:

 

    protected void rgPartDispositionHistory_DataBound(object sender, EventArgs e)
    {
        rgPartDispositionHistory.MasterTableView.Columns.FindByUniqueName("PeopleSoftId").Display = isTwtc;
    }

Tom Hundley
Elegant Software Solutions, LLC

If you have a rad combo box and the dropdown width is larger than the width, you'll get the annoying horizontal scroll bars.  You can fix this in css by using the "!important" tag.  Thanks to Telerik support for this fix:

 

.rcbScroll
{
    overflow-y: auto;
    overflow-x: hidden !important;
}

 

Tom Hundley
Elegant Software Solutions, LLC

 

Reference:
http://www.telerik.com/community/forums/aspnet-ajax/combobox/horizontal-scroll-problem.aspx

Let’s start by saying the code in this example is a really silly, but it’s a great reference for the matter at hand.  Let’s assume you need to do a join on multiple columns and you’ve double checked and triple checked that the types are all the same, but you’re stuck with the following error:  The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.  The problem is related to anonymous types.  The type initializer cannot infer the same anonymous type because the names are not the same.  The solution is simple: specify the name in one of the expressions such that they are the same and the problem is solved.

 

Problem Code:

 

 

Solution:

 

Now that the types AND the *names* are the same, the anonymous type initializer can create the same anonymous type and properly facilitate the join.

 

Happy coding,

 

Tom Hundley

Elegant Software Solutions, LLC

 

Reference:

Thanks to Matt Warren from Microsoft:

http://social.msdn.microsoft.com/Forums/en-US/linqprojectgeneral/thread/bf98ec7a-cb80-4901-8eb2-3aa6636a4fde

If you are using “using” statements to manage your data contexts in Linq to SQL (I’m not sure if this applies to Linq to Entity but I imagine it does") and you get the following error: “type used in a using statement must be implicitly convertible to 'System.IDisposable'”, simply add a reference to the “System.Data.Linq” assembly and it solves the problem.

 

 

Happy coding,

 

Tom Hundley

Elegant Software Solutions, LLC

I just learned something interesting about HTTP Redirects in IIS 7.  I’ve not yet read a lot of details about the inner workings of IIS 7, but I was surprised by this new discovery.  Long story short, HTTP Redirects are not applied purely at the site level, but are also tied to the physical path.

 

I have two websites, each one a separate site in IIS, that both point to the same physical folder (they are both DotNetNuke sites and they use the same DNN installation).  I added an HTTP Redirect to one of the sites (I wanted to point my personal DNN site to my new BlogEngine.Net site).  I didn’t think to check that the other site was unaffected until someone told me that it was also pointed to my blog.  What the hell?  It turns out that site that is pointed to the same physical folder will share the HTTP Redirect settings.  This is of course a very easy fix, but I am a little surprised by this behavior.

 

Happy coding,

 

Tom Hundley

Elegant Software Solutions, LLC

When tasked with configuration management responsibilities, you normally have several environments to which you must deploy your systems and most often, your web.config files have different information for each environment.  Several approaches have been used to address the problem this situation creates such as using T4 (Text Template Transformation Toolkit) or simply creating a separate configuration file for each environment.  I’ve personally used the latter most often as it is a quick and simple solution to the problem and works well for Click Once deployments too.  The challenge with this, however (especially in a team environment), is keeping the config files in sync when you add or change something- most developers forget to update the rest of the files and then deployments become a nightmare.

 

Enter the wonderful world of Web.config transforms.  In the spirit of keeping this short, I’m not going to cover everything about how the transforms work.  There is great reference documentation from Microsoft here: http://msdn.microsoft.com/en-us/library/dd465326(VS.100).aspx

 

We have to start with a quick overview of Web Projects.  When building web applications with Visual Studio, there are two types of projects one may chose:  Web Application or Web Site.  A discussion on which type of project is “better” is a matter for another day.  A great article exploring this question may be found here: http://vishaljoshi.blogspot.com/2009/08/web-application-project-vs-web-site.html

 

A quick summary of the some important points, however, is this.  Web Sites do not require precompilation and may therefore be updated “on the fly” allowing for quick hot-fixes in production environments.  It is, of course, debatable as to whether or not that is a good thing.  The downside is they don’t take advantage of Web.config transformations and all of the other great tools Microsoft is making available like the Web Platform Installer.  Web Applications, on the other hand, do take advantage of many of these great tools.  The downside is that you must recompile the code if you make any changes making debugging a bit more cumbersome and and quick fixes require full blown deployments.  Again, there are many merits and many flaws to both choices, but I want to review the wonderful new world of Web.config Transforms found in Web Application Projects.

 

 

Publish Profile & Configurations

Web Application Projects have a new Profile feature in the Publish functionality.  Right click on the project and chose Publish, like so:

 

 

 

 

There are two important aspects to this example: 1) Profile Name and 2) Build Configuration.  I’m not going to talk about all of the Publish Methods available because this too is a topic for another post.  For now, my publish method of choice is “File System”.

 

Profile

Simply create a profile for each of your environments.  Visual Studio stores these in a publish.xml file and are kept locally.  You can add these to source control of course if you want to make them available to your team.  The way I use the File System methodology is this:  “Dev” Profile = UNC path of the website on the dev server, “Test” Profile = UNC path of the website on the test sever, etc.

 

 

Configuration

Let’s assume for this example you have 4 environments: 1) Local Dev, 2) Development, 3) Testing, and 4) Production.  Use the Configuration Manager to create a Configuration for each of your environments.  “Debug” works for Local Dev and “Release” works for Production, so let’s create two new configurations for Dev and Test.  Note that my “Dev” environment is a development server, not my local machine.

 

 

Config Transforms

Now that that we have Configuration for each of the environments, it’s time to add the transform files to the web.config.  Right click on your Web.config file and you’ll see a spiffy option called “Add Config Transoms”.  Click this and poof, Visual Studio will add transforms for each of your new Configurations.

 

 

The result:

 

 

Refer to the MSDN article for full details of the transform language, but it’s quite simple to replace data.  The easiest things to do are matching a specific key (such as a connection string) or replacing an entire element.  The greatest thing about Web.config transforms is that you only need to keep the deltas in the transform files, but using replace works well if you don’t want to learn the fine points of the transformation language (I use this for my Enterprise Library configuration blocks).

 

Replacing a Connection String

Replacing a connection string is done like this (forgoing the obvious discussion of how connection strings should always be encrypted):

 

        <add name="Axapta.Data.Properties.Settings.AxaptaConnectionString" connectionString="Data Source=XX;Initial Catalog=ax50_test;uid=XXX;pwd=YYY!@#QWE" providerName="System.Data.SqlClient"
        xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />

Replacing an entire Element

 

    <loggingConfiguration name="Logging Application Block" tracingEnabled="true"
     defaultCategory="" logWarningsWhenNoCategoriesMatch="true" xdt:Transform="Replace">
        ...
    </loggingConfiguration>

 

 

Deploying

Once you get everything setup, you’re basically done.  Now when you use the Publish functionality, Visual Studio will apply the transforms to your deployed Web.config based on your active configuration.  Thus, deploying becomes a three step process: 1) Select your configuration in the Configuration Manager, 2) Select your Profile in the Publish Tool, and 3) Click Publish!

 

GiddyUp, EzPz, etc…  <enter choice words of happiness here>

 

One might be able to write a small book on the topics covered in this article, but hopefully I kept it short and sweet so you can use it to get jump started or as a quick reference.

 

Happy coding,

 

Tom Hundley

Elegant Software Solutions, LLC

Often times when using nested grids, you may desire to hide the expand/collapse button if there are no child items.  This is very easy to do, although it’s not as intuitive as you might think.  This example will show you how to accomplish this task  using a Telerik RadGrid for ASP.Net Ajax.

 

1.  First, subscribe to the PreRender event of the Grid control.

2.  Loop through the GridNestedViewItems.

3.  Apply your visibility logic.

 

    protected void rgParts_PreRender(object sender, EventArgs e)
    {
        GridItem[] nestedViewItems = rgParts.MasterTableView.GetItems(GridItemType.NestedView);
        foreach (GridNestedViewItem nestedViewItem in nestedViewItems)
        {
            foreach (GridTableView nestedView in nestedViewItem.NestedTableViews)
            {
                if (((Foo)nestedView.ParentItem.DataItem).Prop1 == "DontShowExpandColmn")
                {
                    TableCell cell = nestedView.ParentItem["ExpandColumn"];
                    cell.Controls[0].Visible = false;
                    nestedViewItem.Visible = false;
                }
            }
        }
    }

Tom Hundley
Elegant Software Solutions, LLC

We are often faced with a requirement of searching a single column by multiple values.  For example, one of my customers wanted to pass a list of part numbers to an inventory database and get all the part information for that list.  There are several good ways of accomplishing this, but one way I discovered that I like a lot is to accept a comma delimited list as the parameter and then convert it to XML to be used in your query.  Better yet, you could simply accept XML as the parameter.  I’ll use an example of the former methodology though, because I can see a lot of practical uses for it such as building a stored procedure and letting a user pass such as list to SSRS, whereas using XML in that scenario would impractical (unless it was done programmatically in which case would be the ideal way of doing it).

 

DECLARE @EmployeeIds varchar(8000)
SET @EmployeeIds = '1,4,5,6'

DECLARE @Xdoc int
DECLARE @EmployeeIdXml varchar(8000)

SET @EmployeeIdXml = '<Employees><Employee EmployeeId="' + REPLACE( @EmployeeIds, ',', '"></Employee><Employee EmployeeId="') + '"></Employee></Employees>'

EXEC sp_xml_preparedocument @Xdoc OUTPUT, @EmployeeIdXml
SELECT EmployeeID, LastName, FirstName, Title FROM Employees
WHERE EmployeeID IN
      (
            SELECT EmployeeID
            FROM OPENXML(@Xdoc, '/Employees/Employee', 1)
            WITH(EmployeeId int '@EmployeeId')
      )

EmployeeID  LastName             FirstName  Title

----------- -------------------- ---------- ------------------------------

1           Davolio              Nancy      Sales Representative

4           Peacock              Margaret   Sales Representative

5           Buchanan             Steven     Sales Manager

6           Suyama               Michael    Sales Representative

 

 

Tom Hundley
Elegant Software Solutions, LLC

Here is a script to rebuild all of your indexes on your database. I find that indexes are often ignored once an application moves to production. It's a good idea to evaluate your usage patterns and setup automated maintenance jobs to proactively rebuild indexes before fragmentation has a chance to adversely effect performance. This is especially important if you are using Guids as primary keys and don't move the clustered index to a more logically sequential column.

 

 -- Show fragmentation for all tables
EXEC sp_MSforeachtable @command1="print '?' DBCC SHOWCONTIG('?')"

--Rebuild all indexes (note this method locks the tables while the indexes are being rebuilt)

USE [myDatabase]
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR

SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0

BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor
DEALLOCATE TableCursor

Tom Hundley

Elegant Software Solutions, LLC

I have heard many debates about using integers v unique identifiers as primary keys. When asked my opinion, as a consultant, the only correct as answer is this: “it depends”. There is no right or wrong answer and each situation must be examined on its own merits. I will state, however, that most people don’t have thorough enough knowledge of SQL  to understand the implications of using guids as primary keys, so their arguments quickly lose validity. The concepts are really quite simple.

 

Let’s start with discussing the difference between a primary key and a clustered index. When a primary key is created on SQL server, it is created as the clustered index by default. The clustered index is simply the physical sorting of the data. Many people don’t realize that the clustered index may be changed and it does not have to be the primary key. This affects index fragmentation and is directly related to the fill factor of the data pages. Fill factor is simply the percentage of each page utilized to store data and is directly related to indexing and performance. If the fill factor of a database is set to 80, then 20 percent of the page will be left empty for index expansion.

 

Consider the default fill factor of 0, however, which is the server default and is the same as 100, meaning that each page is filled to capacity. When your clustered index is set to an auto-incrementing column, each new record will be inserted on the next sequential line of the data page. It’s similar to writing a book- each new line comes after the next. Indexing is very easy because once a record is there, that is where it stays (unless records are deleted, but the effect of deleting records is the same regardless of the type of column your clustered index is on, so it doesn’t need to be discussed for the purposes of this discussion). Let’s assume that you can have 100 records on 1 data page. Records 1-100 are on page 1, 101-200 are on page 2, etc. We know that if there are 250 records, the next record, 251, comes right after 250 and will be on the third page. Thus, the indexes stay nice and neat.

 

Now consider using unique identifiers. Let’s start with defining a guid- it is a quasi-random number generated based on a series of variants which virtually guarantees uniqueness across all space and time until something like the year 2070 (one quick note- guids were not invented by Microsoft. I’ve heard so many mainframe people complain about having a Microsoft concept forced upon them- Microsoft calls them Guids (Globally Unique Identifiers) and the rest of the world calls them UUID (Universally Unique Identifiers) and is an open standard (ITU-T Rec. X.667 | ISO/IEC 9834-8)). The important item to note in that description is the word “random”. For the sake of this discussion, we can safely consider guids to be random numbers. Now that we understand what a clustered index is and have briefly reviewed the concept of fill factor, think about what happens when you insert a random number into your tables. No longer can we say that the next record is number 251 and will be on page 3. It could be number 2 on page 1, or number 2043 on page x- then the next number is completely different, etc. This means that each new record is not happily placed sequentially on the “line”, but is placed wherever it belongs in accordance to its random number. When this happens, your indexes become fragmented. This means that when the index was created and there were 1000 records, the database knew where each record lived. With sequential clustered indexes, each new record is added to the next line or page. With random clustered indexes- each new record is randomly placed somewhere in the pages and the index will no longer know where anything is and eventually becomes useless. The more “broken” the index becomes, the higher the level of fragmentation. The higher the fragmentation, the more SQL has to rely on table scans to find the data.

 

So now that we know that, let’s discuss guids v ints for a minute. Yes, using ints will always be faster than guids. And yes, using ints will always take less space than guids. I think that’s common sense and doesn’t take a $150 an hour consultant to tell us that. But in the year 2008, that’s simply not a good enough argument to say “never use guids as keys”; it’s a simplistic and uninformed statement. Let’s start with space issue first. Space was something that needed closely considered in the old “green screen” mainframe days. Yes, size mattered back then. But today we live in a world where you can buy a terabyte hard drive for $150. Size is simply now a commodity. That being said, I’ll repeat what I said earlier: “It depends”. Ignoring size is not acceptable for all systems- if you’re building databases for Google or Walmart, you still need to be mindful of such things in your data architecture and data modeling. For most systems, however, it is a non-issue.

 

Now let’s discuss speed. Yes, using ints will always be faster than using guids. This is a true statement and will never be disputed. The question, however, is “how much faster?”. On most systems you are talking about nanoseconds, at which point the speed differences is negligible. I will create another benchmark to demonstrate this to you at a later time, and I very much encourage you to do it on your own, but I have done this before with SQL 2005 and I benchmarked a database with 1,000,000 records and the difference in performance was measured in the nanoseconds. Again, this add up when you’re talking about hundreds of millions of records or even billions of records, so if you’re Google or Walmart, pay close attention to this. As for the rest of the world, it usually doesn’t matter.

 

So now I know what most of you are thinking at this point: “but I’ve seen systems will less than 1M records and the performance was horrible!”. Yup, and I can tell you exactly the reason why: index fragmentation. The developer or dba who built the tables using guids as primary keys didn’t understand the two things we talked about above: 1) use guids as your primary keys but change your clustered index to something more practical like a timestamp or other numeric value, and 2) if there aren’t any appropriate tables to build your clustered index on you keep it on the unique identifier- you MUST establish maintenance plans for your indexes. Each system must have its usage patterns evaluated to determine the best maintenance plan- hourly, nightly, weekly, monthly? Defrag (can be done online without locking) v Rebuild (locks table access)?

 

The last thing that most of you will be thinking right now is that it just seems like a lot of work to use a guid. Well, sure- there is extra work involved, but you get a lot of benefits from using guids. Especially in today’s world of SOA where systems are crossing organizational boundaries, the guaranteed uniqueness is a huge benefit that very often outweighs any of the cons associated with using guids. Another very practical benefit is that by being able create your guid in your business objects, you can fully build out parent / child relationships in the business tier without ever having to connect to the data tier to get the ID of your next object. And some developers will tell you they have a hard time reading guids and it’s easier to use ints- well, for that one all I can say is that they’ve never really done it. I’ve been using guids for a long time and it’s as simple as reading 2-5 digit number.

 

As for my own preferences, most of my tables will default to guids as primary keys (not clustered indexes) for true data, and will ints for things such as lookup tables and system data. But as I said, a good consultant will evaluate everything about a system and make an appropriate recommendation. There is no right or wrong, and if you’re married to one answer over the other, you’re not doing your job.

 

Tom Hundley
Elegant Software Solutions, LLC