create proc getMemberList
@viewTable varchar(50),--要显示的表或视图
@sqlString varchar(300), --查询语句
@viewField varchar(200),--字段(多个用正中逗号隔开)
@pagesize int, --每页大小
@showPage int --当前页
As
declare @jilu bigint
set @showPage=@showPage-1
set @jilu=@pagesize*@showPage
if (@viewField='')
begin
set @viewField='*'
end
declare @sql nvarchar(200)
set @sql=N'select top '+convert(varchar(10),@pagesize)+' '+convert(varchar(200),@viewField)+' from '+convert(varchar(50),@viewTable)
set @sql=@sql+' where id not in (select top ' +convert(varchar(10),@jilu)+ ' id from '+convert(varchar(50),@viewTable)+' where 1=1 '+@sqlString+' order by id) ' +@sqlString+' order by id '
exec sp_executesql @sql
return
public DataTable GetMemberList(string viewTable,string sqlString,string viewField,int pageSize,int showPage)
{
DataTable ds = new DataTable();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
try
{
conn = new _DB().SqlConDb();
cmd = new SqlCommand("getMemberList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@viewTable", SqlDbType.VarChar).Value = viewTable;
cmd.Parameters.Add("@sqlString", SqlDbType.VarChar).Value = sqlString.ToString();
cmd.Parameters.Add("@viewField", SqlDbType.VarChar).Value = viewField;
cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = pageSize;
cmd.Parameters.Add("@showPage", SqlDbType.Int).Value = showPage;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
conn.Dispose();
conn.Close();
}
return ds;
}
当这样调用用时就正常
GetMemberList(“表”, " and u_name like '%" + u_name + "%' ", “*“, 20, 1);
GetMemberList(“表”, " and u_name like '%" + u_name + "%' and id>20 ", “*“, 20, 1);但这样就不正常
GetMemberList(“表”, " and u_name like '%" + u_name + "%' and u_name like '%" + u_name + "%' ", “*“, 20, 1);就是说当搜索条件是单条件时,都行,但当有两个条件是字符串就不行,一个是字符串,一个是数字也行
@viewTable varchar(50),--要显示的表或视图
@sqlString varchar(300), --查询语句
@viewField varchar(200),--字段(多个用正中逗号隔开)
@pagesize int, --每页大小
@showPage int --当前页
As
declare @jilu bigint
set @showPage=@showPage-1
set @jilu=@pagesize*@showPage
if (@viewField='')
begin
set @viewField='*'
end
declare @sql nvarchar(200)
set @sql=N'select top '+convert(varchar(10),@pagesize)+' '+convert(varchar(200),@viewField)+' from '+convert(varchar(50),@viewTable)
set @sql=@sql+' where id not in (select top ' +convert(varchar(10),@jilu)+ ' id from '+convert(varchar(50),@viewTable)+' where 1=1 '+@sqlString+' order by id) ' +@sqlString+' order by id '
exec sp_executesql @sql
return
public DataTable GetMemberList(string viewTable,string sqlString,string viewField,int pageSize,int showPage)
{
DataTable ds = new DataTable();
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataAdapter da = null;
try
{
conn = new _DB().SqlConDb();
cmd = new SqlCommand("getMemberList", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@viewTable", SqlDbType.VarChar).Value = viewTable;
cmd.Parameters.Add("@sqlString", SqlDbType.VarChar).Value = sqlString.ToString();
cmd.Parameters.Add("@viewField", SqlDbType.VarChar).Value = viewField;
cmd.Parameters.Add("@pagesize", SqlDbType.Int).Value = pageSize;
cmd.Parameters.Add("@showPage", SqlDbType.Int).Value = showPage;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
cmd.Dispose();
conn.Dispose();
conn.Close();
}
return ds;
}
当这样调用用时就正常
GetMemberList(“表”, " and u_name like '%" + u_name + "%' ", “*“, 20, 1);
GetMemberList(“表”, " and u_name like '%" + u_name + "%' and id>20 ", “*“, 20, 1);但这样就不正常
GetMemberList(“表”, " and u_name like '%" + u_name + "%' and u_name like '%" + u_name + "%' ", “*“, 20, 1);就是说当搜索条件是单条件时,都行,但当有两个条件是字符串就不行,一个是字符串,一个是数字也行
报什么错?
GetMemberList(“表”, " and u_name like '%" + u_name + "%' and 2>1 and u_name like '%" + u_name + "%' ", “*“, 20, 1);
这样试一下