Bring Your Own Database with the .NET Backend

  • Article

Azure Mobile Services helps provision a database for you when you create a new mobile service. But what if you want to connect to your own, existing SQL Azure database with the .NET backend? For example:

  • You have an existing Mobile Service that uses the Node backend and has a lot of data in the database, and you want to access that data with the .NET backend.
  • You have data in an existing database that you want to expose through the .NET backend.

The basic steps are:

  1. Add the database model and database types that map to your database tables.
  2. Add an application setting that contains the connection string for your database.
  3. Add a constructor for your model's data context that takes the connection string, read from app settings.
  4. Add client types (also known as Data Transfer Objects, or DTOs)  that implement ITableData, so they can be used by the TableController<T> .
  5. Add AutoMapper mappings between the model types and the client types.
  6. Update the controllers to use the client types and the mappings.

For more background on steps 4 through 6, take a look at Mapping between Database Types and Client Types in the .NET Backend using AutoMapper.

I'll show how to handle the scenario where you want to reuse your Node backend's database, but you can easily apply the same process to the "bring your own database" scenario.

Setup the Node Backend Database (optional)

To start with, I'll set up a Mobile Service with a Node backend, so I'll have a database with data in it.

  1. Create Node Mobile Service App.
  2. Follow the Quickstart steps to create a new client application.
  3. Run the client application to insert some data.
  4. In the Mobile Service portal, go to the Node app, and click on the CONFIGURE tab
  5. From the Click on the SQL Database's Dashboard tab.
  6. Click on the Manage allowed IP addresses link, and add your IP address.
  7. Click the show connection strings link.
  8. Copy the ADO.NET connection string.

Add Database Model to .NET Backend

Now that the database is set up and we have the connection string, we are ready to prepare the .NET Mobile Service app.

  1. Create .NET Mobile Service app.

  2. Follow the Quickstart steps to create a new client app.

  3. Build the Visual Studio project once to pull down the necessary NuGet packages.

  4. Rename TodoItem to TodoItemDto, to prevent a name collision once we import the database model.

  5. Add an Entity Data Model that connects to the existing database.

    1. Right-click on the Models folder in your server project and choose Add -> New Item
    2. Choose Data -> ADO.NET Entity Data Model.
    3. Give it a helpful name, like ExistingNodeDatabaseContext, and press ADD.
    4. Select Code First from Database.
    5. In the Choose Your Data Connection page, enter a new connection for your server and database.
    6. Still in the Choose Your Data Connection page, select the No, exclude sensitive data...  radio button, then click Next.
    7. In the Choose Your Database Objects and Settings page, expand the Tables node to see the available database schemas. Choose the schema with the same name as the Node Mobile Service. Click Next to generate the data model.

  6. Add a reference to the System.ComponentModel.DataAnnotations (Assemblies -> Framework -> System.ComponentModel.DataAnnotations) to correct any compiler errors in the generated code.

  7. Open the generated ExistingNodeDatabaseContext class and add this constructor:

     public ExistingNodeDatabaseContext(string connectionString)
    : base(connectionString) { }

Add Mappings Between Database Types and Client Types

We need to map the new generated database TodoItem type to the existing TodoItemDto type. The database model cannot be exposed directly to callers of the service, because it does not implement ITableData. However, TodoItemDto does implement ITableData, and has largely the same shape. We can use AutoMapper move data between the types.

  1. Add this domain manager to allow the controllers to communicate with the new context. Note: This is the same domain manager as used in Tables with Integer Keys and the .NET Backend.

     public class SimpleMappedEntityDomainManager<TData, TModel>
    : MappedEntityDomainManager<TData, TModel>
    where TData : class, ITableData
    where TModel : class
{
    private Func<TModel, string> keyString;
    public SimpleMappedEntityDomainManager(DbContext context,
        HttpRequestMessage request, ApiServices services,
        Func<TModel, string> keyString)
        : base(context, request, services)
    {
        this.keyString = keyString;
    }
    public override SingleResult<TData> Lookup(string id)
    {
        return this.LookupEntity(p => this.keyString(p) == id);
    }
    public override Task<TData> UpdateAsync(string id, Delta<TData> patch)
    {
        return this.UpdateEntityAsync(patch, id);
    }
    public override Task<bool> DeleteAsync(string id)
    {
        return this.DeleteItemAsync(id);
    }
}

  2. In the TodoItemController, replace the assignment of context and DomainManager with the new database context and mapped domain manager:

     //YourServiceContext context = new YourServiceContext();
//DomainManager = new EntityDomainManager<TodoItemDto>(
// context, Request, Services);
ExistingNodeDatabaseContext context = new ExistingNodeDatabaseContext(
    Services.Settings["ExistingDbConnectionString"]);
DomainManager = new SimpleMappedEntityDomainManager<TodoItemDto, TodoItem>(
    context, Request, Services, todoItem => todoItem.id);

  3. In App_Start\WebApiConfig, remove the Database.SetInitializer and add mappings. We need to explain how to map between the TodoItemDto entity and the new generated TodoItem entity. This mapping explicitly says how to map between properties whose name do not match, and how to convert nullable types to non-nullable types.

     //Database.SetInitializer(new yourProjectNameInitializer());
AutoMapper.Mapper.Initialize(cfg =>
{
    // Mapping from database type to client type
    cfg.CreateMap<TodoItem, TodoItemDto>()
        .ForMember(dst => dst.Complete, map => map.MapFrom(
                   src => src.complete ?? false))
        .ForMember(dst => dst.CreatedAt, map => map.MapFrom(
                   src => src.C__createdAt))
        .ForMember(dst => dst.UpdatedAt, map => map.MapFrom(
                   src => src.C__updatedAt))
        .ForMember(dst => dst.Version, map => map.MapFrom(
                   src => src.C__version));
    // Mapping from client type to database type
    cfg.CreateMap<TodoItemDto, TodoItem>()
        .ForMember(dst => dst.C__createdAt, map => map.MapFrom(
                   src => src.CreatedAt ?? DateTimeOffset.MinValue))
        .ForMember(dst => dst.C__updatedAt, map => map.MapFrom(
                   src => src.UpdatedAt))
        .ForMember(dst => dst.C__version, map => map.MapFrom(
                   src => src.Version));
});

    Note: If you get an error The name 'AutoMapper' does not exist in the current context, then add references to AutoMapper.dll and AutoMapper.Net4.dll from \packages\AutoMapper.3.1.1\lib\net40. This was an issue with older quickstarts, but it should be fixed soon.

  4. In App_Start\WebApiConfig, delete the definition of yourInitializer.

  5. In the Models folder, delete the YourServiceContext.cs.

  6. You need to specify an app setting named ExistingDbConnectionString, and give it the value of your ADO.NET connection string.

    •  When running locally, you do this by setting one of the app settings in the Web.config

       <add key="ExistingDbConnectionString" value="yourConnectionString" />

    • When deploying the cloud, you do this by going to the Configure tab for the mobile service, and adding an app setting:

      • Key: ExistingDbConnectionString
      • Value: yourConnectionString

Now, when you run the client app that connects to the .NET Backend, it will be using the same database as the original Node Backend.