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;
        }