[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语句下面,却只能查询所有,而且就算你写了条件,查出来的也是所有数据。
原因我已经知道。但就是不知道怎么修改,达到查询所有和按条件查询的两种效果。
求解析。谢谢哈。在线等。
set @id=1
--set @id=nullselect * from tablename where id=isnull(@id,id)
--有id就判断id id为null 就是得到全部
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]; }这样吗
[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]; }
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]; }