Mike Taulty recently spoke to our Windows Phone User Group (North West), and got me interested in WAMS. We had been dabbling with a mobile sales demo, but other things got in the way, and we didn't make much progress.
The catalyst for doing more was some nice person stealing my Surface RT at Mobile World Congress in Barcelona. I decided to write FindMySurface, and talking about it to Peter, decided that SQL Azure would be a great vehicle for this. We could log to SQL Azure, and enquire on it from a web site or a PC. Mike had done a great job of explaining things, and his blog goes into far more detail.
Using the Azure portal, I created the tables we needed, and added the indexes. This was a no-brainer really, as we had no data, and we were going to collect it on-line from the Surface. After getting my head around the fact that the 'id' field MUST be present in every table, and MUST be called 'id', most of it fell into place. I wrote a 'database' class for my app, and put all the database access in the class. Using 'async', I could wait for records to be retrieved, and then display them. I did lots of searching, and asked Mike for advice on a couple of things, but generally, found it was fairly easy to code. I read a couple of wrinkles, including the fact that WAMS is more efficient retrieving 50 records at a time, as that is how it is 'batched' on the servers. This was built into the application, and was made configurable.
FindMySurface logs your position depending on various system events, and also runs in background. You can enquire on the whereabouts of your Surface (or ANY Windows 8 device) and plot it on a map. Performance of the enquiries is very impressive. You can also enquire on its location using a web site (currently being written as I write this).
Flushed with the success and the ease with which I could talk to WAMS, I decided to write a Windows Phone application, to enquire on FindMySurface. Imaginatively, this is also called FindMySurface. The database class for the Windows 8 project fitted into the Windows Phone project without changes, and the phone project was very quick to write, using the mapping code culled from other projects.
Having got an initial experience of WAMS under my belt, I turned my thoughts back to the mobile sales demo I mentioned earlier. We had designed a database, and populated it with test data using the excellent SQL Data Generator from Redgate software (I LOVE Redgate products). The first job was to ensure that each field had an 'id' field on it. Most of them did, but were called 'CustomerID', 'UserID' etc. So I rejigged the layouts, and links, and made sure everything hung together. The database already had data in customers, products etc., so the next stage was to get the database up to the cloud, and link it to WAMS.
The first thing to do is to get our database up into the cloud. In SQL Server Management Studio, right click on the database, select Tasks, and then Deploy Database to SQL Azure.
You will then get a prompt to connect to your Azure server. You can get the address of the server from the Management Portal.
Your database will then be created on your Azure server - you get a progress report as it is creating.
Now go back to the Azure portal, and you will see that your database is listed there under SQL Databases.
Our next stage is to create our Mobile Service, so in the portal, click on Mobile Services, and create a new Mobile Service. You also need to use an existing database instance, and choose the same region where your database was created.
When you move on, select your APPASalesMobile database we created and uploaded earlier, and log in with your user name and password.
Your mobile service is then created, as shown below.
So, just to re-cap, we have uploaded our database we created and loaded with SQL Server Management Studio, and created our mobile service pointing to that database.
Our next move is to get the two talking to each other, and this is where I struggled in the past. The bit I was missing was even though I had created a database and pointed to it in mobile services, I still had no tables, and therefore no data. The only option appeared to be 'Create a new table', but I didn't want to do that, did I? A bit (well a lot really) of searching took me to an article by FlipW which put me on the right track.
Step1 is in the SQL Azure database management, create a new schema and then link to the schema. In the query window, alter the schema to point the tables to the mobile service you created earlier.
Click on new query, and then enter the following.
This creates a Schema, and links it to our APPAMobileSales mobile service. Now go back to the mobile services portal
and click on 'Data'
At this point, you will, like me, be incredibly disappointed - we have just done all this work, and we still have no tables. This is where the 'magic' happens, although it does seem strange at first. Click on 'Add A Table' (trust me).
Customers is the name of one of our tables - so click on the 'tick'. Azure whirrs away creating our table
The whirring stops, and lo-and-behold the mist clears, and we have a customers table with 4 indexes and our 5000 records.
Click on the Customers table, and we can see that all our data is there.
All out customers are there. Now repeat the Adding of tables, and add the rest of our tables.
All of our tables are now showing up in our mobile service, along with their record counts. Just to prove it, our Database even shows the same counts.
We now have a populated database for our sales demo!
So, to recap on what we have done to get here.
We created our database, as we would normally, using SQL Server Management Studio. We also made sure that every table had an identity column called 'id'. We then populated our database with test data. The next step was to transfer this database to the cloud, using SQL Server Management Studio.
After the transfer, we then created a new Azure mobile service, using the database we had just created. Then, in the Azure Management Portal we then altered the schema to point at our newly created mobile service.
Finally, back in Mobile Services, we added the tables, and as they were linked to our database, they automatically populated.
Using this method, we can easily move our production databases to the cloud, and access them using WAMS.
I think Microsoft have done a great job with WAMS, and they have made it so easy to access Azure databases from Windows Phone and Windows 8 apps. Kudos to Scott Guthrie and his team for doing this.
So all we have to do now is write our demo.
After starting to write the demo, I realised a lot of the code is repetitive, so we have released APPA Wams to make your life easier. APPA Wams will generate code to define and maintain your Windows Azure Mobile Services database - more details in my blog posting.
Originally posted 22/Mar/2013