求解析、在线等。
[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语句下面,却只能查询所有,而且就算你写了条件,查出来的也是所有数据。
原因我已经知道。但就是不知道怎么修改,达到查询所有和按条件查询的两种效果。
求解析。谢谢哈。在线等。
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 就是得到全部
.
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];
}
[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];
}
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