Updating the SqlCe database from a DataSet

C

CenGenTech

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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
 
V

vikash

Hi
I dont have much idea about but i use the Fill() method of DataAdapter
to fill data into the table and i did not find it here. You may be
using some alternative method...filling scema then updating..i suppose.
Try using the Fill() method once ensuring proper connection.

Thanks & Regards
Vikash
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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
Click to expand...
 
C

CenGenTech

Thanks for the suggestion but I don't think the Fill() will discern
between new and existing records. The dataset may have new records
that require a simple insert or existing records that need to be
updated. The DataAdapter.Update method is supposed to auto generate
the sql statement for each scenario. Unfortunately, I believe its the
RowState that's causing the problem, which is Unchanged because I pull
the data from the server based on the last sync date on the mobile
device. Using one date allows me to pull thousands of records very
quickly as opposed to comparing the dates of each record. Long story
short - I believe I'm trying to accomplish something the method is not
intended to handle.

Let me know if you think I'm wrong but I've tried everything I know to
do to get it to insert the records and it won't. I'm resolved to
creating the insert and update statements manually and running them
through a loop to get the data in the SqlCe database.
Hi
I dont have much idea about but i use the Fill() method of DataAdapter
to fill data into the table and i did not find it here. You may be
using some alternative method...filling scema then updating..i suppose.
Try using the Fill() method once ensuring proper connection.

Thanks & Regards
Vikash
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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
Click to expand...
Click to expand...
 
I

Ilya Tumanov [MS]

Consider using RDA or replication instead. Most common reasons to come up
with self made solutions instead of using RDA or replication are:



1. Need to install IIS - you already have it for WS.

2. Attempt to reduce number of required CALs to 1 (as in for WS only). But
SQL Server license agreement does not work that way and require CAL for each
device regardless if you connect directly or through a proxy like WS.




--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

CenGenTech said:
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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
Click to expand...
 
C

CenGenTech

IIya,

I've never tried replication. How would it work with a business
process that allows the user to determine when and what data to
replicate? In addition, the tables are not even close to the same
structure!

Regards,
Johnny
Consider using RDA or replication instead. Most common reasons to come up
with self made solutions instead of using RDA or replication are:



1. Need to install IIS - you already have it for WS.

2. Attempt to reduce number of required CALs to 1 (as in for WS only). But
SQL Server license agreement does not work that way and require CAL for each
device regardless if you connect directly or through a proxy like WS.




--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

CenGenTech said:
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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
Click to expand...
Click to expand...
 
I

Ilya Tumanov [MS]

If you interested in RDA or replication, please read all about it here and
see if it suits your needs:



http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_connectivity.asp



http://msdn2.microsoft.com/en-us/library/ms172931.aspx



If you need more info, just search...


--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

CenGenTech said:
IIya,

I've never tried replication. How would it work with a business
process that allows the user to determine when and what data to
replicate? In addition, the tables are not even close to the same
structure!

Regards,
Johnny
Consider using RDA or replication instead. Most common reasons to come up
with self made solutions instead of using RDA or replication are:



1. Need to install IIS - you already have it for WS.

2. Attempt to reduce number of required CALs to 1 (as in for WS only).
But
SQL Server license agreement does not work that way and require CAL for
each
device regardless if you connect directly or through a proxy like WS.




--
Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

CenGenTech said:
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
contains three different tables that need to be loaded 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. In
fact, everything in the method works just like I want it but the but I
can't get the DataAdapter.Update method to insert 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();
}
}
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Updating SqlCe Database from a DataSet 0
Importing an xml into sqlCE 1
SqlCE Deletion of record from DataSet 3
Reading Xml into a dataset then updating the sqlce datatable causes castexception 1
Yet another DataAdapter and Transaction problem 1
Xml file to SQLCe 3
copying dataset from asp.net webservice to local db 2
Xml to SqlCe 2

Top