public BicycleInfo CreateBikeByReader(OleDbDataReader rd)
{
BicycleInfo bike = new BicycleInfo();
bike.StationID = (string)rd["CSVRID"].ToString();
if (rd["CALL"] != DBNull.Value)
{
bike.ServicePhone = (string)rd["CALL"];
}
if (rd["SERVICE"] != DBNull.Value)
{
bike.ServiceState = (string)rd["SERVICE"];
}
if (rd["TIME_"] != DBNull.Value)
{
bike.ServiceTime = (string)rd["TIME_"];
}
if (rd["ADDR"] != DBNull.Value)
{
bike.StationAddr = (string)rd["ADDR"];
}
bike.StationName = (string)rd["NAME"];
//bike.StationRemarks = (string)rd["CSVRID"];
bike.X = (double)rd["BD_X"];
bike.Y = (double)rd["BD_Y"];
return(bike);
}
public QueryResultSet <BicycleInfo> SearchBikesByName(int start, int pagesize, string keyword)
{
string sql = string.Format("select * from ( SELECT top {0} * from ( select * from ( select top {1} * from bike_ghj where NAME like '%{2}%' or ADDR like '%{2}%' order by CSVRID ) order by CSVRID desc ) ) order by CSVRID", pagesize, start + pagesize, keyword);
//string sql = string.Format("SELECT TOP {0} * FROM BIKES_GHJ ORDER BY ID DESC ", top);
if (this._conn.State != System.Data.ConnectionState.Open)
{
this._conn.Open();
}
string sql0 = string.Format("SELECT COUNT(*) FROM BIKE_GHJ where NAME like '%{0}%' or ADDR like '%{0}%'", keyword);
OleDbCommand cmdCount = this._conn.CreateCommand();
cmdCount.CommandText = sql0;
cmdCount.CommandType = System.Data.CommandType.Text;
int nCount = (int)cmdCount.ExecuteScalar();
OleDbCommand mycmd = new OleDbCommand(sql, this._conn);
OleDbDataReader mydr = mycmd.ExecuteReader();
List <BicycleInfo> lstDemos = new List <BicycleInfo>();
while (mydr.Read())
{
BicycleInfo demo = this.CreateBikeByReader(mydr);
lstDemos.Add(demo);
}
this._conn.Close();
QueryResultSet <BicycleInfo> result = new QueryResultSet <BicycleInfo>();
result.TotalCount = nCount;
result.ResultSet = lstDemos;
return(result);
}
public QueryResultSet <BicycleInfo> QueryBikesByRect(int start, int pagesize, double xmin, double ymin, double xmax, double ymax)
{
string sql = string.Format("select * from ( SELECT top {0} * from ( select * from ( select top {1} * from bike_ghj order by CSVRID where bd_x between {2} and {3} and bd_y between {4} and {5} ) order by CSVRID desc ) ) order by CSVRID", pagesize, start + pagesize, xmin, xmax, ymin, ymax);
//string sql = string.Format("SELECT TOP {0} * FROM BIKES_GHJ ORDER BY ID DESC ", top);
if (this._conn.State != System.Data.ConnectionState.Open)
{
this._conn.Open();
}
string sql0 = string.Format("SELECT COUNT(*) FROM BIKE_GHJ where bd_x between {0} and {1} and bd_y between {2} and {3}", xmin, xmax, ymin, ymax);
OleDbCommand cmdCount = this._conn.CreateCommand();
cmdCount.CommandText = sql0;
cmdCount.CommandType = System.Data.CommandType.Text;
int nCount = (int)cmdCount.ExecuteScalar();
OleDbCommand mycmd = new OleDbCommand(sql, this._conn);
OleDbDataReader mydr = mycmd.ExecuteReader();
List <BicycleInfo> lstDemos = new List <BicycleInfo>();
while (mydr.Read())
{
BicycleInfo demo = this.CreateBikeByReader(mydr);
lstDemos.Add(demo);
}
this._conn.Close();
QueryResultSet <BicycleInfo> result = new QueryResultSet <BicycleInfo>();
result.TotalCount = nCount;
result.ResultSet = lstDemos;
return(result);
}
