Tuesday, February 3, 2009

SQL Management Studio - Speed-up database list load

Recently, we came across a seemingly strange issue where SQL Management Studio 2008 errored-out when trying to connect to a server with a limited permission login. Studio 2005 allowed us in without a problem.

After scrawling through blogs and forums galore, I finally discovered that it was because Studio 2008 was trying to read the 'Collation' property of each and every database on the server, and was crashing on the very first one, thus not letting me view any databases at all.

In order to stop this failing, we needed to view 'Object Explorer Details' (F7) on the 'Databases' folder, and remove the column header 'Collation'. This fixed the problem and now allowed us to view all databases, and then open the one we were permitted to.

Following up on this, on a hunch, I decided to see what happen when we un-check all other columns (database properties) except for name. Up till now, refreshing (and initially loading) a server's list of databases took painfully long... those extra seconds that kill us developers!

As expected and to my delight, with all those properties not being requested the database list loaded almost instantly on click. Better still, Studio 2008 remembers the column settings so that hereon in, only looks to load the name of the database initially, and speeds up the process every time we now open SQL Management Studio.

Binding a DataList to an ArrayList

The following example shows how to databind a DataList to an ArrayList containing uppercase Alpha Characters.

C# Function to create and attach ArrayList:
private void bindAlphabetList()
{
ArrayList al_az = new ArrayList();
for (int i = 65; i < datasource =" al_az;">

Source Code for the DataList:
<asp:DataList ID="dl_az" runat="server" RepeatDirection="Horizontal" >
<ItemTemplate>
<asp:LinkButton ID="lb_letter" runat="server" Text='<%# Container.DataItem %>'></asp:LinkButton>
</ItemTemplate>
<SeparatorTemplate> | </SeparatorTemplate>
</asp:DataList>
Note how an attribute is bound to the ArrayList value:
Text='<%# Container.DataItem %>'

Alternatively an attribute can be bound to the Index of the DataListItem in which it is enclosed, as follows:
Text='<%# Container.ItemIndex %>'

or the Index of the GridViewRow if working with a GridView:
Text='<%# Container.DataItemIndex %>'