c#数据库课程设计源码

一、数据库设计

1、数据库名称：StudentDB

2、数据表名称：Students

3、字段说明

StudentID（int类型，主键，自增长）：学生编号。

Name（nvarchar(50)类型）：学生姓名。

Age（int类型）：学生年龄。

Gender（nvarchar(10)类型）：学生性别。

二、C#代码实现

1、创建数据库连接类

using System;
using System.Data.SqlClient;
namespace StudentManagementSystem
{
    class DBConnection
    {
        private static string connectionString = "Server=.;Database=StudentDB;Integrated Security=True";
        public static SqlConnection GetConnection()
        {
            return new SqlConnection(connectionString);
        }
    }
}

2、创建学生信息类

namespace StudentManagementSystem
{
    class Student
    {
        public int StudentID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Gender { get; set; }
    }
}

3、创建数据访问层

using System;
using System.Data;
using System.Data.SqlClient;
namespace StudentManagementSystem
{
    class StudentDAL
    {
        public DataTable GetAllStudents()
        {
            string query = "SELECT * FROM Students";
            SqlConnection conn = DBConnection.GetConnection();
            SqlCommand cmd = new SqlCommand(query, conn);
            DataTable dt = new DataTable();
            try
            {
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }
        public void AddStudent(Student student)
        {
            string query = "INSERT INTO Students (Name, Age, Gender) VALUES (@Name, @Age, @Gender)";
            SqlConnection conn = DBConnection.GetConnection();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@Name", student.Name);
            cmd.Parameters.AddWithValue("@Age", student.Age);
            cmd.Parameters.AddWithValue("@Gender", student.Gender);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public void UpdateStudent(Student student)
        {
            string query = "UPDATE Students SET Name = @Name, Age = @Age, Gender = @Gender WHERE StudentID = @StudentID";
            SqlConnection conn = DBConnection.GetConnection();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@StudentID", student.StudentID);
            cmd.Parameters.AddWithValue("@Name", student.Name);
            cmd.Parameters.AddWithValue("@Age", student.Age);
            cmd.Parameters.AddWithValue("@Gender", student.Gender);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        public void DeleteStudent(int studentID)
        {
            string query = "DELETE FROM Students WHERE StudentID = @StudentID";
            SqlConnection conn = DBConnection.GetConnection();
            SqlCommand cmd = new SqlCommand(query, conn);
            cmd.Parameters.AddWithValue("@StudentID", studentID);
            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}

4、创建业务逻辑层和表示层

using System;
namespace StudentManagementSystem
{
    class Program
    {
        static void Main(string[] args)
        {
            StudentDAL dal = new StudentDAL();
            while (true)
            {
                Console.WriteLine("1. View All Students");
                Console.WriteLine("2. Add Student");
                Console.WriteLine("3. Update Student");
                Console.WriteLine("4. Delete Student");
                Console.WriteLine("5. Exit");
                Console.Write("Enter your choice: ");
                int choice = int.Parse(Console.ReadLine());
                switch (choice)
                {
                    case 1:
                        DisplayStudents(dal.GetAllStudents());
                        break;
                    case 2:
                        AddStudent(dal);
                        break;
                    case 3:
                        UpdateStudent(dal);
                        break;
                    case 4:
                        DeleteStudent(dal);
                        break;
                    case 5:
                        return;
                    default:
                        Console.WriteLine("Invalid choice. Please try again.");
                        break;
                }
            }
        }
        static void DisplayStudents(DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine($"StudentID: {row["StudentID"]}, Name: {row["Name"]}, Age: {row["Age"]}, Gender: {row["Gender"]}");
            }
        }
        static void AddStudent(StudentDAL dal)
        {
            Student student = new Student();
            Console.Write("Enter student name: ");
            student.Name = Console.ReadLine();
            Console.Write("Enter student age: ");
            student.Age = int.Parse(Console.ReadLine());
            Console.Write("Enter student gender: ");
            student.Gender = Console.ReadLine();
            dal.AddStudent(student);
            Console.WriteLine("Student added successfully!");
        }
        static void UpdateStudent(StudentDAL dal)
        {
            Console.Write("Enter student ID to update: ");
            int id = int.Parse(Console.ReadLine());
            Student student = new Student();
            Console.Write("Enter new name: ");
            student.Name = Console.ReadLine();
            Console.Write("Enter new age: ");
            student.Age = int.Parse(Console.ReadLine());
            Console.Write("Enter new gender: ");
            student.Gender = Console.ReadLine();
            student.StudentID = id;
            dal.UpdateStudent(student);
            Console.WriteLine("Student updated successfully!");
        }
        static void DeleteStudent(StudentDAL dal)
        {
            Console.Write("Enter student ID to delete: ");
            int id = int.Parse(Console.ReadLine());
            dal.DeleteStudent(id);
            Console.WriteLine("Student deleted successfully!");
        }
    }
}