Unidev Tech Blog

Archive for the ‘SQL Server’ Category

I had an interesting requirement for a project recently. I needed to randomly sort database records that already have a sort preference number. That is to say, each record with the same sort number needs to be randomized in a group with others of the same number. Then those are returned as a group in an order based on the original sort order compared to a group of other sort numbers. Using this data for example:

I always want 1 and 2 to come before 3, 4, or 5 and for those before 6 and 7. But 1 and 2 could be in any order just like 3, 4, and 5 and so on.  So either of these could be valid results: 1,2,5,3,4,7,6 or 2,1,4,3,5,6,7

I originally just returned them in an order of the SortPref padded with zeroes and appended with a NewID() guid.  For example:

SELECT Right('0000000000' + Convert(varchar(10), SortPref), 10)
+ Convert(varchar(50), (NewID())) as SortPref

000000010EDE455E6-ED80-4752-9A26-3321226EDAA9

     000000010B6D63097-9CBE-4D88-B43F-6CDEA460A028

     000000200D02F7DDB-5245-42EF-A148-F8F34836DB1F

etc…

This worked, but one caveat is that I wanted the random order to be consistent, at least for a time. But every subsequent query returned a completely new order. For example, showing 3 per page, I could see 1,2,5. Then going to page 2 to see three more, I could see 3,5,7. That second batch was not the next three from the previous query. It is items 4-6 of a brand new query (notice 5 is in both). Then going back to page 1 will show 3 completely new random results in a different order, possibly 2, 1, 4.

Using the SQL Rand()function seemed like it was going to be great because passing it the same seed each time will produce the same “random” number and also consistent  numbers each time thereafter. Each time the following SQL is executed it will return the same 3 numbers.

SELECT Rand(18), Rand(), Rand()

So I ditched the padding and guid and wanted to combine the existing sort preference number with a random decimal. I needed a new number for each row returned so I put the random number generating code right in the SELECT statement. This seems likely to work, but it does not. Rand() works similar to GetDate() in that the current datetime is fetched once and returned for every row. So I was getting the same suffix for each row.

SELECT SortPref + Rand (Cast (NewID () AS VarBinary)) AS SortPref

Since I need to get a new random number for each row, I instead made an update statement to put that result into a new integer column. This got me what I wanted and then I just sort by that column.

UPDATE List SET SortPref_RAND = SortPref + Rand( Cast( NewID() AS VarBinary ))

SELECT SortPref_RAND ORDER BY SortPref_RAND 

I chose to run this update daily so that a user’s results will not change even from the morning to evening. I keep track of the last date the random column was updated and if it is yesterday (so the first time a search is performed after midnight) then the update is performed again.

I had a usual instance where a DropDownList was bound to a table in a database. However, that list of data could change in the database without regard to those values having been previously stored (as text, not IDs, unfortunately) in another table. Meaning that when the DropDownList would now be loaded with the newly updated data, the previously selected value would not have a corresponding choice in the drop down anymore.

The DropDownList doesn’t take too kindly to this and gives an ArgumentOutOfRangeException error. But I needed that old value as a possible choice in the dropdown still, as well as the new choices.

The problem was that I needed to get the saved value from the database (which failed to be the initial SelectedValue). So I just stored it in the ToolTip for the DropDownList, then I could retrieve it and update it from anywhere.

Note: Be sure to use OnDataBinding not OnDataBound to add the new item.

<asp:DropDownList ID=”HotelNameDropDown” runat=”Server” DataSourceID=”ObjectDataSource1″

SelectedValue=’<%# Bind(“HotelName”) %>‘ AppendDataBoundItems=”true”

OnDataBinding=”HotelNameDropDown_OnDataBinding” ToolTip=’<%# Bind(“HotelName”) %>‘

OnSelectedIndexChanged=”HotelNameDropDown_OnSelectedIndexChanged”

DataTextField=”HotelName” DataValueField=”HotelName” />

Depending on when you postback, you might also need to update the ToolTip when the user changes the DropDownList.

protected void HotelNameDropDown_OnSelectedIndexChanged(object sender, EventArgs e)

{

// Set the tooltip to the selected value onchange, becuase we will use the tooltip below

DropDownList ddl = (DropDownList)sender;

ddl.ToolTip = ddl.SelectedValue;

}

Here I try to bind the DropDownList with the data, if this fails then it creates a new ListItem at the top of the list with a name and value of what we had stored in the ToolTip from the .aspx page.

protected void HotelNameDropDown_OnDataBinding(object sender, EventArgs e)

{

DropDownList ddl = (DropDownList)sender;

ddl.DataBinding -= new EventHandler(HotelNameDropDown_OnDataBinding);

try

{

ddl.AppendDataBoundItems = false;

ddl.DataBind();

ddl.SelectedValue = ddl.ToolTip;

}

catch(ArgumentOutOfRangeException)

{

ddl.Items.Clear();

ddl.AppendDataBoundItems = true;

ListItem li = new ListItem(ddl.ToolTip, ddl.ToolTip);

li.Selected = true;

ddl.Items.Insert(0, li);

}

}

