Development | .NET Compact Framework

Harnessing SSCEDirect for fast SQLCE data loading
Written by brady moritz  [author's bio]  [read 29340 times]
Edited by Derek

   

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 

Back to .NET Compact Framework | [Article Index]