MSSQL 2005 and SQL Mobile Edition - how transfer data?

[Magda]

Hello,

how fast transfer data beetween MSSQL 2k5 and SQL Mobile Edition? I want do
this when terminal is connected by ActiveSync. W don't want use RDA or
replication so I have to use SqlConnect, SqlCeConnect etc.etc. And I have a
problem: TIME. How fast transfer data from MSSQL to SQL Mobile. I use query
with parameters. I must do INSERT and UPDATE. How do this really fast?
Maybe SqlCeResultSet? But what with UPDATE? Please, help

 

[Ginny Caughey [MVP]]

Magda,

SqlCeResultSet is generally the fastest way to read/write SQL Compact data.
I think you'll just have to test and see if it's going to be faster doing
the updates to the SQL Server from the device side or whether it's faster to
copy a sdf file from the device to the desktop and run your updates from
there.

 

[Magda]

Dnia Thu, 26 Apr 2007 18:11:59 -0400, Ginny Caughey [MVP] napisa³(a):

Magda,

SqlCeResultSet is generally the fastest way to read/write SQL Compact data.
I think you'll just have to test and see if it's going to be faster doing
the updates to the SQL Server from the device side or whether it's faster to
copy a sdf file from the device to the desktop and run your updates from
there.

Hmm, ok. So, how do insert and update using SqlCeResultSet? I don't know
this. All samples show how to add a new record... For example
(http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.aspx)


rd = cmd.ExecuteReader();
conn = new SqlCeConnection("Data Source = Test.sdf");
conn.Open();

cmd.CommandText = "SELECT * FROM myTable";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);

sqlCeUpdatableRecord rec = rs.CreateRecord();

while (rd.read()) {

how update and insert records in sqlceresultset? this is only insert.

rec.SetInt32(0, 34);
rec.SetDecimal(1, (decimal)44.66);
rec.SetString(2, "Sample text");
rec.setstring(3,rd["something"].ToString();

rs.Insert(rec);
}



or how update and insert records using query with parameter? in SQLite is
"insert or replace" query, in SQL mobile i have to catch exception on
insert when record exist and do update on this record?

 

[Ginny Caughey [MVP]]

Updates and deletes with SqlCeResultSet are even easier than inserts. Just
use SqlCeResultSet.SetString, etc. to change a value in a column, then call
Update when you're finished. Similarly you can just call Delete to delete a
row.

--
Ginny

Dnia Thu, 26 Apr 2007 18:11:59 -0400, Ginny Caughey [MVP] napisa³(a):

Magda,

SqlCeResultSet is generally the fastest way to read/write SQL Compact
data.
I think you'll just have to test and see if it's going to be faster doing
the updates to the SQL Server from the device side or whether it's faster
to
copy a sdf file from the device to the desktop and run your updates from
there.

Hmm, ok. So, how do insert and update using SqlCeResultSet? I don't know
this. All samples show how to add a new record... For example
(http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.aspx)


rd = cmd.ExecuteReader();
conn = new SqlCeConnection("Data Source = Test.sdf");
conn.Open();

cmd.CommandText = "SELECT * FROM myTable";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable |
ResultSetOptions.Scrollable);

sqlCeUpdatableRecord rec = rs.CreateRecord();

while (rd.read()) {

how update and insert records in sqlceresultset? this is only insert.

rec.SetInt32(0, 34);
rec.SetDecimal(1, (decimal)44.66);
rec.SetString(2, "Sample text");
rec.setstring(3,rd["something"].ToString();

rs.Insert(rec);
}



or how update and insert records using query with parameter? in SQLite is
"insert or replace" query, in SQL mobile i have to catch exception on
insert when record exist and do update on this record?