Download DTS for SQL Server 2005 Express

Somehow in the SQL Server 2005 management studio I had import/export (Launched the DTS Wizard) options when I right clicked on my databases even if they were on an SQL Express version server. When I reformatted my computer and installed the latest SQL express (SP2) I noticed that these menu options were gone! (also they changed the wording of "Modify" to "Design")



I did some searching and found that you can install the DTS wizard through this SQL toolkit:



http://go.microsoft.com/fwlink/?LinkId=65111



Once it is installed you have to run the exe because I still don't see the Import/Export menu option in SMS. The exe is located at C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

AJAX Enabled Website Template Not Showing?

I recently reformatted my laptop and reinstalled everything. I installed Visual Studio 2005 and AJAX Extensions 1.0. When I went to go create a new website I did not see the familiar "AJAX-Enabled Website Template". If you are having the same problem here is the answer!



Download this zip file and put it in:

C:\Documents and Settings\<User Directory>\My Documents\Visual Studio 2005\Templates\ProjectTemplates

The next time you open up visual studio you will see the new template.

Basic Threading Technique

I thought I would share the threading technique that I use quite often. I haven't read much about what .NET has to offer in regards to this topic but my method is basic and works well so I stick with it.



I've created a new console application to demonstrate:

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Text;

   4:  using System.Data;

   5:  using System.Threading;

   6:  using System.Collections;

   7:   

   8:  namespace FeedvertizeConsole {

   9:      class Program {

  10:   

  11:          static ArrayList threads = new ArrayList();

  12:          static Queue q = new Queue();

  13:   

  14:          static void Main(string[] args) {

  15:   

  16:              // Get all feeds and add them to the queue

  17:              Console.WriteLine("Adding feeds to queue");

  18:              clsFeed feed = new clsFeed();

  19:              DataSet ds = feed.FillDs();

  20:              foreach (DataRow row in ds.Tables[0].Rows) {

  21:                  clsFeed container = new clsFeed();

  22:                  container.CategoryID = int.Parse(row["CategoryID"].ToString());

  23:                  container.FeedID = int.Parse(row["FeedID"].ToString());

  24:                  container.FeedURL = row["FeedURL"].ToString();

  25:                  q.Enqueue(container);

  26:              }

  27:   

  28:              // Create 16 threads

  29:              Console.WriteLine("Creating threads");

  30:              for (int i = 0; i < 16; i++) {

  31:                  Program.CreateThread(i.ToString());

  32:              }

  33:   

  34:              Console.WriteLine("Waiting for threads to finish");

  35:              foreach (Thread thread in threads) {

  36:                  while (thread.ThreadState != ThreadState.Stopped) {

  37:                      // Keep looping until all of the threads are stopped.

  38:                  }

  39:              }

  40:   

  41:              Console.WriteLine("\nDone!\n");

  42:              Console.WriteLine("\nPress any key to exit.");

  43:              string n0th1ng = Console.ReadLine();

  44:          }

  45:   

  46:          static void CreateThread(string threadName) {

  47:              Thread thread = new Thread(new ThreadStart(ProcessFeeds));

  48:              thread.Name = threadName;

  49:              thread.Start();

  50:              threads.Add(thread);

  51:          }

  52:   

  53:          static void ProcessFeeds() {

  54:              while (q.Count > 0) {

  55:                  clsFeed feed = (clsFeed)q.Dequeue();

  56:                  HttpResponse response = Http.GetPage(feed.FeedURL);

  57:                  if (response.ResponseCode == 200) {

  58:                      // Process the feed

  59:                  }

  60:              }

  61:          }

  62:   

  63:      }

  64:  }

The outline here is simple: Create a Queue and add objects we want to process to it. Create an ArrayList to hold our threads, then add and start the threads. Each thread dequeue's the next available object and processes it meanwhile our main program waits for all threads to stop.



In my example clsFeed and Http objects are ones that I wrote but you should be able to use everything else as a template to create your own threaded application. Good luck!



If anyone else would like to make a post on their threading technique, send me an email and I'll post it up!

Using cursors and Query Optimization

I am currently tracking sessions/hits on a few of my sites. One of the things I want to know is what are the most popular search terms that people use to find my sites. While writing a stored procedure to get my list of popular search terms I decided to see if a single query would be faster to count the number of searches for a particular term or if using a cursor would be faster. I've heard that cursors are "bad" but I use them often so I wanted to put it to the test. Here is my query:



