employeedb.cs

这是一个管理系统的源码

using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
//该源码下载自www.51aspx.com(５1ａsｐｘ．ｃｏｍ)

namespace Service
{
	/// <summary>
	/// Summary description for EmployeeDB.
	/// </summary>
	public class EmployeeDB
	{
		public SqlDataReader GetEmployees()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetEmployees",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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}

		public DataSet GetEmployeeDS()
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlDataAdapter da = new SqlDataAdapter("Pr_GetEmployees",myConnection);

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

			DataSet ds = new DataSet();

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

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

			//返回 ds
			return ds;
		}
		
		public SqlDataReader GetSingleEmployee(int nEmployeeID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetSingleEmployee",myConnection);

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

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

			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("10002",ex.Message,ex);
			}

			//返回 dr
			return dr;
		}	
	
		public int AddEmployee(String sEmployeeName)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_AddEmployee",myConnection);

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

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

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

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

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

			return (int)parameterID.Value;
		}

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

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

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

			SqlParameter parameterEmployeeName = new SqlParameter("@EmployeeName",SqlDbType.VarChar,50);
			parameterEmployeeName.Value = sEmployeeName;
			myCommand.Parameters.Add(parameterEmployeeName);

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

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

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

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

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

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

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

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

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

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

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

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

	public class EmployeeQuestionDB
	{
		public SqlDataReader GetEmployeeByQuestion(int nQuestionID)
		{
			//定义数据库的Connection and Command 
			SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
			SqlCommand myCommand = new SqlCommand("Pr_GetEmployeeByQuestion",myConnection);

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

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

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

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

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

			SqlParameter parameterEmployeeID = new SqlParameter("@EmployeeID",SqlDbType.Int,4);
			parameterEmployeeID.Value = nEmployeeID;
			myCommand.Parameters.Add(parameterEmployeeID);

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

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

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

			SqlParameter parameterEmployeeID = new SqlParameter("@EmployeeID",SqlDbType.Int,4);
			parameterEmployeeID.Value = nEmployeeID;
			myCommand.Parameters.Add(parameterEmployeeID);

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

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

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

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