com_systemdd.asmx.cs

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

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.IO;
using System.Xml;

namespace ERPWebService.BdStudio
{
	/// <summary>
	/// COM_SystemDd 的摘要说明。
	/// </summary>
	public class COM_SystemDd : System.Web.Services.WebService
	{
		protected sysbase.COM_BASE COM_BASE = new sysbase.COM_BASE();
		protected sysbase.COMSoft COMSoft = new sysbase.COMSoft();
		protected SqlConnection SqlConn;

		public COM_SystemDd()
		{
			//CODEGEN：该调用是 ASP.NET Web 服务设计器所必需的
			InitializeComponent();
		}

		#region Component Designer generated code
		
		//Web 服务设计器所必需的
		private IContainer components = null;
				
		/// <summary>
		/// 设计器支持所需的方法 - 不要使用代码编辑器修改
		/// 此方法的内容。
		/// </summary>
		private void InitializeComponent()
		{
			this.SqlConn = new SqlConnection((string)Application["db_connstring"]);
		}

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

		// WEB 服务示例
		// HelloWorld() 示例服务返回字符串 Hello World
		// 若要生成，请取消注释下列行，然后保存并生成项目
		// 若要测试此 Web 服务，请按 F5 键

//		[WebMethod]
//		public string HelloWorld()
//		{
//			return "Hello World";
//		}

		[WebMethod]
		public DataSet DsMainTableListForAll(string[] FieldValue)
		{
			string[] str_FieldName = {"t_name","t_title","p_num"};
			string[] str_FieldType = {"string","string","string"};
			string str_SearchType = "like";
			DataSet ds = new DataSet();
			string str_Sql = string.Format("select * from system_dd ");
			string strWhere = this.COM_BASE.CreateSql(FieldValue,str_FieldName,str_SearchType,str_FieldType);
			if (strWhere.Trim() != string.Empty && strWhere != null )
			{
				strWhere+=" and t_type<>'3'";
				str_Sql = string.Format(" {0} where {1} ",str_Sql,strWhere);
			}
			else if (strWhere.Trim() == string.Empty)
			{
				str_Sql = string.Format(" {0} where t_type <> '3'",str_Sql);
			}
			
			ds = this.COM_BASE.CreateDataSetFromSql(str_Sql);			
			return ds;
		}

		[WebMethod]
		public DataSet DsMainFieldListForAll(string TableNameId)
		{
//			DataSet ds = new DataSet();
//			string strSql = string.Format(" select t_name  from system_dd where t_mun = '{0}'",TableNameId);
//			ds = this.COM_BASE.CreateDataSetFromSql(strSql);
//			if (ds.Tables[0].Rows[0]["t_name"].ToString().Trim() != string.Empty && ds.Tables[0].Rows[0]["t_name"].ToString() != null)
//			{
//				strSql = string.Format(" select * from system_dd where t_tablename = '{0}' ",ds.Tables[0].Rows[0]["t_name"].ToString().Trim());
//			}
//			ds = this.COM_BASE.CreateDataSetFromSql(strSql);
			DataSet ds = new DataSet();
			string strSql = string.Format(" select * from system_dd where t_tablename = '{0}'",TableNameId);
			ds = this.COM_BASE.CreateDataSetFromSql(strSql);
			return ds;
 		}

		[WebMethod]
		public void DsMainListForDataSet(string[] FieldValue,ref DataSet ds)
		{
			string[] str_FieldName = {"t_num"};
			string[] str_FieldType = {"int"};
			string str_SearchType = "=";
			string str_Sql = string.Format(" {0} ",this.COM_BASE.CreateSql(FieldValue,str_FieldName,str_SearchType,str_FieldType));
			ds.Tables[0].Select(str_Sql);	
		}

