userdb.cs

这是一个管理系统的源码

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

			//返回 dr
			return dr;
		}

		public int AddRole(String sRoleName,int nRepairState,int nMarkTable)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddRole",myConnection);

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

			//创建访问数据库的参数
			SqlParameter parameterRoleName = new SqlParameter("@RoleName",SqlDbType.VarChar,50);
			parameterRoleName.Value = sRoleName;
			myCommand.Parameters.Add(parameterRoleName);

			SqlParameter parameterRepairState = new SqlParameter("@RepairState",SqlDbType.Int,4);
			parameterRepairState.Value = nRepairState;
			myCommand.Parameters.Add(parameterRepairState);

			SqlParameter parameterMarkTable = new SqlParameter("@MarkTable",SqlDbType.Int,4);
			parameterMarkTable.Value = nMarkTable;
			myCommand.Parameters.Add(parameterMarkTable);

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

			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)parameterRoleID.Value;
		}

		public void UpdateRole(int nRoleID,int nRepairState,int nMarkTable)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_UpdateRole",myConnection);

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

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

			SqlParameter parameterRepairState = new SqlParameter("@RepairState",SqlDbType.Int,4);
			parameterRepairState.Value = nRepairState;
			myCommand.Parameters.Add(parameterRepairState);

			SqlParameter parameterMarkTable = new SqlParameter("@MarkTable",SqlDbType.Int,4);
			parameterMarkTable.Value = nMarkTable;
			myCommand.Parameters.Add(parameterMarkTable);

			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 DeleteRole(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteRole",myConnection);

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

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

			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 UserRoleDB
	{
		public SqlDataReader GetRoleByUser(int nUserID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetRoleByUser",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 void AddUserRole(int nUserID,int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddUserRole",myConnection);

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

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

			SqlParameter parameterRoleID = new SqlParameter("@RoleID",SqlDbType.Int,4);
			parameterRoleID.Value = nRoleID;
			myCommand.Parameters.Add(parameterRoleID);

			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 DeleteUserRole(int nUserID,int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteUserRole",myConnection);

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

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

			SqlParameter parameterRoleID = new SqlParameter("@RoleID",SqlDbType.Int,4);
			parameterRoleID.Value = nRoleID;
			myCommand.Parameters.Add(parameterRoleID);

			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 class RoleDirectionDB
	{
		public SqlDataReader GetDirCountByRole(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetDirCountByRole",myConnection);

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

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

			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 GetDirecrtionByRole(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetDirectionByRole",myConnection);

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

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

			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 void AddRoleDirection(int nRoleID,int nDirectionID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddRoleDirection",myConnection);

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

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

			SqlParameter parameterDirectionID = new SqlParameter("@DirectionID",SqlDbType.Int,4);
			parameterDirectionID.Value = nDirectionID;
			myCommand.Parameters.Add(parameterDirectionID);

			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 DeleteRoleDirection(int nRoleID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_DeleteRoleDirection",myConnection);

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

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

			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();
				}
			}
		}
	}
}