C#代码
///
<summary>
/// 查找直达公交线
/// </summary>
/// <param name="startStationName"> 起点 </param>
/// <param name="endStationName"> 终点 </param>
/// <returns> 返回包含"lineName名称"的DataSet </returns>
public static DataSet TransferStraightForward( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_StraightForward = String.Format( " select distinct tStations.lineName "
+ " from tStations, "
+ " (select siteID,lineName from tStations where stationName='{0}' and upOrDown='上行线') A, "
+ " (select siteID,lineName from tStations where stationName='{1}' and upOrDown='上行线')B, "
+ " (select siteID,lineName from tStations where stationName='{0}' and upOrDown='下行线') C, "
+ " (select siteID,lineName from tStations where stationName='{1}' and upOrDown='下行线')D "
+ " where a.lineName in ( "
+ " select distinct E.lineName from "
+ " (select lineName,upOrDown from tStations where stationName = '{0}') E, "
+ " (select lineName,upOrDown from tStations where stationName = '{1}') F "
+ " where E.lineName = F.lineName and E.upOrDown = F.upOrDown "
+ " ) AND "
+ " a.lineName=b.lineName and c.lineName=b.lineName and c.lineName = d.lineName "
+ " and "
+ " ( "
+ " ( A.siteId < B.SiteID "
+ " AND tStations.siteID between A.siteId and B.SiteID "
+ " ) "
+ " OR "
+ " ( A.siteId >= B.SiteID "
+ " AND "
+ " tStations.siteID between c.siteId and d.SiteID "
+ " ) "
+ " ) " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_StraightForward, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}
/// <summary>
/// 查询一次换乘
/// </summary>
/// <returns></returns>
public static DataSet TransferOnce( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_transOnce = String.Format( " select A.stationName, a.lineName, b.lineName from " +
" ( " +
" select distinct stationName, lineName from tStations where lineName in " +
" (select lineName from tStations where stationName= '{0}') " +
" )A, " +
" ( " +
" select distinct stationName, lineName from tStations where lineName in " +
" (select lineName from tStations where stationName= '{1}') " +
" )B " +
" where A.stationName=B.stationName and " +
" a.lineName <> b.lineName and " +
" A.stationName<>'{0}' and A.stationName<>'{1}' and " +
" a.lineName not in (select lineName from tStations where stationName= '{1}') and " +
" b.lineName not in (select lineName from tStations where stationName= '{0}') " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_transOnce, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}
/// <summary>
/// 查询2次换乘
/// </summary>
/// <returns></returns>
public static DataSet TransferTwice( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_transTwice = String.Format( " select c.lineName, c.stationName, d.stationName from " +
" ( " +
" select distinct lineName, stationName from tStations where stationName in " +
" ( " +
" select distinct stationName from tStations where lineName in " +
" (select lineName from tStations where stationName = '{0}') " +
" ) " +
" ) C, " +
" ( " +
" select distinct lineName, stationName from tStations where stationName in " +
" ( " +
" select distinct stationName from tStations where lineName in " +
" (select lineName from tStations where stationName = '{1}') " +
" ) " +
" ) D " +
" where C.lineName = D.lineName and c.stationName <> d.stationName " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_transTwice, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}
/// 查找直达公交线
/// </summary>
/// <param name="startStationName"> 起点 </param>
/// <param name="endStationName"> 终点 </param>
/// <returns> 返回包含"lineName名称"的DataSet </returns>
public static DataSet TransferStraightForward( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_StraightForward = String.Format( " select distinct tStations.lineName "
+ " from tStations, "
+ " (select siteID,lineName from tStations where stationName='{0}' and upOrDown='上行线') A, "
+ " (select siteID,lineName from tStations where stationName='{1}' and upOrDown='上行线')B, "
+ " (select siteID,lineName from tStations where stationName='{0}' and upOrDown='下行线') C, "
+ " (select siteID,lineName from tStations where stationName='{1}' and upOrDown='下行线')D "
+ " where a.lineName in ( "
+ " select distinct E.lineName from "
+ " (select lineName,upOrDown from tStations where stationName = '{0}') E, "
+ " (select lineName,upOrDown from tStations where stationName = '{1}') F "
+ " where E.lineName = F.lineName and E.upOrDown = F.upOrDown "
+ " ) AND "
+ " a.lineName=b.lineName and c.lineName=b.lineName and c.lineName = d.lineName "
+ " and "
+ " ( "
+ " ( A.siteId < B.SiteID "
+ " AND tStations.siteID between A.siteId and B.SiteID "
+ " ) "
+ " OR "
+ " ( A.siteId >= B.SiteID "
+ " AND "
+ " tStations.siteID between c.siteId and d.SiteID "
+ " ) "
+ " ) " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_StraightForward, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}
/// <summary>
/// 查询一次换乘
/// </summary>
/// <returns></returns>
public static DataSet TransferOnce( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_transOnce = String.Format( " select A.stationName, a.lineName, b.lineName from " +
" ( " +
" select distinct stationName, lineName from tStations where lineName in " +
" (select lineName from tStations where stationName= '{0}') " +
" )A, " +
" ( " +
" select distinct stationName, lineName from tStations where lineName in " +
" (select lineName from tStations where stationName= '{1}') " +
" )B " +
" where A.stationName=B.stationName and " +
" a.lineName <> b.lineName and " +
" A.stationName<>'{0}' and A.stationName<>'{1}' and " +
" a.lineName not in (select lineName from tStations where stationName= '{1}') and " +
" b.lineName not in (select lineName from tStations where stationName= '{0}') " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_transOnce, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}
/// <summary>
/// 查询2次换乘
/// </summary>
/// <returns></returns>
public static DataSet TransferTwice( string startStationName,
string endStationName)
{
string strErr = "" ;
CheckStationName( ref startStationName, ref endStationName, ref strErr);
SmartBusDAL.DataBase datBs = new SmartBusDAL.DataBase();
string sql_transTwice = String.Format( " select c.lineName, c.stationName, d.stationName from " +
" ( " +
" select distinct lineName, stationName from tStations where stationName in " +
" ( " +
" select distinct stationName from tStations where lineName in " +
" (select lineName from tStations where stationName = '{0}') " +
" ) " +
" ) C, " +
" ( " +
" select distinct lineName, stationName from tStations where stationName in " +
" ( " +
" select distinct stationName from tStations where lineName in " +
" (select lineName from tStations where stationName = '{1}') " +
" ) " +
" ) D " +
" where C.lineName = D.lineName and c.stationName <> d.stationName " ,
startStationName, endStationName);
datBs.OpenConn();
DataSet ds = datBs.ExceSqlToDataSet(sql_transTwice, ref strErr);
datBs.CloseConn();
if (strErr != "" )
{
throw new Exception(strErr);
}
return ds;
}