Walkthrough: Attaching an Azure SQL database to your .NET Backend
Overview
I needed to attach an existing Azure SQL database to my .NET Backend. This will allow me to expose existing data using the Azure Mobile Service I have defined. This will walk you through the steps I took and get you on your way!
Setup
Create a new Mobile Service or download the starter project from your existing Mobile Service based on the .NET Backend.
My existing Azure SQL database has the following structure the schema is in the dbo schema (you can see this through the Management portal of your Azure SQL Databases and will be used for this example:
Details
Get and Build base project
My existing Mobile Service only has the TodoItem table associated with it.
Download the starter project form the Mobile Services Dashboard if you don’t already have a local copy to work with or if this is a brand new project you can create your Mobile Service inside of Visual Studio.
Or you can create an new Mobile Service project and publish it later:
Ensure you build your solution at least once to get the appropriate packages
Add Tables from Existing Database
Now we will add the existing database to the Mobile Services project.
Right click on the Models folder in your project and choose ‘Add’, ‘ADO.NET Entity Data Model’
You can name it whatever you want but to stay organized, name it the same as your table.
Hit ‘OK’ then choose ‘Code First from database’:
Hit next and then choose ‘New Connection’
Here you need some information from your azure management portal for your database:
Copy the server information (in my case you can see what I entered). Ensure you are using SQL authentication and them find your database in the combo box and select it:
Hit ‘OK’
This will populate your information. Select ‘No’ on the radio button about sensitive data and hit ‘Next’:
I selected 2 tables under the dbo schema and hit ‘Finish’:
This generates Model Files for each of the Tables I have chosen (and that is it).
Next we need to hook these models up to the Database and expose them through a Table Controller.
Create EntityData models
To expose these new Models to the Table Controller, we need to get these into the format the Entity Framework understands. We will call these ‘Dto’ or ‘DataTableObject’ classes.
Concentrating on one table (StuffToGet) here is the code generated by my previous steps:
namespace MobileServiceTest.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
[Table("StuffToGet")]
public partial class StuffToGet
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ID { get; set; }
[Required]
[StringLength(50)]
public string Name { get; set; }
[StringLength(15)]
public string Description { get; set; }
public int? Purchased { get; set; }
}
}
Right click on the ‘DataObjects’ folder and create a new class with the same name as your Model but append ‘Dto’:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MobileServiceTest.DataObjects
{
public class StuffToGetDto
{
}
}
Then make the following similar modifications to your class to make it an EntiyData derived class (removing the primary ID field and normalizing the types to Mobile Service Friendly types:
using Microsoft.WindowsAzure.Mobile.Service;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MobileServiceTest.DataObjects
{
public class StuffToGetDto:EntityData
{
public string Name { get; set; }
public string Description { get; set; }
public int? Purchased { get; set; }
}
}
Now we need a controller for that Dto (DataTableObject)
Create Table controller
Right click on the ‘Controllers’ folder of your solution explorer and choose: ‘Add’, ‘Controller’
Select the Mobile Services table controller as pictured:
Add the new DataModel you just created and hit the + button to create a new DbContext for the existing AzureSQL db:
Hit ‘Add’ and this will create the DbContext and Table Controller for this connection and table. And hit ‘OK’
Modify the created DbContext
Now remove the DbContext code and make these similar changes (see comments). In our case the generated file is ExistingAzureSqlContext.cs:
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using Microsoft.WindowsAzure.Mobile.Service;
using Microsoft.WindowsAzure.Mobile.Service.Tables;
namespace MobileServiceTest.Models
{
public class ExistingAzureSqlContext : DbContext
{
// change contructor to take the dbConnection string
public ExistingAzureSqlContext(string dbConnectionString) :
base(dbConnectionString)
{
}
//change model to the Model and not the Dto
public System.Data.Entity.DbSet<StuffToGet> StuffToGet { get; set; }
}
}
This database context will be used in the controller code to attach to the Azure SQL table.
Create Utility Code
Now we need some utility code to map between the Model and the Dto.
Right click on the Models folder and create a new class called ‘SimpleMappedEntityDomainManager’ and put this in the file:
using AutoMapper;
using AutoMapper.Impl;
using Microsoft.WindowsAzure.Mobile.Service;
using Microsoft.WindowsAzure.Mobile.Service.Tables;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
using System.Net.Http;
using System.Reflection;
using System.Threading.Tasks;
using System.Web;
using System.Web.Http;
using System.Web.Http.OData;
namespace MobileServiceUtilities.Models
{
//Utilities for Hooking up models
public static class MySqlFuncs
{
[DbFunction("SqlServer", "STR")]
public static string StringConvert(long number)
{
return number.ToString();
}
[DbFunction("SqlServer", "LTRIM")]
public static string LTRIM(string s)
{
return s == null ? null : s.TrimStart();
}
// Can only be used locally.
public static long LongParse(string s)
{
long ret;
long.TryParse(s, out ret);
return ret;
}
}
public class SimpleMappedEntityDomainManager<TData, TModel>
: MappedEntityDomainManager<TData, TModel>
where TData : class, ITableData, new()
where TModel : class
{
private Expression<Func<TModel, object>> dbKeyProperty;
public SimpleMappedEntityDomainManager(DbContext context,
HttpRequestMessage request, ApiServices services,
Expression<Func<TModel, object>> dbKeyProperty)
: base(context, request, services)
{
this.dbKeyProperty = dbKeyProperty;
}
public override SingleResult<TData> Lookup(string id)
{
return this.LookupEntity(GeneratePredicate(id));
}
public override async Task<TData> UpdateAsync(string id, Delta<TData> patch)
{
return await this.UpdateEntityAsync(patch, ConvertId(id));
}
public override Task<bool> DeleteAsync(string id)
{
return this.DeleteItemAsync(ConvertId(id));
}
private static Expression<Func<TModel, bool>> GeneratePredicate(string id)
{
var m = Mapper.FindTypeMapFor<TModel, TData>();
var pmForId = m.GetExistingPropertyMapFor(new PropertyAccessor(typeof(TData).GetProperty("Id")));
var keyString = pmForId.CustomExpression;
var predicate = Expression.Lambda<Func<TModel, bool>>(
Expression.Equal(keyString.Body, Expression.Constant(id)),
keyString.Parameters[0]);
return predicate;
}
private object ConvertId(string id)
{
var m = Mapper.FindTypeMapFor<TData, TModel>();
var keyPropertyAccessor = GetPropertyAccessor(this.dbKeyProperty);
var pmForId = m.GetExistingPropertyMapFor(new PropertyAccessor(keyPropertyAccessor));
TData tmp = new TData() { Id = id };
var convertedId = pmForId.CustomExpression.Compile().DynamicInvoke(tmp);
return convertedId;
}
private PropertyInfo GetPropertyAccessor(Expression exp)
{
if (exp.NodeType == ExpressionType.Lambda)
{
var lambda = exp as LambdaExpression;
return GetPropertyAccessor(lambda.Body);
}
else if (exp.NodeType == ExpressionType.Convert)
{
var convert = exp as UnaryExpression;
return GetPropertyAccessor(convert.Operand);
}
else if (exp.NodeType == ExpressionType.MemberAccess)
{
var propExp = exp as System.Linq.Expressions.MemberExpression;
return propExp.Member as PropertyInfo;
}
else
{
throw new InvalidOperationException("Unexpected expression node type: " + exp.NodeType);
}
}
}
}
Modify Controller code to use new mapper
Modify the Controller code you just generated as follows and ensure you add this reference to the top (see comments):
using MobileServiceUtilities.Models;
public class StuffToGetDtoController : TableController<StuffToGetDto>
{
protected override void Initialize(HttpControllerContext controllerContext)
{
base.Initialize(controllerContext);
//modify the context to use the constructor that will take a connection string - stored in web.config
ExistingAzureSqlContext context = new ExistingAzureSqlContext(Services.Settings["ExistingDbConnectionString"]);
// DomainManager = new EntityDomainManager<StuffToGetDto>(context, Request, Services);
// set DomainManger to a new one that we created
DomainManager = new SimpleMappedEntityDomainManager<StuffToGetDto, StuffToGet>(
context,
Request,
Services,
stuffToGet => stuffToGet.ID );
}
Map the Id field in the Dto to the ID field in the Model
Finally we need to map between the Dto and Model objects. In this case the integer ‘ID’ field in the model has to map to the Mobile Service friendly ‘Id’ field which is a string.
Open WebApiConfig.cs in the App_Start folder and add this code to the end of the Register function:
AutoMapper.Mapper.Initialize(cfg =>
{
// Mapping from database type to client type
cfg.CreateMap<StuffToGet, StuffToGetDto>()
.ForMember(dst => dst.Id, map => map.MapFrom(src => MySqlFuncs.LTRIM(MySqlFuncs.StringConvert(src.ID))));
// Mapping from client type to database type
cfg.CreateMap<StuffToGetDto, StuffToGet>()
.ForMember(dst => dst.ID, map => map.MapFrom(src => MySqlFuncs.LongParse(src.Id)));
});
Add Connection String to Web.Config
The connection string specified in the DbContext needs to be added to your WebConfig.
You can get this string from your Azure SQL Management dashboard:
Copy the ADO.NET one:
Open your Web.Config and add the key ‘ExistingDbConnectionString’ and the value is this connection string, ensuring you put your password in the section that says {your_password_here}
<appSettings>
<!-- Service Bus specific app setings for messaging connections -->
<add key="PreserveLoginUrl" value="true" />
<!-- Use these settings for local development. After publishing to
Mobile Services, these settings will be overridden by the values specified
in the portal. -->
<add key="ExistingDbConnectionString" value="Server=tcp:nagog6s9ep.database.windows.net,1433;Database=dbForJason;User ID=dbadmin@nagog6s9ep;Password=mypassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;"/>
<add key="MS_MobileServiceName" value="MobileServiceTest" />
Build
Finally, build the solution. Resolve and missing ‘using’ statements by right clicking on them and choosing ‘Resolve’
Test
Now you can run this solution local and test in IE.
Hit the ‘Try it’ button in IE and try to POST, GET and PATCH data into your Azure DB through the Mobile service!
Now, How about adding additional tables?
Easy!
Add Additional Tables from database
We included a 2nd table when we go the StuffToGet table. Let’s now add Table2 as well since we included the model when we got the first table.
Go through the same steps above:
Add the Dto class:
using Microsoft.WindowsAzure.Mobile.Service;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace MobileServiceTest.DataObjects
{
public class Table2Dto:EntityData
{
public string Column1 { get; set; }
public string Column2 { get; set; }
}
}
Create Table controller
This is where it will get a little weird. In this step do NOT create a new dbContext. Instead select the existing one (note that the new one does not show up).
Add a table controller and use the existing dbContext :
Now open that db context (MobileServicesTestContext) and remove the table from that:
}
//REMOVE this
//public System.Data.Entity.DbSet<MobileServiceTest.DataObjects.Table2Dto> Table2Dto { get; set; }
}
And add this into ExistingAzureSqlContext.cs under the other table definition:
//change model to the Model and not the Dto
public System.Data.Entity.DbSet<StuffToGet> StuffToGet { get; set; }
public System.Data.Entity.DbSet<Table2> Table2 { get; set; }
}
Like before modify the Controller to use the ExistingAzureSqlContext:
protected override void Initialize(HttpControllerContext controllerContext)
{
base.Initialize(controllerContext);
ExistingAzureSqlContext context = new ExistingAzureSqlContext(Services.Settings["ExistingDbConnectionString"]);
// DomainManager = new EntityDomainManager<StuffToGetDto>(context, Request, Services);
// set DomainManger to a new one that we will create
DomainManager = new SimpleMappedEntityDomainManager<Table2Dto, Table2>(
context,
Request,
Services,
table2 => table2.ID);
}
Map the Id field in the Dto to the ID field in the Model
And add the automapper information:
AutoMapper.Mapper.Initialize(cfg =>
{
// Mapping from database type to client type
cfg.CreateMap<StuffToGet, StuffToGetDto>()
.ForMember(dst => dst.Id, map => map.MapFrom(src => MySqlFuncs.LTRIM(MySqlFuncs.StringConvert(src.ID))));
// Mapping from client type to database type
cfg.CreateMap<StuffToGetDto, StuffToGet>()
.ForMember(dst => dst.ID, map => map.MapFrom(src => MySqlFuncs.LongParse(src.Id)));
// Mapping from database type to client type
cfg.CreateMap<Table2, Table2Dto>()
.ForMember(dst => dst.Id, map => map.MapFrom(src => MySqlFuncs.LTRIM(MySqlFuncs.StringConvert(src.ID))));
// Mapping from client type to database type
cfg.CreateMap<Table2Dto, Table2>()
.ForMember(dst => dst.ID, map => map.MapFrom(src => MySqlFuncs.LongParse(src.Id)));
});
Build
Run and test!
Conclusion
Once you run through this you should be able to see how you can easily bring your own DB to you Mobile Service .NET Backend. You can do some cool stuff with the Mapper as well so check out the links below.
Let me know if this was useful to you!
Follow me @jsandersrocks and my team at @WSDevSol on Twitter.
More information
Rename the table exposed by the controller:
You can rename the Controller to change the public facing name of the table.
So if you want StuffToGetDto to be exposed as StuffToGet, simply rename the class in the file it is defined:
/ old name public class StuffToGetDtoController : TableController<StuffToGetDto>
//new name of controller without Dto suffix
public class StuffToGetController : TableController<StuffToGetDto>
{
Build Mobile Services .NET Backend with no Azure Subscription:
If you don't have an Azure account you can still play with this. From Visual Studio select the ‘Windows Azure Mobile Service’ template under the ‘Visual C#’, ‘Cloud’ templates when you create a new project
Other links:
Bring Your Own Database with the .NET Backend
Mapping between Database Types and Client Types in the .NET Backend using AutoMapper.
Tables with Integer Keys and the .NET Backend.
Comments
- Anonymous
February 11, 2015
Great walkthrough! I can GET, POST, and DELETE with my sample table, but I can't PATCH. Any ideas why that might be? I did generate EF code-first from a local copy of the SQL database rather than the (hopefully) identical one hosted in Azure.