Database

Gandalf - The White wrote:

If not worked then will ask you.

Gandalf - The White wrote:

Thanks for soln.

SELECT id, lastName, title, age
       from #employee ORDER BY
       title DESC, age ASC;

Glen Harvy wrote:

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

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.

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();

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();

select ID as 'Address.ID', Line1 as 'Address.Line1' ....