The .NET's DataSet is a very interesting object that can make data import and export functions with XML.
The key code is as follows:
///
/// export the Table of Database
///
/// Table name
/// File path
/// Connection of Database
///
Public Bool ExpostTableToXML (String M_Tablename, String M_FilePath, OLEDBCONNECTION M_PUBCONN)
{
Try
{
DataSet DS = New DataSet ();
OLEDBDataAdapter m_adapter = new oledbdataadapter ("", m_pubconn);
m_adapter.selectcommand = new oledbcommand ("Select * from" m_tablename, m_pubconn;
m_adapter.fill (DS, M_TABLENAME);
System.IO.FileStream Fs = new system.io.filestream (m_filepath, system.IO.filemode.create);
DS.WRITEXML (FS, XMLWRITE.WRITESCHEMA);
fs.close ();
Return True;
} catch (system.exception error)
{
MessageBox.show (Error.Message, "Error", MessageBoxButtons.ok, MessageBoxicon.Error;
Return False;
}
}
///
/// import the xml file to database
///
/// File path
/// Table name
/// Connection of Database
///
Public Bool Importxmltable (String M_FilePath, String M_Tables, OLEDBCONNECTION M_PUBCONN)
{
Try
{
DataSet DS = New Dataset ();
Dataset mdsmain = new dataset ();
OLEDBCOMMAND M_COMM = New OLEDBCOMMAND (", M_Pubconn);
OLEDBDataAdapter m_adapter = new oledbdataadapter (); mdsmain.readxml (m_filepath, xmlreadmode.readschema);
// delete from TableName
m_comm.commandtext = "delete from" m_tablename;
m_comm.executenonury ();
// set the m_adapter
m_comm.commandtext = "SELECT *" M_TABLENAME;
m_adapter.selectCommand = m_comm;
OLEDBCommandbuilder CB = New OLEDBCommandbuilder (m_adapter);
m_adapter.fill (DS, M_TABLENAME);
System.io.MemoryStream ms = new system.io.MemoryStream ();
Mdsmain.writeXml (MS, XMLWRITEMODE.DIFFGRAM);
Ms.seek (0, seekorigin.begin);
DS.Readxml (MS, XMLReadmode.diffgram);
m_adapter.Update (DS, M_TABLENAME);
Ds.acceptchanges ();
Ms.close ();
Return True;
} catch (system.exception error)
{
Messagebox.show (Error.Message.toString (), "Error", MessageBoxButtons.ok, MessageBoxicon.Error);
}
Return False;
}
}
Test code:
Private void Button1_Click (Object Sender, System.Eventargs E)
{
ExportTableToXML ("T_fieldset", "C: //xx.xml", m_pubconn);
ExportTableToXML ("T_fieldItem", "C: //yy.xml", m_pubconn);
Messagebox.show ("Success");
}
Private void button2_click (Object Sender, System.Eventargs E)
{
Importxmltotable ("C: //xx.xml", "t_fieldset", m_pubconn);
ImportXmltotable ("C: //yy.xml", "t_fielditem", m_pubconn);
Messagebox.show ("Success");
}
note:
1. When I import data, I will delete the original data;
2. Two functions import, export only for single table;
