How can I replace DataTable code with SqlCeDataReader or SqlCeResultSet?

Question

The CF Whisperer recommended this:

"Avoid DataTables. They are gigantic memory hogs, and under CE you can't afford a memory hog. Basically it duplicates the entire source table into RAM for you. SqlCeDataReader and SqlCeResultSet are your friends."

here

...regarding this code:

var dt = new DataTable();
dt.Columns.Add(new DataColumn("Id", typeof(Int32)));
dt.Columns.Add(new DataColumn("DeptNumber", typeof(Int32)));
dt.Columns.Add(new DataColumn("DeptName", typeof(string)));

foreach (HHSUtils.Department dept in depts)
{
    try
    {
        dt.Rows.Add(dept.Id, dept.DeptNumber, dept.DeptName);
        countAdded++;
    }
    catch (Exception ex)
    {
        MessageBox.Show(string.Format("deptId == {0}, deptNumber == {1},  
          deptName == {2} ({3})", dept.Id, dept.DeptNumber, dept.DeptName, ex.Message));
    }
}
// Now move it all into the table
using (var bc = new SqlCeBulkCopy(dataSource))
{
    bc.DestinationTableName = "Departments";
    bc.WriteToServer(dt);
}

I don't understand how I'm to replace DataTable with SqlCeDataReader or SqlCeResultSet here. My code above with DataTable is DDL and doesn't seem to be equate (in my mind) to SqlCeDataReader or SqlCeResultSet, as their purpose is (reputedly?) for working with queried data...???

score 1 · Accepted Answer · answered Dec 11 '13 at 21:56

1

I don't fully understand the first loop. If you have a set of Department items, why then duplicate all of them into a DataTable at all? You're effectively just making a second copy of each into memory, and if there are a lot of them, that will be a problem. It's also a waste of processing time.

I'd be far more inclined to do something like this (untested, uncompiled pseudocode):

var cmd = new SqlCeCommand(connection);
cmd.CommandText = "MyTableName";
cmd.CommandType = CommandType.TableDirect
using(var rs = cmd.ExecuteResultSet(
                      ResultSetOptions.Updatable | ResultSetOptions.Scrollable))
{
    foreach(var dept in depts)
    {
        var record = rs.CreateRecord();
        record.SetInt(0, dept.ID);
        // do other fields

        rs.Insert(record);
    }
}

We're doing better because:

We're not duplicating records
We're not effectively iterating the data set twice
We're going table direct

answered Dec 11 '13 at 21:56

ctacke

66,480
18
94
155

It may need to be that way for the Bulk Insert to work right; the Bulk Insert is from a library by ErikEJ. The example code to accomplish this was, I think, precisely like the code I have (except for the obvious differences in column names, and my exception handling code (MesageBox)). – B. Clay Shannon-B. Crow Raven Dec 11 '13 at 22:29
What was confusing me a little was that I was mixing up you and ErikEJ in my mind, I think; I call him "The SQL Server CE Whisperer" and you "The CF Whisperer," but they are so closely connected in my mind that, not knowing you personally, the two of you can easily meld into one in my pitiably overtaxed cranium. – B. Clay Shannon-B. Crow Raven Dec 11 '13 at 22:34
1

It's easy to tell us apart. He's the smart one. – ctacke Dec 11 '13 at 23:28
1

FWIW, I'm willing to bet that the code above is effectively what Erik is doing in the BulkCopy tool, there just isn't an easy way to get a collection of objects into his stuff. A callback or a Task-based overload added to his would be cool and would make it easier to meld what you need into what he has. – ctacke Dec 11 '13 at 23:32

How can I replace DataTable code with SqlCeDataReader or SqlCeResultSet?

1 Answers1