		[WebMethod]
		public DataSet DsSelectSingleData(string[] FieldValue,string SearchType,string DataType)
		{
			string[] str_FieldName = {"t_num"};
			string[] str_FieldType = {"int"};
			string str_SearchType = "=";
			DataSet ds = new DataSet();
			string str_Sql = string.Empty;
			switch (DataType)
			{
				case "0":
					//str_Sql = string.Format("select t_num,p_num,t_name,t_title,t_type,t_creatsql,remark from system_dd");
					str_Sql = string.Format("select * from system_dd");
					break;				
				case "1":
					//str_Sql = string.Format("select t_num,t_name,t_title,t_dtype,t_size,t_decimal,t_pk,remark from system_dd");
					str_Sql = string.Format("select * from system_dd");
					break;
			}			
			string str_Sql1 = string.Format("select t_num from system_dd order by t_num");
			switch (SearchType)
			{
				case "0":
					str_Sql = string.Format(" {0} where {1} ",str_Sql,this.COM_BASE.CreateSql(FieldValue,str_FieldName,str_SearchType,str_FieldType));
					ds = this.COM_BASE.CreateDataSetFromSql(str_Sql);
					//sReturn = string.Empty;
					break;
				case "1":
					DataSet dsPrev = this.COM_BASE.CreateDataSetFromSql(str_Sql1);
					string str_Prev = string.Empty;
					for (int i=0;i<dsPrev.Tables[0].Rows.Count;i++)
					{
						if (FieldValue[0] == dsPrev.Tables[0].Rows[i]["t_num"].ToString() && i > 0)
						{
							str_Prev = dsPrev.Tables[0].Rows[i-1]["t_num"].ToString();
							break;
						}
						else if (FieldValue[0] == dsPrev.Tables[0].Rows[i]["t_num"].ToString())
						{
							//sReturn = "没有上一条!";
						}
					}
					string[] FieldValuePrev = {str_Prev};
					if( FieldValuePrev != null)
					{
						str_Sql = string.Format(" {0} where {1} ",str_Sql,this.COM_BASE.CreateSql(FieldValuePrev,str_FieldName,str_SearchType,str_FieldType));
						ds = this.COM_BASE.CreateDataSetFromSql(str_Sql);
					}					
					break;
				case "2":
					DataSet dsNext = this.COM_BASE.CreateDataSetFromSql(str_Sql1);
					string str_Next = string.Empty;
					for (int i=0;i<dsNext.Tables[0].Rows.Count;i++)
					{
						if (FieldValue[0] == dsNext.Tables[0].Rows[i]["t_num"].ToString() && (i+1) < dsNext.Tables[0].Rows.Count)
						{
							str_Next = dsNext.Tables[0].Rows[i+1]["t_num"].ToString();
							break;
						}
						else if (FieldValue[0] == dsNext.Tables[0].Rows[i]["t_num"].ToString())
						{
							//sReturn = "没有下一条!";
						}

					}
					string[] FieldValueNext = {str_Next};
					if( FieldValueNext != null)
					{
						str_Sql = string.Format(" {0} where {1} ",str_Sql,this.COM_BASE.CreateSql(FieldValueNext,str_FieldName,str_SearchType,str_FieldType));
						ds = this.COM_BASE.CreateDataSetFromSql(str_Sql);
					}		
					break;
			}//switch						

			return ds;
		}


		[WebMethod]
		public void InsertData(DataSet ds,string InsertType,ref string sReturn)
		{
			SqlCommand mySqlCommand = new SqlCommand();
			SqlTransaction myTrans;
			string SqlString = string.Empty;

			this.SqlConn.Open();//启动连接
			myTrans = this.SqlConn.BeginTransaction();//建立事务
			mySqlCommand.Connection = this.SqlConn;//建立sql命令连接
			mySqlCommand.Transaction = myTrans;//绑定事务

			string str_TableName = "system_dd";
			string str_t_num = string.Empty;
			DataTable dt = ds.Tables[0];

			foreach(DataRow row in ds.Tables[0].Rows)
			{
				if(row[0].ToString()=="自动生成")
				{
					ds.Tables[0].Columns.Remove("t_num");
				}
			}
			int nErr;
			try
			{
				nErr= this.COM_BASE.InsertTableBase(ref mySqlCommand,ref SqlConn,ref myTrans,str_TableName,dt,ref SqlString);
			}
			catch (Exception e)
			{
				nErr = 1;
			}			
			
			if (nErr == 1)
			{
				sReturn =  "增加失败";
			}
			else 
			{
				myTrans.Commit();
				this.SqlConn.Close();
				switch (InsertType)
				{
					case "0":
						sReturn = "增加成功";
						break;
					case "1":
						sReturn = "增加成功";
						break;
					case "2":
						string strSql = string.Format("select t_num from system_dd ");
						str_t_num = this.COM_BASE.GetID(strSql);
						sReturn = str_t_num;
						break;
				}
			}

		}

