793291696readxmltoserver

readxmltoserver

form1.cs(思路为1 dataset读xml文件 2 sqlconnection建立与数据库连接
3 sqlcommand将sql语句进行参数注入再执行到连接的数据库中)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ji
{
    public partial class Form1 : Form
    {
        string connStr = "Data Source=localhost;Initial Catalog=SCNT;User ID=sa;Password=root;\r\n";
        public Form1()
        {
            InitializeComponent();
        }
        public void daoru()//导入数据库
        {
            DataSet ds = new DataSet();
            ds.ReadXml("D:/DDD/scut.xml");

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                // Insert courses
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Course (id, name) VALUES (@CourseId, @CourseName)", conn))
                {
                    cmd.Parameters.Add("@CourseId", SqlDbType.Int);
                    cmd.Parameters.Add("@CourseName", SqlDbType.NVarChar);

                    foreach (DataRow row in ds.Tables[0].Rows)//ds.table[0]对应的是xml文件的最大的父标签
                    {
                        int courseId = Convert.ToInt32(row["courseId"]);//
                        string courseName = row["course"].ToString();//获取父标签下名为course的子标签的标签内文本

                        cmd.Parameters["@CourseId"].Value = courseId;
                        cmd.Parameters["@CourseName"].Value = courseName;

                        cmd.ExecuteNonQuery();
                    }
                }

                // Insert students and scores
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Student (id, name, Gender) VALUES (@StudentId, @StudentName, @Gender);" +
                                                        "INSERT INTO Score (course_id, student_id, score) VALUES (@CourseId, @StudentId, @Score)", conn))
                {
                    cmd.Parameters.Add("@StudentId", SqlDbType.Int);
                    cmd.Parameters.Add("@StudentName", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@Gender", SqlDbType.NVarChar);
                    cmd.Parameters.Add("@CourseId", SqlDbType.Int);
                    cmd.Parameters.Add("@Score", SqlDbType.Int);

                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        int studentId = Convert.ToInt32(row["id"]);
                        string studentName = row["name"].ToString();
                        string gender = row["gender"].ToString();
                        int courseId = Convert.ToInt32(row["courseId"]);
                        int score = Convert.ToInt32(row["score"]);

                        cmd.Parameters["@StudentId"].Value = studentId;
                        cmd.Parameters["@StudentName"].Value = studentName;
                        cmd.Parameters["@Gender"].Value = gender;
                        cmd.Parameters["@CourseId"].Value = courseId;
                        cmd.Parameters["@Score"].Value = score;

                        cmd.ExecuteNonQuery();
                    }
                }
            }

        }
        private void Form1_Load(object sender, EventArgs e)
        {
            daoru();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string courseName = textBox1.Text;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                string query = @"SELECT student.name, score.score
                     FROM student
                     INNER JOIN score ON student.id = score.student_id
                     INNER JOIN course ON score.course_id = course.id
                     WHERE course.name = @courseName
                     ORDER BY Score.Score DESC";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@CourseName", courseName);

                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);

                    dataGridView1.DataSource = dt;
                }
            }
        }
    }
}

xml文件

<?xml version="1.0" encoding="utf-8"?>
<grades>
<grade>
<id>2019001</id>
<name>张三</name>
<gender>男</gender>
<course>机器学习</course>
<courseId>111</courseId>
<score>85</score>
</grade>
<grade>
<id>2019002</id>
<name>李四</name>
<gender>男</gender>
<course>操作系统</course>
<courseId>222</courseId>
<score>90</score>
</grade>
<grade>
<id>2019003</id>
<name>王五</name>
<gender>男</gender>
<course>数据结构</course>
<courseId>333</courseId>
<score>95</score>
</grade>
</grades>