从数据库中读取出数据,然后用XML输出

  1        private static void WriteXml(string strFileName,string code, DateTime from,DateTime to)
  2        {
  3                if(from>to)
  4                {
  5                    return;
  6                }
  7            //将locationid读到数组中
  8            string select="";
  9            select="select LocationID from Master_GoodsByLocation where GoodsCode='"+code+"'order by LocationID";
 10            OleDbDataAdapter m_dal=new OleDbDataAdapter(select,m_dc);
 11            DataSet m_dsl=new DataSet();
 12            m_dal.Fill(m_dsl,"Master_GoodsByLocation");
 13            DataTable tl=m_dsl.Tables["Master_GoodsByLocation"];
 14            string[] LocationID1=new string[tl.Rows.Count];
 15            int i=0;
 16            foreach(DataRow rl in tl.Rows)
 17            {
 18                LocationID1[i]=Convert.ToString(rl[0]);
 19                i++;
 20            }
 21            m_dsl.Reset();
 22                System.Xml.XmlTextWriter w=new XmlTextWriter(strFileName, System.Text.Encoding.UTF8);
 23                w.Formatting=Formatting.Indented;
 24
 25                w.WriteStartDocument(true);
 26                //start rood element
 27
 28                w.WriteStartElement("Data");
 29                w.WriteAttributeString("Type", "CheckData");
 30                w.WriteAttributeString("GoodsCode",code);
 31                w.WriteAttributeString("StartDate", from.ToString("yyyy-MM-dd"));
 32                w.WriteAttributeString("EndDate", to.ToString("yyyy-MM-dd"));
 33
 34                //start location
 35                foreach(string location in LocationID1)
 36                {
 37                    // node "Location"
 38                    w.WriteStartElement("Location");
 39                    w.WriteAttributeString("ID", location);
 40
 41                    //node "ForecastDemand"
 42                    w.WriteStartElement("ForecastDemand");
 43                    OleDbCommand oc=new OleDbCommand("select Date,ForecastDemand from Temp_ForecastDemand where GoodsCode='"+code+"'and LocationID='"+location+"'and Date<=#"+to+"#and Date>=#"+from+"# order by Date",m_dc);
 44                    OleDbDataReader reader=oc.ExecuteReader();
 45                    while(reader.Read())
 46                    {
 47                        w.WriteStartElement("Record");
 48                        w.WriteAttributeString("Date",Convert.ToDateTime(reader["Date"]).ToShortDateString());
 49                        w.WriteAttributeString("Value",reader["ForecastDemand"].ToString());
 50                        w.WriteWhitespace(" ");
 51                        w.WriteEndElement();
 52                    }
 53                    reader.Close();
 54                    w.WriteWhitespace(" ");
 55                    w.WriteEndElement();
 56
 57                    //node "RealSale"
 58                    w.WriteStartElement("RealSale");
 59                    oc=new OleDbCommand("select Date,RealSale from Temp_RealSale where GoodsCode='"+code+"'and LocationID='"+location+"'and Date<=#"+to+"#and Date>=#"+from+"#order by Date",m_dc);
 60                    reader=oc.ExecuteReader();
 61                    while(reader.Read())
 62                    {
 63                        w.WriteStartElement("Record");
 64                        w.WriteAttributeString("Date",Convert.ToDateTime(reader["Date"]).ToShortDateString());
 65                        w.WriteAttributeString("Value",reader["RealSale"].ToString());
 66                        w.WriteWhitespace(" ");
 67                        w.WriteEndElement();
 68                    }
 69                    reader.Close();
 70                    w.WriteWhitespace(" ");
 71                    w.WriteEndElement();
 72
 73                    //node "Abnormity"
 74                    w.WriteStartElement("Abnormity");
 75                    oc=new OleDbCommand("select Date,Abnormity from Temp_Abnormity where GoodsCode='"+code+"'and LocationID='"+location+"'and Date<=#"+to+"#and Date>=#"+from+"#order by Date",m_dc);
 76                    reader=oc.ExecuteReader();
 77                    while(reader.Read())
 78                    {
 79                        w.WriteStartElement("Record");
 80                        w.WriteAttributeString("Date",Convert.ToDateTime(reader["Date"]).ToShortDateString());
 81                        w.WriteAttributeString("Value",reader["Abnormity"].ToString());
 82                        w.WriteWhitespace(" ");
 83                        w.WriteEndElement();
 84                    }
 85                    reader.Close();
 86                    w.WriteWhitespace(" ");
 87                    w.WriteEndElement();
 88
 89                    //node "StatByMonth"
 90                    w.WriteStartElement("StatByMonth");
 91                    oc=new OleDbCommand("select StartDate,RealStock,StockoutTimes,SafetyStock,SafetyStockTime from Temp_StatByMonth where GoodsCode='"+code+"'and LocationID='"+location+"'and StartDate<=#"+to+"#and StartDate>#"+from.AddMonths(-1)+"#order by StartDate",m_dc);
 92                    reader=oc.ExecuteReader();
 93                    while(reader.Read())
 94                    {
 95                        w.WriteStartElement("Record");
 96                        w.WriteAttributeString("Date",Convert.ToDateTime(reader["StartDate"]).ToShortDateString());
 97                        w.WriteAttributeString("RealStock",reader["RealStock"].ToString());
 98                        w.WriteAttributeString("StockoutTimes",reader["StockoutTimes"].ToString());
 99                        w.WriteAttributeString("SafetyStock",reader["SafetyStock"].ToString());
100                        w.WriteAttributeString("SafetyStockTime",reader["SafetyStockTime"].ToString());
101                        w.WriteWhitespace(" ");
102                        w.WriteEndElement();
103                    }
104                    reader.Close();
105                    w.WriteWhitespace(" ");
106                    w.WriteEndElement();
107
108                    //end location
109                    w.WriteWhitespace(" ");
110                    w.WriteEndElement();
111                }
112                //end root element
113                w.WriteWhitespace("\r\n");
114                w.WriteEndElement();
115
116                //w.WriteEndElement();
117                w.Flush();
118                w.Close();
119                //return true;
120        }