Development | .NET Compact Framework

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

   

Page 1  Page 2 

SSCEDirect has two basic objects that can be used for loading data, these are the RowsetLoader and the DataTableLoader. RowsetLoader exposes methods that allow row-by-row insertion of data, and thus is useful for .Net code that parses a flat file or xml file and inserts each row of data sequentially. However, if your data is already in a DataTable object, the DataTableLoader class allows its entire contents to be loaded into the selected database table with a single method call. Lets first look at how the same sample data can be loaded using the RowsetLoader.

//Set license key at startup using static method. Set only once per application lifespan.
RowsetLoader.SetLicense("Trial License", "LHTT3Z4XX7");
...
public void SampleLoad(){
RowsetLoader rl = new RowsetLoader();
rl.Open("demotable", txtDB.Text);
rl.Delimiter = "|"; //We are using a delimited string of data, so set the delimiter.
rl.ParseDates = false; //Faster load method, but supports limited date formats.
ErrCodes err;

//test a delimited string of data. Can also pass a string array instead.
string Testdata = "123456|Hello RowsetLoader|Char Data|2002-10-10 10:10:10|" +
"123456789012345|56789.123|123.456|112233445566|true|{" +
new Guid(1,2,3,4,5,6,7,8,9,0,1).ToString()+ "}|12345|123|123.12345678";

//for testing, insert the same data (cnt) times. Would normally loop through rows of real data here.
for(int i = 0; i < cnt; i++)
{
//You should check return value for error
err = rl.AddRow(Testdata);
}
rl.Close();
}

And this example shows how the DataTableLoader would do the job (the DataTable population is a clone of the first example):

public void SampleLoad()
{
//Manually build a DataTable object, normally this is loaded directly from an XML file.
//The datatypes listed are currently all the types supported by SSCEDirect.
DataTable dt = new DataTable("testtable");
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();

DataTableLoader dtl = new DataTableLoader();
start = DateTime.Now; //this will only time the insert time, not the time just used to load the DataTable
ErrCodes err;
err = dtl.Open(dt.TableName,txtDB.Text);

//call CheckDataTable to verify the datatable conforms to the open database table.
//this will also set the maxlength of any string type columns, since maxlength must be set before calling LoadTable.
err = dtl.CheckDataTable(dt);

//LoadTable actually copies the DataTable data into the SqlCE database table.
err = dtl.LoadTable(dt);
dtl.Close();
MessageBox.Show("Done");
}

SSCEDirect can also be instructed how to load data that is not in the exact format as the destination table, or if certain data should be ignored. The following example demonstrates some methods to use if a column should be set to null or if the column should be skipped on insert, such as in the the case of an identity column that auto-generates its own value.

...
//After calling the Open() method, set any columns that should not have values inserted.

//ignoresource indicates that, when skipping input to a destination column,
//the source column does exist and should be skipped over as well
rl.IgnoreSource = true;

//now set a couple of columns to skip input for. If the column has a default value
//or is an identity, these values will be inserted instead.
rl.IgnoreColumn(0);
rl.IgnoreColumn(1);

//continue with LoadTable() or AddRow(), followed by Close()
...

//while looping and calling AddRow, this will set the 5th column to a null database value.
rl.NullColumn(4);
...

These examples should be adequate to get you started with speeding up your database loading in your own Compact Framework project.

When you start on your own, keep in mind that the current version of the software does have a few shortcomings, the most notable one being lack of support for blob items or NTEXT. Support for NTEXT is expected to be included by the next release.

Fitiri also has some new features slated for a future version which will enhance the product's capabilities even further. One planned feature is direct WebService support (to speed up the .Net unmarshalling process, another bottleneck with the Compact Framework). Also planned is direct flat file instead of requiring the developer to pass each row individually, which is expected to nearly double the speed of generic flat file loading.

I hope this helps enable you to focus your development work on the business logic of your mobile solutions and not have to worry about performance issues with the Compact Framework. I would love to hear your feedback on this article.

Previous Page 

Back to .NET Compact Framework | [Article Index]