# Using keywords async/await in database queries (Windows Phone 8)

I have a local database in Windows Phone 8 app. The app includes a lot of queries to the database and I don't want bad effect on the responsiveness of UI.

For example I have a table of users and method to get a user from database by id.

Current variant

public class CacheDataContext : DataContext
{
public static string DBConnectionString = "Data Source=isostore:/Cache.sdf";

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

public static AutoResetEvent OperationOnDatabaseUser = new AutoResetEvent(true);

public Table<User> UserItems;
}

public class CacheDataContextUser : CacheDataContext
{
public CacheDataContextUser(string connectionString)
: base(connectionString) { }

public User GetUser(string id)
{
try
{
OperationOnDatabaseUser.WaitOne();
using (CacheDataContext context = new CacheDataContext(DBConnectionString))
{
//find user in the data base and return
}
}
finally
{
OperationOnDatabaseUser.Set();
}
}
}


I need ensure safety of the data if at the same time on database wallow different requests to add, modify, delete data. For this I use AutoResetEvent. Not sure what I'm doing it right, but so far no problems.

I can get user from the database:

using (DataBaseUser = new CacheDataContextFriends(ConnectionString))
{
var user = DataBaseUser.GetUser(id);
}


Async/await

But I want work with the database using keywords async/await.

public class CacheDataContextUser : CacheDataContext
{
public CacheDataContextUser(string connectionString)
: base(connectionString) { }

private object threadLock = new object();

{
using (CacheDataContext context = new CacheDataContext(DBConnectionString))
{
var result = await Task<User>.Factory.StartNew(() =>
{
{
//find user in the data base and return
}
});
return result;
}
}
}


I'm afraid to rewrite the method as described above, because I'm not sure it's right. Please tell me what the problem may be. My main goal is to improve the responsiveness of the app.

Before even addressing the actual question, have you actually proven that database queries are harming your responsiveness? Since the queries are to local storage it's possible that they are fast enough to not require using asynchrony, particularly if they are simple queries or the database is small.

Next, I notice you've assumed that the database itself is not threadsafe, i.e. that you must only allow access from one thread at a time. Are you sure that's actually true? Many (most?) database handle concurrency themselves, so you may be adding an unnecessary layer of synchronization. I looked around a bit, but could not find anything specifically documenting concurrent access to isolated storage databases. I would start by researching that, or possibly asking a question on StackOverflow. If the database does allow concurrent access then you just need to worry about update conflicts, which you could hopefully avoid in a single-user phone application.

What I'm getting at here is that multi-threading and locking is hard. Don't do it unless you're sure you have a good reason to do it.

If you really must to multi-threading, then the C# lock keyword is a good place to start. Unfortunately, your example probably will not work properly because each CacheDataUserContext instance will have it's own lock object - so if you create more than one instance they could conflict with each other.

You "Current Variant" actually gets this more right, because your AutoResetEvent is a static variable, so there is only a single instance of it across the system. However, as I understand DataContext it lets you use Linq statements against the database, which will not know anything about your lock and hence will not be synchronized.

I think you'd have to create a separate application layer to wrap the DataContext and expose just the certain operations that your application needs. This is generally called the "Repository Pattern". Inside the repository you could create a single lock object, wrap a lock around all accesses to a DataContext, and use Task.Factory.StartNew inside each of the repository methods to make them asynchronous.

• Thank you for your answer! My database is not very big, but sometimes happens bad responsiveness. For example on the map when you need to select the users with the right coordinates. If very often scale the map, it may slow down. In general, I understand your ideas, thank you. By the way, why lock better than AutoResetEvent? I can use static lock object in CacheDataContext: protected static readonly object threadLock = new object(); – Alexandr Feb 8 '14 at 21:26
• lock is simpler. It's also the language's default choice for mutual exclusion, so anyone reading the code will be able to tell what you're trying to do more easily. – breischl Feb 10 '14 at 14:58

I faced similar issues, please have a try of my implementation: ThreadSafe IsoStorageManager

I'm doing heavy testing right now, and current version seems to be quite stable.