SQL Server has a lot of useful built-in aggregate functions, but for some reason there is nothing built-in (that I know of,) to concatenate strings. This drives me batty. I have this problem over and over again, and have been looking for an elegant solution for a while now. The problem is always the same:

I have data like this:

and I want to get the results, for example, with all the models for Toyota, like this:

It seems like it should be easy – it seems like it should be as simple as Grouping By the Car’s Make column. I’ve seen solutions where you Create and Drop tables, and even methods where you would create and parse xml from your results. It just seems like there should be an easier way.

Well there is.

Finally I have stumbled upon it, and I wanted to share in case anyone else runs in to this problem as much as I do:

In our car example above, this becomes

Tah-Dah!

So on from there I went, Googling by day, and Binging by night until I realized that I was using Bing at work. I would type my question in to Google, scan the initial results, then head over to Bing to find what I really wanted. I laughed to myself, shook my head, and changed my default search engine – and I’ve never looked back.

The “Decision Engine”

With time, our requirements for a ‘good’ search engine have changed. A search engine is no longer about giving you a list of relevant page results, but now needs to give you the relevant information. There is a very big difference here. I don’t want to hunt around through even the top 2 or 3 sites to find what I’m looking for. I want my search engine to do the leg work and show me the information that I want to know. Get in, get the info, get back to what I was doing. This is why I love Bing – it actually takes the relevant information out of the search results and shows it to me, so I don’t have to load a second page to get what I want.

While Google and Bing compete for market share, they really aren’t building the same product. Google is indeed a Search Engine – it searches for the best pages for you, and gives you a list of those pages. In my opinion, this isn’t what Bing does. Bing reads those pages for you, and gives you the information you want.

John Battelle says it best:

My thoughts exactly.

Google Plays Catchup

You may have noticed that, since their 2010 redesign, Google is looking more and more like Bing. They have adapted the Bing sidebar, and updated their image search to be more like Bing, and bloggers are screaming for the general public to notice.

But enough of all that, because I know that you are thinking I am bias and that you can find proof of anything on the internet if you look hard enough. So what I want to do, is show you. I want to introduce you Bing and all of its features that you never knew about. I want to show you a side-by-side comparison of why I think you should Bing instead of Google. I want you to see that Bing gives you more information instead of page results, with less clicks, in a cleaner, prettier, and just plain better way – So how about an example?

And that is only the Places & Travel sections! Have you seen the Finance & Stocks section? Tv & Entertainment section? How about the Recipes section?

Are you convinced yet? If not, I just want to share with you a couple more of my favourite parts of Bing:

What do you think? Are you a Googler? Or do you Bing? I want your opinion!

I’ve been so happy to hear all the positive feedback for my Low Cholesterol Diet Chart. It is great to know that it is helping people manage their diets, and I hope it also inspires you to use Excel for your own personal projects.

Get the Charts:

Original Diet Chart(.xlsx) ( .pdf)
System Fonts Only (.xlsx)

Daily Food Cards (.xlsx) (.pdf)
System Fonts Only (.xlsx)

To use the original sheet, you will need to have Smilly font installed

Although Excel was originally developed more for analyzing data – for budgets, reports, and various statistical tasks – it has many features that can be used to create simple, everyday projects that will help keep you organized.

To read more about lowering your cholesterol, check out this post.

The idea behind my diet chart was that goals are easier to achieve when you can see the finish line, but also measure your progress in increments. I thought of the cells in an Excel sheet as pieces of progress. You can make a similar chart to track any goals you like – exercise, homework or video game achievements

Excel makes it easy to lay out text and pictures how you imagine them in your head, because you can use the cells to place text on any part of the page. You can style an Excel sheet as easily as a Word doc or a Publisher file – it even has themes
that you can apply to your whole sheet.

To get you started, lets walk through some of the features that I used in my diet chart. Or, skip to a section to find the information you want:

• Layout Features
• Cell Merging
• Text Wrapping and Alignment
• Artistic Features
• Custom Fonts
• Coloured Cells
• Table Borders

Layout Features:

These features can be used to change the way the text is laid out on the page

Cell Merging

• What is it?

  Cell merging is when you take a selection of touching cells and make them in to one big cell
  .

• How do I do it?

  To merge a group of cells, select a rectangle of touching cells and press

• Why I Used It:

  In my example below, one serving of bread/grains can be substituted for a serving of potatoes or a serving of dessert. By merging the cells, it shows that they are all equal – one serving can be used for any category.

Example:

For more information on merging cells, check out this article

Text Wrapping and Alignment

• What is it?

  Text Wrapping is when you allow multiple lines of text in one cell; Instead of the text being too long for the cell and getting cut off, it wraps around to the next line. Text Alignment refers to the position of the text in the wrapped cell – is it aligned to the right? center? top or bottom?

• How do I do it?

  
  To change the wrapping and alignment properties of a cell, highlight the cells you want to change. Next, find the alignment section of the Ribbon (right). Use the ‘Wrap Text’ button to toggle wrapping the text of the highlighted cells. Use the horizontal (left, center, right) and vertical (top, middle, bottom) buttons to change the text alignment in the highlighted cells. Alternatively, you can right-click on one of the highlighted cells and choose “Format Cells…”, which will bring up the Format Cells dialog box. Here, you can check the box for ‘Wrap Text’, or choose instead to shrink the text to fit the space, using the ‘Shrink to Fit’ option.

• Why I Used It:

  I had a lot of extra information that I wanted to have on my chart, for example how big a serving is supposed to be, or examples of items in each category. I took advantage of all the empty cells I had, merged them, and filled them with text. Wrapping the text let me fit a lot of information in a small space.

Example:

For more information on cell formatting, check out this article

Artistic Features:

These are features you can use to add colour and style to your text and cells.

Custom Fonts

• What is it?

  Any font that you have installed on your computer can be used in Excel – so if the hundreds of fonts that come with Windows aren’t enough for you, don’t worry, you can use custom fonts here!

• How do I do it?

  You can use them just like any system fonts. Custom fonts are available all over the web, for a small price or for free from sites like 1001 Free Fonts. Just download the .ttf file, open and install it and you can use it in Excel. Just select the cell you want to change, and use the font dropdown to choose something new (right).

• Why I Used It:

  Custom fonts make your spreadsheets more personal and interesting.

Example:

Coloured Cells

• What is it?

  You can change the background colour and font colour of any cell in your worksheet to give it some visual interest. There are also a bunch of pre-set cell styles that you can use with the click of a button.

• How do I do it?

  To choose from a pre-defined style, select the cells you want to change, then find the ‘Cell Styles’ button (right), and choose from the list. To choose your own colours, select the cells you want to change and find the background/font colour buttons to select your colours:

• Why I Used It:

  Adding Font and Background colours helps draw your eyes to the important information. You can tell which servings are required (bright green), good (green) and bad (red) with just a glance.

For more information on changing font colours, check out this article

Table Borders

• What is it?

  For each cell on your spreadsheet, you can adjust the thickness and colour of the border on each of the cell’s four sides. You can remove borders completely or change the style of the lines to dotted or dashed.

• How do I do it?

  You can choose from a list of pre-defined borders by selecting the cells you wish to change, and making ia selection from the Borders dropdown (right). Use the ‘Line Color’ and ‘Line Style’ menus at the bottom to choose different colours and line patterns. For a more complex borders, select the ‘More Borders’ option at this bottom to bring up the ‘Format Cells – Borders’ dialog box.

• Why I Used It:

  I defined the borders in my sheet to clearly show the food categories (bold lines) and the serving allowances (thin lines) for the week. Use the example below to see how adding these lines makes the sheet more clear.

For more information on cell borders, check out this article

Other Features:

There are tons of other features in Excel that you can use – far too many to describe in this article. You can find information on how to add an image, perform simple math functions for your data, or even create charts, by visiting the Office Help and How-to section for Excel.

Was this article helpful? Is there something else you wish I had covered? Let me know! I’d love to hear from you.

So I made one. And you can too!

I use a Windows Phone 7 (LG Quantum), and have worked in C# and XAML for a few years now so I felt like I had a bit of a running start. Unfortunately, when you get all excited about building something like a phone app you just want it to work. You don’t want to learn all about it, you want to press a button, Hello World, and pat yourself on the back. I got very frustrated at the lack of samples out there that would help me get going, so I decided to put one together.

scenario:

My scenario for this app is as follows:

• I have Windows 7 Phone
• I have a web application written in C#/asp.Net
• my website uses built-in asp.Net forms authentication
• I want to be able to access the data from my web app, on my phone
• I have a vague idea about what a web service is
• I could fight my way out of a paper bag, if that bag was made out of XAML

