笔者项目中遇到从DataSet中按某条件查询单个数据问题,DataSet后是Sql语句从数据库中查询取得一个集合。其中表结构如下:
CREATE TABLE [dbo].[tb_report](
[time] [datetime] NOT NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[GSM] [varchar](50) NOT NULL,
[Consumption] [varchar](30) NOT NULL
) ON [PRIMARY]
定义一个类,做逻辑处理用
/// <summary>
/// 预警类
/// </summary>
public class Nodate
{
/// <summary>
/// 测站
/// </summary>
public string GSM { get; set; }
/// <summary>
/// 无数据天数
/// </summary>
public int Count { get; set; }
}
按GSM编号查询,并按日期降序排序
DateTime datet = DateTime.Now.AddDays(-15);
cmd.CommandText = "select GSM, Consumption,time from tb_report where time>'" + datet.ToShortDateString() + "' and time<'" + DateTime.Now.AddDays(1).ToShortDateString() + "'";
sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
foreach (Nodate gsm in list)
{
IEnumerable<DataRow> matches = from roport
in ds.Tables[0].AsEnumerable()
where roport.Field<string>("GSM").Equals(gsm.GSM)
orderby roport.Field<DateTime>("time") descending
select roport;
foreach (var item in matches)
{
if (item[1].ToString() == "0") gsm.Count++; //Consumption为0,天数+1
else
break;
}
}