求解析、在线等。

[WebMethod]
  public DataTable QueryRecords(string idno, string name, string deptName, string charge_time, string doorname)
  {
  SqlConnection conn = new SqlConnection("server=.;database=ljptest;uid=sa;pwd=sa");
  string sql = "select A.bh,A.names,B.datetimes,C.doorname,D.deptname from .consumes A inner join y_mj_flow B on A.idno=B.idno inner join y_mj_doors C on C.doorid=B.doorid inner join dept D on D.deptcode=A.deptcode";

  if (doorname != null)
  {
  sql += " where doorname='" + doorname + "'";
  }
  if (charge_time != null)
  {
  sql += " or B.datetimes='" + charge_time + "'";
  }

  if (name != null)
  {
  sql += " or A.names='" + name + "'";
  }
  if (deptName != null)
  {
  sql += " or C.doorname='" + deptName + "'";
  }
  if (idno != null)
  {
  sql += " or A.bh='" + idno + "'";
  }
   
  SqlCommand cmd = new SqlCommand(sql, conn);
  SqlDataAdapter sda = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  sda.Fill(ds);
  return ds.Tables[0];

  }
这样写可以根据条件查询,但是不能查询所有。
但是把SqlCommand放到上面的Sql语句下面,却只能查询所有,而且就算你写了条件,查出来的也是所有数据。
原因我已经知道。但就是不知道怎么修改,达到查询所有和按条件查询的两种效果。
求解析。谢谢哈。在线等。

作者: Te_262_88   发布时间: 2011-06-15

SQL code
declare @id int
set @id=1 
--set @id=null

select * from tablename where id=isnull(@id,id)
--有id就判断id id为null 就是得到全部

作者: maco_wang   发布时间: 2011-06-15

引用 1 楼 maco_wang 的回复:
SQL code
declare @id int
set @id=1
--set @id=null

select * from tablename where id=isnull(@id,id)
--有id就判断id id为null 就是得到全部
.

作者: zy112429   发布时间: 2011-06-15

C# code

public DataTable QueryRecords(string idno, string name, string deptName, string charge_time, string doorname)
  {
  SqlConnection conn = new SqlConnection("server=.;database=ljptest;uid=sa;pwd=sa");
  string sql = "select A.bh,A.names,B.datetimes,C.doorname,D.deptname from .consumes A inner join y_mj_flow B on A.idno=B.idno inner join y_mj_doors C on C.doorid=B.doorid inner join dept D on D.deptcode=A.deptcode";
  string sql1="";
  if (doorname != null)
  {
  sql1 += " or doorname='" + doorname + "'";
  }
  if (charge_time != null)
  {
  sql1 += " or B.datetimes='" + charge_time + "'";
  }

  if (name != null)
  {
  sql1 += " or A.names='" + name + "'";
  }
  if (deptName != null)
  {
  sql1 += " or C.doorname='" + deptName + "'";
  }
  if (idno != null)
  {
  sql1 += " or A.bh='" + idno + "'";
  }
  if(sql1!="")
  {
      sql+=" where "+sql1.substring(3);
  }
  SqlCommand cmd = new SqlCommand(sql, conn);
  SqlDataAdapter sda = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  sda.Fill(ds);
  return ds.Tables[0];

  }


这样吗

作者: zs621   发布时间: 2011-06-15

这样试试
[WebMethod]
  public DataTable QueryRecords(string idno, string name, string deptName, string charge_time, string doorname)
  {
  SqlConnection conn = new SqlConnection("server=.;database=ljptest;uid=sa;pwd=sa");
  string sql = "select A.bh,A.names,B.datetimes,C.doorname,D.deptname from .consumes A inner join y_mj_flow B on A.idno=B.idno inner join y_mj_doors C on C.doorid=B.doorid inner join dept D on D.deptcode=A.deptcode where 1=1 ";

  if (doorname != null)
  {
  sql += " and doorname='" + doorname + "'";
  }
  if (charge_time != null)
  {
  sql += " or B.datetimes='" + charge_time + "'";
  }

  if (name != null)
  {
  sql += " or A.names='" + name + "'";
  }
  if (deptName != null)
  {
  sql += " or C.doorname='" + deptName + "'";
  }
  if (idno != null)
  {
  sql += " or A.bh='" + idno + "'";
  }
   
  SqlCommand cmd = new SqlCommand(sql, conn);
  SqlDataAdapter sda = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  sda.Fill(ds);
  return ds.Tables[0];

  }

作者: cd731107   发布时间: 2011-06-15

[WebMethod]
  public DataTable QueryRecords(string idno, string name, string deptName, string charge_time, string doorname)
  {
  SqlConnection conn = new SqlConnection("server=.;database=ljptest;uid=sa;pwd=sa");
  string sql = "select A.bh,A.names,B.datetimes,C.doorname,D.deptname from .consumes A inner join y_mj_flow B on A.idno=B.idno inner join y_mj_doors C on C.doorid=B.doorid inner join dept D on D.deptcode=A.deptcode";
  sql+=" where 1=1 "; if (doorname != null)
  {
  sql += " and doorname='" + doorname + "'";
  }
  if (charge_time != null)
  {
  sql += " and B.datetimes='" + charge_time + "'";
  }

  if (name != null)
  {
  sql += " and A.names='" + name + "'";
  }
  if (deptName != null)
  {
  sql += " and C.doorname='" + deptName + "'";
  }
  if (idno != null)
  {
  sql += " and A.bh='" + idno + "'";
  }
   
  SqlCommand cmd = new SqlCommand(sql, conn);
  SqlDataAdapter sda = new SqlDataAdapter(cmd);
  DataSet ds = new DataSet();
  sda.Fill(ds);
  return ds.Tables[0];

  }

作者: hsboy86   发布时间: 2011-06-15