declare @Date1 varchar(32), @Date2 varchar(32)

select @Date1 = '1/1/2000'

select @Date2 = '1/1/2009'



declare @d1 datetime, @d2 datetime



select @d1 = convert(datetime, @Date1 + ' 12:00:00 AM')

select @d2 = convert(datetime, @Date2 + ' 11:59:59 PM')



create table #temp (SearchQuery varchar(512), Hits int)





insert into #temp (SearchQuery, Hits)

select distinct Hit.SearchQuery, (select count(*) from Hit as Hit2 where Hit2.SearchQuery like Hit.SearchQuery) as Hits from Hit where SearchQuery NOT LIKE '' AND HitDate Between @d1 AND @d2



/*

DECLARE @SearchQuery varchar(512), @Hits int

DECLARE myCursor CURSOR FOR

select distinct SearchQuery from Hit where SearchQuery NOT LIKE '' and HitDate Between @d1 AND @d2

OPEN myCursor

FETCH NEXT FROM myCursor INTO @SearchQuery

WHILE @@FETCH_STATUS = 0 BEGIN

    select @Hits=COUNT(*) FROM Hit where SearchQuery=@SearchQuery

    insert into #temp (SearchQuery, Hits) values (@SearchQuery, @Hits)

    FETCH NEXT FROM myCursor INTO @SearchQuery

END

CLOSE myCursor

DEALLOCATE myCursor

*/



select * from #temp order by Hits desc



drop table #temp





At first you see that I am running the ORANGE query which has a subquery doing the counting. It took 59~60 seconds to run this on all the data I have.



Next up, I commented out the orange query and ran the GREEN query. It took 47 seconds to run! And people say that cursors are bad? BS! The last thing I did was switch the order of my WHERE clause:



SearchQuery NOT LIKE '' and HitDate Between @d1 AND @d2

to

HitDate Between @d1 AND @d2 and SearchQuery NOT LIKE ''



Which brought the run time down to 40 seconds! I also tried this on the orange query but it still took 59 seconds to run.



Conclusion

Cursors are not bad! You just have to know that in some cases they can work slower or faster and you should test each method to see what is best. Also remember that the order of your WHERE clause can speed your query up as well. In my case searching a date field was much faster than searching text.



What other optimization tips do you have?

AJAX CalendarExtender bug fixes

One thing that has annoyed me about the ASP.NET AJAX Control Toolkit CalendarExtender is that when you select a date the calendar does not disappear. In order to make it disappear you have to click on an area outside of the calendar. I did a search and found the following snippet of code which hides the calendar when you select a date. Works like a charm.



In your code behind add:

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "hideCalendar", "function hideCalendar(cb) { cb.hide(); }", true);

In your CalendarExtender control add the attribute:

OnClientDateSelectionChanged="hideCalendar"



Furthermore, there is a bug which causes the CalendarExtender to display incorrectly. This bug can be found when putting the extender inside of an UpdatePanel + MultiView control. In my case, I had a MultiView inside a TabContainer inside an UpdatePanel. The calendar is so distorted that you can't tell what it is nor can you click on any date - all functionality is lost! The reason is due to a CSS never loading because of the UpdatePanel. The work around for this another snippet of code I found on the web:



string url = Page.ClientScript.GetWebResourceUrl(typeof(ClientCssResourceAttribute), "AjaxControlToolkit.Calendar.Calendar.css"); HtmlLink myHtmlLink = new HtmlLink(); myHtmlLink.Href = url; myHtmlLink.Attributes.Add("rel", "stylesheet"); myHtmlLink.Attributes.Add("type", "text/css"); Page.Header.Controls.Add(myHtmlLink);



Have fun fixing those bugs!

ASP.NET Tabs

This demo will show you how to use the ASP.NET AJAX Control Toolkit Tab Container and Tab Panel controls. You can use these controls on a web page to display a tabbed interface.







One thing to note is that if you are currently on the second tab and trigger an event that does a post back, then your page will reload and not display the same tab you we're on. The work around I did using the UpdatePanel is an easy fix for this.



If you need your page to load with a certain tab selected, use the ActiveTabIndex property. (ex: TabContainer1.ActiveTabIndex = 2 will select the third tab). Usually if I want to do this conditionally I will link to a page with the tab index in the query string and then check for that value in the page_load event. If there is a value then make that tab active!