Hello,
I'm trying to bulk insert data from Sql Server to Sql Server Compact 3.5, using SqlCeResultSet.
But I'm having trubles trying to insert data in a table that has a Identity column.
It seams that for some reason SqlCeResultSet doesn't honor the SET IDENTITY_INSERT option.
Here some source:
SqlCeConnection cn = new SqlCeConnection("Data Source=Database1.sdf");
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = cn;
cn.Open();
cmd.CommandText = "SET IDENTITY_INSERT table1 ON";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * FROM table1";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
SqlCeUpdatableRecord rec = rs.CreateRecord();
rec["id"] = 2;
rec["description"] = "hello";
rec["code"] = 5;
rs.Insert(rec);
rs.Close();
cmd.CommandText = "SET IDENTITY_INSERT table1 OFF";
cmd.ExecuteNonQuery();
cn.Close();
But this actually works:
SqlCeConnection cn = new SqlCeConnection("Data Source=Database1.sdf");
SqlCeCommand cmd = new SqlCeCommandView Complete Post