[Q] The best method to import large data file into SQL CE?

[John]

Hi,

I wonder what is the best method to import large data file (csv) into SQL
CE? e.g. 10k records.
If using 'Insert into' with cmd.ExecuteNonQuery for each record, it cost
device frozen.

TKS,

J L

 

[Darren Shaffer]

John,

You will have to either insert the data on device or use RDA
to pull the data from a corresponding table on SQL Server.
If you are inserting on device from a CSV file, be sure you
use a parameterized query for your INSERT statement to save the query
processor from making a new query plan for each row. Also, remove
any indices from the table before you populate it and then
re-apply them.

-Darren Shaffer

 

[John]

Thanks Darren,

I did means to download the actual CSV file into the device. Can you please
give me more details in the insert statement?

In MySQL can be like following sample, by trigger the update from a batch of
records, that can solve the memory problem:

insert into employee values
(7513,'Nora Edwards','Programmer',128),
(9842, 'Ben Smith', 'DBA', 42),
(6651, 'Ajay Patel', 'Programmer', 128),
(9006, 'Candy Burnett', 'Systems Administrator', 128);

However it seems not working in the same way for SQL CE.

TKS

J L

 

[Darren Shaffer]

John,

You can get your CSV file to device along with your Compact
Framework app by adding the CSV file to your project in VS.Net 2003
and setting the Build Action to Content. After loading your table,
son't forget to delete the CSV file from device to save space.

Here is some sample code to help you with parsing the CSV file
and inserting into your DB. It assumes one line in the CSV file
corresponds to one record in your database table and that there are
values for all columns in the table provided in each row of the CSV
file. There is also an ExecuteQuery method in the code below that I
didn't include for brevity (it opens the db once and runs ExecuteNonQuery
on the connection when called).

This code does not use a parameterized query, but it is easy to convert it
to do so. See the SQL CE Books on Line for examples.

-Darren

private void _loadSQLCEDatabase(string fileName, string tableName)
{
// load local database tables from CSV file data
try
{
FileInfo f = new FileInfo(fileName);
using ( StreamReader sr = f.OpenText() )
{
String line;
while ( ( line = sr.ReadLine() ) != null )
{
StringBuilder dml = new StringBuilder("INSERT INTO " + tableName + "
VALUES (");

string[] values = line.Split(',');
foreach(string s in values)
{
StringBuilder sb = new StringBuilder();
sb.Append(s);
dml.Append("'" + sb.ToString() + "'" + ",");
}
dml.Remove(dml.Length - 1, 1);
dml.Append(")");
ExecuteQuery(dml.ToString());
}
}
}
catch(SqlCeException ex)
{
DisplaySQLCEErrors(ex);
return;
}
catch (Exception ex)
{
MessageBox.Show("An error occurred loading data into the SQLCE Database.
" +
"Details: " + ex.Message, "Data Load Error");
}
}