accessconection.cs

ASP.NET 2.0 (c#) +AJAX 实现的公交查询系统

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// AccessConection 的摘要说明
/// </summary>
public class AccessConection
{
    //连接字符串
    string ConnString = Convert.ToString(ConfigurationManager.ConnectionStrings["MyConnection"]);

	public AccessConection()
	{  
        //构造函数
	}
     

    //线路查询函数
    public string Link_Select(string Line)
    {
        using(SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查询字符串(查询线路)
//            string SqlStr = "SELECT * FROM [线路属性] WHERE bus LIKE '%" + TempStr + "%'";
            
            //建立连接            
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //调用存储过程
            Cmd.CommandText = "Link_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Line", "%" + Line.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();
            string OutStr = "";
            try
            {
                while (Dr.Read())
                {                 
                    OutSB.Append("<div class='Data_Style'>");
                    OutSB.Append("<span>" + Dr["bus"].ToString() + "</span>");
                    OutSB.Append("<span>" + Dr["ticketsell"].ToString() + "</span>");
                    OutSB.Append("<span>" + Dr["price"].ToString() + "</span>");
                    OutSB.Append("<span>IC卡支持:" + Dr["ic"].ToString() + "</span>");
                    OutSB.Append("<span>出车时间:" + Dr["runtime"].ToString() + "</span>");
                    OutSB.Append("<span>所属集团:" + Dr["lnc"].ToString() + "</span>");
                    OutSB.Append("</div>");
                    OutSB.Append("<div class='Data_Style_Longtext'>");
                    OutSB.Append(Dr["beginend"].ToString());
                    OutSB.Append("</div>");
                }
                OutStr = OutSB.ToString();
                if( "" == OutStr.Trim() )
                {
                    OutStr = "Sorry!没有你所查找的数据";
                }
            }
            catch
            {
                OutStr = "数据库连接错误";
            }
            finally
            {
                Dr.Close();
                Conn.Close();                
            }              
            return OutStr;   
        }
    }
    
    //站点查询函数
    public string Site_Select(string Site)
    {
        using(SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查询字符串(查询站点)
//            string SqlStr = "SELECT * FROM [线路表] WHERE sname LIKE '%" + TempStr + "%'";
            
            //建立连接            
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //调用存储过程
            Cmd.CommandText = "Site_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Site", "%" + Site.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();            
            string OutStr = "";
            try
            {
                while (Dr.Read())
                {
                    OutSB.Append("<div class='Data_Style'>");
                    OutSB.Append("乘坐<span>" + Dr["bus"].ToString() + "</span>路车");
                    OutSB.Append("可到达<span>" + Dr["sname"] + "</span>");
                    OutSB.Append("</div>"); 
                    OutSB.Append("<div style='height:5px;'></div>");               
                }
                OutStr = OutSB.ToString();
                if ("" == OutStr.Trim())
                {
                    OutStr = "Sorry!没有你所查找的数据";
                }
            }
            catch
            {
                OutStr = "数据库连接错误";
            }
            finally
            {
                Dr.Close();
                Conn.Close();                
            }  
            return OutStr;   
        }
    }

    //换乘查询
    public string StoS_Select(string Begin_Site,string End_Site)
    {
        using (SqlConnection Conn = new SqlConnection(ConnString))
        {
            //查询字符串(查询站点)
/*
            StringBuilder SB = new StringBuilder();
            SB.Append(" SELECT DISTINCT TOP 10 m.起点线路 AS 起点线路, m.起点站 AS 起点站, m.中转站 AS 中转站, n.终点站 AS 终点站, n.终点线路 AS 终点线路, ABS(M.经过站点数 + N.经过站点数) AS 总经过站点数");
            SB.Append(" FROM    (SELECT A.bus AS 起点线路 , A.sname AS 起点站 , B.sname AS 中转站 ,ABS(A.snum - B.snum ) AS 经过站点数");
            SB.Append(" FROM 线路表 AS A, 线路表 AS B");
            SB.Append(" WHERE A.sname LIKE '%" + TempStrBegin +"%' AND (A.sname <> B.sname AND A.bus = B.bus))");
            SB.Append(" AS m(起点线路,起点站,中转站,经过站点数)");
            SB.Append(" ,");
            SB.Append(" (SELECT C.sname AS 中转站 ,D.sname AS 终点站 ,D.bus AS 终点线路 ,ABS(C.snum - D.snum ) AS 经过站点数");
            SB.Append(" FROM 线路表 AS C, 线路表 AS D");
            SB.Append(" WHERE D.sname LIKE '%" + TempStrEnd + "%' AND (C.sname <> D.sname AND C.bus = D.bus))");
            SB.Append(" AS n(中转站,终点站,终点线路,经过站点数)");
            SB.Append(" WHERE m.中转站 = n.中转站 AND (m.起点线路 <> n.终点线路) ORDER BY 总经过站点数"); 
            string SqlStr = SB.ToString();   
            
*/
            string OutStr = "";
            int i = 0;
                        
            //建立连接  
            Conn.Open();               
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;            
            //调用存储过程
            Cmd.CommandText = "StoS_Select";
            Cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter param = Cmd.Parameters.AddWithValue("@Begin_Site", "%" + Begin_Site.Trim() + "%");
            Cmd.Parameters.AddWithValue("@End_Site", "%" + End_Site.Trim() + "%");
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            SqlDataReader Dr = Cmd.ExecuteReader();
            StringBuilder OutSB = new StringBuilder();
            try
            {
                while (Dr.Read())
                {
                    i++;
                    OutSB.Append("<div class='StoS_Style_Top'>第<span>" + i + "</span>种方案,共经过<span>" + Dr["总经过站点数"].ToString() + "</span>站</div>");
                    OutSB.Append("<div class='StoS_Style_Down'>");
                    OutSB.Append("从<span>" + Dr["起点站"].ToString() + "</span>");
                    OutSB.Append("坐<span>" + Dr["起点线路"].ToString() + "</span>");
                    OutSB.Append("在<span>" + Dr["中转站"].ToString() + "</span>");
                    OutSB.Append("换乘<span>" + Dr["终点线路"].ToString() + "</span>");
                    OutSB.Append("到<span>" + Dr["终点站"].ToString() + "</span>");
                    OutSB.Append("</div>");
                    OutStr = OutSB.ToString();
                }
                OutStr = OutSB.ToString();
                if ("" == OutStr.Trim())
                {
                    OutStr = "Sorry!没有你所查找的数据";
                }
            }
            catch
            {   
                OutStr = "数据库连接出错!!!"; 
            }                                 
            finally
            {
                Conn.Close();
                Dr.Close();
            } 
            return OutStr;
        }

    }
}