![在这里插入图片描述](https://img-blog.csdnimg.cn/ee5bee913ddd42b4a13fb55a3a31a957.png)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace sql_18
{
public partial class Form1 : Form
{
string connectionString = "Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=master;Data Source=TABLET-K4O01JA4";
SqlConnection connection;
public Form1()
{
InitializeComponent();
connection = new SqlConnection(connectionString);
connection.Open();
LoadListView1();
}
private void LoadListView1()
{
SqlCommand sqlCommand = new SqlCommand("select sname from Student", connection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string studentName = sqlDataReader.GetString(0);
if (!comboBox1.Items.Contains(studentName))
{
comboBox1.Items.Add(studentName);
}
}
sqlDataReader.Close();
listView1.View = View.Details;
listView1.Columns.Add("姓名");
listView1.Columns.Add("课程");
listView1.Columns.Add("成绩");
comboBox1.SelectedIndexChanged += comboBox1_SelectedIndexChanged;
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
listView1.Items.Clear();
string selectStudent = comboBox1.SelectedItem.ToString();
SqlCommand sqlCommand = new SqlCommand("select s.sname,c.cname,g.grade from " +
"Student s,Course c,Grades g where s.sid=g.sid and c.cid=g.cid and " +
"s.sname=@StudentName", connection);
sqlCommand.Parameters.AddWithValue("@StudentName", selectStudent);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string name = sqlDataReader.GetString(0);
string course = sqlDataReader.GetString(1);
int grade = sqlDataReader.GetInt16(2);
ListViewItem item = new ListViewItem();
item.Text = name;
item.SubItems.Add(course);
item.SubItems.Add(grade.ToString());
listView1.Items.Add(item);
}
sqlDataReader.Close();
}
private void Form1_FormClosing(object sender,FormClosingEventArgs e)
{
connection.Close();
if (MessageBox.Show("确定关闭窗口吗?", "确认关闭", MessageBoxButtons.YesNo) == DialogResult.No)
{
e.Cancel=true;
}
}
}
}
using System;
using System.IO;
using System.Xml;
class Program
{
static void Main(string[] args)
{
try
{
string txtFile = @"C:\Users\11027\Desktop\Grade.txt";
string xmlFile = @"C:\Users\11027\Desktop\Grade.xml";
XmlDocument document = new XmlDocument();
XmlElement root = document.CreateElement("students");
document.AppendChild(root);
using(StreamReader reader=new StreamReader(txtFile))
{
while (!reader.EndOfStream)
{
string line = reader.ReadLine();
string[] parts = line.Split(',');
string id = parts[0];
string name = parts[1];
string course = parts[2];
string score = parts[3];
XmlElement student = null;
foreach(XmlNode node in root.ChildNodes)
{
if (node["学号"].InnerText == id )
{
XmlElement courseElement = document.CreateElement("课程");
courseElement.SetAttribute("课程名", course);
courseElement.InnerText = score;
node.AppendChild(courseElement);
student = (XmlElement)node;
break;
}
}
if (student == null)
{
student = document.CreateElement("student");
root.AppendChild(student);
XmlElement idElement = document.CreateElement("学号");
idElement.InnerText = id;
student.AppendChild(idElement);
XmlElement nameElement = document.CreateElement("姓名");
nameElement.InnerText = name;
student.AppendChild(nameElement);
XmlElement courseElement = document.CreateElement("课程");
courseElement.SetAttribute("课程名", course);
courseElement.InnerText = score;
student.AppendChild(courseElement);
}
}
}
document.Save(xmlFile);
Console.WriteLine("XML file created successfully.");
}
catch(Exception ex)
{
Console.WriteLine("An error:" + ex.Message);
}
}
}
using System;
using System.Collections.Generic;
using System.Xml;
class Program
{
static void Main(string[] args)
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load("C:\\Users\\11027\\Desktop\\grades.xml");
XmlElement root = xmlDocument.DocumentElement;
XmlNodeList grades = root.GetElementsByTagName("grade");
List<Grade> gradeList = new List<Grade>();
foreach(XmlNode grade in grades)
{
string id = grade.SelectSingleNode("id").InnerText;
string name = grade.SelectSingleNode("name").InnerText;
string course = grade.SelectSingleNode("course").InnerText;
int score = int.Parse(grade.SelectSingleNode("score").InnerText);
Grade newGrade = new Grade(id, name, course, score);
gradeList.Add(newGrade);
}
gradeList.Sort((x,y)=>y.Score-x.Score);
using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"output.txt"))
{
foreach(Grade grade in gradeList)
{
string line = grade.Id + "\t" + grade.Name + "\t" + grade.Course + "\t" + grade.Score;
file.WriteLine(line);
}
}
}
}
internal class Grade
{
public string Id { get; set; }
public string Name { get; set; }
public string Course { get; set; }
public int Score { get; set; }
public Grade(string id,string name,string course,int score)
{
Id = id;
Name = name;
Course = course;
Score = score;
}
}
![在这里插入图片描述](https://img-blog.csdnimg.cn/fe490f26b8914e96902d3ef72a14e7eb.png)
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
namespace xmlToSql
{
public partial class Form1 : Form
{
string connectionString =
"Data Source=localhost;Initial Catalog=master;Integrated security=true";
SqlConnection connection;
public Form1()
{
InitializeComponent();
connection = new SqlConnection(connectionString);
connection.Open();
LoadDataGrid();
LoadListView();
}
private void LoadDataGrid()
{
SqlCommand command1 = new SqlCommand("select * from Student", connection);
SqlDataAdapter adapter1 = new SqlDataAdapter(command1);
DataTable dataTable1 = new DataTable();
adapter1.Fill(dataTable1);
dataGridViewStudent.DataSource = dataTable1;
SqlCommand command2 = new SqlCommand("select * from Course", connection);
SqlDataAdapter adapter2 = new SqlDataAdapter(command2);
DataTable dataTable2 = new DataTable();
adapter2.Fill(dataTable2);
dataGridViewCourse.DataSource = dataTable2;
SqlCommand command3 = new SqlCommand("select * from Grades", connection);
SqlDataAdapter adapter3 = new SqlDataAdapter(command3);
DataTable dataTable3 = new DataTable();
adapter3.Fill(dataTable3);
dataGridViewGrades.DataSource = dataTable3;
SqlCommand command = new SqlCommand("select s.sid,s.sname,c.cname,g.grade " +
"from Student s inner join Grades g on s.sid=g.sid inner join" +
" Course c on g.cid=c.cid", connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataGridViewAll.DataSource = dataTable;
}
private void LoadListView()
{
SqlCommand sqlCommand = new SqlCommand("select cname from Course", connection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string courseName = sqlDataReader.GetString(0);
if (!comboBox1.Items.Contains(courseName))
{
comboBox1.Items.Add(courseName);
}
}
sqlDataReader.Close();
comboBox1.SelectedIndexChanged += comboBox1_SelectedIndexChanged;
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
listView1.View = View.Details;
listView1.Columns.Add("姓名");
listView1.Columns.Add("成绩");
listView1.Items.Clear();
string selectedCourse = comboBox1.SelectedItem.ToString();
SqlCommand sqlCommand = new SqlCommand("select s.sname,g.grade from" +
" Student s,Grades g,Course c where s.sid=g.sid and g.cid=c.cid " +
" and c.cname=@CourseName order by g.grade desc", connection);
sqlCommand.Parameters.AddWithValue("@CourseName", selectedCourse);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
string name = sqlDataReader.GetString(0);
int grade = sqlDataReader.GetInt16(1);
ListViewItem item = new ListViewItem();
item.Text = name;
item.SubItems.Add(grade.ToString());
listView1.Items.Add(item);
}
sqlDataReader.Close();
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "XML Files(*.xml)|*.xml";
if (openFileDialog.ShowDialog()!=DialogResult.OK)
{
return;
}
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(openFileDialog.FileName);
XmlNodeList gradeList = xmlDocument.GetElementsByTagName("grade");
foreach(XmlNode gradeNode in gradeList)
{
string id = gradeNode.SelectSingleNode("id").InnerText;
string name = gradeNode.SelectSingleNode("name").InnerText;
string course = gradeNode.SelectSingleNode("course").InnerText;
string score = gradeNode.SelectSingleNode("score").InnerText;
string courseSelectSql = "select cid from Course where cname=@CourseName";
SqlCommand courseSelectCommand = new SqlCommand(courseSelectSql, connection);
courseSelectCommand.Parameters.AddWithValue("@CourseName", course);
object result = courseSelectCommand.ExecuteScalar();
int courseID = result == null ? 0 : Convert.ToInt32(result);
if (courseID == 0)
{
string courseInsertSql = "insert into Course(cname) output inserted.cid values(@CourseName)";
SqlCommand courseInsertCommand = new SqlCommand(courseInsertSql, connection);
courseInsertCommand.Parameters.AddWithValue("@CourseName", course);
courseID = (int)courseInsertCommand.ExecuteScalar();
}
string studentSelectSql = "select sid from Student where sname=@Name";
SqlCommand studentSelectCommand = new SqlCommand(studentSelectSql, connection);
studentSelectCommand.Parameters.AddWithValue("@Name", name);
string studentID = (string)studentSelectCommand.ExecuteScalar();
if (studentID == null)
{
string studentInsertSql = "insert into Student (sid,sname,ssex) values(@StudentID,@Name,@Sex)";
SqlCommand studentInsertCommand = new SqlCommand(studentInsertSql, connection);
studentInsertCommand.Parameters.AddWithValue("@StudentID", id);
studentInsertCommand.Parameters.AddWithValue("@Name", name);
studentInsertCommand.Parameters.AddWithValue("@Sex", "未知");
}
string GradesSelectSql = "select * from Grades where sid=@StudentID and cid=@CourseID";
SqlCommand GradesSelectCommand = new SqlCommand(GradesSelectSql, connection);
GradesSelectCommand.Parameters.AddWithValue("@StudentID", studentID);
GradesSelectCommand.Parameters.AddWithValue("@CourseID", courseID);
SqlDataReader dataReader = GradesSelectCommand.ExecuteReader();
if (dataReader.HasRows)
{
Console.WriteLine($"记录已存在,更新分数为{score}");
dataReader.Close();
SqlCommand scoreUpdateCommand = new SqlCommand("update Grades set grade=@Score" +
" where sid=@StudentID and cid=@CourseID", connection);
scoreUpdateCommand.Parameters.AddWithValue("Score", score);
scoreUpdateCommand.Parameters.AddWithValue("StudentID", studentID);
scoreUpdateCommand.Parameters.AddWithValue("CourseID", courseID);
scoreUpdateCommand.ExecuteNonQuery();
}
else
{
Console.WriteLine($"记录不存在,插入新纪录,分数为{score}");
dataReader.Close();
SqlCommand gradesInsertCommand = new SqlCommand("insert into Grades " +
"values(@StudentID,@CourseID,@Score)", connection);
gradesInsertCommand.Parameters.AddWithValue("StudentID", studentID);
gradesInsertCommand.Parameters.AddWithValue("CourseID", courseID);
gradesInsertCommand.Parameters.AddWithValue("Score", score);
gradesInsertCommand.ExecuteNonQuery();
}
}
MessageBox.Show("数据导入成功");
LoadDataGrid();
LoadListView();
}
private void btn_add_Click(object sender, EventArgs e)
{
string studentName = textBox1.Text;
string courseName = textBox2.Text;
string score = textBox3.Text;
int studentID = 0;
SqlCommand sqlCommand = new SqlCommand("select sid from Student where sname=@StudentName", connection);
sqlCommand.Parameters.AddWithValue("@StudentName", studentName);
object result = sqlCommand.ExecuteScalar();
if (result != null)
{
studentID = Convert.ToInt32(result);
}
else
{
MessageBox.Show("学生不存在!");
return;
}
int courseID = 0;
SqlCommand sqlCommand1 = new SqlCommand("select cid from Course where cname=@CourseName", connection);
sqlCommand1.Parameters.AddWithValue("@CourseName", courseName);
object result1 = sqlCommand.ExecuteScalar();
if (result1 != null)
{
courseID = Convert.ToInt32(result1);
}
else
{
MessageBox.Show("课程不存在!");
return;
}
SqlCommand insertCommand = new SqlCommand("insert into Grades values(@StudentID,@CourseID,@Score)", connection);
insertCommand.Parameters.AddWithValue("@StudentID", studentID);
insertCommand.Parameters.AddWithValue("@CourseID", courseID);
insertCommand.Parameters.AddWithValue("@Score", score);
LoadDataGrid();
LoadListView();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
connection.Close();
if (MessageBox.Show("确定要关闭窗口吗?", "确认关闭", MessageBoxButtons.YesNo) == DialogResult.No)
{
e.Cancel = true;
}
}
}
}