interface.asmx.cs

用于文件传输的Web服务

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Configuration;
using System.Web.Services;
using System.IO;
using System.Data.OleDb;
using ENet.BCL;
namespace E3.WebService.SZSE
{
	/// <summary>
	/// Summary description for InterFace.
	/// </summary>
	public enum eDataType
	{
		eDbf=1,
		eExcel=2,
		eAccess=3
	}
			
	public class InterFace : System.Web.Services.WebService
	{
		private string WebConnection=ConfigurationSettings.AppSettings["WebConnection"];		
		private string DBFConnection=ConfigurationSettings.AppSettings["DBFConnection"];
		private string ExcelConnection=ConfigurationSettings.AppSettings["ExcelConnection"];
		private string JGGXExcelConnection=ConfigurationSettings.AppSettings["JGGXExcelConnection"];
		private string AccessConnection=ConfigurationSettings.AppSettings["AccessConnection"];
		private string TransPath=ConfigurationSettings.AppSettings["TransPath"];		
		private string JoinDataBase=ConfigurationSettings.AppSettings["JoinDataBase"];
		private string NetWorkPath="";
		private string NetWorkUser="";
		private string NetWorkPassWord="";

		public InterFace()
		{
			//CODEGEN: This call is required by the ASP.NET Web Services Designer
			InitializeComponent();
		}
		private void InitParam()
		{
			try
			{
				CSqlBuilder cls=new CSqlBuilder();
				cls.ConnectionString=this.WebConnection;
				string StrSql="SELECT * FROM E3_Base_WebService_Param";
				DataTable dt=cls.GetDataTable(StrSql);
				if(dt.Rows.Count>0)
				{
					Hashtable hs=new Hashtable();
					for(int i=0;i<dt.Rows.Count;i++)
					{
						hs[dt.Rows[i]["ParamName"].ToString()]=dt.Rows[i]["ParamValue"].ToString();
					}
//					this.WebConnection=hs["WebConnection"].ToString();
					this.DBFConnection=hs["DBFConnection"].ToString();
					this.ExcelConnection=hs["ExcelConnection"].ToString();
					this.AccessConnection=hs["AccessConnection"].ToString();
					this.TransPath=hs["TransPath"].ToString();
					this.JoinDataBase=hs["JoinDataBase"].ToString();
					this.JGGXExcelConnection=hs["JGGXExcelConnection"].ToString();
					this.NetWorkPath=hs["NetWorkPath"].ToString();
					this.NetWorkUser=hs["NetWorkUser"].ToString();
					this.NetWorkPassWord=hs["NetWorkPassWord"].ToString();
					if(this.TransPath.Substring(TransPath.Length-1,1)!="\\")
					{
						this.TransPath+="\\";
					}
					if(this.NetWorkPath.Substring(NetWorkPath.Length-1,1)!="\\")
					{
						this.NetWorkPath+="\\";
					}
				}			

			}
			catch
			{}			

		}

