You might have noticed that the offline
application we have been experimenting
with in this demo series is always
talking to SQL Server as the backend
database. Now you ask: What If my
backend database is not SQL Server? Am I
going to write loads of code to achieve
the same functionality that I get in few
lines of code with SQL Server as a
backend? After all, this a Microsoft
technology and SQL Server is the
database of choice, who cares about any
other databases!? you think!.
Well, that is not the case. The sync
services was designed with heterogeneous
database support in mind. If you read
into the API closely, you would notice
that the server provider class is named
as DbServerSyncProvider (eh, not
SqlServerSyncProivder) which implies
that it is for any database and not
limited to SQL Server. As long as you
have ADO.NET provider for the database
of your choice, you are set to take
advantage of Sync Services.
In this demo, I will change the offline
application to talk to Oracle database.
To keep things in focus and simplify the
code, I will just implement a download
only scenario for 'orders' table.
However, you can implement all other
features of the sync services against
Oracle with same ease and control.
ADO.NET has a special data access
provider for Oracle database which is
what I am going to use in this demo. You
could also use OLEDB or ODBC providers
if you so choose.
The snapshot below shows the demo main
form. Notice that I added a new text box
for connection string such that you can
connect to the Oracle database easily. I
also added two buttons at the button of
the form to create and delete the
'orders' table. The random insert,
update and delete buttons are also there
as shortcut to simplify manipulation of
data on the Oracle database. Since this
is a download only demo, these data
manipulation buttons will be disabled
when switching to the client database
grid view.
As shown
below, the code for the SyncAdapter is
rather straight forward. I only need to
set the commands for selecting
incremental inserts, updates and deletes
and that suffice for download only
scenario:
// orders table
SyncAdapter adaptorOrders =
new
SyncAdapter("orders");
// select incremental inserts command
OracleCommand incInsOrdersCmd =
new
OracleCommand();
incInsOrdersCmd.CommandType =
CommandType.Text;
incInsOrdersCmd.CommandText =
"SELECT id,
col2, col3, col4 FROM orders " +
"WHERE
track_insert is null OR ( track_insert
> :" +
SyncSession.SyncLastReceivedAnchor +
" " +
"and track_insert
<= :" +
SyncSession.SyncNewReceivedAnchor +
" )";
incInsOrdersCmd.Parameters.Add(":"
+ SyncSession.SyncLastReceivedAnchor,
OracleType.Timestamp);
incInsOrdersCmd.Parameters.Add(":"
+ SyncSession.SyncNewReceivedAnchor,
OracleType.Timestamp;
adaptorOrders.SelectIncrementalInsertsCommand
= incInsOrdersCmd;
// select incremental updates command
OracleCommand incUpdOrdersCmd = (OracleCommand)incInsOrdersCmd.Clone();
incUpdOrdersCmd.CommandText =
"SELECT id,
col2, col3, col4 FROM orders " +
"WHERE (track_update > :"+SyncSession.SyncLastReceivedAnchor+")
" +
"and (track_update
<= :"+SyncSession.SyncNewReceivedAnchor+")
" +
"and (track_insert
<= :"+SyncSession.SyncLastReceivedAnchor+")
";
incUpdOrdersCmd.Parameters.Add(":"
+ SyncSession.SyncLastReceivedAnchor,
OracleType.Timestamp);
incUpdOrdersCmd.Parameters.Add(":"
+ SyncSession.SyncNewReceivedAnchor,
OracleType.Timestamp);
adaptorOrders.SelectIncrementalUpdatesCommand
= incUpdOrdersCmd;
serverSyncProvider.SyncAdapters.Add(adaptorOrders);
If there
is one thing I want to stress on in this
demo, other than the ability to use
SyncServerProvider for heterogeneous
backend, is the type mapping between the
server database and the client database.
Oracle database has different type
system than that of SqlCe which is the
client store. To illustrate this
point, I used different table schema for
'orders' table with different data type
for each column.
Now, if
you try to run the application it will
fail to create the schema on the
client. Basically the schema information
retrieved from the Oracle server
database is not enough for SqlCe to
create the orders table. In order to fix
that, I need to abandon the automatic
schema retrieval logic that the server
provider uses and instead supply a
SyncSchema object to the provider with
all mapping information. The code below
shows how I did that for the first 3
columns:
//
// 5. Custom Schema
//
SyncSchema syncSchema =
new
SyncSchema();
serverSyncProvider.Schema = syncSchema;
syncSchema.Tables.Add("orders");
syncSchema.Tables["orders"].Columns.Add("id");
syncSchema.Tables["orders"].Columns["id"].AllowNull = false;
syncSchema.Tables["orders"].Columns["id"].ProviderDataType = "NUMERIC";
syncSchema.Tables["orders"].Columns["id"].NumericPrecision = 5;
syncSchema.Tables["orders"].Columns["id"].NumericScale = 0;
syncSchema.Tables["orders"].PrimaryKey
= new
string[]
{ "id"
};
syncSchema.Tables["orders"].Columns.Add("col2");
syncSchema.Tables["orders"].Columns["col2"].ProviderDataType
= "CHAR";
syncSchema.Tables["orders"].Columns["col2"].MaxLength = 40;
syncSchema.Tables["orders"].Columns.Add("col3");
syncSchema.Tables["orders"].Columns["col3"].DataType = typeof(String);
syncSchema.Tables["orders"].Columns["col3"].MaxLength = 32;
syncSchema.Tables["orders"].Columns.Add("col4");
syncSchema.Tables["orders"].Columns["col4"].ProviderDataType = "NUMERIC";
syncSchema.Tables["orders"].Columns["col4"].NumericPrecision = 38;
syncSchema.Tables["orders"].Columns["col4"].NumericScale = 0;
The rest
of the columns can be mapped similarly.
If you
inspected the auto generated schema by
the provider, you would notice that the
NumericPrecision and NumericScale values
are not set which is why the client
fails to use it. The ability to author
the schema and pass it to the server
provider gives you control on how the
tables are set on the client regardless
of backend data store. This is such a
powerful feature and will allow you to
build custom server providers for none
database backbends.
Enjoy!
Install OfflineAppDemo
Application Steps:
Version
1.2 [C#]:
Download Now
|