Does this sound like you? GREAT! Even YOU can make a WP7 App!

I am going to break this tutorial down into a few parts, so you can find specific help if you need it.

• Tools you need to start
• Building a Webservice
• Saying Hello World! On your WP7.
• Authenticating your Users (with a webservice)
• Pass the Cookies
• Publishing Your App

$$ Before you Start $$

A word of caution before you begin. The way to get your app on to your phone is through AppHub. Much like for iPhone, you have to officially become an app developer in order to get your app on your phone. I agree that this is dumb that to make a personal app for your personal use you have to sign up. And yes, it is another way for them to make money off you with the annual fee of $99. I suggest that you build an app or two before you sign up, to make sure it is really worth it for you. Luckily, you don’t need to sign up to get all the tools you need to start dabbling, you can put that decision off until you have a sparkling new app ready to go.

Once you are signed up and verified, you can then unlock your phone for development and load on your apps in ‘developer mode’, or you can submit them for certification and put them in the AppStore. Remember, students can join for free as always through the Dreamspark program.

Tools You Need to Start

If you visited the AppHub site, you probably stumbled across the section about the tools you need to get going. If you already have an asp.Net site, then you most likely have Visual Web Developer Express. You will also need the Windows Phone SDK, Windows Visual Studio 2010 for Windows Phone, and the Emulator. These should all be available at the AppHub for you to download and install.

Overview of the Pieces

Here is a simplified workflow diagram to explain how this works.

OK! Lets get coding already!

Building a Webservice

The reason you should start here, is because if you can’t access your data, there is no point in making an app on your phone. Most likely the data you are trying to access is stored on the internet, and you need the data to make it useful.

Adding a webservice to an existing website is simple. Really! First make a directory and update the web.config to give access to unknown and unauthenticated users. Right click -> ‘Add New…’ then Choose “WFC Service” and give it a name (I’m going to call it ‘HelloWorldService.svc’). Notice that Visual Studio will add two code files for you in your App_Code folder: ServiceName.cs and IServiceName.cs. Your three files works as follows:

• ServiceName.svc : this is the gateway to your web service. Clients (like your phone app) will use this url to access your service.
• IServiceName.cs : This file is an interface for your service, and it will define the ‘Contract’. In other words, it will tell the client which methods they can call, what data they have to provide, and what to expect in return.
• ServiceName.cs : This is the implementation of the contract. The code that actually does anything will go in here.

Now open up your ServiceName.svc file and take a look. As expected, there isn’t really anything in there. All it does is reference the implementation of the service, and we don’t have to make any changes:

Ok, let’s move on to the Contract. Open up the IServiceName.cs File, in your App_Code directory

As you can see, the interface is marked with the words ‘ServiceContract’ to indicate that these are the rules for using the service. Methods inside that are marked with ‘Operation Contract’ indicate that these methods (or, operations) are available to be called. If you add a method and leave the ‘Operation Contract’ tag off, it will not be visible to clients of your service. I am going to change the default method:

I bet you can guess what it is going to do

Open up the ServiceName.cs file now, and update your method to match the interface. Add the functionality for your Hello function, or copy mine from below. One other thing to note: Because our service lives in an asp.Net application, we have to add a tag above our implementation about Compatibility Requirements. (For more information about this, you can visit MSDN:Hosting WCF Side-by-Side with ASP.NET )

Last but not least, you have to add the following to your web.config file to configure the webservices

We have now defined a method in our web service. If you run your application and browse to the ServiceName.svc file, you should see the default web service information page, as well as a link to the wsdl for your service. (What is a WSDL?)

And you’re done! You have a webservice that will say hello – so lets say hello to your WP7.

Saying Hello World! On your WP7

Open up Visual Studio 2010 Express for Windows Phone and create a new project. You will want to choose either a Panorama or Pivot application – all this really refers to is the base control that it will place for you in your generated MainPage.xaml page. (For more information about Pivot vs. Panorama and which is right for your application, check out this blog post by Jeff Wilcox). Let’s choose a Pivot application. I am going to name mine HelloWorldApplication. As you will notice, generating the project also created some ViewModel and Data files and folders for you, and you can keep or delete them. Just don’t delete App.xaml, MainPage.xaml, or the image files.

Here is where your XAML skills come in handy, although you don’t need to be an Expression Blend expert. Windows phone interfaces are generally pretty slick – no fancy do-dads or graphics, just sleek, lighting fast data. (was that a plug? probably.) Let’s remove the default code that was generated for us, and create a form with just a Name textbox and a button:

