tabdb.cs

计算机学院网站及管理系统

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

namespace ComputerWeb
{
	/// <summary>
	/// Summary description for TabDB.
	/// </summary>
	public class TabDB
	{
		public SqlDataReader GetTabByUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetTabByUser",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter parameterUserID = new SqlParameter("@UserID",SqlDbType.Int,4);
			parameterUserID.Value = nUserID;
			myCommand.Parameters.Add(parameterUserID);

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public SqlDataReader GetTabs()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetTabs",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public SqlDataReader GetSingleTab(int nTabID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleTab",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//添加储存过程的参数
			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Value = nTabID;
			myCommand.Parameters.Add(parameterTabID);

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public int AddTab(String sTabName,int nTabOrder,String sDirectUrl)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddTab",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数	
			SqlParameter parameterTabName = new SqlParameter("@TabName",SqlDbType.VarChar,32);
			parameterTabName.Value = sTabName;
			myCommand.Parameters.Add(parameterTabName);

			SqlParameter parameterTabOrder = new SqlParameter("@TabOrder",SqlDbType.Int,4);
			parameterTabOrder.Value = nTabOrder;
			myCommand.Parameters.Add(parameterTabOrder);

			SqlParameter parameterDirectUrl = new SqlParameter("@DirectUrl",SqlDbType.VarChar);
			parameterDirectUrl.Value = sDirectUrl;
			myCommand.Parameters.Add(parameterDirectUrl);

			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Direction = ParameterDirection.ReturnValue;
			myCommand.Parameters.Add(parameterTabID);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}

			return (int)parameterTabID.Value;
		}

		public void UpdateTabOrder(int nTabID,String sMoveFlag)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateTabOrder",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Value = nTabID;
			myCommand.Parameters.Add(parameterTabID);

			SqlParameter parameterMoveFlag = new SqlParameter("@MoveFlag",SqlDbType.VarChar,20);
			parameterMoveFlag.Value = sMoveFlag;
			myCommand.Parameters.Add(parameterMoveFlag);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

		public void UpdateTabName(int nTabID,String sTabName)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateTabName",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Value = nTabID;
			myCommand.Parameters.Add(parameterTabID);

			SqlParameter parameterTabName = new SqlParameter("@TabName",SqlDbType.VarChar,32);
			parameterTabName.Value = sTabName;
			myCommand.Parameters.Add(parameterTabName);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ex)
			{
				throw new MyException("10001","数据库连接失败!",ex);
			}

			try 
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception ex)
			{
				throw new MyException("10001",ex.Message,ex);
			}
			finally 
			{
				if (myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}

		public void DeleteTab(int nTabID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteTab",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			//创建访问数据库的参数
			SqlParameter parameterTabID = new SqlParameter("@TabID",SqlDbType.Int,4);
			parameterTabID.Value = nTabID;
			myCommand.Parameters.Add(parameterTabID);

			try
			{
				//打开数据库的连接
				myConnection.Open();
			}
			catch(Exception ec)
			{
				throw new MyException("10001","数据库连接失败!",ec);
			}

			try
			{
				//执行数据库的存储过程（访问数据库）
				myCommand.ExecuteNonQuery();
			}
			catch(Exception er)
			{
				throw new MyException("10001",er.Message,er);
			}	
			finally
			{
				if(myConnection.State == ConnectionState.Open)
				{
					//关闭数据库的连接
					myConnection.Close();
				}
			}
		}
	}

	public class RoleDB
	{
		public SqlDataReader GetRoles()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetRoles",myConnection);

			//定义访问数据库的方式为存储过程
			myCommand.CommandType = CommandType.StoredProcedure;

			SqlDataReader dr = null;

			try
			{
				//打开数据库的连接
				myConnection.Open();