通过动态创建表来实现成绩存放

经过一番思量，感觉成绩存放采用动态创建表更合理。本来想着通过查询DB中现有表名来确定某专业成绩是否存在，后来决定重新建一个只有一个字段（专业名）的表来记录已经有哪些专业的成绩表存在。

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/**//// <summary>
/// Summary description for DbOperation
/// </summary>
public class DbOperation
...{
    string Department;
    double StuID;
    string Course;
    double Score;

    public static string connString = ConfigurationManager.AppSettings["provider"].ToString()
            + HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"])
            + ";";

public DbOperation()
...{
}

    public DbOperation(string department,double stuID,string course,double score)
    ...{
        Department = department;
        StuID = stuID;
        Course = course;
        Score = score;
    }

  
    public void InsertGradeOfDepartment()
    ...{
        OleDbConnection Conn = new OleDbConnection(connString);
        string sql = "select * from GradeAmount where Department='"+Department+"'";
        Conn.Open();
        OleDbCommand Cmd = new OleDbCommand(sql, Conn);
        OleDbDataReader dr = Cmd.ExecuteReader();
        if (dr.Read())
        ...{
            Conn.Close();
            InsertGrade();

        }
        else
        ...{
            Conn.Close();
            CreatGradeTable();
            InsertStu2GradeTable();
            SignDepartment();
            InsertGrade();
        }
    }

    private void CreatGradeTable()
    ...{
        string CreatSql = "Create table " + Department
                        + " (StuID Number, StuName Text)";
        string GetCourseSql = "select CourseName from Course where Department='" 
                        + Department + "'";

        OleDbConnection CreatConn = new OleDbConnection(connString);

        try
        ...{
            CreatConn.Open();
            OleDbCommand CreatCmd = new OleDbCommand(CreatSql, CreatConn);
            CreatCmd.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter(GetCourseSql, CreatConn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Course");

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            ...{
                string temp = ds.Tables[0].Rows[i][0].ToString();
                temp = temp.Replace('.', '_');
                string AlterSql = "alter table " + Department + " add [" + temp + "] Number;";
                OleDbCommand AlterCmd = new OleDbCommand(AlterSql, CreatConn);
                AlterCmd.ExecuteNonQuery();
            }
        }
        catch
        ...{
        }
        finally
        ...{
            CreatConn.Close();
        }
    }

       private void InsertStu2GradeTable()
    ...{
        OleDbConnection Conn = new OleDbConnection(connString);
        Conn.Open();
        string GetStuSql = "select StuID,StuName from StuInfo where Department='"+Department+"'";
        OleDbDataAdapter da = new OleDbDataAdapter(GetStuSql, Conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "Stu");
        for (int i=0; i < ds.Tables[0].Rows.Count; i++)
        ...{
            double stuId =(double) ds.Tables[0].Rows[i][0];
            string stuName = ds.Tables[0].Rows[i][1].ToString();
            string InsertSql = "Insert Into " + Department + " (StuID,StuName) Values("+stuId+",'"+stuName+"')";
            OleDbCommand InsCmd = new OleDbCommand(InsertSql, Conn);
            InsCmd.ExecuteNonQuery();
        }
        Conn.Close();
    }

    private void SignDepartment()
    ...{
        OleDbConnection Conn = new OleDbConnection(connString);
        Conn.Open();
        string sql = "Insert into GradeAmount (Department) Values('"+Department+"')";
        OleDbCommand Cmd = new OleDbCommand(sql, Conn);
        Cmd.ExecuteNonQuery();
        Conn.Close();
    }

 private void InsertGrade()
    ...{
        string InsertSql = "update "+Department+" set "+Course+"="+Score+" where StuID="+StuID;
        OleDbConnection Conn = new OleDbConnection(connString);
        try
        ...{
            Conn.Open();
            OleDbCommand InsertCmd = new OleDbCommand(InsertSql, Conn);
            InsertCmd.ExecuteNonQuery();
        }
        catch
        ...{
        }
        finally
        ...{
            Conn.Close();
        }
    }

}

//虽然这将导致很多代码重写，但是感觉无论是为了学习还是仅仅为了毕业设计都是值得的。