coursedb.cs

教务管理系统 基于.net和sqlserver2000的教务管理系统

using System;
using System.Data;
using System.Data.SqlClient;

namespace Park.Common
{
	/// <summary>
	/// CourseDB 的摘要说明。
	/// </summary>
	public class CourseDB
	{
        public static bool AddCourseLevel(string levelname,string coursename)
        {
            SqlConnection myConnection = Utils.GetConnection();

            SqlCommand myCommand = new SqlCommand("CourseSystem_Add_CourseLevel", myConnection);

            // 
            myCommand.CommandType = CommandType.StoredProcedure;

            // 添加参数
            SqlParameter LevelName = new SqlParameter("@LevelName", SqlDbType.VarChar, 50);
            LevelName.Value = levelname ;
            myCommand.Parameters.Add(LevelName);

            // 添加参数
            SqlParameter CourseName = new SqlParameter("@CourseName", SqlDbType.VarChar, 50);
            CourseName.Value = coursename;
            myCommand.Parameters.Add(CourseName);

            // 添加参数
            SqlParameter retval = new SqlParameter("@retval", SqlDbType.Int);
            retval.Direction = ParameterDirection.Output;
            myCommand.Parameters.Add(retval);

            // 执行存储过程
            myConnection.Open();

            try
            {
                myCommand.ExecuteNonQuery();
            }
            catch (Exception)
            {
                myCommand.Dispose();
                myConnection.Dispose();
                return false;
            }

            myCommand.Dispose();
            myConnection.Dispose();

           
            return retval.Value.Equals(1);
           
        }
		public static bool AddCourse(string coursename,out string id)
		{
			id = "";

			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Add_Course", myConnection);

			// 
			myCommand.CommandType = CommandType.StoredProcedure;

			// 添加参数
			SqlParameter CourseName = new SqlParameter("@coursename", SqlDbType.VarChar, 30);
			CourseName.Value = coursename;
			myCommand.Parameters.Add(CourseName);

			// 添加参数
			SqlParameter ID = new SqlParameter("@id", SqlDbType.Int);
			ID.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(ID);

			// 添加参数
			SqlParameter retval = new SqlParameter("@retval", SqlDbType.Int);
			retval.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(retval);

			// 执行存储过程
			myConnection.Open();

			try
			{
				myCommand.ExecuteNonQuery();
			}
			catch(Exception )
			{
				myCommand.Dispose();
				myConnection.Dispose();
				return false;
			}	

			myCommand.Dispose();
			myConnection.Dispose();

			id=ID.Value.ToString();
			return  retval.Value.Equals(1);
		}	

		public static void DeleteCourse(string courseid)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Delete_Course", myConnection);

			// 
			myCommand.CommandType = CommandType.StoredProcedure;

			// 添加参数
			SqlParameter CourseId = new SqlParameter("@courseid", SqlDbType.NVarChar, 20);
			CourseId.Value = courseid;
			myCommand.Parameters.Add(CourseId);

			// 执行存储过程
			myConnection.Open();

			myCommand.ExecuteNonQuery();
			myCommand.Dispose();
			myConnection.Dispose();

			return ;
		}

		public static void DeleteTeacher(string courseid,string removeid)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Delete_Teacher", myConnection);

			// 
			myCommand.CommandType = CommandType.StoredProcedure;

			// 添加参数
			SqlParameter CourseID = new SqlParameter("@courseid", SqlDbType.Int);
			CourseID.Value = courseid;
			myCommand.Parameters.Add(CourseID);

			// 添加参数
			SqlParameter RemoveID = new SqlParameter("@teacherid", SqlDbType.NVarChar, 20);
			RemoveID.Value = removeid;
			myCommand.Parameters.Add(RemoveID);

			// 执行存储过程
			myConnection.Open();

			myCommand.ExecuteNonQuery();
			myCommand.Dispose();
			myConnection.Dispose();
		}

		public static void GetCourseStatus(string courseid,out string coursename,out string status)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Get_CourseStatus", myConnection);

			// 
			myCommand.CommandType = CommandType.StoredProcedure;

			// 添加参数
			SqlParameter CourseID = new SqlParameter("@courseid", SqlDbType.Int);
			CourseID.Value = courseid;
			myCommand.Parameters.Add(CourseID);

			// 添加参数
			SqlParameter CourseName = new SqlParameter("@coursename", SqlDbType.NVarChar, 20);
			CourseName.Direction =  ParameterDirection.Output;
			myCommand.Parameters.Add(CourseName);

			// 添加参数
			SqlParameter CourseStatus = new SqlParameter("@coursestatus", SqlDbType.NVarChar, 20);
			CourseStatus.Direction =  ParameterDirection.Output;
			myCommand.Parameters.Add(CourseStatus);

			// 执行存储过程
			myConnection.Open();

			myCommand.ExecuteNonQuery();
			status = CourseStatus.Value.ToString();
			coursename = CourseName.Value.ToString();
			myCommand.Dispose();
			myConnection.Dispose();
		}	

		public static void ChangeCourseStatus(string courseid)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Change_CourseStatus", myConnection);

			// 
			myCommand.CommandType = CommandType.StoredProcedure;

			// 添加参数
			SqlParameter CourseID = new SqlParameter("@courseid", SqlDbType.Int);
			CourseID.Value = courseid;
			myCommand.Parameters.Add(CourseID);


			// 执行存储过程
			myConnection.Open();

			myCommand.ExecuteNonQuery();
			myCommand.Dispose();
			myConnection.Dispose();
		}
		#region Status
		public static void GetTeacherStatus(out string message)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Get_TeacherStatus", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter Message = new SqlParameter("@message", SqlDbType.VarChar, 50);
			Message.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(Message);


			// Execute the command
			myConnection.Open();
			myCommand.ExecuteNonQuery();

			message =  Message.Value.ToString();
			myCommand.Dispose();
			myConnection.Dispose();
		}

		public static void ChangeTeacherStatus()
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Change_TeacherStatus", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Execute the command
			myConnection.Open();
			myCommand.ExecuteNonQuery();

			myCommand.Dispose();
			myConnection.Dispose();
		}
		public static void GetStudentStatus(out string message)
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Get_StudentStatus", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Add Parameters to SPROC
			SqlParameter Message = new SqlParameter("@message", SqlDbType.VarChar, 50);
			Message.Direction = ParameterDirection.Output;
			myCommand.Parameters.Add(Message);


			// Execute the command
			myConnection.Open();
			myCommand.ExecuteNonQuery();

			message =  Message.Value.ToString();
			myCommand.Dispose();
			myConnection.Dispose();
		}

		public static void ChangeStudentStatus()
		{
			SqlConnection myConnection=Utils.GetConnection();

			SqlCommand myCommand = new SqlCommand("CourseSystem_Change_StudentStatus", myConnection);

			// Mark the Command as a SPROC
			myCommand.CommandType = CommandType.StoredProcedure;

			// Execute the command
			myConnection.Open();
			myCommand.ExecuteNonQuery();

			myCommand.Dispose();
			myConnection.Dispose();
		}
		#endregion
	}
}