querydb.cs

介绍一个最短路径的算法编程！

				}
				else 
				{
					if(strRight!="")
					{
						strQueryPara=b;
						m_iOptionCondition++;
					}

				}
			}
			else 
			{
				if(strRight!="")
					strQueryPara=b;

			}
			return strQueryPara;
		}

		public string GetQueryPara(string strLeft ,string strOperator ,int nValue)
		{
			string strQueryPara="";
			string a,b;
			a=(" where " +strLeft +strOperator) +nValue.ToString();
			b=(" and " +strLeft +strOperator ) + nValue.ToString();
			if(m_nGateCrossState!=1)
			{
				if(m_iOptionCondition==0)
				{
					if(nValue!=-1)
					{
						strQueryPara=a;
						m_iOptionCondition++;
					}
				}
				else 
				{
					if(nValue!=-1)
					{
						strQueryPara=b;
						m_iOptionCondition++;
					}

				}
			}
			else 
			{
				if(nValue!=-1)
					strQueryPara=b;

			}
			return strQueryPara;
		}

		public string GetQueryPara(string strLeft ,string strOperator ,float fValue)
		{
			string strQueryPara="";
			string a,b;
			a=(" where " +strLeft +strOperator) +fValue.ToString();
			b=(" and " +strLeft +strOperator ) + fValue.ToString();
			if(m_nGateCrossState!=1)
			{
				if(m_iOptionCondition==0)
				{
					if(fValue!=float.Parse("-9.19"))
					{
						strQueryPara=a;
						m_iOptionCondition++;
					}
				}
				else 
				{
					if(fValue!=float.Parse("-9.19"))
					{
						strQueryPara=b;
						m_iOptionCondition++;
					}

				}
			}
			else 
			{
				if(fValue!=-1)
					strQueryPara=b;

			}
			return strQueryPara;
		}

	}

	public class QueryDB
	{
		private SqlConnection  con;
		private string m_strConnection;
		private SqlCommand cmd;
		public  DataSet  ds;
		private DataTable dt;
		private string m_QueryString;
		private SqlDataAdapter adpt;
		public QueryDB()
		{
			//
			// TODO: 在此处添加构造函数逻辑
			//
			m_strConnection = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
		}
		public bool ConnectDB()
		{
			if(m_strConnection!="")
			{
				ds=new  DataSet();
				con = new  SqlConnection(m_strConnection);
				adpt=new SqlDataAdapter();	

				return true;
			}
			else 
			{
				return false;
			}

		} 
		//判断报关单类型 0：报关单 1：转关单 －1：其他
		public int  GetEentryType(string strEntry)
		{
			int iResult=-1;
			ConnectDB();
			string strQuery;
			DataTable dt;
			strQuery="   select ENTRY_ID from  sENTRY_HEAD  where sENTRY_HEAD.ENTRY_ID = '" ;
			strQuery+=strEntry;
			strQuery+="'";
			dt =ExeQuery(strQuery,"sENTRY_HEAD");
			if(	dt.Rows.Count>0)
			{
				DisConnectDB();
				return 0;
			}
			DisConnectDB();
			ConnectDB();
			strQuery="   select count(*) from  sIM_DECL_CUR_HEAD  where sIM_DECL_CUR_HEAD.TURN_NO = '" ;
			strQuery+=strEntry;
			strQuery+="'";
			dt =ExeQuery(strQuery,"sIM_DECL_CUR_HEAD");
			if(	dt.Rows.Count>0)
			{
				iResult= 1;
			}

			DisConnectDB();
			return iResult;

		}
			

		public DataTable ExeQuery(string strQuery,string strTableName)
		{
			try
			{
				cmd= new SqlCommand (strQuery,con);
				adpt.SelectCommand=cmd;
				con.Open();
				adpt.Fill(ds,strTableName);
			}
			catch(System.Exception err)
			{
				throw err;
			}
			finally
			{
				DisConnectDB();
				
			}			
			return ds.Tables[strTableName];
			

		}
		public int GetRecNoOfCommitView(string  SQL)
		{
			
			int iTotalNum=0;
			string str="drop  view testview "; 
			cmd= new SqlCommand (str,con);
			con.Open();
			cmd.CommandTimeout = 180;
			cmd.Connection=con;


			try
			{
				
				cmd.CommandText=SQL;
				//cmd.ExecuteNonQuery();
				//	SqlDataReader read=cmd.ExecuteReader(CommandBehavior.CloseConnection);
				SqlDataReader read=cmd.ExecuteReader();
				while (read.Read()) 
				{
					iTotalNum++;
				}
				// always call Close when done reading.
				read.Close();
				
				
			}
			catch
			{
				iTotalNum=0;
			}
			return iTotalNum;
		

		}
		public DataTable GetDataTable(string SQl,int TopLine,int LineNum)
		{
			SqlCommand myCMD = new SqlCommand(SQl,con);
			myCMD.CommandTimeout = 180;
			SqlDataAdapter myDataAdapter=new SqlDataAdapter();
			myDataAdapter.SelectCommand=myCMD;
			con.Open();
			DataSet dsTable=new DataSet("sMANIFEST_CONTA_REL");
			myDataAdapter.Fill(dsTable,TopLine,LineNum,"sMANIFEST_CONTA_REL");
			con.Close();	
			int iColMax=dsTable.Tables["sMANIFEST_CONTA_REL"].Columns.Count;
			if(iColMax==7)
			{
				ReplaceGoodsType(ref dsTable);
			}

			return dsTable.Tables["sMANIFEST_CONTA_REL"];

		}
		void ReplaceGoodsType(ref DataSet ds )
		{
			string strSQL=" select GoodsType, GoodsTypeName from mGoodsType  ";
			SqlCommand myCMD = new SqlCommand(strSQL,con);
			myCMD.CommandTimeout = 180;
			SqlDataAdapter myDataAdapter=new SqlDataAdapter();
			myDataAdapter.SelectCommand=myCMD;
			con.Open();
			DataSet dsTable=new DataSet("mGoodsType");
			myDataAdapter.Fill(dsTable,"mGoodsType");
			con.Close();	
			int iColMax=ds.Tables["sMANIFEST_CONTA_REL"].Columns.Count; 
			int SourRowCount,DestRowCount;
			string strSour,strDest;
			if(iColMax==7)
			{
				
				
				SourRowCount=ds.Tables["sMANIFEST_CONTA_REL"].Rows.Count;
				DestRowCount=dsTable.Tables["mGoodsType"].Rows.Count;
				
				for(int i=0;i<SourRowCount;i++)
				{
					strSour=ds.Tables["sMANIFEST_CONTA_REL"].Rows[i][6].ToString();
					if(strSour=="")
						continue;
					for(int j=0;j<DestRowCount;j++)
					{
						strDest=dsTable.Tables["mGoodsType"].Rows[j][0].ToString();
						if(strSour.CompareTo(strDest)==0)
						{
							ds.Tables["sMANIFEST_CONTA_REL"].Rows[i][6]=dsTable.Tables["mGoodsType"].Rows[j][1].ToString();
							break;
						}

					}
				}
				ds.Tables["sMANIFEST_CONTA_REL"].Columns[6].ColumnName="GoodsTypeName";
			}
		//	dsTable.Dispose();
		//	myDataAdapter.Dispose();
		//	myCMD.Dispose();

		}


		public DataTable GetDataTableByCommitView(string  SQL,int TopLineNo,int LineNum)
		{
		
			 string str="drop  view testview "; 
			cmd= new SqlCommand (str,con);
			cmd.CommandTimeout = 180;
			con.Open();
			bool bQuerySuccess=false;
		
			try
			{

				cmd.CommandText=SQL;
				SqlDataReader sqlReader=cmd.ExecuteReader();
				

				DataRow    Row;

				dt=new DataTable();
				DataColumn EntryCol=new DataColumn("Entry_ID",System.Type.GetType("System.String"));
				DataColumn Ship_Name_EnCol=new DataColumn("Ship_Name_En",System.Type.GetType("System.String"));				
				DataColumn Voyage_noCol=new DataColumn("Voyage_no",System.Type.GetType("System.String"));;
				DataColumn Bill_noCol=new DataColumn("Bill_no",System.Type.GetType("System.String"));;
				DataColumn Conta_NoCol=new DataColumn("Conta_No",System.Type.GetType("System.String"));;
				DataColumn Vehi_NoCol=new DataColumn("Vehi_No",System.Type.GetType("System.String"));;
				DataColumn GoodsTypeNameCol=new DataColumn("GoodsTypeName",System.Type.GetType("System.String"));;	
				DataColumn PER_TIMECol=new DataColumn("PER_TIME",System.Type.GetType("System.DateTime"));;
				DataColumn ArriveTimeCol=new DataColumn("ArriveTime",System.Type.GetType("System.DateTime"));;
				DataColumn PlaceNameCol=new DataColumn("PlaceName",System.Type.GetType("System.String"));;
				DataColumn ContaBillRelaNameCol=new DataColumn("ContaBillRelaName",System.Type.GetType("System.String"));;

				dt.Columns.Add(EntryCol);
				dt.Columns.Add(Ship_Name_EnCol);
				dt.Columns.Add(Voyage_noCol);
				dt.Columns.Add(Bill_noCol);
				dt.Columns.Add(Conta_NoCol);
				dt.Columns.Add(Vehi_NoCol);
				dt.Columns.Add(GoodsTypeNameCol);
				dt.Columns.Add(PER_TIMECol);
				dt.Columns.Add(ArriveTimeCol);
				dt.Columns.Add(PlaceNameCol);
				dt.Columns.Add(ContaBillRelaNameCol);

				bool bRead=true;
				bool bFind=false;
				int iCount=0;
				int iReader=0;
				
				while(bRead)
				{
					if(iCount>=TopLineNo)
						bFind=true;
					if(bFind==false)
						bRead=sqlReader.Read();
					else 
					{
						bRead=sqlReader.Read();
						bool bResult;
						if(bRead)
						{
							Row=dt.NewRow();
							if(sqlReader.IsDBNull(0)!=true)
								Row["Entry_ID"]=sqlReader.GetString(0);
							if(sqlReader.IsDBNull(1)!=true)
								Row["Ship_Name_En"]=sqlReader.GetString(1);
							if(sqlReader.IsDBNull(2)!=true)
								Row["Voyage_no"]=sqlReader.GetString(2);
							if(sqlReader.IsDBNull(3)!=true)
								Row["Bill_no"]=sqlReader.GetString(3);
							if(sqlReader.IsDBNull(4)!=true)
								Row["Conta_No"]=sqlReader.GetString(4);
							if(sqlReader.IsDBNull(5)!=true)
								Row["Vehi_No"]=sqlReader.GetString(5);
							if(sqlReader.IsDBNull(6)!=true)
								Row["GoodsTypeName"]=sqlReader.GetString(6);
							if(sqlReader.IsDBNull(7)!=true)
								Row["PER_TIME"]=sqlReader.GetDateTime(7);
							if(sqlReader.IsDBNull(8)!=true)
								Row["ArriveTime"]=sqlReader.GetDateTime(8);
							if(sqlReader.IsDBNull(9)!=true)
								Row["PlaceName"]=sqlReader.GetString(9);
							if(sqlReader.IsDBNull(10)!=true)
								Row["ContaBillRelaName"]=sqlReader.GetString(10);

							dt.Rows.Add(Row);

						}
						iReader++;
						if(iReader>LineNum)
							bRead=false;
					}
					iCount++;
				
				}
				bQuerySuccess=true;
				sqlReader.Close();
			}
			catch(Exception)
			{

				
			}
			finally
			{
				con.Close();	
				
			}
			if(bQuerySuccess)
				return dt;
			else {

				return null;
			}
		}
		
		public DataSet GetQueryResultDS(string strQuery ,int nGateCross)
		{
//			DataTable t_dt;
			ConnectDB();
			
			cmd= new SqlCommand (strQuery,con);
			adpt.SelectCommand=cmd;
			con.Open();
			
		//	if(nGateCross==0)
	//		{
		//		adpt.Fill(ds,"mBarConta");
//				t_dt=ds.Tables["mBarConta"];
		//	}
		//	else 
		//	{
				adpt.Fill(ds,"test");
//				t_dt=ds.Tables["test"];
				
	//		}
			
			return ds;
			
		}


		public bool DisConnectDB()
		{
			adpt.Dispose();
			con.Close();
			con.Dispose();	
			ds.Dispose();
			cmd.Dispose();
			return true;
		}
		//得到货单关系所有可能的选项
		public void GetGoodsBillRelationNameList( ArrayList alUserID)
		{
			ConnectDB();
			string tstrQueryString="select ContaBillRelaName from mContaBillRetaType ;" ;
			string tstrTable="mContaBillRetaType";

			DataTable dt =ExeQuery(tstrQueryString,tstrTable);
			
			alUserID.Add("所有关系");
			for(int I=0;I<dt.Rows.Count;I++)
			{
				alUserID.Add(dt.Rows[I][0].ToString());
				
			}
			DisConnectDB();