workservice.asmx.cs

ASP.NET的一些开发实例,有论坛管理系统等

			// oleDbInsertCommand1
			// 
			this.oleDbInsertCommand1.CommandText = "INSERT INTO 工作记录表(任务编号, 执行人, 任务内容, 填写时间, 发布人, 发布人是否浏览, 发布人填写时间, 发布人填写内容, 执行人是否浏览)" +
				" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
			this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("任务编号", System.Data.OleDb.OleDbType.VarChar, 20, "任务编号"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("执行人", System.Data.OleDb.OleDbType.VarChar, 25, "执行人"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("任务内容", System.Data.OleDb.OleDbType.VarChar, 2000, "任务内容"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, "填写时间"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人", System.Data.OleDb.OleDbType.VarChar, 25, "发布人"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人是否浏览", System.Data.OleDb.OleDbType.VarChar, 100, "发布人是否浏览"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, "发布人填写时间"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人填写内容", System.Data.OleDb.OleDbType.VarChar, 2000, "发布人填写内容"));
			this.oleDbInsertCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("执行人是否浏览", System.Data.OleDb.OleDbType.VarChar, 100, "执行人是否浏览"));
			// 
			// oleDbUpdateCommand1
			// 
			this.oleDbUpdateCommand1.CommandText = @"UPDATE 工作记录表 SET 任务编号 = ?, 执行人 = ?, 任务内容 = ?, 填写时间 = ?, 发布人 = ?, 发布人是否浏览 = ?, 发布人填写时间 = ?, 发布人填写内容 = ?, 执行人是否浏览 = ? WHERE (任务编号 = ?) AND (填写时间 = ?) AND (执行人 = ?)";
			this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("任务编号", System.Data.OleDb.OleDbType.VarChar, 20, "任务编号"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("执行人", System.Data.OleDb.OleDbType.VarChar, 25, "执行人"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("任务内容", System.Data.OleDb.OleDbType.VarChar, 2000, "任务内容"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, "填写时间"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人", System.Data.OleDb.OleDbType.VarChar, 25, "发布人"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人是否浏览", System.Data.OleDb.OleDbType.VarChar, 100, "发布人是否浏览"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, "发布人填写时间"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("发布人填写内容", System.Data.OleDb.OleDbType.VarChar, 2000, "发布人填写内容"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("执行人是否浏览", System.Data.OleDb.OleDbType.VarChar, 100, "执行人是否浏览"));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_任务编号", System.Data.OleDb.OleDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "任务编号", System.Data.DataRowVersion.Original, null));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "填写时间", System.Data.DataRowVersion.Original, null));
			this.oleDbUpdateCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_执行人", System.Data.OleDb.OleDbType.VarChar, 25, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "执行人", System.Data.DataRowVersion.Original, null));
			// 
			// oleDbDeleteCommand1
			// 
			this.oleDbDeleteCommand1.CommandText = @"DELETE FROM 工作记录表 WHERE (任务编号 = ?) AND (填写时间 = ?) AND (执行人 = ?) ";
			this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
			this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_任务编号", System.Data.OleDb.OleDbType.VarChar, 20, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "任务编号", System.Data.DataRowVersion.Original, null));
			this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_填写时间", System.Data.OleDb.OleDbType.DBTimeStamp, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "填写时间", System.Data.DataRowVersion.Original, null));
			this.oleDbDeleteCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_执行人", System.Data.OleDb.OleDbType.VarChar, 25, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "执行人", System.Data.DataRowVersion.Original, null));
			// 
			// dataSet21
			// 
			this.dataSet21.DataSetName = "DataSet2";
			this.dataSet21.Locale = new System.Globalization.CultureInfo("zh-CN");
			this.dataSet21.Namespace = "http://www.tempuri.org/DataSet2.xsd";
			((System.ComponentModel.ISupportInitialize)(this.dataSet21)).EndInit();

		}

		/// <summary>
		/// 清理所有正在使用的资源。
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if(disposing && components != null)
			{
				components.Dispose();
			}
			base.Dispose(disposing);		
		}
		
		#endregion

		[WebMethod]
		public bool AddWork(WorkGroupService.task.work work)
		{
			try
			{
				this.oleDbConnection1.Open();
				this.oleDbInsertCommand1.Parameters[0].Value=work.tNO;
				this.oleDbInsertCommand1.Parameters[1].Value=work.tExecutor;
				this.oleDbInsertCommand1.Parameters[2].Value=work.exContent;
				this.oleDbInsertCommand1.Parameters[3].Value=work.exDate;
				this.oleDbInsertCommand1.Parameters[4].Value=work.tPromulgator;
				this.oleDbInsertCommand1.Parameters[5].Value=work.prScan;
				if(work.prDate.Equals(Convert.ToDateTime("0001-1-1")))
					this.oleDbInsertCommand1.Parameters[6].Value=System.DBNull.Value;
				else
					this.oleDbInsertCommand1.Parameters[6].Value=work.prDate;
				if(!System.DBNull.Equals(work.prContent,System.DBNull.Value))
					this.oleDbInsertCommand1.Parameters[7].Value=work.prContent;
				this.oleDbInsertCommand1.Parameters[8].Value=work.exScan;
				this.oleDbInsertCommand1.ExecuteNonQuery();
				return true;
			}
			catch(Exception e)
			{
				bs.Write("AddWork(WorkGroupService.task.work work)");
				bs.Write(e.Message);
				return false;
			}
			finally
			{
				this.oleDbConnection1.Close();
			}

		}
		

		[WebMethod] 
		public bool DeleteWork(WorkGroupService.task.work work)
		{
			try
			{
				this.oleDbConnection1.Open();
				this.oleDbDeleteCommand1.Parameters[0].Value=work.tNO;
				this.oleDbDeleteCommand1.Parameters[1].Value=work.exDate;
				this.oleDbDeleteCommand1.Parameters[2].Value=work.tExecutor;
				this.oleDbDeleteCommand1.ExecuteNonQuery();
				return true;
			}
			catch(Exception e)
			{
				bs.Write("DeleteWork(WorkGroupService.task.work work)");
				bs.Write(e.Message);
				return false;
			}
			finally
			{
				this.oleDbConnection1.Close();
			}
		}


		[WebMethod]
		public bool ModWork(WorkGroupService.task.work work)
		{
			try
			{
				this.oleDbConnection1.Open();
				this.oleDbUpdateCommand1.Parameters[0].Value=work.tNO;
				this.oleDbUpdateCommand1.Parameters[1].Value=work.tExecutor;
				this.oleDbUpdateCommand1.Parameters[2].Value=work.exContent;
				this.oleDbUpdateCommand1.Parameters[3].Value=work.exDate;
				this.oleDbUpdateCommand1.Parameters[4].Value=work.tPromulgator;
				this.oleDbUpdateCommand1.Parameters[5].Value=work.prScan;
				if(work.prDate.Equals(Convert.ToDateTime("1900-1-1")))
					this.oleDbUpdateCommand1.Parameters[6].Value=System.DBNull.Value;
				else
					this.oleDbUpdateCommand1.Parameters[6].Value=work.prDate;
				this.oleDbUpdateCommand1.Parameters[7].Value=work.prContent;
				this.oleDbUpdateCommand1.Parameters[8].Value=work.exScan;
				this.oleDbUpdateCommand1.Parameters[9].Value=work.tNO;
				this.oleDbUpdateCommand1.Parameters[10].Value=work.exDate;
				this.oleDbUpdateCommand1.Parameters[11].Value=work.tExecutor;
				this.oleDbUpdateCommand1.ExecuteNonQuery();
				return true;
			}
			catch(Exception e)
			{
				bs.Write("ModWork(WorkGroupService.task.work work)");
				bs.Write(e.Message);
				return false;
			}
			finally
			{
				this.oleDbConnection1.Close();
			}

		}
		
		[WebMethod]
		public WorkGroupService.task.work GetWork(string[] pks)
		{
			WorkGroupService.task.work GetWork=new WorkGroupService.task.work();
			try
			{
				string sqlwhere=string.Format("任务编号='{0}' and 执行人='{1}' and 填写时间 {2}",pks[0].Trim(),pks[1].Trim(),this.DateFormat(pks[2].Trim()));
				string sql=this.selectSql(sqlwhere);
				DataSet ds=this.bs.CreateDataSetFromSql(sql);
				GetWork.tNO=ds.Tables[0].Rows[0]["任务编号"].ToString();
				GetWork.tExecutor=ds.Tables[0].Rows[0]["执行人"].ToString();
				GetWork.exContent=ds.Tables[0].Rows[0]["任务内容"].ToString();
				GetWork.exDate=DateTime.Parse(ds.Tables[0].Rows[0]["填写时间"].ToString());
				GetWork.tPromulgator=ds.Tables[0].Rows[0]["发布人"].ToString();
				GetWork.prScan=ds.Tables[0].Rows[0]["发布人是否浏览"].ToString();
				GetWork.prDate=DateTime.Parse(ds.Tables[0].Rows[0]["发布人填写时间"].ToString());
				GetWork.prContent=ds.Tables[0].Rows[0]["发布人填写内容"].ToString();
				GetWork.exScan=ds.Tables[0].Rows[0]["执行人是否浏览"].ToString();
			}
			catch(Exception ex)
			{
				bs.Write("WorkGroupService.task.work GetWork(string[] pks)");
				bs.Write(ex.Message);
			}
			return GetWork;
		}

		[WebMethod]
		public string selectSql(string sqlwhere)
		{
			if(sqlwhere.Trim()!=string.Empty)
				this.oleDbSelectCommand1.CommandText=string.Format("{0} where {1}",this.oleDbSelectCommand1.CommandText,sqlwhere);
			return this.oleDbSelectCommand1.CommandText;
		}

		[WebMethod(MessageName="selectSqlbyOrder")]
		public string selectSql(string sqlwhere,string[] OrderType)
		{
			if(sqlwhere.Trim()!=string.Empty)
				this.oleDbSelectCommand1.CommandText=string.Format("{0} where {1} {2}",this.oleDbSelectCommand1.CommandText,sqlwhere,this.bs.sqlorder(OrderType));
			return this.oleDbSelectCommand1.CommandText;
		}

	
		private string DateFormat(string date)
		{
			DateTime dt=DateTime.Parse(date);
			DateTime dtAddDay=dt.AddDays(1);
			date=string.Format("between TO_DATE('{0}','YYYY-MM-DD') and TO_DATE('{1}','YYYY-MM-DD')",date,dtAddDay.ToString("yyyy-MM-dd"));
			return date;
		}
	}
}