Freigeben über

Using an existing Azure SQL table with Windows Azure Mobile Services

  • Artikel

You can use an existing Azure SQL table with your Mobile Services by moving the table to the schema used by your Mobile Service.  This blog post will show you one technique to accomplish this.

In this sample the table we want to use with the Mobile Service is in the dbo schema.  The table is aptly named ‘TableToImport’!  I want to move this to the schema for my Mobile Service and use it seamlessly from Mobile Services.

There are a couple of requirements for using an Azure SQL table seamlessly with Mobile Services (at the time of this writing):

  • The table must be in the schema of your Mobile Service (the schema is the name of your Mobile Service)
  • The table must have an identity column called ‘id’ and it must be all lower case letters
  • The column types must be correct (for example not nvarchar(50) but nvarchar(max) )

You can access your Azure SQL table in different ways.  I will use the designer from the portal here:

image

This is the Table that I want to move over to my Mobile Service schema (note the schema is ‘dbo’ as seen in [dbo].[TableToImport]:

image

Note that the first column ‘ID’ is the wrong case, I can change it easily here:

image

The next task is to find out what schema I wish to move this to.  The schema for your Mobile Service is the name of your mobile service.  In my case it is ‘jsandersattachtodb’ as shown below:

image

 

To move the table from the ‘dbo’ schema to the ‘jsandersattachtodb’ schema you can click the ‘New Query’ icon and execute an SQL statement similar to this:

ALTER SCHEMA JsandersAttachToDb TRANSFER dbo.TableToImport

image

Then I can simply type the name of the table that I want to add and it will pick up the definition of the table from the existing table!

image

image

But something is wrong… I see that I have two records but if I click on the table to browse the data in the Mobile Service portal it in it does not display in the Portal UI!

The issue is that when I created the table, I did not set the ‘id’ column to be an identity column.  There is no easy way to do this (that I could find) so the solution is to create a table with an identical structure and ensure the ‘id’ column is set to identity.  Then copy the data over!

image

Using the Query Window in the SQL management portal I can execute a statement to copy over the data into my new table:

 

SET IDENTITY_INSERT JsandersAttachToDb.NextTableToImport ON
GO
INSERT INTO JsandersAttachToDb.NextTableToImport (id, Column1, Column2) SELECT id, Column1, Column2 FROM JsandersAttachToDb.TableToImport

Now my table is integrated into my Mobile Service and works properly!

 

This is just one technique to using existing Azure SQL tables in your Mobile Service.  As I come across different requirements from customers I will be sure to add new blogs to show how to accomplish these (or point you to existing walkthroughs).  You can get more SQL Azure help for copying etc… from the Windows Azure SQL Database forum as well.

Drop me a note and let me know if you liked this or it saved you some time!

Additional Information:

Dinesh’s blog describes common datatypes in Mobile Services

Comments

  • Anonymous
    May 24, 2013
    Cool content! I d like to thank you for such informative work! Like this!    <a href="bestessaywriter.net/.../a>

  • Anonymous
    December 03, 2013
    This is really interesting! Is it even possible to use a View this way? thanks

  • Anonymous
    December 03, 2013
    @Jonas, I wouldn't lie to you :-)

  • Anonymous
    December 26, 2013
    Hi, thanks for this post as I have a alread populated SQL database and now I want to access its tables through Azure Mobile Services. So I guess I'll proceed as you proposed to transfer my tables from the SQL database to the service tables area. But still I'm puzzled:

  1. What is the use of the SQL database attached to the service if the service has a separate area to store data?
  2. Can't understand the rational behind having the specific table area in the service. SQL is a great tool to store data, no? Thanks

  • Anonymous
    December 26, 2013
    Hi @Ro, You seem a bit confused.  The only thing that is changed is the schema.  You don't need to move anything if you conform to the structure specified.  Also as I mentioned in the post this is on ONE WAY to access the data.  See my blog for using Views with this to access data in a different schema.  It is Stored in the SQL database either way so I don't understand your question "1".  Likewise I don't understand your question "2".  Can you expand on those? Jeff

  • Anonymous
    July 12, 2014
    The comment has been removed

  • Anonymous
    July 13, 2014
    Hey Scott, If you are on the .NET backend you can definitely check out my latest blog.  It allows you to use different IDs and different types as well.  AutoMapper is wonderful for this kind of thing. Walkthrough: Attaching an Azure SQL database to your .NET Backend blogs.msdn.com/.../walkthrough-attaching-an-azure-sql-database-to-you-net-backend.aspx

  • Anonymous
    January 22, 2015
    Thanks for the information, but I'm still having a problem using an HTTP GET to a table in my Azure Mobile Services. Azure no longer has a 'Data' tab under the Mobile Services tab. So I created my mobile service, and set a database for it, and created my table under the seperate DB section, but no matter what I try I can't seem to open the table using a GET request. I'm trying the URL mobilemeshtest.azure-mobile.net/.../TableTest Where TableTest is the name of my table, I'm passing in the application key header too, I also followed the instructions above you mentioned, and created a new table under the schema named 'mobilemeshtest', but in Fiddler all I get is a 404 error trying to use the GET on the table. Any ideas?

  • Anonymous
    January 22, 2015
    Hi Greg, Check out the comment just above yours.  That is for the .NET backend (Since you don't have a Data tab, that was my clue you are using a .NET backend).

  • Anonymous
    August 28, 2015
    Thanks for cool stuff.