EF development-time initializer for your .NET-based mobile service

Update: look at that… based on your feedback we’ve baked this right into the product (starting with version 1.0.342 of our NuGet package). Check out this tutorial for instructions on how to use the new initializers, and please disregard the code below. What’s in the box works better!

A few folks have encountered an issue while developing a .NET-based mobile service using our VS template or portal quickstart. If you publish your mobile service to the cloud and then make some changes to the model and re-publish, the EF initializer will fail complaining that it doesn’t have sufficient permissions. The error might look like so in the Logs tab of the portal:

Exception=System.InvalidOperationException: This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection. ---> System.Data.SqlClient.SqlException: Login failed for user 'zXCBHhDWhTLogin'.

This is the case because by default we use the DropCreateDatabaseIfModelChanges initializer, but the user under which your mobile service accesses your Azure SQL database doesn’t have the permission to drop the database.

To work around that we have developed an alternative initializer that does largely the same thing, but works using the permission set we already have. Please exercise caution: this initializer will delete your data. It is intended to be used during development while you are experimenting with your model and database schema.

Here is the example: instead of dropping the whole database, it just drops the tables used by your mobile service:

public class DropCreateSchemaTablesIfModelChanges<TContext> : CreateDatabaseIfNotExists<TContext> where TContext : DbContext
{
    private string schemaName;

    public DropCreateSchemaTablesIfModelChanges(string schemaName)
    {
        this.schemaName = schemaName;
    }

    public override void InitializeDatabase(TContext context)
    {
        if (context == null)
        {
            throw new ArgumentException("Context is null");
        }

        if (String.IsNullOrEmpty(schemaName))
        {
            throw new ArgumentException("Please provide a non-empty schema name");
        }

        if (context.Database.Exists())
        {
            try
            {
                if (context.Database.CompatibleWithModel(true))
                {
                    return;
                }
            }
            catch
            {

            }
            finally
            {
                var command = @"
                    DECLARE @tableName NVARCHAR(500)
                    DECLARE cur cursor
                        FOR
                            SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
                            FROM INFORMATION_SCHEMA.TABLES
                            WHERE TABLE_SCHEMA = @ms_schema
                    OPEN cur
                    FETCH NEXT FROM cur INTO @tableName
                        WHILE @@FETCH_STATUS = 0
                        BEGIN
                            EXEC('DROP TABLE ' + @tableName);
                            FETCH NEXT FROM cur INTO @tableName;
                        END
                    CLOSE cur
                    DEALLOCATE cur";

                var cs = context.Database.Connection.ConnectionString;

                using (var objConn = new SqlConnection(cs))
                {
                    objConn.Open();
                    var objCmd = new SqlCommand(command, objConn);
                    objCmd.Parameters.Add("@ms_schema", SqlDbType.NVarChar, 64);
                    objCmd.Parameters["@ms_schema"].Value = schemaName;
                    objCmd.ExecuteNonQuery();
                }
            }


        }

        // Make sure all the tables are there
        base.InitializeDatabase(context);

        this.Seed(context);
        context.SaveChanges();
    }

    protected virtual void Seed(TContext context)
    {
    }
}

To use this initializer, create a class that inherits from it so you can specify a Seed method with your sample data:

public class myAwesomeServiceInitializer : DropCreateSchemaTablesIfModelChanges<myAwesomeServiceContext>
{
    public myAwesomeServiceInitializer(string schemaName) : base(schemaName)
    {

    }

    protected override void Seed(myAwesomeServiceContext context)
    {
        List<TodoItem> todoItems = new List<TodoItem>
        {
            new TodoItem { Id = "1", Text = "First item", Complete = false },
            new TodoItem { Id = "2", Text = "Second item", Complete = false },
        };

        foreach (TodoItem todoItem in todoItems)
        {
            context.Set<TodoItem>().Add(todoItem);
        }

        base.Seed(context);
    }
}

Then, put the following in your WebApiConfig.Register method:

Database.SetInitializer(new myAwesomeServiceInitializer(ServiceSettingsDictionary.GetSchemaName()));

Hope this helps!

The new support for writing mobile services in .NET has made it even easier for developers writing Windows Phone 8.1 apps to add a cloud hosted backend. Check out this short video to learn more.

(Source: channel9.msdn.com)

Using custom connection strings with the mobile services .NET backend

Since we introduced the .NET backend in Mobile Services, with rich support for connecting to existing databases, we have received a lot of questions around using custom connection strings. By default, Mobile Services creates an Azure SQL Database for you and the connection string used to refer to that is named MS_TableConnectionString. Our quickstart and Visual Studio project template uses that connection string by default. 

So what do you do if you want to use your own connection string? It depends on what kind of database yo have.

  • If your database is a Azure SQL database, just use the nifty Change Database button on the Configure tab of your mobile service. This will directly update the MS_TableConnectionString.
    image
  • If you are bringing your own database (for example SQL Server on IaaS), you cannot currently modify the MS_TableConnectionString connection string. We are working on enabling that and adding first-class support for connection string management right in the portal, but for now we have a simple workaround. Read on.

The first thing you need to do is find the App Settings section on the Configure tab of your mobile service. Create a new setting called for example onPremisesDatabase and set the value as your custom connection string. 

image

Inside your app, find the DbContext that your .NET mobile service is using. Modify it as shown below. You might need to add an assembly reference to System.Configuration to your project.

public class customDatabase1Context : DbContext
{
    public customDatabase1Context()
        : base(ConfigurationManager.AppSettings["onPremisesDatabase"])
    {
    }
}

Then simply publish your changes and your service will now use your custom connection string. You are welcome to go and update the app setting in the portal if you need to and you don’t need to re-publish your app for the change to take effect.

Hope this helps!

If you want to learn about the new option of writing a .NET backend for your Azure Mobile Service, check out this video I made with Nick and Chris. We cover using new or existing data models and also demonstrate how to remotely debug the live service.

(Source: channel9.msdn.com)

Azure Mobile Services at //build/ 2014

//build/ 2014 wrapped up Friday, and it has been a great opportunity for our team to highlight an exciting set of new enterprise feature we’ve been working on for the last few months. We started with our segment in Scott’s keynote, which showcased a killer use case for the modern enterprise: how to build a service using .NET, add authentication with Active Directory, save documents to SharePoint, and build cross-platform clients with Xamarin. You can skip forward to 45:23 in the video if you are just interested in the mobile segment

The keynote segment touched on a few features that we then explored in-depth in our presentation:

Here are the slides for our talk. I’m working on getting the samples posted as well, keep an eye on this space. 

We had some other great Mobile Services talks at //build/ that you may want to check out as well: