Updating SqlCe Database from a DataSet

[Guest]

I have a PocketPC mobile application that gets its data from the Sql Server
database via a web service. The web service returns a dataset that I need to
load into the SqlCe database on the mobile device. I have a method to
perform this task but it doesn't load the data into the SqlCe database. The
data is downloaded into the dataset from the web service just fine but I
can't get the data into the SqlCe database. Any suggestions would be
appreciated!!!

Here's the method (I've reformatted it to fit better in the small window):

private void Syncronization()
{
SqlCeConnection conn = null;
conn = new SqlCeConnection ("Data Source = VMI.sdf");
SqlCeDataAdapter daCustomers = new SqlCeDataAdapter();
SqlCeDataAdapter daShipTos = new SqlCeDataAdapter();
SqlCeDataAdapter daItems = new SqlCeDataAdapter();

//instantiate SqlCe SELECT command
SqlCeCommand SqlCeCmdCustomers = new SqlCeCommand("SELECT * FROM vmi_cust
WHERE 1 = 2", conn);
SqlCeCommand SqlCeCmdShipTo = new SqlCeCommand("SELECT * FROM vmi_ship
WHERE 1 = 2", conn);
SqlCeCommand SqlCeCmdItems = new SqlCeCommand("SELECT * FROM
vmi_item_master WHERE 1 = 2", conn);


try
{
//Create an array and load all the ShipToID's into the array
string [][] arrayShipToID = new
string[dsVMI_Cust.Tables[0].Rows.Count][];

for(int x = 0; x < dsVMI_Cust.Tables[0].Rows.Count; x++)
{
arrayShipToID[x] = new string[] {
dsVMI_Cust.Tables[0].Rows[x]["ship2_id"].ToString(),
dsVMI_Cust.Tables[0].Rows[x]["is_existing_customer"].ToString()
};
}

//Create Dataset to get the results of the GetShipTosAndItemsSyncData
web service method
DataSet dsRetCustItems = new DataSet();

//Open the connection to the SqlCe database
conn.Open();

//Create SqlCe command object to store
//this is a way to get the schema from the database table, without
filling any data.
daCustomers.SelectCommand = SqlCeCmdCustomers;
daShipTos.SelectCommand = SqlCeCmdShipTo;
daItems.SelectCommand = SqlCeCmdItems;

//fill the data adapter's schema with that of the table in the dataset
daCustomers.FillSchema(dsRetCustItems,SchemaType.Mapped, "vmi_cust");
daShipTos.FillSchema(dsRetCustItems,SchemaType.Mapped, "vmi_ship");
daItems.FillSchema(dsRetCustItems,SchemaType.Mapped, "vmi_item_master");

//force the DataAdapter to generate the insert command, can also be manually
set.
SqlCeCommandBuilder cbCustomers = new SqlCeCommandBuilder(daCustomers);
SqlCeCommandBuilder cbShipTos = new SqlCeCommandBuilder(daShipTos);
SqlCeCommandBuilder cbItems = new SqlCeCommandBuilder(daItems);

daCustomers.MissingMappingAction = MissingMappingAction.Passthrough;
daShipTos.MissingMappingAction = MissingMappingAction.Passthrough;
daItems.MissingMappingAction = MissingMappingAction.Passthrough;

//seems this is needed to force the insert command to be assigned.
daCustomers.InsertCommand = cbCustomers.GetInsertCommand();
daShipTos.InsertCommand = cbShipTos.GetInsertCommand();
daItems.InsertCommand = cbItems.GetInsertCommand();

//call update to move all the data from sampletable DataTable into the
database table
daCustomers.Update(dsRetCustItems, "VMI_CUST");
daShipTos.Update(dsRetCustItems, "VMI_SHIP");
daItems.Update(dsRetCustItems, "VMI_ITEM_MASTER");

cbCustomers.Dispose();
cbShipTos.Dispose();
cbItems.Dispose();
}
catch(SqlCeException SqlCeex)
{
MessageBox.Show("Error insert/updating SqlCe database: " +
SqlCeex.Message);
}
catch(Exception ex)
{
MessageBox.Show("Error in syncronization process: " + ex.Message);
}
finally
{
daCustomers.Dispose();
daShipTos.Dispose();
daItems.Dispose();
SqlCeCmdCustomers.Dispose();
SqlCeCmdShipTo.Dispose();
SqlCeCmdItems.Dispose();
conn.Close();
conn.Dispose();
}
}