存储过程与触发器Web调用代码

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace WangKang.DataBase
...{
    public class WebDB:DB
    ...{
        private string connstring = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
        //查询所有学生信息
        public DataSet student_select()
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_student_select", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //通过学好查询某个学生信息
        public DataSet student_select_bySID(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_student_select_bySID", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //
        public DataSet student_avg(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_student_avg", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //插入学生信息
        public void student_insert(string SID, string SName, string SClass, string SSex)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_student_insert", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            mycomm.Parameters.Add(new SqlParameter("@SName", SName));
            mycomm.Parameters.Add(new SqlParameter("@SClass", SClass));
            mycomm.Parameters.Add(new SqlParameter("@SSex", SSex));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //删除学生信息
        public void student_delete(string SID)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_student_delete", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //修改学生信息
        public void student_update(string SID, string SName, string SClass, string SSex)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_student_update", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            mycomm.Parameters.Add(new SqlParameter("@SName", SName));
            mycomm.Parameters.Add(new SqlParameter("@SClass", SClass));
            mycomm.Parameters.Add(new SqlParameter("@SSex", SSex));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //通过学好查看某个学生的选课情况student_one_information
        public DataSet student_one_information(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_student_one_information", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //查询所有学生的选课情况
        public DataSet student_all_information()
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_student_all_information", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //查找课程信息
        public DataSet class_select()
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_class_select", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //通过课程号查找课程信息
        public DataSet class_select_byEID(string EID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_class_select_byEID", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@EID", EID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //插入课程信息
        public void class_insert(string EID, string EName, double ETime)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_class_insert", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            mycomm.Parameters.Add(new SqlParameter("@EName", EName));
            mycomm.Parameters.Add(new SqlParameter("@ETime", ETime));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //删除课程信息
        public void class_delete(string EID)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_class_delete", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //修改课程信息
        public void class_update(string EID, string EName, double ETime)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_class_update", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            mycomm.Parameters.Add(new SqlParameter("@EName", EName));
            mycomm.Parameters.Add(new SqlParameter("@ETime", ETime));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //通过学好查看得到分数的课程信息
        public DataSet class_in(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_class_in", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //通过学好查看没有得到分数的课程信息
        public DataSet class_notin(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_class_notin", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //查询成绩表
        public DataSet score_select()
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_score_select", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //通过学好查询成绩表
        public DataSet score_select_bySID(string SID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_score_select_bySID", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@SID", SID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //通过课程号查看成绩
        public DataSet score_select_byEID(string EID)
        ...{
            try
            ...{
                SqlDataAdapter adapter = new SqlDataAdapter("proc_score_select_byEID", connstring);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@EID", EID));
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            catch (Exception Err)
            ...{
                return DataError(Err);
            }
        }
        //插入学生成绩
        public void score_insert(string SID, string EID, double EScore)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_score_insert", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            mycomm.Parameters.Add(new SqlParameter("@EScore", EScore));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //删除某个学生某课成绩
        public void score_delete(string SID, string EID)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_score_delete", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //通过学好删除成绩
        public void score_delete_bySID(string SID)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_score_delete_bySID", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //通过课程号删除成绩
        public void score_delete_byEID(string EID)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_score_delete_byEID", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //修改课程成绩
        public void score_update(string SID, string EID, double EScore)
        ...{
            SqlConnection myconn = new SqlConnection(connstring);
            myconn.Open();
            SqlTransaction mytran = myconn.BeginTransaction();
            SqlCommand mycomm = new SqlCommand("proc_score_update", myconn);
            mycomm.Transaction = mytran;
            mycomm.CommandType = CommandType.StoredProcedure;
            mycomm.Parameters.Add(new SqlParameter("@SID", SID));
            mycomm.Parameters.Add(new SqlParameter("@EID", EID));
            mycomm.Parameters.Add(new SqlParameter("@EScore", EScore));
            try
            ...{
                mycomm.ExecuteNonQuery();
                mytran.Commit();
            }
            catch (Exception Err)
            ...{
                mytran.Rollback();
                throw new Exception(Err.Message);
            }
            finally
            ...{
                myconn.Close();
            }
        }
        //错误信息表
        public DataSet DataError(Exception Err)
        ...{
            DataSet ErrDataSet = new DataSet("Errors");
            DataTable ErrDataTable = ErrDataSet.Tables.Add("Error");
            ErrDataTable.Columns.Add("Message");
            ErrDataTable.Rows.Add(new object[] ...{ Err.Message });
            return ErrDataSet;
        }
    }
}