More info about this topic can be found here:

http://www.jeffgaroutte.net/post/2008/07/09/The-DropDownList2c-the-DataBind-and-the-Missing-Value.aspx

by Allan Sieker

When it comes time to write an interesting article about a technical topic my mind goes into a deadlock in trying to find the balance between space/time constraints and keeping the content interesting.  Too short of an article and it seems trivial because the technical stuff doesn’t get covered enough.  Too technical, and the article becomes long and boring.  All the while, wanting to keep things original, informative, and light.  So did I end up with?  How about a series of related articles that covers something that is near and dear to all of us?   

 I bought my first computer in 1978 (yes, I still have it) and as my home network expanded to what it is now – several servers, workstations, and laptops, the need for keeping a file inventory goes with the territory.  Sure, over the years I created my own file databases written in several languages (BASIC, Pascal, dBASE, Clipper, VB, C#) and they all served their purpose, but technology keeps improving and I always want more.   

 What if data warehousing concepts were applied to capture the “slowly changing dimension” of file updates?  What if file collections were recognized and managed as applications and other entities?  What if all of the computer file inventories were gathered locally then stored centrally for searches via a web interface?   What if backup history were also available?

 This article series will cover a broad range of technical topics with the end goal being a respectable system for home or business usage.  Concepts will be discussed and code will be available for download.  References to other articles and postings will also be made.

 Here is a brief list of planned topics: 

• Using recursive methods to collect file information from all of the folders on a drive.
• Creating console and Windows “file agent” applications to collect file data and write to an XML file.
• Creating a SQL Server file inventory database.
• Balancing cost and architecture to avoid over-costing and over-engineering.
• Importing “file agent” XML into the database.
• Creating an ASP.NET web application for searching and retrieving application & file information.
• Making “file agent” applications downloadable from the web site using ClickOnce.
• Using the Visual Studio Report Designer and the ReportViewer control to create web reports.
• Creating a “wrapper” application for Microsoft’s Backup to manage and track backups.
• Detecting media and image file duplicates based on file content instead of file name.

 Feedback is always welcome.

by Allan Sieker

Just recently I had the opportunity to investigate a timeout error happening on an ASP.NET web application of a new client.   This web application is a very intense and complex business solution involving tiered hierarchies of entities for companies, resources, processes, and projects.  Definitely not your typical web site.

The source of the timeout was an embedded SQL query consisting of a SQL view joined to another table.  A SqlDataAdapter was being used to fill a DataSet from a SQL Server database.   Very straight forward on the surface, but then I noticed that the a CommandTimeout setting of 60 was being used.  Why would such a simple query be taking more than 60 seconds to execute?

My first was to set a breakpoint just before the execution of the query so I could see what the actual parameter values were.   My second step was to copy/paste the query into an open query tab on SQL Server Management Studio and alter the query to use literal values from the first step.   To my surprise, the query did take a bit of time to execute.  Time to drill down.

I scripted out the SQL view and found that it consisted of a 13 table join where 3 of the joins were to views with joins of their own.  And one of the returning columns was a function having several querys of its own – and a 3 table join.  Time to get another Diet Mt. Dew…

I now understood why the previous developer slapped in a 60 second timeout.  Sure, I could have increased the timeout to 120 seconds and it would probably have solved the problem.  But would it be the professional way of solving the problem?  I thought not.  Besides, I didn’t want to put the client through a compile/rebuild/release cycle for just changing one line of code.  Another important thing to consider is that the users will have to endure this long query several times a day.  I couldn’t take that road.

This query and all of its branches was way too complicated to digest quickly, so I started breaking each branch down and checking to see if the queries were taking advantage of indexes. 

As it turned out, in about an hour I found four places where an index could be added and the yield of that effort took the 60+ second query down to 3 seconds.  The users were  ecstatic.

So the moral of my story is this:  Please do not blindly increase the timeout values without an honest attempt to tweak the query.    The time you take this one time will benefit your users every day.

 By Allan Sieker

If you want to learn something from one of the best, please check out Clint Edmonson’s web site where you can download free coding standards for both VB and C#.  I worked with Clint at AB and I must say that he is one sharp guy.  I am sure he raised Microsoft’s collective talent a few notches when he joined them.  Be sure to look him up at the St. Louis “Day of .NET” Conference in August.

Shifting gears from .NET to SQL….

I ran across “Ten Common Database Design Mistakes” and found it to be most interesting.  I’m sure you will too.

I recently needed to update an existing C# solution of multiple projects to add transactions around two different data updates.  I wanted to have this pattern for each one:

• Start transaction
• Delete all existing data
• Insert all new data
• Commit transaction

Read the rest of this entry »