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