Swim Event Times

While there are many approaches which come and go for importing Excel and CSV data files into SQL Server nothing is as easy as using OpenDataSource.  I know there are many who use SSIS and its brethren but some things when dealing Excel combined with SSIS, like nvarchar, costs time and diminishes its ease of use.  So let's have a quick go of importing data, which I do on a local version of SQL running on my workstation.

Here's what my sample data looks like in Excel:

So open a query window in SSMS and fire off the following query:

SELECT distinct * --[First Name], [Last Name], [EmailAddress]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\MSSQL\clients\INTLdist\Queries\LoadEandF\SampleEmailList.xls;Extended Properties=Excel 8.0')...[Sheet1$]
where [FirstName] is not null

You'll see I'm using the provider for Excel's (97-2003) older version that are still in xls binary format. Also note the location of the Data Source which is local to the SQL Server you are running.

While you'll see that this as a rather simplistic sample, imagine an Excel file with 20 or greater columns and you'll begin to appreciate the ability to see column names and the ability to get right to the data without having to use some wizard to move "from/to" a destination.

So you might begin to think of things you could use this for.

Say someone lands a huge (>10GB) text file (SampleHugeFile.csv) on your network and you have to take a look inside to see what was delivered.  You're not going to use notepad or any other desktop client tool since most will eat your memory alive.  Instead just use the following and you'll see what's in the file without incurring the memory penalty:

SELECT  top 10 WorkRef, WorkKey
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\MSSQL\clients\INTLdist\Queries\LoadEandF\;Extended Properties="Text;HDR=YES"')...SampleHugeList#csv

I know some may see this as extreme but if you only have a hammer then everything starts to look like a nail.

Now for the final piece, since most of the world is using later versions of Excel you'll need slightly different OPENDATASOURCE provider notation:

select top 10 *
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\MSSQL\clients\INTLdist\Queries\LoadEandF\SampleEmailListInLaterVersion.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

 Note the change in providers when using the OPENDATASOURCE since you'll need to switch to this when using newer versions of Excel which support the xlsx file format.

Now start to mix it up and start using a CTE to manipulate the data like so:

;with CTE as 
(
SELECT distinct * --[First Name], [Last Name], [EmailAddress]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\MSSQL\clients\INTLdist\Queries\LoadEandF\SampleEmailList.xls;Extended Properties=Excel 8.0')...[Sheet1$]
where [FirstName] is not null

)
	SELECT  
	   PARSENAME(REPLACE(EmailAddress,'@','.'),3) Email, 
       PARSENAME(REPLACE(EmailAddress,'@','.'),2) Domain, 
       PARSENAME(REPLACE(EmailAddress,'@','.'),1) DomainExt 
FROM CTE

results in:

 SQL Server has many hidden gems that you need to see just once and then you'll understand how it can increase your productivity.

Tags: Excel, SQL Server, OpenDataSource

Back to the beginning.  When you need content and the content exists on other web sites you'll have to devise a way to acquire the data.  And I'm not talking about OData or some other nice structure of data lying around on the internet.  What I'm talking about is, for a lack of better term, "SCRAPING" data from other URL's.  Some would call this Web Scraping but this technique really applies to a whole realm of techniques and not just in use on the web.

Some believe that type of development is fraught with problems since your tying your app to a URL and that web design.  Just know these pitfalls up front and try to mitigate as many problems as possible.

Try and contact the URL's owners and try to get them to come onboard with your development.  That way at least you'll have knowledge of upcoming changes which could cause side-effects on your W7P app.

Disclaimer:  I'm a developer and not a copyright lawyer but be warned that some sites believe that they own the data and have the sole rights to that data.  It's the old "Creative" vs "Collection" argument. Collection of data is not creative but do your own copyright research.

Now let's talk about overall design.  Some designs call for an intermediate server to make the calls to the target URL but I opted to make the Windows 7 Phone software smart enough to exist on its own and pull the data directly from the target.  This minimizes the points of failure. It's either working from the W7P or not plus it also permits the phone to operate anonymously from the target URL's perspective.

Now the main tool that I quickly made use of is Fiddler2.  Fiddler2 allows you to look at the payload when making calls across the web.  This includes everything that is sent from your Request and the subsequent Response.

The site from which the data for this app will be scraped is an ASP.NET site.  I can't be sure but the site appears to make use of a Content Management System. I could have gone further into looking at the CMS since I believe that it's Open Source and pulling apart the code but I decided to spend the time on how and if I could get access to the data across the web from the phone. 

Other sites and their software platforms and how they implement cookies/session data etc. will change the makeup of how you access the pages but you need to start with Fiddler2.

I know IE9/Firefox have similar developer tools but when this application started up, Fiddler2 was my choice.

Get to know Fiddler2 and exactly what pages you need to hit.  Make screen shots of the flow that you'll need to capture and test scenarios that will cover the flow of pages.

You and Fiddler2 will spend many long nights together.

Tags: W7P, Fiddler2, web scraping

I had implemented a "Touch and Hold" gesture for editing and deleting items from the Swimmer's page. 

The "Touch and Hold" would bring up a Context menu for editing and deleting a selected swimmer.

After receiving complaints from beta users that this was not discoverable I thought I needed to provide a better experience for the user. 

What I ended up using was a List box with check boxes and additional icons in the application bar to control the experience.  So before, I had a context menu with "Edit" and "Delete" which was revised into two additional icons on the application bar.

I made great use of the following example:

http://listboxwthcheckboxes.codeplex.com/

Now it's quite apparent on how to delete swimmers and edit the swimmer's date range when you need to refresh and download with the latest meet data.

Tags: W7P, Listbox, USA Swimming, swim times, Gestures

As many developers, I've completed most of the development using the phone emulator since I wanted to wait until Verizon was offering a Windows 7 Phone.  While the emulator provides an invaluable tool, you soon realize you missed something when you start developing and testing with a real device.

For this development I immediately noticed that the "Tap" gesture was not fluid enough to give the user a feeling that they had just touched an element on the screen. 

So here I employed the the "Tilt Effect" as so nicely provided by:

http://www.scottlogic.co.uk/blog/colin/2011/05/metro-in-motion-part-4-tilt-effect/

I went with the following value since I think it gave me a better pushed-in effect:

<StackPanel Orientation="Horizontal" HorizontalAlignment="Left" local:MetroInMotion.Tilt="5">

Now touching an element gives a nice feedback to the user so that they can feel as if the element on screen was pushed in.  I've used this in all the places where the action of touching the element would bring you another page.  Specifically I added it to the Swimmer's Page (Add, Edit, Delete) and the Panorama page displaying the "Best", "Meets" and "Strokes" data.

Tags: W7P, Tilt Effect, USA Swimming, swim times