		private void UpLoadTable(string TransName,string LocalConnection,eDataType DataType)
		{

			string TableName="";
			bool bNeedDelete=true;
			string sKeyField="";				
			string MakeResultTableID="";
			this.GetTransTableConfig(TransName,ref TableName,ref bNeedDelete,ref sKeyField,ref MakeResultTableID);
			if(sKeyField!="")
			{
				string[] sKey=sKeyField.Split('|');
				sKeyField="";
				for(int i=0;i<sKey.Length;i++)
				{
					sKeyField+="convert(varchar,"+sKey[i]+")+";
				}
				sKeyField=sKeyField.Substring(0,sKeyField.Length-1);
			}
			if(sKeyField==null)
				sKeyField="";

			if(TableName==null)
				TableName=TransName;
			string sSource="";
			if(DataType==eDataType.eDbf)
				sSource="("+LocalConnection+")..."+TransName;
			else if(DataType==eDataType.eExcel)			
				sSource="("+LocalConnection.Replace("(file)",TransName)+")...[Sheet1$]";			
			else if(DataType==eDataType.eAccess)			
				sSource="("+LocalConnection+")..."+TransName;

			string StrSql="";				
			OleDbConnection m_DbConn;			
			OleDbTransaction m_DbTrans;
			try
			{
				m_DbConn=new OleDbConnection(WebConnection);				
				m_DbConn.Open();				
			}
			catch(Exception e)
			{				
				string str=e.Message.ToString();
				return ;
			}
			m_DbTrans=m_DbConn.BeginTransaction();

			try
			{
				StrSql="select Top 1 * from "+TableName;
				DataTable dt=this.GetDataTable(StrSql,WebConnection);
				string sField="";
				for(int i=0;i<dt.Columns.Count;i++)
				{
					sField+=dt.Columns[i].ColumnName+",";
				}

				sField=sField.Substring(0,sField.Length-1);
				StrSql="";
				if(bNeedDelete==false||sKeyField!="")
				{
					StrSql="delete from "+TableName+" where "+sKeyField+" in("+
						"select "+sKeyField+" from OpenDataSource"+
						sSource+");";
				}
				else
				{
					StrSql="delete from "+TableName+";";
				}
				StrSql+="insert into "+TableName+
					" select "+sField+" from OpenDataSource"+sSource;

				this.ExecuteSql(StrSql,m_DbConn,m_DbTrans);
				if(MakeResultTableID!="")
				{					
					try
					{
						StrSql="SELECT CombineSQL FROM E3_Report_Catalog";
						DataTable dtCombine=this.GetDataTable(StrSql,WebConnection);
						if(dtCombine.Rows.Count>0)
						{
							for(int i=0;i<dtCombine.Rows.Count;i++)
							{
								if(dtCombine.Rows[i]["CombineSQL"].ToString()!="")
								{
									try
									{
										StrSql=dtCombine.Rows[i]["CombineSQL"].ToString();
										StrSql=StrSql.Replace("\r"," ");
										StrSql=StrSql.Replace("\t"," ");
										StrSql=StrSql.Replace("\n"," ");
										this.ExecuteSql(StrSql,m_DbConn,m_DbTrans);
									}
									catch
									{}
								}
							}
						}
					}
					catch
					{}					
				}
				m_DbTrans.Commit();
			}
			catch(System.Exception ex)
			{
				string sErr=ex.Message;
				sErr=sErr;
				m_DbTrans.Rollback();
			}
			m_DbConn.Close();	
		}