… and you should end up with a nice little hello world form! We are going to use this form to put in our name, and then call our HelloWorld web service. I have already added in the button click handler, but now, lets hook up the web service.

Adding Service References

In order for your application to subscribe to a service, it needs to be able to see it in action. If the service is running when you add it, visual studio will generate all the classes you need to call the contract methods. At this point, you should have both Visual Web Developer and Visual Studio for WP open. Go back to Visual Web Devloper and click run. Internet Explorer should start and show your website (which the webservice is a part of). Do you remember when we navigated to the wsdl page for your web service? Do you remember the url? Mine was http://localhost:627…/svc/HelloWorldService.svc

Once you figure out your service address, minimize IE and Visual Web Developer, and return to your windows phone application. In Solution Explorer, find the References folder and right click -> Add Service Reference… This will bring up a dialog. In the address bar, enter the address of your service and press Go. It will find your service, and display it below. You can click on the contract (interface) to view the available methods. Enter a namespace for this reference, and hit OK.

Notice that a new folder called ‘Service References’ has been generated for you, and a reference to the HelloWorld service has been added. Also, your project now contains a ServiceReferences.Config file. Open it up and you can see that your HelloWorld service has been defined in the config file.

An important thing to know about web service calls is that they are asynchronous. Because of that, we have to wire up an eventhandler for when the service method returns, then send off our request. Head back to your code-behind file (MainPage.xaml.cs) and add a static reference to your service, using the namespace that you specified when the reference was created. In our button click method, we want to wire everything up and send off the request. In the request completed method, we want to say hello to the user. Your code file should look something like this:

And we are ready to run! Is your webservice still running? If not, go back and run visual web developer to get it going. You can put a breakpoint in your SayHello method to verify that it gets hit. Once everything is up and running, head back to your phone application and run that too. The emulator will start up and start your app automatically. Try it out! Put in your name and click ‘Say Hello’. Hi!

Enter Forms Authentication

So now have the basics down, so it is time to figure out how to authenticate your users. You don’t want just anyone getting your data. We want to use the same Forms Authentication that we used for our web app, so our users can use the same username/password combinations. Head back to visual web developer where your hello service is located, and add a new WCF service. Give it a name (like, AuthenticationService.svc) and save. We don’t want to implement a new authentication service, we want to reference the existing one – so go ahead and delete the IAuthenticationService.cs and AuthenticationService.cs files that were generated for you. In the AuthenticationService.svc file you created, replace the text with the following:

This will reference the actual FormsAuthentication service. Neat-o! Remember to configure this service by adding to your web.config file:

Now that we are going to have authentication, we don’t want just anyone using our HelloWorld Service. We can now update our service function to deny access to unauthorized users. And because our user will be authenticated, we know their username, and our webservice no longer needs to take their name as a parameter. Make sure you update the IServiceName Contract as well!

Now lets hook up our app to use Authentication. We will need to update our application to get the new contract for our HelloWorldService. Right click on the HelloWorldService, and select ‘Update Service Reference’. Once that is complete, you will need to add a service reference to the AuthenticationService. Do this the same way we added one for the HelloService, above.

Our form still needs a password box where the user can enter their password, so add that to the form. Now that we have an authentication service and a username/pass combo, we can authenticate our users. Go to the buttonClick method for our ‘Say Hello’ button so that it calls the authentication service (with the username/pass) instead of the HelloWorldService. Only once they are authenticated we will call the HelloWorldService. But how will the HelloWorldService know we have already been authenticated? We will obviously need some piece of proof from the AuthenticationService to show to the HelloWorldService – and we store it in our cookieContainer.

Pass the Cookies

We will hold a reference to the CookieContainer in our application that we can pass along with our service requests. Since we pass the same container on all of our requests – both to the AuthenticationService AND the HelloWorldService – all the services will know we are an authenticated user.

The first thing we have to do is update the ServiceReferences.ClientConfig file to indicate that our services should expect a CookieContainer to be included. Open the config file and add the enableHttpCookieContainer=”true” attribute to each of the bindings that will use it:

The last thing to do is to update your service calls to each pass a reference to the cookie container. Here is our final code-behind file:

So go try it out already! Make sure your webservice is running and put in some breakpoints

