xml与数据库交互

把xml文件的数据插入数据库

void SetMsg()
 {
  SqlConnection con = new SqlConnection("server=.;database=Students;uid=sa;pwd=");
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            string sql1="insert into student(";
            string sql2="values (";
            string sql3;
            string sql4;
            XmlDocument xd = new XmlDocument();
            xd.Load(@"D:/student.xml");
            XmlElement xe=xd.DocumentElement;
            XmlElement r;
            XmlElement h;
            for (int i = 0; i < xe.ChildNodes.Count; i++)
            {
                sql3 = sql1;
                sql4 = sql2;
                r = (XmlElement)xe.ChildNodes[i];
              
                for(int j=0;j<r.ChildNodes.Count;j++)
                    {
                        h = (XmlElement)r.ChildNodes[j];
                        if (j == r.ChildNodes.Count - 1)
                        {
                            sql3 += "'" + h.Name + "')";
                            sql4 += "'" + h.InnerText + "')";
                        }
                        else
                        {
                            sql3 += "'" + h.Name + "',";
                            sql4 += "'" + h.InnerText + "',";
                        }
                        //MessageBox.Show(sql3 + sql4);
                    }

                cmd.CommandText = sql3 + sql4;
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ee)
                {
                    MessageBox.Show(ee.ToString());
                }
            }
            con.Close();
        }

把数据库数据导出成xml文件

void GetMsg()
{
            SqlConnection con = new SqlConnection("server=.;database=pubs;uid=sa;pwd=");
            SqlCommand cmd = new SqlCommand("select * from jobs", con);

            con.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            string[] fieldname = new string[dr.FieldCount];
            for (int i = 0; i < dr.FieldCount; i++)
            {
                fieldname[i] = dr.GetName(i);
            }

            XmlTextWriter xtw = new XmlTextWriter(@"D:/jobs.xml",null);

            xtw.WriteStartDocument();
            xtw.WriteStartElement("jobs");
            while (dr.Read())
            {
                xtw.WriteStartElement("record");
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    xtw.WriteElementString(fieldname[i], dr[i].ToString());
                }
                xtw.WriteEndElement();
            }
            xtw.WriteEndElement();
            xtw.WriteEndDocument();
            xtw.Flush();
            xtw.Close();
            con.Close();
        }