		[WebMethod]
		public void EditData(DataSet ds,string EditType,ref string sReturn)
		{
			SqlCommand mySqlCommand = new SqlCommand();
			SqlTransaction myTrans;
			string SqlString = string.Empty;

			this.SqlConn.Open();//启动连接
			myTrans = this.SqlConn.BeginTransaction();//建立事务
			mySqlCommand.Connection = this.SqlConn;//建立sql命令连接
			mySqlCommand.Transaction = myTrans;//绑定事务

			string str_TableName = "system_dd";
			string s_pk = "t_num";
			DataTable dt = ds.Tables[0];

			string str_t_num = string.Empty;
			int nErr;
			try
			{
				str_t_num = dt.Rows[0]["t_num"].ToString();
				//dt.Columns.Remove(s_pk);
				nErr = this.COM_BASE.UpdateTableBase(ref mySqlCommand,ref SqlConn,ref myTrans,str_TableName,dt,ref SqlString,ref s_pk);
				
			}
			catch (Exception e)
			{
				nErr = 1;
			}			
			if (nErr == 1)
			{
				sReturn = "修改失败";
			}
			else 
			{
				switch (EditType)
				{
					case "0":
						sReturn = "修改成功";
						break;
					case "1":
						sReturn = "修改成功";
						break;
					case "2":
						sReturn = str_t_num;
						break;
				}
				
			}
			if(nErr != 1)
			{
				myTrans.Commit();
				this.SqlConn.Close();
			}
			
		}

		[WebMethod]
		public void DeleteData(string sPkValue,string DeleteType,ref string sReturn)
		{
			SqlCommand mySqlCommand = new SqlCommand();
			SqlTransaction myTrans;
			string SqlString = string.Empty;

			this.SqlConn.Open();//启动连接
			myTrans = this.SqlConn.BeginTransaction();//建立事务
			mySqlCommand.Connection = this.SqlConn;//建立sql命令连接
			mySqlCommand.Transaction = myTrans;//绑定事务

			string str_TableName = "system_dd";
			switch (DeleteType)
			{
				case "1":
					string strSql = string.Format(" select t_name from system_dd where t_num = {0} ",sPkValue);
					DataSet ds=this.COM_BASE.CreateDataSetFromSql(strSql);
					if (ds.Tables[0].Rows[0]["t_name"].ToString().Trim() != string.Empty && ds.Tables[0].Rows[0]["t_name"].ToString() != null)
					{
						SqlString = string.Format(" t_tablename = '{0}' ",ds.Tables[0].Rows[0]["t_name"].ToString().Trim());
					}
					if (SqlString != string.Empty)
					{
						SqlString = string.Format(" t_num = {0} or {1} ",sPkValue,SqlString);
					}
					break;
				case "2":
					SqlString = string.Format(" t_num = {0} ",sPkValue);
					break;
			}
			int nErr = this.COM_BASE.DeleteTableBase(ref mySqlCommand,ref SqlConn,ref myTrans,str_TableName,ref SqlString);
			if (nErr == 1)
			{
				sReturn = "删除失败";
			}
			else
			{
				sReturn = "删除成功";
			}
			if(nErr != 1)
			{
				myTrans.Commit();
				this.SqlConn.Close();
			}
		}

