How to use MS Sync Framework to filter client-specific data?

Question

Let's say I've got a SQL 2008 database table with lots of records associated with two different customers, Customer A and Customer B.

I would like to build a fat client application that fetches all of the records that are specific to either Customer A or Customer B based on the credentials of the requesting user, then stores the fetched records in a temporary local table.

Thinking I might use the MS Sync Framework to accomplish this, I started reading about row filtering when I came across this little chestnut:

Do not rely on filtering for security. The ability to filter data from the server based on a client or user ID is not a security feature. In other words, this approach cannot be used to prevent one client from reading data that belongs to another client. This type of filtering is useful only for partitioning data and reducing the amount of data that is brought down to the client database.

So, is this telling me that the MS Sync Framework is only a good option when you want to replicate an entire table between point A and point B?

Doesn't that seem to be an extremely limiting characteristic of the framework? Or am I just interpreting this statement incorrectly? Or is there some other way to use the framework to achieve my purposes?

Ideas anyone?

Thanks!

leppie leppie · Accepted Answer · 2011-01-06T07:54:23

No, it is only a security warning.

We use filtering extensively in our semi-connected app.

Here is some code to get you started:

//helper
void PrepareFilter(string tablename, string filter)
{
  SyncAdapters.Remove(tablename);

  var ab = new SqlSyncAdapterBuilder(this.Connection as SqlConnection);
  ab.TableName = "dbo." + tablename;
  ab.ChangeTrackingType = ChangeTrackingType.SqlServerChangeTracking;
  ab.FilterClause = filter;
  var cpar = new SqlParameter("@filterid", SqlDbType.UniqueIdentifier);
  cpar.IsNullable = true;
  cpar.Value = DBNull.Value;
  ab.FilterParameters.Add(cpar);

  var nsa = ab.ToSyncAdapter();
  nsa.TableName = tablename;

  SyncAdapters.Add(nsa);
}

// usage
void SetupFooBar()
{
  var tablename = "FooBar";
  var filter = "FooId IN (SELECT BarId FROM dbo.GetAllFooBars(@filterid))";

  PrepareFilter(tablename, filter);
}

How to use MS Sync Framework to filter client-specific data?

1 Answers