I am trying to export data from multiple tables in SQL Server to an XML file
so I can then import it to another database. It seems to be working fine for
exporting, but I am having trouble importing the file. Can someone show me
where I am going wrong. Even though I say the export is working, I am
including it below in case there is something in there that needs to be
changed. Also, the code I am including is VERY rough right now and will
change quite a bit once I figure out what I am doing. For the import, If I
understand it correctly, which I don't since I'm here :), I am supposed open
a dataset and then update it. Is that correct? If someone can show me where I
am going wrong and explain it, that would be great. Thanks in advance.
public static int Export()
{
string databaseName = HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] + databaseName;
DataSet ds;
using (SqlConnection conn = new SqlConnection(connectionString))
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
}
DataRelation dr;
DataColumn parentCol;
DataColumn childCol;
parentCol = ds.Tables["Tprs"].Columns["TprID"];
childCol = ds.Tables["Tprs1"].Columns["TprID"];
dr = new DataRelation("TprAssociatedData", parentCol, childCol);
// Add the relation to the DataSet.
ds.Relations.Add(dr);
ds.WriteXml(@"D:\Data\test.xml", XmlWriteMode.WriteSchema);
return ds.Tables[0].Rows.Count;
}
public static int Import()
{
string databaseName = HttpContext.Current.Session["DatabaseName"].ToString();
string connectionString =
ConfigurationSettings.AppSettings["DatabaseConnectionString"] + databaseName;
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("GetTprsForExport", conn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet("TPRS");
adapter.Fill(ds, "Tprs");
DataSet ds1 = new DataSet("TPRS");
ds1.ReadXml(@"D:\Data\test.xml", XmlReadMode.ReadSchema);
adapter.Update(ds1, "Tprs");
}
return 1;
}