		[WebMethod]
		public void CopyData(string[] CopyData,string CopyType,ref string sReturn)
		{
			string strSql = string.Empty;
			DataSet ds;
			DataSet dsData;
			switch (CopyType)
			{
				case "0":
					strSql = string.Format(" select t_name from system_dd where p_num = '{0}'",CopyData[0].ToString());
					ds = this.COM_BASE.CreateDataSetFromSql(strSql);
					string[] strTableName = CopyData[2].Split(new char[]{','});
					string strSqlWhere = string.Empty;
					foreach (DataRow dr in ds.Tables[0].Rows)
					{
						foreach( string TableName in strTableName)
						{
							string str_t_name = string.Format("'{0}'",dr["t_name"].ToString());
							if(str_t_name == TableName)
							{
								strSqlWhere = string.Format(" or t_tablename = {0} {1} ",TableName,strSqlWhere);
							}
						}
					}
					if ( strSqlWhere.Trim() != string.Empty )
					{
						strSql = string.Format(" {0} {1} ",strSql,strSqlWhere);
						dsData = this.COM_BASE.CreateDataSetFromSql(strSql);
						foreach(DataRow row in dsData.Tables[0].Rows)
						{
							dsData.Tables[0].Columns.Remove("t_num");
							if (row["p_num"].ToString() == CopyType[0].ToString())
							{
								row["p_num"] = CopyType[1].ToString();
							}
						}
						this.InsertData(dsData,"0",ref sReturn);
					}

					break;
				case "1":
					strSql = string.Format(" select * from system_dd where t_mun = '{0}'",CopyData[0].ToString());
					ds = this.COM_BASE.CreateDataSetFromSql(strSql);
					if (ds.Tables[0].Rows[0]["t_name"].ToString().Trim() != string.Empty && ds.Tables[0].Rows[0]["t_name"].ToString() != null)
					{
						strSql = string.Format("{0} or t_tablename = '{1}' ",strSql,ds.Tables[0].Rows[0]["t_name"].ToString().Trim());
					}
					dsData = this.COM_BASE.CreateDataSetFromSql(strSql);
					foreach(DataRow row in dsData.Tables[0].Rows)
					{
						dsData.Tables[0].Columns.Remove("t_num");
						if (row["t_tablename"].ToString() == ds.Tables[0].Rows[0]["t_name"].ToString().Trim())
						{
							row["t_tablename"] = CopyType[1].ToString();
						}
						if (row["t_mun"].ToString() == CopyType[1].ToString())
						{
							row["t_name"] = CopyType[0].ToString();
						}
					}
					this.InsertData(dsData,"0",ref sReturn);
					break;
			}
		}

		[WebMethod]
		public void PutSingleTable(string SourceTableId)
		{
			SqlCommand mySqlCommand = new SqlCommand();
			SqlTransaction myTrans;

			this.SqlConn.Open();//启动连接
			myTrans = this.SqlConn.BeginTransaction();//建立事务
			mySqlCommand.Connection = this.SqlConn;//建立sql命令连接
			mySqlCommand.Transaction = myTrans;//绑定事务

			string TableName = string.Empty;
			string type = string.Empty;

			DataSet ds = new DataSet();
			string strSql = string.Format(" select t_name,t_type,t_creatsql from system_dd where t_num = '{0}'",SourceTableId);
			ds = this.COM_BASE.CreateDataSetFromSql(strSql);
			if (ds.Tables[0].Rows[0]["t_type"].ToString() == "1")
			{
				TableName = ds.Tables[0].Rows[0]["t_name"].ToString();
				type = "1";
				DataSet dsData = this.DsMainFieldListForAll(TableName);
				strSql = this.COM_BASE.CreateTableSql(TableName,dsData);				
			}
			if (ds.Tables[0].Rows[0]["t_type"].ToString() == "2")
			{
				TableName = ds.Tables[0].Rows[0]["t_name"].ToString();
				type = "2";
				strSql = ds.Tables[0].Rows[0]["t_creatsql"].ToString();
			}
            this.COM_BASE.CreateTable(ref mySqlCommand,ref SqlConn,ref myTrans,ref TableName,ref type,ref strSql);
		}
		
		[WebMethod]
		public void PutProjectDataBase(string SourceProjectId)
		{
			string strSql = string.Format(" select t_num from system_dd where p_num = '{0}'",SourceProjectId);
			DataSet ds = this.COM_BASE.CreateDataSetFromSql(strSql);			
			foreach (DataRow dr in ds.Tables[0].Rows)
			{
				this.PutSingleTable(dr["t_num"].ToString());				
			}
			
		}
	}
}