In our talk about Data in Azure Mobile Service in our last post. We have data stored in relational data store that is Microsoft Azure SQL. However the aspect of data goes beyond this, because we also need to include some scripts/controllers because its not about the data alone, its also about accessing or manipulating that data. We will look for SQL Tables, accessing them, modifying them and creating new ones. We will also look for REST API that are automatically created when you create a new mobile service that are used to perform CURD operations. We will also look for permissions about who can perform those CURD operations.
Prerequisites
Before you start of with this topic make sure you have following things with you
- Microsoft SQL Management Studio
- Fiddler Web Debugger
Creating Table
Lets start off by creating a new simple table in Azure Mobile Service. To create a table follow these steps
- Click on Mobile Service in Azure Portal.
- Click on the Mobile Service Name you want to create table in.
- In Mobile Service Click on Data tab.
You will have all of your tables listed. On the bottom you will see a create button. Click on that button and you will have this screen shown up
Here in table name type the name of the table you want to create. Now we talked about the permission. Here are the permission. We can change them weather we want the table admin to update or any one with the application key can update that. For now leave it as it is. Click on the tick mark and table has been created.
Once the table has been created you can click on it view it further properties. You will have Browse option. Under this option you can view the data in the table. You Scripts tab under which you can view the Scripts for curd operation. You have column options under which you can view the column of the table. You have permission options where you can manage permissions for the table.
For now click on the column and lets have a look at the table column you have just created.
Lets play around with table to see how it works out. How we can add column. Connect this Service through SQL Management Studio to view the table details.
This is the view SQL Management Studio Project Browser. In green box you see the list of tables you have created. In the red box you can see you can explore your tables and view the number of columns in it. Lets explore these in a bit detail using Fiddler Web Debugger. We will see how the REST API interacts with SQL tables. We will use our App URL and we will play around with POST and GET request.
Start the Fiddler debugger copy the base URL of your mobile service (it can be found in mobile service dashboard) and paste in the composer as shown below.
Once the URL has been copied we can start off by seeing the POST and GET request. First we check out GET request to Read the data from the table. We need to modify the URL. Take your base URL and we want to explore the tables. Now write the URL as https://your base url/Tables/table_name. Select the GET Request and Hit Execute and check out for the Result. We will get 401 error and there will be no response.
The reason we got 401 error is that we did not have permission to access that tables. As while creating your tables you set the permission to “any one with application key” so we need to send some additional information along with URL in headers that the application key. Where you can find your application key? The answer is simple and easy. You have already downloaded the sample app for windows 8 or android. Your application key can be found there. For now just open he Windows 8 App sample code in Visual Studio and in App.xaml file your app key can be found.
Now you need to send the key in the headers. In fiddler again type in your application key. “X-ZUMO-APPLICATION: ‘your app key ‘ “. Here ZU means Azure and MO mean Mobile.
Now if you click Execute you will get a JSON response. But the will be null because you have nothing in your table.
This is simple JSON Response you can also play around and look for different views. Since you have no Columns in your table and lets go and create some columns by a POST Request from Fiddler. For POST Request you need to send the data in JSON format. Here is who you will write up the data in Request Body
{“Title”: “This is Request from Fiddler”, “Complete”: true}
Now this request will automatically create the two Columns for you once you execute this request.
Make sure you have selected POST and you have written the JSON in correct format in Request body and go and hit Execute. Now you get a 201 Request telling you that data has been posted successfully.
Now go back to Azure Portal. Open Mobile Service go to Data and Access the table you will see the data has been added by the POST request and Two Columns have been created Automatically.
and also you can check this by making a get request from fiddler and you will get a JSON Response
Now Back to SQL Management Studio you can explore the tables. and you will find out the data types have been set automatically.
Title has beenset nvarchar and Complete has been set to bit. Now how does this things works automatically? This is quite simple. Come back to Azure portal and open your mobile service and click Configure Tab. There you will see dynamic schema is set to ON. Due to which it works in this way. You can turn it off.
Now go and play around more with data. Make some more POST and Get Request. Try creating more columns by POST request. Try to Execute the delete requests. Try to execute the Update request. The Update request can be execute by selecting PATCH and giving the column name in JSON format.
- Design