I hope that this tutorial gave you a running start at developing apps for Windows Phone 7. Getting all the setup and services running is usually the hardest part (for me anyways), and once that is done you tend to fly through the rest. If you want to take a look at some sample apps (with code) to get inspired, you can visit the App Gallery.

Publishing Your App

Ready to publish? Visit the AppHub to sign up for your app developer account. The FAQ section has tons of information on specific questions regarding the submission process, app certification, and forums for support.

Happy Coding!

Download the Sample Code for the finished projects Here!

Was this article helpful for you? Missing something? Questions or feedback? Let me know!

It’s Time to Talk•fool•ery!

<Shameless Promotion>

Just finished listening to the latest Talk•fool•ery podcast on Zune marketplace. I’m glad you guys feel the pressure of the sample ladies too – I always feel so guilty for not buying-after-trying no matter what they are selling! So much that most of the time I avoid taking a sample or even making eye contact!

You should be checking out this FREE podcast available to you through Zune marketplace or iTunes (if anyone really uses bloated iTunes anymore). It goes great with an afternoon coffee! See you next Tuesday!

</Shameless Promotion>

Talk•fool•ery is a free podcast created by Corey Fischer and Iris Dias, chalk full of sarcasm, foolishness, and frank discussions about life’s shenanigans. Plus, visit their facebook fan page to see one-of-a-kind animated videos from last week’s episode.

Category: Comedy
Language: English

The first thing I discovered was the ShareThis sharing widget. This awesome little tool allows users to share, like, tweet, +1 (or whatever) pages from your site, and reports statistics about what has been shared. I recommend this as a great first step for anyone wanting to bridging from personal app to social media.

Integrating is addicting though, and soon I wanted a way to pull my meal plan calendar from the app and display it in outlook along side my other calendars. Problem was, I didn’t really know anything about how to do this. I didn’t know how .ical feeds worked or any idea how to publish one. Turns out it is very very simple, thanks to Doug at DDay Software – Thanks Doug!

His library DDay.iCal makes it so simple to create an .ical file that can be consumed by programs like gmail calendar, outlook, and Apple iCal. In fact, let me show you just how easy:

And you’re done!

From here you just add the address where you saved the .ical file to your outlook or google calendar. Update, and watch the events roll in! New/Updated events will appear every time you send/receive in outlook or iCal, however google calendar has yet to implement a ‘refresh’ button. This is a bit of a hot topic on the google forums, and users have been requesting this for years. unfortunately for google calendar users, you will have to wait the google-decided 6 to 24 hours for any of your .ical links to refresh.

At this time, I am only opening this app up to friends and family, and am looking for new users! If you are interested in using this app, send me an email and I will set you up!

New Exciting Features!

View your meal plan in your favorite calendar program!
Each user gets their own private .ics calendar link to add to Outlook, Apple iCal or Google Calendar. See what is for dinner without having to log in!

Share your recipes on Facebook, Twitter and more!
Want to share your recipes outside of this app? Check out Chef View for your public recipes, and find the sharing tool in the top right hand side.

Seadragon image viewer!
The References section now has a Seadragon image viewer to view those large photo files.

More features available in Safari and Chrome!
ChefView is now available in both of these browsers. (Note there are still a small number of limited features when adding/editing recipes in these browsers).

Upload Your Recipe Photos!
Everyone loves taking pictures of their beautiful food creations! Take some pictures as you cook or of your final product and upload them to the recipe. Other users can take a look, and you get points for adding them.

User Ratings!
See which of your friends is busy cooking away with new user ratings. Ratings are based on your last 3 months of activity, with points awarded for sharing recipes, posting reviews and adding photos.

Grocery Quick List!
Add items to the new quicklist to keep track of what you need to buy. If you use the meal plan grocery list generator, these items will be included for you!

Single-user sharing!
Want to share a recipe with another user, but not everyone? In Edit view you can select the specific users to share with.

Paging Recipe View!
Your recipe list will load faster than ever with the new paged recipe list view.

Import a Friend’s Recipe!
Want to hold on to a recipe shared by another user? You can now import it into your collection and keep it as a private recipe for yourself!

Edit copied recipe text before importing!
Copying a recipe from a website for import doesn’t always format properly. Now you can edit the clipboard text for a more successful import. Just use the ‘Add from Clipboard’ button when adding a new recipe, then double click on the clipboard text to edit it.

Faster and Better!
The app has been optimized and streamlined for better performance!

And more!