replace a datatable with an older version.

Glen Harvy 0

Hi, I would like to be able to copy the data in a table from an old version of a sqlce database into the current version of the sqlce database. The datatable must be replaced ie the row ID numbers must remain the same. I thought that writing the old datatable to to an xml file and then reading that data back in would do the trick but I can't get it to work. Has anyone achieved what I wish and if so, how did they go about it as I've tried a few alternatives without any luck. Thanks,

Glen Harvy

SimulationofSai

Glen Harvy wrote:

The datatable must be replaced ie the row ID numbers must remain the same.

Is the RowID an identity column? If so, disable Identity by setting SET IDENTITY INSERT [Table Name] ON and import the data from the older database.

Glen Harvy wrote:

I thought that writing the old datatable to to an xml file and then reading that data back in would do the trick but I can't get it to work.

That statement is devoid of any useful information. Is there any particular error you face?

Blue_Boy

Check this this[^]

I Love T-SQL "Don't torture yourself,let the life to do it for you."

Glen Harvy 0

Thanks for your help. Your suggestion would seem to resolve my problem however I am not aware of a way to achieve my end result without using datasets and datatables as in the following code:

string tempDBLocation = MainForm.dataDirectory + @"Temp\";
string tempXMLFile = tempDBLocation + @"tempData.xml";

// Replace the current Database with the selected backup
File.Copy(MainForm.dataDirectory + "my.sdf", MainForm.dataDirectory + "my_org.sdf", true);
File.Copy(tempDBLocation + "my.sdf", MainForm.dataDirectory + "my.sdf", true);

// Fill and save to xml the table
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
this.adminDataSet1.Config.WriteXml(tempXMLFile, XmlWriteMode.WriteSchema);

// Replace the original database
File.Copy(MainForm.dataDirectory + "my_org.sdf", MainForm.dataDirectory + "my.sdf", true);
File.Delete(tempDBLocation + "my.sdf");
File.Delete(MainForm.dataDirectory + "my_org.sdf");

// empty the current datatable of all data
this.configTableAdapter1.Fill(this.adminDataSet1.Config);
for (int i = 0; i < this.adminDataSet1.Config.Rows.Count; i++)
{
this.adminDataSet1.Config.Rows[i].Delete();
}
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();

// Read the xml data into the table
this.adminDataSet1.Config.ReadXmlSchema(tempXMLFile);
this.adminDataSet1.Config.ReadXml(tempXMLFile);
this.configTableAdapter1.Update(this.adminDataSet1.Config);
this.adminDataSet1.AcceptChanges();

I would prefer not to use ado.net but am unable to locate how to achieve what I want without it. It seems I have a lot more research to do. Thanks for your assistance.

Glen Harvy

Glen Harvy 0

Here's my latest effort:

  string tempDBLocation = MainForm.dataDirectory + @"Temp\\";
  string tempXMLFile = tempDBLocation + @"tempData.xml";

  string conString = "Data Source=" + tempDBLocation + "my.sdf;Persist Security Info=False";
  SqlCeConnection conn = new SqlCeConnection(conString);
  SqlCeCommand command = conn.CreateCommand();
  conn.Open();
  string tableName = "Config";
  SqlCeDataAdapter mySqlCeDataAdapter = new SqlCeDataAdapter("select \* from " + tableName, conn);
  DataTable myDT = new DataTable(tableName);
  mySqlCeDataAdapter.Fill(myDT);
  myDT.WriteXml(tempXMLFile,XmlWriteMode.WriteSchema);
  conn.Close();

  conString = Properties.Settings.Default.mycourtsConnectionString.ToString();
  SqlCeConnection conn2 = new SqlCeConnection(conString);
  SqlCeCommand command2 = conn2.CreateCommand();
  conn2.Open();
  SqlCeDataAdapter mySqlCeDataAdapter2 = new SqlCeDataAdapter("select \* from " + tableName, conn2);
  DataTable myDT2 = new DataTable(tableName);
  mySqlCeDataAdapter2.Fill(myDT2);
  SqlCeCommandBuilder ccmdBuilder = new SqlCeCommandBuilder(mySqlCeDataAdapter2);
  for (int i = 0; i < myDT2.Rows.Count; i++)
  {
    myDT2.Rows\[i\].Delete();
  }
  myDT2.ReadXmlSchema(tempXMLFile);
  myDT2.ReadXml(tempXMLFile);
  command2.CommandText = "SET IDENTITY\_INSERT " +tableName + " ON";
  command2.ExecuteScalar();
  mySqlCeDataAdapter2.Update(myDT2);
  conn2.Close();

And of course the Update command now generates this error: The column cannot contain null values. [ Column name = ID,Table name = Config ] It makes sense of course but how do I get around this - or must I generate an insert command and do it that way.

Glen Harvy