.net的dataset是一个很有趣的对象,借助与xml可以很快捷的做出数据导入,导出功能.
关键代码如下:
/// <summary>
/// export the table of database
/// </summary>
/// <param name="m_TableName">table name</param>
/// <param name="m_FilePath">file path</param>
/// <param name="m_PubConn">Connection of database</param>
/// <returns></returns>
public bool ExportTableToXml(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, XmlWriteMode.WriteSchema);
fs.Close();
return true;
}catch (System.Exception error)
{
MessageBox.Show(error.Message,"error",MessageBoxButtons.OK,MessageBoxIcon.Error);
return false;
}
}
/// <summary>
/// import the xml file to database
/// </summary>
/// <param name="m_FilePath">file path</param>
/// <param name="m_TableName">table name</param>
/// <param name="m_PubConn">connection of database</param>
/// <returns></returns>
public bool ImportXmlToTable(string m_FilePath,string m_TableName,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.ExecuteNonQuery();
//set the m_Adapter
m_Comm.CommandText = "select * from "+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;
}
}
测试代码:
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");
}
注意:
1. 我在导入数据的时候,将原来的数据删除了的;
2. 2个函数导入,导出只是针对单个表;