students.cs

学生成绩管理系统

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace ADO
{
    class Students
    {
        public Students() {}
        //连接字符串
        private string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\教学管理.mdb;Persist Security Info=True";
        //由查询字符串queryString返回Reader
        public OleDbDataReader GetReaderOfStudents(string queryString)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand command = new OleDbCommand(queryString, connection);
            connection.Open();
            OleDbDataReader reader = command.ExecuteReader();
            return reader;
        }

        public DataTable GetTableOfStudents(string queryString)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            //OleDbCommand command = new OleDbCommand(queryString, connection);
            connection.Open();
            OleDbDataAdapter myda = new OleDbDataAdapter(queryString, connection);
            DataSet ds = new DataSet();
            myda.Fill(ds);
            return ds.Tables[0];
        }

        /*//由查询字符串queryString返回Reader
        public OleDbDataReader GetReaderOfdStudents(string queryString)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            OleDbCommand command = new OleDbCommand(queryString, connection);
            OleDbDataReader reader = command.ExecuteReader();
            return reader;
        }
        public DataTable GetTableOfdStudents(string queryString)
        {
            OleDbConnection connection = new OleDbConnection(connectionString);
            //OleDbCommand command = new OleDbCommand(queryString, connection);
            connection.Open();
            OleDbDataAdapter myda = new OleDbDataAdapter(queryString, connection);
            DataSet ds = new DataSet();
            //ds.Tables["Students"].Rows[].Delete();
            //ds.Tables["Students"].AcceptChanges();
            myda.Fill(ds);
            return ds.Tables[0];
        }*/

        //返回所有学生信息，以Reader表示
        public OleDbDataReader GetReaderOfAllStudents()
        {
            string queryString = "SELECT * FROM Students";
            return GetReaderOfStudents(queryString);
        }
        //返回所有学生信息，以Table表示
        public DataTable GetTableOfAllStudents()
        {
            string queryString = "SELECT * FROM Students";
            return GetTableOfStudents(queryString);
        }
     
        //删除选定学生信息，并返回学生信息，以Reader表示
        public OleDbDataReader GetReaderOfDeleteStudents(string queryStrings)
        {
            string queryString = "DELETE FROM Students WHERE 学号 = " + queryStrings;
            return GetReaderOfStudents(queryString);
        }
        //删除选定学生信息，并返回所有学生信息，以Table表示
        public DataTable GetTableOfDeleteStudents(string queryStrings)
        {
            string queryString = "DELETE FROM Students WHERE 学号 = " + queryStrings;
            return GetTableOfStudents(queryString);
        }
        //查询字段名为fieldName,值为value的学生信息，返回Reader
        //注意不同的类型数据进行比较时的格式不一样
        public OleDbDataReader QueryReaderOfStudents(string fieldName, string value)
        {
            string queryString = "";
            if (fieldName == "出生日期")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=#" + value + "#";
            else if (fieldName == "高考分数")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=" + value;
            else if (fieldName == "年龄")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=" + value;
            else
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "='" + value + "'";
            return GetReaderOfStudents(queryString);
        }
        //查询字段名为fieldName,值为value的学生信息，返回Table
                public DataTable QueryTableOfStudents(string fieldName, string value)
        {
            string queryString = "";
            if (fieldName == "出生日期")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=#" + value + "#";
            else if (fieldName == "高考分数")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=" + value;
            else if (fieldName == "年龄")
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "=" + value;
            else
                queryString = "SELECT * FROM STUDENTS where " + fieldName + "='" + value + "'";
            return GetTableOfStudents(queryString);
        }
        //多条件查询字段,值为value的学生信息，返回Reader
        public OleDbDataReader QueryReaderOfSelectStudents(string value1, string value2, string value3, string value4, string value5, string value6, string value7, string value8)
        {
            string queryString = "";
            if (value1 == "高考分数")
                queryString = "SELECT * FROM STUDENTS where " + value1 + "=" + value1;
                //queryString = "SELECT * FROM STUDENTS where " (value1 != "" ? "姓名" :(value2 != "" ? "性别" :(value3 != "" ? "年龄" :(value4 != "" ? "籍贯" :)))) + "=" + value1;
            else if (value1 == "年龄")
                queryString = "SELECT * FROM STUDENTS where " + value1 + "=" + value1;
            else
                queryString = "SELECT * FROM STUDENTS where " + value1 + "='" + value1 + "'";
            return GetReaderOfStudents(queryString);
        }
        //多条件查询字段,值为value的学生信息，返回Table
        public DataTable QueryTableOfSelectStudents(string value1, string value2, string value3, string value4, string value5, string value6, string value7, string value8)
        {
            string queryString = "";
            if (value1 == "高考分数")
                queryString = "SELECT * FROM STUDENTS where " + value1 + "=" + value1;
            else if (value1 == "年龄")
                queryString = "SELECT * FROM STUDENTS where " + value1 + "=" + value1;
            else
                queryString = "SELECT * FROM STUDENTS where " + value1 + "='" + value1 + "'";
            return GetTableOfStudents(queryString);
        }
        //修改学号为ID的学生信息
        public void UpdateStudent(string ID, string Name, string Sex, int Age, DateTime Birthday, string Add, string Pro, string Grade, string Class, string Tel, int GK)
        {
            string updateString = @"UPDATE [students] SET [姓名] = @name, [性别] = @sex, [年龄] = @age, [出生日期] = @birthday, [籍贯] = @add, [专业] = @pro,[年级] = @grade, [班级] = @class, [电话号码] = @tel, [高考分数] = @gk WHERE (学号 = @ID)";
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand command = new OleDbCommand(updateString, connection);
            connection.Open();
            command.Parameters.Add(new OleDbParameter("@ID", ID));
            command.Parameters.Add(new OleDbParameter("@name", Name));
            command.Parameters.Add(new OleDbParameter("@sex", Sex));
            command.Parameters.Add(new OleDbParameter("@age", Age));
            command.Parameters.Add(new OleDbParameter("@birthday", Birthday));
            command.Parameters.Add(new OleDbParameter("@add", Add));
            command.Parameters.Add(new OleDbParameter("@pro", Pro));
            command.Parameters.Add(new OleDbParameter("@grade", Grade));
            command.Parameters.Add(new OleDbParameter("@class", Class));
            command.Parameters.Add(new OleDbParameter("@tel", Tel));
            command.Parameters.Add(new OleDbParameter("@gk", GK));
            command.ExecuteNonQuery();
        }
        //插入学生信息记录
        public void InsertStudent(string ID, string Name, string Sex, int Age, DateTime Birthday, string Add, string Pro, string Grade, string Class, string Tel, int GK)
        {
            string insertString = @"Insert into [students]([学号],[姓名],[性别],[年龄],[出生日期],[籍贯],[专业],[年级],[班级],[电话号码],[高考分数])
                                    values (@ID, @name, @sex, @age, @birthday, @add, @pro, @grade, @class, @tel, @gk)";
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand command = new OleDbCommand(insertString, connection);
            connection.Open();
            command.Parameters.Add(new OleDbParameter("@ID", ID));
            command.Parameters.Add(new OleDbParameter("@name", Name));
            command.Parameters.Add(new OleDbParameter("@sex", Sex));
            command.Parameters.Add(new OleDbParameter("@age", Age));
            command.Parameters.Add(new OleDbParameter("@birthday", Birthday));
            command.Parameters.Add(new OleDbParameter("@add", Add));
            command.Parameters.Add(new OleDbParameter("@pro", Pro));
            command.Parameters.Add(new OleDbParameter("@grade", Grade));
            command.Parameters.Add(new OleDbParameter("@class", Class));
            command.Parameters.Add(new OleDbParameter("@tel", Tel));
            command.Parameters.Add(new OleDbParameter("@gk", GK));
            command.ExecuteNonQuery();
        }


    }
}