1. DataSet To XML:
如何把数据导出来变成XML? 这种问题经常遇到,vs提供了dataset做为数据库里面数据的容器,并且为这个容器添加了一系列游泳的属性: WriteXml就是这个功能的主要方法:
- protected void Button6_Click(object sender, EventArgs e)
- {
- System.Data.DataTable excelTable = new System.Data.DataTable();
- SqlConnection sqlconn = new SqlConnection("Data Source=efacsdb;Initial Catalog=whatifdb;User ID=sa; Password=L-BBgD7q");
- sqlconn.Open();
- SqlDataAdapter sda = new SqlDataAdapter("select top 10 * from temp_upload_mapping", sqlconn);
- DataSet ds = new DataSet();
- sda.Fill(ds, "table1");
- excelTable = ds.Tables["table1"];
- //使用WriteXml很容易就产生了一个xml:
- ds.WriteXml(@"c:\temp\buyer_group.xml");
- sqlconn.Close();
- }
出来的结果如下: (table1即为dataset的datatable, Newdataset即为dataset的名字;)
- <?xml version="1.0" standalone="yes"?>
- <NewDataSet>
- <table1>
- <rowid>4</rowid>
- <colname>suppliername</colname>
- <tablename>t_tt_sourcing_generate_sup_basic</tablename>
- <itemname>Company Name</itemname>
- </table1>
- <table1>
- <rowid>6</rowid>
- <colname>mgr_contactor</colname>
- <tablename>t_tt_sourcing_contact_detail</tablename>
- <itemname>Contact Person</itemname>
- </table1>
- </NewDataSet>
2. XML to DATASET:
得到的XML文件以后,只要文件满足一定的格式要求,就可以通过ReadXml直接独到Dataset里面 ;
- protected void Button7_Click(object sender, EventArgs e)
- {
- string fromPath = @"c:\temp\buyer_group.xml";
- FileInfo xmlFile = new FileInfo(fromPath);
- DataSet dsXML = null;
- if (xmlFile.Exists)
- {
- FileStream fsReadxml = new FileStream(fromPath,FileMode.Open);
- XmlTextReader myXmlReader = new XmlTextReader(fsReadxml);
- dsXML = new DataSet();
- dsXML.ReadXml(myXmlReader);
- DataView dv = new DataView(dsXML.Tables[0]);
- dv.RowFilter = " tablename='t_tt_sourcing_contact_detail'";
- gvXML.DataSource = dv;
- gvXML.DataBind();
- myXmlReader.Close();
- }
- }
测试过程table ddl:
- drop table temp_upload_mapping
- create table temp_upload_mapping (rowid int,colname varchar(60),tablename varchar(60),itemname varchar(100))
- insert into temp_upload_mapping values (
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Company Name',4,'suppliername','t_tt_sourcing_generate_sup_basic')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Contact Person',6,'mgr_contactor','t_tt_sourcing_contact_detail')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Contact Person Title',7,'mgr_title','t_tt_sourcing_contact_detail')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('email',8,'mgr_email','t_tt_sourcing_contact_detail')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Tel',9,'mgr_telephone','t_tt_sourcing_contact_detail')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Fax',10,'mgr_fax','t_tt_sourcing_contact_detail')
- insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Address',11,'mgr_address','t_tt_sourcing_contact_detail'insert into temp_upload_mapping (itemname,rowid,colname,tablename) values ('Province/State',12,'mgr_province','t_tt_sourcing_contact_detail')