Page 1
Page 2
When Microsoft released Sql Server
CE, developers for Pocket PC and CE based devices were given
a excellent platform for building data intense mobile applications.
This mobile database engine provides a set of features that
are very similar to the full-scale Sql Server product, which
are enhanced by powerful replication capabilities for keeping
this data syncronized with a backend Sql Server. Since the
majority of mobile applications use local databases as a
form of cache for server-based data, this syncronization
capabilities is very useful. Sql Ce became even more useful
when the .Net compact framework was released as it could
then be utilized via ADO.Net interfaces with a powerful
object-oriented language.
As mobile solutions continue to grow
in popularity, developers are increasingly facing data connectivity
challenges and are not always able replicate their backend
data from a Sql Server. Legacy data can be exposed in a
variety of ways and many systems are exposing data via a
more generic XML or Webservice interface, and mobile developers
are increasingly interfacing with these sources.
However, when a mobile developer attempts
to use ADO.Net to syncronize more than a few records with
these alternative data sources, they often discover the
hard way that performance issues can make this a very slow
operation. The process of downloading an XML or flat file
and trying to load it into SQL CE via an ADO.Net DataSet
can take a number of minutes for a few thousand records.
10,000 records can even require 10 minutes! With growing
memory capacities of modern devices, mobile data requirements
can easily expand into many tens or even hundreds of thousands
of records.
SSCEDirect is a product recently release
by Fitiri Inc. as a way to speed up these bulk data loading
operations with Sql Server CE. Performance improvements
seen with a sample 10,000 records is a reduction from 10
minutes using a DataSet to approx 20 seconds using SSCEdirect.
Lets walk through a simple example of
how to use the SSCEDirect software (Fitiri offers a demo
version at their website that allows you to try the full
features, but will randomly alter some of your database
varchar data). You can request a demo of the software at
this url: http://www.fitiri.com/SSCEDirect.html
Lets review some example C# source code
to do a performance comparison. The first example will use
a DataSet to insert new records into a Sql Ce database.
Code to track elapsed time should be added here to calculate
how long it takes, but is left out of this example to keep
it cleaner.
public void SampleLoad(){
DataSet ds = new DataSet();
//We will manually load a dataset for
this example, normally this would be
//done by loading data from serialized XML.
DataTable dt = new DataTable("sampletable");
dt.Columns.Add(new DataColumn("col1", typeof(System.Int32)));
dt.Columns.Add(new DataColumn("col2", typeof(System.String)));
dt.Columns.Add(new DataColumn("col3", typeof(System.String)));
dt.Columns.Add(new DataColumn("col4", typeof(System.DateTime)));
dt.Columns.Add(new DataColumn("col5", typeof(System.Decimal)));
dt.Columns.Add(new DataColumn("col6", typeof(System.Double)));
dt.Columns.Add(new DataColumn("col7", typeof(System.Single)));
dt.Columns.Add(new DataColumn("col8", typeof(System.Int64)));
dt.Columns.Add(new DataColumn("col9", typeof(System.Boolean)));
dt.Columns.Add(new DataColumn("col10", typeof(System.Guid)));
dt.Columns.Add(new DataColumn("col11", typeof(System.Int16)));
dt.Columns.Add(new DataColumn("col12", typeof(System.Byte)));
dt.Columns.Add(new DataColumn("col13", typeof(System.Decimal)));
//add (cnt) dummy records to the dataset.
for(int i = 0; i< cnt; i++)
{
DataRow dr = dt.NewRow();
dr[0]=123456;
dr[1]="This is a string of data to be inserted";
dr[2]="some nchar data";
dr[3]=DateTime.Now;
dr[4]=(Decimal)(123456789012345);
dr[5]=(Double)56789.123;
dr[6]=(Single)123.456;
dr[7]= 112233445566;
dr[8] = true;
dr[9] = new Guid(1,2,3,4,5,6,7,8,9,0,1);
dr[10] = (Int16)12345;
dr[11] = (Byte)123;
dr[12] = (Decimal)(123.1266666);
dt.Rows.Add(dr);
}
dt.AcceptChanges();
//add the DataTable to the DataSet object
ds.Tables.Add(dt);
//this is a way to get the schema from
the database table, without filling any data.
da = new SqlCeDataAdapter("select * from sampletable
where 1 = 0",conn);
da.FillSchema(ds,SchemaType.Mapped, "sampletable");
//force the DataAdapter to generate the insert command,
can also be manually set.
cb = new SqlCeCommandBuilder(da);
da.MissingMappingAction = MissingMappingAction.Passthrough;
//seems this is needed to force the insert command to be
assigned.
da.InsertCommand = cb.GetInsertCommand();
//call update to move all the data from sampletable DataTable
into the database table
da.Update(ds, "sampletable");
da.Dispose();
}
Next Page