		[WebMethod(BufferResponse=false)]
		public string TransFile(string fileName,byte[] b)
		{
			try
			{			
				this.InitParam();
				if(TransPath.Substring(TransPath.Length-1,1)!=@"\")			
					TransPath+=@"\";
				if(this.NetWorkPath.Substring(NetWorkPath.Length-1,1)!=@"\")			
					NetWorkPath+=@"\";

				if(JoinDataBase==null)
					JoinDataBase="0";
				FileInfo fi =new FileInfo(TransPath+fileName);
				if (fi.Exists)
				{
					fi.Delete();
				}
				FileStream fs =new FileStream(TransPath+fileName,FileMode.Create,FileAccess.Write);
				fs.Write(b,0,b.Length);
				fs.Close();
				//Copy to NetworkPath
				try
				{
					if(this.NetWorkUser!=""&&this.NetWorkPassWord!="")
					{
						System.Net.NetworkCredential myCred = new System.Net.NetworkCredential(this.NetWorkUser,this.NetWorkPassWord);
						System.Net.WebClient Client = new System.Net.WebClient();
						System.Net.CredentialCache myCache = new System.Net.CredentialCache();
					}					

					if(this.NetWorkPath!="")
					{
						File.Copy(TransPath+fileName,this.NetWorkPath+fileName,true);
					}				
				}
				catch(Exception e)
				{
					string str=e.Message.ToString();
					return str;
				}	
				
				//Copy to NetworkPath
				if(fi.Extension.ToLower()==".dbf"&&JoinDataBase=="1")
				{				
					string TableName=fi.Name.ToUpper().Replace(fi.Extension.ToUpper(),"");
					this.UpLoadTable(TableName,this.DBFConnection,eDataType.eDbf);

				}
				else if(fi.Extension.ToLower()==".xls"&&JoinDataBase=="1")
				{				
					if(fi.Name.ToUpper().Substring(0,4)=="JGGX"||fi.Name.ToUpper().Substring(0,4)=="JGXX")
					{
						string TableName=fi.Name.ToUpper().Replace(fi.Extension.ToUpper(),"");									
						this.UpdateExcelTable(TableName,this.JGGXExcelConnection);						
					}
					else
					{
						string TableName=fi.Name.ToUpper().Replace(fi.Extension.ToUpper(),"");					
						this.UpLoadTable(TableName,this.ExcelConnection,eDataType.eExcel);
					}
				}
				else if(fi.Extension.ToLower()==".txt"&&JoinDataBase=="1")
				{					
					this.UpdateTXTTable(fi);
				}				
				
				this.UpdateSMEDBFTable(fi.Name.ToUpper().Replace(fi.Extension.ToUpper(),""));
				
				if(fi.Extension.ToLower()==".mdb"&&JoinDataBase=="1")
				{				
					for(int i=0;i<ConfigurationSettings.AppSettings.Count;i++)
					{
						string KeyName=ConfigurationSettings.AppSettings.Keys[i];
						if(KeyName.IndexOf("AccessTable")!=-1)
						{
							string TableName=ConfigurationSettings.AppSettings[i];	
							try
							{
								this.UpLoadTable(TableName,this.AccessConnection,eDataType.eAccess);
							}
							catch
							{}							
						}		

					}
					
				}
				
				return "";
			}
			catch(Exception e)
			{
				string str=e.Message.ToString();
				return str;
			}			
		}
//		private void UpLoadJGGXTable(string TableName)
//		{
//			try
//			{
//				string StrConn=this.JGGXExcelConnection.Replace("(file)",TableName);
//				string TableName="";
//				bool bNeedDelete=true;
//				string sKeyField="";
//				this.GetTransTableConfig(TransName,ref TableName,ref bNeedDelete,ref sKeyField);
//
//
//
//				if(TableName==null)
//					TableName=TransName;
//				string StrSql="";				
//				OleDbConnection m_DbConn;
//				OleDbTransaction m_DbTrans;
//				OleDbCommand m_DbCmd=new OleDbCommand();
//				try
//				{
//					m_DbConn=new OleDbConnection(WebConnection);				
//					m_DbConn.Open();				
//				}
//				catch(Exception e)
//				{				
//					string str=e.Message.ToString();
//					return;
//				}
//			
//
//				m_DbTrans=m_DbConn.BeginTransaction();		
//	
//				m_DbCmd.Connection=m_DbConn;
//				m_DbCmd.Transaction=m_DbTrans;
//
//
//			}
//			catch
//			{}
//		}
		private void UpdateSMEDBFTable(string TransName)
		{
			try
			{
				
				ENet.BCL.CSqlBuilder cls=new ENet.BCL.CSqlBuilder();
				cls.ConnectionString=this.WebConnection;
				string StrSql="";
				StrSql="SELECT * FROM DTS_TABLENAME where FileName='"+TransName.Substring(0,2)+"'";
				DateTime dtCreat;
				try
				{
					string CreateTime="20"+TransName.Substring(2,2)+"-"+
						TransName.Substring(4,2)+"-"+TransName.Substring(6,2);
					dtCreat=DateTime.Parse(CreateTime);
				}
				catch
				{
					dtCreat=DateTime.Now;
				}
				

				DataTable dt=cls.GetDataTable(StrSql);
				if(dt.Rows.Count>0)
				{
					if(dt.Rows[0]["FileType"].ToString().ToLower()=="dbf")
					{
						string svrTable=dt.Rows[0]["TableName"].ToString();
						StrSql="select * from "+TransName;			
						DataTable DtDbf=this.GetDataTable(StrSql,DBFConnection);		
						Hashtable hsData=new Hashtable();
						for(int i=0;i<DtDbf.Rows.Count;i++)
						{
							Hashtable hsEntry=new Hashtable();
							for(int j=0;j<DtDbf.Columns.Count;j++)
							{
								hsEntry.Add(DtDbf.Columns[j].ColumnName,DtDbf.Rows[i][j]);
							}
							hsEntry.Add("Create_Time",dtCreat);
							hsData.Add(i,hsEntry);
						}
						cls.CreateSQL(svrTable,this.WebConnection);
						StrSql="delete from "+svrTable+" where Create_Time='"+dtCreat.ToString()+"'";
						cls.SQLInsertMutiRow(hsData,StrSql);
					}
					else if(dt.Rows[0]["FileType"].ToString().ToLower()=="txt")
					{
					}			
				}
			}
			catch
			{}

		}
		private void UpdateTXTTable(System.IO.FileInfo fi)
		{
			try
			{
				string txtName=fi.Name.ToUpper().Replace(fi.Extension.ToUpper(),"");
				

				ENet.BCL.CSqlBuilder cls=new ENet.BCL.CSqlBuilder();
				cls.ConnectionString=this.WebConnection;
				string StrSql="";
				StrSql="SELECT * FROM DTS_TABLENAME where FileName='"+txtName+"' and FileType='TXT'";
				DataTable dt=cls.GetDataTable(StrSql);
				if(dt.Rows.Count>0)
				{
					//fi.LastWriteTime					
					FileStream FS=new FileStream(fi.FullName,FileMode.Open,FileAccess.Read);
					StreamReader Sread = new StreamReader(FS,System.Text.Encoding.Default);
					string txt=Sread.ReadToEnd();
					Sread.Close();
					string svrTable=dt.Rows[0]["TableName"].ToString();
					svrTable=svrTable.Replace(" ","");
					StrSql="delete from "+svrTable+" where CURYEAR="+fi.LastWriteTime.Year+
								" and CURMONTH="+fi.LastWriteTime.Month+
								" and CURDAY="+fi.LastWriteTime.Day;
					Hashtable hsData=new Hashtable();
					hsData.Add("UPLOAD_TIME",DateTime.Now);
					hsData.Add("CONTENT",txt);
					hsData.Add("CREATE_TIME",fi.LastWriteTime);
					hsData.Add("CURYEAR",fi.LastWriteTime.Year);
					hsData.Add("CURMONTH",fi.LastWriteTime.Month);
					hsData.Add("CURDAY",fi.LastWriteTime.Day);
					hsData.Add("CURWEEK",0);
					cls.CreateSQL(svrTable,this.WebConnection);
					cls.SQLInserSingleRow(hsData,StrSql);					
				}
				
			}
			catch
			{}
			
		}
		[WebMethod(BufferResponse=false)]
		public DataSet GetDataSet(string StrSql,string StrCnn)
		{
			DataSet DS=new DataSet();
			if(StrCnn=="1")
			{
				try
				{
					System.IO.File.Delete(StrSql);
				}
				catch
				{}
			}
			else if(StrCnn=="2")
			{
				try
				{
					OleDbConnection Conn=new OleDbConnection(this.WebConnection);
					Conn.Open();			
					System.Data.OleDb.OleDbCommand Cmd=new OleDbCommand();
					Cmd.CommandTimeout=0;
					Cmd.Connection=Conn;
					Cmd.CommandType=System.Data.CommandType.Text;
					Cmd.CommandText=StrSql;
					Cmd.ExecuteNonQuery();	
					Conn.Close();
				}
				catch
				{}				
			}
			else
			{
				OleDbConnection Conn=new OleDbConnection(StrCnn);
				Conn.Open();			
				System.Data.OleDb.OleDbCommand Cmd=new OleDbCommand();
				Cmd.CommandTimeout=0;
				Cmd.Connection=Conn;
				Cmd.CommandType=System.Data.CommandType.Text;
				Cmd.CommandText=StrSql;
				System.Data.OleDb.OleDbDataAdapter oda=new OleDbDataAdapter(Cmd);