scheduleda.cs

asp.net办公自动化实例导航——非常经典的OA源代码

using System;
using System.Data;
using System.Data.SqlClient;
using qminoa.Common.Data;

namespace qminoa.DA.Schedule
{
	public class ScheduleDA
	{
		private	String conn=System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");

		public ScheduleDA()
		{

		}

		public SqlCommand cpOtherManRead(int schid)
		{
			SqlConnection objconnection = new SqlConnection(conn);
			SqlCommand objcommand=new SqlCommand();
			objcommand.Connection =objconnection;  
			objcommand.CommandText = "cpOtherManRead";
			objcommand.CommandType = CommandType.StoredProcedure;
			objcommand.Parameters.Add(new SqlParameter("@schid", SqlDbType.Int));
			objcommand.Parameters["@schid"].Value=schid;
			return objcommand;
		}

		public SqlCommand SchRead(int EmpID,DateTime FDate)
		{
			SqlConnection objconnection = new SqlConnection(conn);
			SqlCommand objcommand=new SqlCommand(); 
			objcommand.Connection =objconnection;
			objcommand.CommandText = "cpMyAllSch";
			objcommand.CommandType = CommandType.StoredProcedure; 
			objcommand.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int));
			objcommand.Parameters["@EmpID"].Value=EmpID;
			objcommand.Parameters.Add(new SqlParameter("@FDate", SqlDbType.DateTime));
			objcommand.Parameters["@FDate"].Value=FDate;
			return objcommand;
		}

		public int IsMySchedule(int EmpID,int id)
		{
			int opt = -1;
			SqlCommand objcommand = new SqlCommand();
			SqlDataReader cpread;
			SqlConnection objconnection = new SqlConnection(conn);
			objcommand.Connection =objconnection;
			objcommand.CommandText = "cpIsMySchedule";
			objcommand.CommandType = CommandType.StoredProcedure;
			objcommand.Parameters.Add(new SqlParameter("@SchID", SqlDbType.Int));
			objcommand.Parameters["@SchID"].Value=id;
			objcommand.Connection.Open();
			cpread = objcommand.ExecuteReader();
			cpread.Read();
			if(EmpID == Int16.Parse(cpread.GetValue(0).ToString()))
			{
				opt = 0;
			}
			cpread.Close();
			objcommand.Connection.Close();
			return opt;
		}

		public DataSet cpMySch(int SchID)
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = "cpMySchByID";
			command.CommandType = CommandType.StoredProcedure;
			command.Connection = new SqlConnection(conn);
			SqlParameter param = new SqlParameter(paramChg(ScheduleData.SCHID_FIELD),SqlDbType.Int);
			param.Value = SchID;
			command.Parameters.Add(param);
			SqlDataAdapter commandAdp = new SqlDataAdapter();
			commandAdp.SelectCommand = command;
			DataSet data = new DataSet();
			try
			{
				commandAdp.Fill(data);
			}
			catch(Exception ex)
			{
				throw new ApplicationException(ex.ToString());
			}
			return data;
		}

		public DataSet cpScheduleTip(int empID)
		{
			SqlCommand command = new SqlCommand();
			command.CommandText = "cpScheduleTip";
			command.CommandType = CommandType.StoredProcedure;
			command.Connection = new SqlConnection(conn);
			SqlParameter param = new SqlParameter("@EmpID",SqlDbType.Int);
			param.Value = empID;
			command.Parameters.Add(param);
			SqlDataAdapter commandAdp = new SqlDataAdapter();
			commandAdp.SelectCommand = command;
			DataSet data = new DataSet();
			try
			{
				commandAdp.Fill(data);
			}
			catch(Exception ex)
			{
				throw new ApplicationException(ex.ToString());
			}
			return data;
		}

		public void cpSchDel(int schid)
		{
			SqlConnection objconnection = new SqlConnection(conn);
			SqlCommand objcommand=new SqlCommand(); 
			objcommand.Connection =objconnection;  
			objcommand.CommandText = "cpSchDel";
			objcommand.CommandType = CommandType.StoredProcedure;
 			objcommand.Parameters.Add(new SqlParameter("@schid", SqlDbType.Int));
			objcommand.Parameters["@schid"].Value=schid;
			objcommand.Connection.Open();
			objcommand.ExecuteNonQuery();
			objcommand.Connection.Close();
		}

		public void cpDelOtherMan(int schid)
		{
			SqlConnection objconnection = new SqlConnection(conn);
			SqlCommand objcommand=new SqlCommand(); 
			objcommand.Connection =objconnection;  
			objcommand.CommandText = "cpDelOtherMan";
			objcommand.CommandType = CommandType.StoredProcedure; 
			objcommand.Parameters.Add(new SqlParameter("@schid", SqlDbType.Int));
			objcommand.Parameters["@schid"].Value=schid;
			objcommand.Connection.Open();
			objcommand.ExecuteNonQuery();
			objcommand.Connection.Close();
		}

		public void cpOtherManAddUp(int schid,string otherman)
		{
			SqlConnection objconnection = new SqlConnection(conn);
			SqlCommand objcommand=new SqlCommand(); 
			objcommand.Connection =objconnection;  
			objcommand.CommandText = "cpOtherManAddUp";
			objcommand.CommandType = CommandType.StoredProcedure; 
			objcommand.Parameters.Add(new SqlParameter("@schid", SqlDbType.Int));
			objcommand.Parameters["@schid"].Value=schid;
			objcommand.Parameters.Add(new SqlParameter("@otherman", SqlDbType.VarChar));
			objcommand.Parameters["@otherman"].Value=otherman;
			objcommand.Connection.Open();
			objcommand.ExecuteNonQuery();
			objcommand.Connection.Close();
		}

		public int ScheduleAdd(ScheduleData schdata)
		{
			SqlCommand command = new SqlCommand();
			command.CommandType = CommandType.StoredProcedure;
			command.Connection = new SqlConnection(conn);
			command.CommandText = "cpScheduleAdd";
			SqlParameter paramTmp = new SqlParameter("@Identify",SqlDbType.Int);
			paramTmp.Direction = ParameterDirection.ReturnValue;
			command.Parameters.Add(paramTmp);
			command.Parameters.Add(paramChg(ScheduleData.EMPID_FIELD),SqlDbType.Int);
			command.Parameters.Add(paramChg(ScheduleData.SUBJECT_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.SCHTYPE_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.PLACE_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.CONTENT_FIELD),SqlDbType.Text);
			command.Parameters.Add(paramChg(ScheduleData.PRIVFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REPEATFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REQUENCATE_FIELD),SqlDbType.Char);
			command.Parameters.Add(paramChg(ScheduleData.UPTODATE_FIELD),SqlDbType.DateTime);
			command.Parameters.Add(paramChg(ScheduleData.REMINDFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REMINDDAY_FIELD),SqlDbType.SmallInt);
			command.Parameters.Add(paramChg(ScheduleData.FDATE_FIELD),SqlDbType.DateTime);
			command.Parameters.Add(paramChg(ScheduleData.TDATE_FIELD),SqlDbType.DateTime);
			command.Parameters[paramChg(ScheduleData.EMPID_FIELD)].SourceColumn = ScheduleData.EMPID_FIELD;
			command.Parameters[paramChg(ScheduleData.SUBJECT_FIELD)].SourceColumn = ScheduleData.SUBJECT_FIELD;
			command.Parameters[paramChg(ScheduleData.SCHTYPE_FIELD)].SourceColumn = ScheduleData.SCHTYPE_FIELD;
			command.Parameters[paramChg(ScheduleData.PLACE_FIELD)].SourceColumn = ScheduleData.PLACE_FIELD;
			command.Parameters[paramChg(ScheduleData.CONTENT_FIELD)].SourceColumn = ScheduleData.CONTENT_FIELD;
			command.Parameters[paramChg(ScheduleData.PRIVFLAG_FIELD)].SourceColumn = ScheduleData.PRIVFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REPEATFLAG_FIELD)].SourceColumn = ScheduleData.REPEATFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REQUENCATE_FIELD)].SourceColumn = ScheduleData.REQUENCATE_FIELD;
			command.Parameters[paramChg(ScheduleData.UPTODATE_FIELD)].SourceColumn = ScheduleData.UPTODATE_FIELD;
			command.Parameters[paramChg(ScheduleData.REMINDFLAG_FIELD)].SourceColumn = ScheduleData.REMINDFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REMINDDAY_FIELD)].SourceColumn = ScheduleData.REMINDDAY_FIELD;
			command.Parameters[paramChg(ScheduleData.FDATE_FIELD)].SourceColumn = ScheduleData.FDATE_FIELD;
			command.Parameters[paramChg(ScheduleData.TDATE_FIELD)].SourceColumn = ScheduleData.TDATE_FIELD;
			SqlDataAdapter commandAdp = new SqlDataAdapter();
			commandAdp.UpdateCommand = command;
			int result = commandAdp.Update(schdata,ScheduleData.SCHEDULE_TABLE);
			if(result>0)
				return (int)commandAdp.UpdateCommand.Parameters["@Identify"].Value;
			else
				return 0;
		}

		public bool ScheduleUpdate(ScheduleData schdata)
		{
			SqlCommand command = new SqlCommand();
			command.CommandType = CommandType.StoredProcedure;
			command.Connection = new SqlConnection(conn);
			command.CommandText = "cpScheduleUpdate";
			command.Parameters.Add(paramChg(ScheduleData.SCHID_FIELD),SqlDbType.Int);
			command.Parameters.Add(paramChg(ScheduleData.EMPID_FIELD),SqlDbType.Int);
			command.Parameters.Add(paramChg(ScheduleData.SUBJECT_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.SCHTYPE_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.PLACE_FIELD),SqlDbType.VarChar);
			command.Parameters.Add(paramChg(ScheduleData.CONTENT_FIELD),SqlDbType.Text);
			command.Parameters.Add(paramChg(ScheduleData.PRIVFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REPEATFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REQUENCATE_FIELD),SqlDbType.Char);
			command.Parameters.Add(paramChg(ScheduleData.UPTODATE_FIELD),SqlDbType.DateTime);
			command.Parameters.Add(paramChg(ScheduleData.REMINDFLAG_FIELD),SqlDbType.Bit);
			command.Parameters.Add(paramChg(ScheduleData.REMINDDAY_FIELD),SqlDbType.SmallInt);
			command.Parameters.Add(paramChg(ScheduleData.FDATE_FIELD),SqlDbType.DateTime);
			command.Parameters.Add(paramChg(ScheduleData.TDATE_FIELD),SqlDbType.DateTime);
			command.Parameters[paramChg(ScheduleData.SCHID_FIELD)].SourceColumn = ScheduleData.SCHID_FIELD;
			command.Parameters[paramChg(ScheduleData.EMPID_FIELD)].SourceColumn = ScheduleData.EMPID_FIELD;
			command.Parameters[paramChg(ScheduleData.SUBJECT_FIELD)].SourceColumn = ScheduleData.SUBJECT_FIELD;
			command.Parameters[paramChg(ScheduleData.SCHTYPE_FIELD)].SourceColumn = ScheduleData.SCHTYPE_FIELD;
			command.Parameters[paramChg(ScheduleData.PLACE_FIELD)].SourceColumn = ScheduleData.PLACE_FIELD;
			command.Parameters[paramChg(ScheduleData.CONTENT_FIELD)].SourceColumn = ScheduleData.CONTENT_FIELD;
			command.Parameters[paramChg(ScheduleData.PRIVFLAG_FIELD)].SourceColumn = ScheduleData.PRIVFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REPEATFLAG_FIELD)].SourceColumn = ScheduleData.REPEATFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REQUENCATE_FIELD)].SourceColumn = ScheduleData.REQUENCATE_FIELD;
			command.Parameters[paramChg(ScheduleData.UPTODATE_FIELD)].SourceColumn = ScheduleData.UPTODATE_FIELD;
			command.Parameters[paramChg(ScheduleData.REMINDFLAG_FIELD)].SourceColumn = ScheduleData.REMINDFLAG_FIELD;
			command.Parameters[paramChg(ScheduleData.REMINDDAY_FIELD)].SourceColumn = ScheduleData.REMINDDAY_FIELD;
			command.Parameters[paramChg(ScheduleData.FDATE_FIELD)].SourceColumn = ScheduleData.FDATE_FIELD;
			command.Parameters[paramChg(ScheduleData.TDATE_FIELD)].SourceColumn = ScheduleData.TDATE_FIELD;

			SqlDataAdapter commandAdp = new SqlDataAdapter();
			commandAdp.UpdateCommand = command;
			commandAdp.Update(schdata,ScheduleData.SCHEDULE_TABLE);
			if(schdata.HasErrors)
			{
				schdata.Tables[ScheduleData.SCHEDULE_TABLE].GetErrors()[0].ClearErrors();
				return false;
			}
			else
			{
				schdata.AcceptChanges();
				return true;
			}
		}

		private string paramChg(string str)
		{
			str = "@"+str;
			return str;
		}
		public DataSet dropPlacebind()
		{
			DataSet objdataset=new DataSet ();
			SqlDataAdapter objadapter=new SqlDataAdapter("cpDropPlaceBind",conn);
			objadapter.Fill(objdataset, "tablename");
			return objdataset;
		}

		public DataSet dropTypebind()
		{
			DataSet objdataset=new DataSet ();
			SqlDataAdapter objadapter=new SqlDataAdapter("cpDropTypeBind",conn);
			objadapter.Fill(objdataset, "tablename");
			return objdataset;
		}

		public DataTable cpdepempread(string Branch,string Dep,string EmpName)
		{
			SqlDataAdapter objadapter = new SqlDataAdapter();
			SqlCommand objcommand;
			DataTable objdataset = new DataTable();
			objadapter.SelectCommand = new SqlCommand(); 
			objadapter.SelectCommand.Connection = new SqlConnection(conn);
			objcommand = objadapter.SelectCommand;
			objcommand.CommandText = "cpDepEmpRead";
			objcommand.CommandType = CommandType.StoredProcedure;
			objcommand.Parameters.Add(new SqlParameter("@Branch", SqlDbType.VarChar));
			objcommand.Parameters["@Branch"].Value=Branch;
			objcommand.Parameters.Add(new SqlParameter("@Dep", SqlDbType.VarChar));
			objcommand.Parameters["@Dep"].Value=Dep;
			objcommand.Parameters.Add(new SqlParameter("@EmpName", SqlDbType.VarChar));
			objcommand.Parameters["@EmpName"].Value=EmpName;
			objadapter.Fill(objdataset);
			return objdataset;
		}

		public DataTable cpdepschread(DateTime fdate,int empid)
		{
			SqlDataAdapter objadapter = new SqlDataAdapter();
			SqlCommand objcommand;
			DataTable objdataset = new DataTable();
			objadapter.SelectCommand = new SqlCommand(); 
			objadapter.SelectCommand.Connection = new SqlConnection(conn);
			objcommand = objadapter.SelectCommand;
			objcommand.CommandText = "cpDepSchRead";
			objcommand.CommandType = CommandType.StoredProcedure;
			objcommand.Parameters.Add(new SqlParameter("@Fdate", SqlDbType.DateTime));
			objcommand.Parameters["@Fdate"].Value=fdate;
			objcommand.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int));
			objcommand.Parameters["@EmpID"].Value=empid;
			objadapter.Fill(objdataset);
			return objdataset;
		}
	}
}