• 代码千万行,注释第一行。命名不规范,同事泪千行。

C#SqlServer导出到XML及XML写入数据库

C# Secondar 7个月前 (02-15) 307次浏览 已收录 0个评论

整理硬盘文件发现在校时写的练习项目,没事发出来呗

Form.cs代码

截图

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Xml;
using System.Xml.Linq;

namespace XML_SQL
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        XmlDocument document = new XmlDocument();
        XmlNodeList NodeList;
        XmlNode Node;
        int curent;
        string xmltmp;
        string flag = "false", newflag = "false";
        bool newxml = false;
        DataSet ds = new DataSet();
        bool ne = false;
        string server = "server=.;database=JWInfo;Trusted_Connection=SSPI";//数据库连接字符串
        private void bt_top_Click(object sender, EventArgs e)
        {
            curent = 0;
            if (flag != "false" && flag == "XML")
            {
                xml(curent);
            }
            else if (flag != "false" && flag == "SQL")
            {
                sql(curent);
            }
        }

        private void bt_left_Click(object sender, EventArgs e)
        {
            curent -= 1;
            if (flag != "false" && flag == "XML")
            {
                xml(curent);
            }
            else if (flag != "false" && flag == "SQL")
            {
                sql(curent);
            }
        }

        private void bt_right_Click(object sender, EventArgs e)
        {
            curent += 1;
            if (flag != "false" && flag == "XML")
            {
                xml(curent);
            }
            else if (flag != "false" && flag == "SQL")
            {
                sql(curent);
            }
        }

        private void bt_bottom_Click(object sender, EventArgs e)
        {
            if (flag != "false" && flag == "XML")
            {
                curent = NodeList.Count - 1;
                xml(curent);
            }
            else if (flag != "false" && flag == "SQL")
            {
                curent = ds.Tables["my"].Rows.Count - 1;
                sql(curent);
            }
        }
        private void bt_sqltoxml_Click(object sender, EventArgs e)
        {
            //            string strconn = "server=7jf-14;database=JWInfo;Trusted_Connection=SSPI";
            //            //string strconn = "server=DESKTOP-TH85VE7;database=glxt;Trusted_Connection=SSPI";
            //            SqlConnection conn = new SqlConnection(strconn);
            //            conn.Open();
            //            string strtqsl = @"select 学生信息.学号,学生信息.姓名,学生信息.性别,学生信息.身份证号,班级.班级名称,籍贯.籍贯,学籍.学籍名称,民族.民族,政治面貌.政治面貌 
            //                                from 学生信息,班级,籍贯,学籍,民族,政治面貌
            //                                where 学生信息.班级编号=班级.班级编号 and 学生信息.籍贯编号=籍贯.籍贯编号 
            //                                and 学生信息.学籍编号=学籍.学籍编号 and 学生信息.民族编号=民族.民族编号 
            //                                and 学生信息.政治面貌编号=政治面貌.政治面貌编号";
            //            SqlDataAdapter DA = new SqlDataAdapter(strtqsl, strconn);
            //            DA.Fill(ds, "my");
            //MessageBox.Show(ds.Tables["my"].Rows[1]["学号"].ToString().Trim());

            document.AppendChild(document.CreateXmlDeclaration("1.0", "UTF-8", "yes"));
            //根元素
            document.AppendChild(document.CreateElement("class"));
            //根节点

            for (int i = 0; i < ds.Tables["my"].Rows.Count; i++)
            {
                XmlElement element = document.CreateElement("student");
                //子节点
                XmlElement xuehao = document.CreateElement("学号");
                XmlElement xingming = document.CreateElement("姓名");
                XmlElement xingbie = document.CreateElement("性别");
                XmlElement shenfenzheng = document.CreateElement("身份证号");
                XmlElement banjimingcheng = document.CreateElement("班级名称");
                XmlElement jiguan = document.CreateElement("籍贯");
                XmlElement xuejimingcheng = document.CreateElement("学籍名称");
                XmlElement minzhu = document.CreateElement("民族");
                XmlElement zhengzhimianmao = document.CreateElement("政治面貌");
                //子节点赋值
                xuehao.InnerText = ds.Tables["my"].Rows[i]["学号"].ToString().Trim();
                xingming.InnerText = ds.Tables["my"].Rows[i]["姓名"].ToString().Trim();
                xingbie.InnerText = ds.Tables["my"].Rows[i]["性别"].ToString().Trim();
                shenfenzheng.InnerText = ds.Tables["my"].Rows[i]["身份证号"].ToString().Trim();
                banjimingcheng.InnerText = ds.Tables["my"].Rows[i]["班级名称"].ToString().Trim();
                xuejimingcheng.InnerText = ds.Tables["my"].Rows[i]["学籍名称"].ToString().Trim();
                jiguan.InnerText = ds.Tables["my"].Rows[i]["籍贯"].ToString().Trim();
                minzhu.InnerText = ds.Tables["my"].Rows[i]["民族"].ToString().Trim();
                zhengzhimianmao.InnerText = ds.Tables["my"].Rows[i]["政治面貌"].ToString().Trim();
                //子节点添加至根节点
                element.AppendChild(xuehao);
                element.AppendChild(xingming);
                element.AppendChild(xingbie);
                element.AppendChild(shenfenzheng);
                element.AppendChild(banjimingcheng);
                element.AppendChild(xuejimingcheng);
                element.AppendChild(jiguan);
                element.AppendChild(minzhu);
                element.AppendChild(zhengzhimianmao);
                //添加到根元素
                ((XmlElement)document.SelectSingleNode("/class")).AppendChild(element);
            }
            SaveFileDialog tmp = new SaveFileDialog();
            tmp.Filter = "XML|*.xml";
            if (tmp.ShowDialog() == DialogResult.OK)
            {
                string tmpFileName = tmp.FileName;
                try
                {
                    document.Save(tmpFileName);
                    xmltmp = tmpFileName;
                    newflag = "sql";
                    MessageBox.Show("xml文档创建成功");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
            bt_sqltoxml.Enabled = false;
            bt_newxml.Enabled = true;
        }

        private void bt_ioxml_Click(object sender, EventArgs e)
        {
            flag = "XML";
            OpenFileDialog tmp = new OpenFileDialog();
            tmp.Filter = "XML(*.XML)|*.XML|xml(*.xml)|*.xml";

            if (tmp.ShowDialog() == DialogResult.OK)
            {
                if (tmp.FileName != null)
                {
                    bt_top.Enabled = false;
                    bt_left.Enabled = false;
                    //document = new XmlDocument();
                    xmltmp = tmp.FileName;
                    document.Load(tmp.FileName);
                    NodeList = document.SelectNodes("/class/student");
                    Node = NodeList.Item(0);
                    tb_xuehao.Text = Node.ChildNodes[0].InnerText.ToString();
                    tb_xingming.Text = Node.ChildNodes[1].InnerText.ToString();
                    tb_xingbie.Text = Node.ChildNodes[2].InnerText.ToString();
                    tb_shenfenzheng.Text = Node.ChildNodes[3].InnerText.ToString();
                    tb_banjiming.Text = Node.ChildNodes[4].InnerText.ToString();
                    tb_xueji.Text = Node.ChildNodes[5].InnerText.ToString();
                    tb_jiguan.Text = Node.ChildNodes[6].InnerText.ToString();
                    tb_minzhu.Text = Node.ChildNodes[7].InnerText.ToString();
                    tb_zhngzhimianmao.Text = Node.ChildNodes[8].InnerText.ToString();
                    bt_newxml.Enabled = true;
                    bt_update.Enabled = true;
                    bt_xmltosql.Enabled = true;
                    bt_sqltoxml.Enabled = false;
                    newflag = "io";
                    if (NodeList.Count <= 1)
                    {
                        bt_right.Enabled = false;
                        bt_bottom.Enabled = false;
                    }
                    else
                    {
                        curent = 0;
                        bt_right.Enabled = true;
                        bt_bottom.Enabled = true;
                    }
                }
            }
        }
        public void xml(int i)
        {
            if (i > 0 || i < NodeList.Count)
            {
                Node = NodeList.Item(i);
                tb_xuehao.Text = Node.ChildNodes[0].InnerText.ToString();
                tb_xingming.Text = Node.ChildNodes[1].InnerText.ToString();
                tb_xingbie.Text = Node.ChildNodes[2].InnerText.ToString();
                tb_shenfenzheng.Text = Node.ChildNodes[3].InnerText.ToString();
                tb_banjiming.Text = Node.ChildNodes[4].InnerText.ToString();
                tb_xueji.Text = Node.ChildNodes[5].InnerText.ToString();
                tb_jiguan.Text = Node.ChildNodes[6].InnerText.ToString();
                tb_minzhu.Text = Node.ChildNodes[7].InnerText.ToString();
                tb_zhngzhimianmao.Text = Node.ChildNodes[8].InnerText.ToString();
            }
            if (i == 0)
            {
                Node = NodeList.Item(i);
                tb_xuehao.Text = Node.ChildNodes[0].InnerText.ToString();
                tb_xingming.Text = Node.ChildNodes[1].InnerText.ToString();
                tb_xingbie.Text = Node.ChildNodes[2].InnerText.ToString();
                tb_shenfenzheng.Text = Node.ChildNodes[3].InnerText.ToString();
                tb_banjiming.Text = Node.ChildNodes[4].InnerText.ToString();
                tb_xueji.Text = Node.ChildNodes[5].InnerText.ToString();
                tb_jiguan.Text = Node.ChildNodes[6].InnerText.ToString();
                tb_minzhu.Text = Node.ChildNodes[7].InnerText.ToString();
                tb_zhngzhimianmao.Text = Node.ChildNodes[8].InnerText.ToString();
                bt_top.Enabled = false;
                bt_left.Enabled = false;
            }
            if (NodeList.Count > 1 && i + 1 < NodeList.Count)
            {
                bt_right.Enabled = true;
                bt_bottom.Enabled = true;
            }
            if (i + 1 >= NodeList.Count)
            {
                bt_right.Enabled = false;
                bt_bottom.Enabled = false;
                bt_top.Enabled = true;
                bt_left.Enabled = true;
            }
            if (i > 0)
            {
                bt_top.Enabled = true;
                bt_left.Enabled = true;
            }
        }

        private void tb_iosql_Click(object sender, EventArgs e)
        {
            flag = "SQL";
            string strconn = server;
            SqlConnection conn = new SqlConnection(strconn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            string strtqsl = @"select 学生信息.学号,学生信息.姓名,学生信息.性别,学生信息.身份证号,班级.班级名称,籍贯.籍贯,学籍.学籍名称,民族.民族,政治面貌.政治面貌 
                                from 学生信息,班级,籍贯,学籍,民族,政治面貌
                                where 学生信息.班级编号=班级.班级编号 and 学生信息.籍贯编号=籍贯.籍贯编号 
                                and 学生信息.学籍编号=学籍.学籍编号 and 学生信息.民族编号=民族.民族编号 
                                and 学生信息.政治面貌编号=政治面貌.政治面貌编号";
            SqlDataAdapter DA = new SqlDataAdapter(strtqsl, strconn);
            DA.Fill(ds, "my");

            tb_xuehao.Text = ds.Tables["my"].Rows[0]["学号"].ToString().Trim();
            tb_xingming.Text = ds.Tables["my"].Rows[0]["姓名"].ToString().Trim();
            tb_xingbie.Text = ds.Tables["my"].Rows[0]["性别"].ToString().Trim();
            tb_shenfenzheng.Text = ds.Tables["my"].Rows[0]["身份证号"].ToString().Trim();
            tb_banjiming.Text = ds.Tables["my"].Rows[0]["班级名称"].ToString().Trim();
            tb_xueji.Text = ds.Tables["my"].Rows[0]["学籍名称"].ToString().Trim();
            tb_jiguan.Text = ds.Tables["my"].Rows[0]["籍贯"].ToString().Trim();
            tb_minzhu.Text = ds.Tables["my"].Rows[0]["民族"].ToString().Trim();
            tb_zhngzhimianmao.Text = ds.Tables["my"].Rows[0]["政治面貌"].ToString().Trim();
            if (ds.Tables["my"].Rows.Count <= 1)
            {
                bt_right.Enabled = false;
                bt_bottom.Enabled = false;
            }
            else
            {
                curent = 0;
                bt_right.Enabled = true;
                bt_bottom.Enabled = true;
            }
            bt_sqltoxml.Enabled = true;
        }

        public void sql(int i)
        {
            if (i > 0 || i < ds.Tables["my"].Rows.Count)
            {
                tb_xuehao.Text = ds.Tables["my"].Rows[i]["学号"].ToString().Trim();
                tb_xingming.Text = ds.Tables["my"].Rows[i]["姓名"].ToString().Trim();
                tb_xingbie.Text = ds.Tables["my"].Rows[i]["性别"].ToString().Trim();
                tb_shenfenzheng.Text = ds.Tables["my"].Rows[i]["身份证号"].ToString().Trim();
                tb_banjiming.Text = ds.Tables["my"].Rows[i]["班级名称"].ToString().Trim();
                tb_xueji.Text = ds.Tables["my"].Rows[i]["学籍名称"].ToString().Trim();
                tb_jiguan.Text = ds.Tables["my"].Rows[i]["籍贯"].ToString().Trim();
                tb_minzhu.Text = ds.Tables["my"].Rows[i]["民族"].ToString().Trim();
                tb_zhngzhimianmao.Text = ds.Tables["my"].Rows[i]["政治面貌"].ToString().Trim();
            }
            if (i == 0)
            {
                tb_xuehao.Text = ds.Tables["my"].Rows[i]["学号"].ToString().Trim();
                tb_xingming.Text = ds.Tables["my"].Rows[i]["姓名"].ToString().Trim();
                tb_xingbie.Text = ds.Tables["my"].Rows[i]["性别"].ToString().Trim();
                tb_shenfenzheng.Text = ds.Tables["my"].Rows[i]["身份证号"].ToString().Trim();
                tb_banjiming.Text = ds.Tables["my"].Rows[i]["班级名称"].ToString().Trim();
                tb_xueji.Text = ds.Tables["my"].Rows[i]["学籍名称"].ToString().Trim();
                tb_jiguan.Text = ds.Tables["my"].Rows[i]["籍贯"].ToString().Trim();
                tb_minzhu.Text = ds.Tables["my"].Rows[i]["民族"].ToString().Trim();
                tb_zhngzhimianmao.Text = ds.Tables["my"].Rows[i]["政治面貌"].ToString().Trim();
                bt_top.Enabled = false;
                bt_left.Enabled = false;
            }
            if (ds.Tables["my"].Rows.Count > 1 && i + 1 < ds.Tables["my"].Rows.Count)
            {
                bt_right.Enabled = true;
                bt_bottom.Enabled = true;
            }
            if (i + 1 >= ds.Tables["my"].Rows.Count)
            {
                bt_right.Enabled = false;
                bt_bottom.Enabled = false;
                bt_top.Enabled = true;
                bt_left.Enabled = true;
            }
            if (i > 0)
            {
                bt_top.Enabled = true;
                bt_left.Enabled = true;
            }
        }


        private void bt_newxml_Click(object sender, EventArgs e)
        {
            if (newxml)//false
            {
                newxmlto1();
            }
            else//true
            {

                if (ne)
                {
                    XmlElement element = document.CreateElement("student");
                    //子节点
                    XmlElement xuehao = document.CreateElement("学号");
                    XmlElement xingming = document.CreateElement("姓名");
                    XmlElement xingbie = document.CreateElement("性别");
                    XmlElement shenfenzheng = document.CreateElement("身份证号");
                    XmlElement banjimingcheng = document.CreateElement("班级名称");
                    XmlElement jiguan = document.CreateElement("籍贯");
                    XmlElement xuejimingcheng = document.CreateElement("学籍名称");
                    XmlElement minzhu = document.CreateElement("民族");
                    XmlElement zhengzhimianmao = document.CreateElement("政治面貌");
                    //子节点赋值
                    xuehao.InnerText = tb_xuehao.Text;
                    xingming.InnerText = tb_xingming.Text;
                    xingbie.InnerText = tb_xingbie.Text;
                    shenfenzheng.InnerText = tb_shenfenzheng.Text;
                    banjimingcheng.InnerText = tb_banjiming.Text;
                    xuejimingcheng.InnerText = tb_xueji.Text;
                    jiguan.InnerText = tb_jiguan.Text;
                    minzhu.InnerText = tb_minzhu.Text;
                    zhengzhimianmao.InnerText = tb_zhngzhimianmao.Text;
                    //子节点添加至根节点
                    element.AppendChild(xuehao);
                    element.AppendChild(xingming);
                    element.AppendChild(xingbie);
                    element.AppendChild(shenfenzheng);
                    element.AppendChild(banjimingcheng);
                    element.AppendChild(xuejimingcheng);
                    element.AppendChild(jiguan);
                    element.AppendChild(minzhu);
                    element.AppendChild(zhengzhimianmao);
                    //添加到根元素
                    ((XmlElement)document.SelectSingleNode("/class")).AppendChild(element);
                    ne = true;
                    tb_xuehao.Text = "";
                    tb_xingming.Text = "";
                    tb_xingbie.Text = "";
                    tb_shenfenzheng.Text = "";
                    tb_banjiming.Text = "";
                    tb_xueji.Text = "";
                    tb_jiguan.Text = "";
                    tb_minzhu.Text = "";
                    tb_zhngzhimianmao.Text = "";
                }
                else
                {
                    tb_xuehao.Text = "";
                    tb_xingming.Text = "";
                    tb_xingbie.Text = "";
                    tb_shenfenzheng.Text = "";
                    tb_banjiming.Text = "";
                    tb_xueji.Text = "";
                    tb_jiguan.Text = "";
                    tb_minzhu.Text = "";
                    tb_zhngzhimianmao.Text = "";
                    ne = true;
                }
            }
            bt_right.Enabled = false;
            bt_left.Enabled = false;
            bt_top.Enabled = false;
            bt_bottom.Enabled = false;
            bt_save.Enabled = true;
        }
        private void newxmlto1()
        {
            if (newflag != "false" && newflag == "io")
            {
                document.AppendChild(document.CreateXmlDeclaration("1.0", "UTF-8", "yes"));
                document.AppendChild(document.CreateElement("class"));
            }
            newxml = true;
        }

        private void bt_save_Click(object sender, EventArgs e)
        {
            try
            {
                document.Save(xmltmp);
                MessageBox.Show("保存成功");
                bt_save.Enabled = false;
                if (flag == "XML")
                {
                    xml(NodeList.Count - 1);
                }
                else if (flag == "SQL")
                {
                    sql(1);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        private void bt_update_Click(object sender, EventArgs e)
        {
            Node.ChildNodes[0].InnerText = tb_xuehao.Text;
            Node.ChildNodes[1].InnerText = tb_xingming.Text;
            Node.ChildNodes[2].InnerText = tb_xingbie.Text;
            Node.ChildNodes[3].InnerText = tb_shenfenzheng.Text;
            Node.ChildNodes[4].InnerText = tb_banjiming.Text;
            Node.ChildNodes[5].InnerText = tb_xueji.Text;
            Node.ChildNodes[6].InnerText = tb_jiguan.Text;
            Node.ChildNodes[7].InnerText = tb_minzhu.Text;
            Node.ChildNodes[8].InnerText = tb_zhngzhimianmao.Text;
            bt_save.Enabled = true;
        }

        private void bt_xmltosql_Click(object sender, EventArgs e)
        {
            bool flag=true;
            try
            {
                for (int i = 0; i < NodeList.Count; i++)
                {
                    SqlConnection con = new SqlConnection(server);
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    SqlCommand com = new SqlCommand();
                    com.Connection = con;
                    com.CommandType = CommandType.Text;
                    string Tsql = "insert into 学生信息 values (@xuehao,@xingming,@xingbie,@shenfenzheng,@banji,@jiguan,@xueji,@zhengzhi,@minzu)";
                    //string Tsql = "delete from 学生信息  where 学号=@xuehao";
                    com.CommandText = Tsql;
                    Node = NodeList.Item(i);
                    com.Parameters.AddWithValue("xuehao", Node.ChildNodes[0].InnerText.ToString());
                    com.Parameters.AddWithValue("xingming", Node.ChildNodes[1].InnerText.ToString());
                    com.Parameters.AddWithValue("xingbie", Node.ChildNodes[2].InnerText.ToString());
                    com.Parameters.AddWithValue("shenfenzheng", Node.ChildNodes[3].InnerText.ToString());
                    com.Parameters.AddWithValue("banji", Node.ChildNodes[4].InnerText.ToString());
                    com.Parameters.AddWithValue("jiguan", Node.ChildNodes[5].InnerText.ToString());
                    com.Parameters.AddWithValue("xueji", Node.ChildNodes[6].InnerText.ToString());
                    com.Parameters.AddWithValue("zhengzhi", Node.ChildNodes[7].InnerText.ToString());
                    com.Parameters.AddWithValue("minzu", Node.ChildNodes[8].InnerText.ToString());
                    com.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                flag=false;
            }
            if (flag)
            {
                MessageBox.Show("写入成功");
            }
            else
            {
                MessageBox.Show("写入失败");
            }
        }
    }
}

项目文件,文件内包含数据库脚本
链接: https://pan.baidu.com/s/1r7yBFxmVkK3mPnm7eTCH4w
提取码: ut46


一只大萝北 , 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权
转载请注明原文链接:C#SqlServer导出到XML及XML写入数据库
喜欢 (0)
[]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址