string where = "1=1"; if (!String.IsNullOrEmpty(this.txtAreaName.Text)) { where += " and AreaName like '%" + this.txtAreaName.Text.Trim().SqlReplace() + "%'"; } if (!String.IsNullOrEmpty(this.txtCardID.Text)) { where += " and CardNum like '%" + this.txtCardID.Text.Trim().SqlReplace() + "%'"; } if (!String.IsNullOrEmpty(ddlWarnType.SelectedValue)) { where += " and Name = '" + ddlWarnType.SelectedItem.Text + "'"; }
StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(tid)) { sb.Append(" and typeid=" + tid); } if (!string.IsNullOrEmpty(cname)) { sb.Append(" and title like '%" + cname + "%'"); } Int64 total = 0; string TableName = " yjfw_info "; string ReFieldsStr = " id,InfoID,title,ifoLaiyuan,fabutime,shzt,typeid,tjzt,bzInt"; string OrderString = " bzInt desc,ID desc "; string StrWhere = " 1=1" + sb.ToString(); DataTable dt = pub.FindPageList(out total, PageIndex, 10, StrWhere, TableName, ReFieldsStr, OrderString, "ID"); RepList.DataSource = dt.DefaultView; RepList.DataBind();
StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(tid)) { sb.Append(" and typeid=" + tid); } if (!string.IsNullOrEmpty(cname)) { sb.Append(" and title like '%" + cname + "%'"); } Int64 total = 0; string TableName = " yjfw_info "; string ReFieldsStr = " id,InfoID,title,ifoLaiyuan,fabutime,shzt,typeid,tjzt,bzInt"; string OrderString = " bzInt desc,ID desc "; string StrWhere = " 1=1" + sb.ToString(); DataTable dt = pub.FindPageList(out total, PageIndex, 10, StrWhere, TableName, ReFieldsStr, OrderString, "ID"); RepList.DataSource = dt.DefaultView; RepList.DataBind();
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go--[gjss] 10,20,'a','','','','','',''ALTER procedure [dbo].[gjss] ( @startIndex int=1, @endIndex int=3286, @ProductName varchar(100)=NULL, @Inputer varchar(50)=NULL, @ProductStandard varchar(100)=NULL, @PriceOriginal varchar(20)=NULL, @ISBN varchar(20)=NULL, @CreateTimeStart varchar(20)=NULL, @CreateTimeEnd varchar(20)=NULL, @PrintNO varchar(20)=NULL, @VersionNO varchar(20)=NULL ) as SET NOCOUNT ON begin declare @start int declare @end int declare @SqlWhere varchar(500) declare @strSql varchar(1000) declare @strSql1 varchar(1000)set @start= @startIndex * @endIndex set @end =@start+@endIndex set @SqlWhere=' where 1=1 ' if @PrintNO<>'' begin set @SqlWhere=@SqlWhere+' and PrintNO = '''+@PrintNO+'''' end if @VersionNO<>'' begin set @SqlWhere=@SqlWhere+' and VersionNO = '''+@VersionNO+'''' end if @ProductName<>'' begin set @SqlWhere=@SqlWhere+' and ProductName like ''%'+@ProductName+'%''' end if @Inputer<>'' begin set @SqlWhere=@SqlWhere+' and Inputer like ''%'+@Inputer+'%''' end if @ProductStandard<>'' begin set @SqlWhere=@SqlWhere+' and ProductStandard like ''%'+@ProductStandard+'%''' end if @PriceOriginal<>'' begin set @SqlWhere=@SqlWhere+' and PriceOriginal like ''%'+@PriceOriginal+'%''' end if @ISBN<>'' begin set @SqlWhere=@SqlWhere+' and ISBN = '''+@ISBN+'''' end if @CreateTimeStart <>'' begin set @SqlWhere=@SqlWhere+' and CreateTime >= '''+@CreateTimeStart+'''' end if @CreateTimeEnd <>'' begin set @SqlWhere=@SqlWhere+' and CreateTime <= '''+@CreateTimeStart+'''' end set @strSql='select temptbl.* from ( SELECT ROW_NUMBER() OVER (ORDER BY productId asc)AS Row,*
from My_Products ' set @strSql1=') as temptbl where temptbl.Row > '+cast(@start as varchar(10)) +' and temptbl.Row<='+cast(@end as varchar(10)) set @strSql=@strSql+@SqlWhere+@strSql1 exec(@strSql) print @strSqlset @strSql='select count(productId) from My_Products '+@SqlWhere exec(@strSql) print @strSql
end
linq to sql var ft = (from t in adcd.dms_file_type select t); if(!string.IsNullOrEmpty(extname)) { ft = ft.where(t=>t.extname.Equals(extname)) }
public IQueryable<Class> Query(Class model) { var query = db.Class.AsQueryable(); if (model != null) { if (!string.IsNullOrEmpty(model.LinkText)) { query = query.Where(a=>a.LinkText.Contains(model.LinkText)); } if (0 != model.LocationID) { query = query.Where(a=>a.LocationID == model.LocationID); } if (null != model.StartDate) { query = query.Where(a=>a.StartDate >= model.StartDate); } if (null != model.EndDate) { query = query.Where(a=>a.EndDate <= model.EndDate); }
/// 根据日期,资源名称,资源描述查询资源信息
/// </summary>
/// <returns></returns>
public DataSet FN_SerchByDateAndType(Guid FolderId, NRModel.File model, string createdate, string endate)
{
string strSql = "select * from t_File where 1 =1 and FolderId=@FolderId";
string strWhere = "";
if (!string.IsNullOrEmpty(model.FileNam))
{
strWhere += " and FileNam like @FileNam";
}
//if (!string.IsNullOrEmpty(model.Decription)k)
//{
// strWhere += " and Decription like @Decription";
//}
if (!string.IsNullOrEmpty(createdate) || !string.IsNullOrEmpty(endate))
{
strWhere += " and CreateOn between @createdate and @endate order by ModefyOn desc";
}
strSql += strWhere;
SqlParameter[] parameters = {
new SqlParameter("@FolderId", SqlDbType.UniqueIdentifier),
new SqlParameter("@FileNam", SqlDbType.NVarChar, 256),
new SqlParameter("@createdate", SqlDbType.NVarChar),
new SqlParameter("@endate", SqlDbType.NVarChar)
};
parameters[0].Value = FolderId;
parameters[1].Value = "%" + model.FileNam + "%";
//parameters[1].Value = "%" + model.Decription + "%";
parameters[2].Value = createdate;
parameters[3].Value = endate;
return DbHelperSQL.Query(strSql, parameters);
//SqlParameter[] parameters = new SqlParameter[4];
//parameters[0] = new SqlParameter("@FileNam", model.FileNam);
//parameters[1] = new SqlParameter("@stardate", createdate);
//parameters[2] = new SqlParameter("@enddate", endate);
////执行存储过程
//return DbHelperSQL.RunProcedure("P_UserSerch", parameters, "t_File");
}
{
orm = new BLL_OrmObj();
this.GridView1.DataSource = orm.Select(this.txt1.Text == "" ? (int?)null : Convert.ToInt32(this.txt1.Text),this.txt2.Text == "" ? (int?)null : Convert.ToInt32(this.txt2.Text),this.txt3.Text == "" ? (int?)null : Convert.ToInt32(this.txt3.Text),this.txt4.Text == "" ? (int?)null : Convert.ToInt32(this.txt4.Text),this.txt5.Text == "" ? (int?)null : Convert.ToInt32(this.txt5.Text),this.txt6.Text == "" ? (int?)null : Convert.ToInt32(this.txt6.Text));
this.GridView1.DataBind();
}
orm.Select()是调用BLL层的方法,其中orm.Select(...) 括号里面的就是你的六个文本框的值,我上面的是把文本框的值转换成了int类型,你也可以不转换。
if (!String.IsNullOrEmpty(this.txtAreaName.Text))
{
where += " and AreaName like '%" + this.txtAreaName.Text.Trim().SqlReplace() + "%'";
}
if (!String.IsNullOrEmpty(this.txtCardID.Text))
{
where += " and CardNum like '%" + this.txtCardID.Text.Trim().SqlReplace() + "%'";
}
if (!String.IsNullOrEmpty(ddlWarnType.SelectedValue))
{
where += " and Name = '" + ddlWarnType.SelectedItem.Text + "'";
}
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(tid))
{
sb.Append(" and typeid=" + tid);
}
if (!string.IsNullOrEmpty(cname))
{
sb.Append(" and title like '%" + cname + "%'");
}
Int64 total = 0;
string TableName = " yjfw_info ";
string ReFieldsStr = " id,InfoID,title,ifoLaiyuan,fabutime,shzt,typeid,tjzt,bzInt";
string OrderString = " bzInt desc,ID desc ";
string StrWhere = " 1=1" + sb.ToString();
DataTable dt = pub.FindPageList(out total, PageIndex, 10, StrWhere, TableName, ReFieldsStr, OrderString, "ID");
RepList.DataSource = dt.DefaultView;
RepList.DataBind();
StringBuilder sb = new StringBuilder();
if (!string.IsNullOrEmpty(tid))
{
sb.Append(" and typeid=" + tid);
}
if (!string.IsNullOrEmpty(cname))
{
sb.Append(" and title like '%" + cname + "%'");
}
Int64 total = 0;
string TableName = " yjfw_info ";
string ReFieldsStr = " id,InfoID,title,ifoLaiyuan,fabutime,shzt,typeid,tjzt,bzInt";
string OrderString = " bzInt desc,ID desc ";
string StrWhere = " 1=1" + sb.ToString();
DataTable dt = pub.FindPageList(out total, PageIndex, 10, StrWhere, TableName, ReFieldsStr, OrderString, "ID");
RepList.DataSource = dt.DefaultView;
RepList.DataBind();
set QUOTED_IDENTIFIER ON
go--[gjss] 10,20,'a','','','','','',''ALTER procedure [dbo].[gjss]
(
@startIndex int=1,
@endIndex int=3286,
@ProductName varchar(100)=NULL,
@Inputer varchar(50)=NULL,
@ProductStandard varchar(100)=NULL,
@PriceOriginal varchar(20)=NULL,
@ISBN varchar(20)=NULL,
@CreateTimeStart varchar(20)=NULL,
@CreateTimeEnd varchar(20)=NULL,
@PrintNO varchar(20)=NULL,
@VersionNO varchar(20)=NULL
)
as
SET NOCOUNT ON
begin
declare @start int
declare @end int
declare @SqlWhere varchar(500)
declare @strSql varchar(1000)
declare @strSql1 varchar(1000)set @start= @startIndex * @endIndex
set @end =@start+@endIndex
set @SqlWhere=' where 1=1 '
if @PrintNO<>''
begin
set @SqlWhere=@SqlWhere+' and PrintNO = '''+@PrintNO+''''
end
if @VersionNO<>''
begin
set @SqlWhere=@SqlWhere+' and VersionNO = '''+@VersionNO+''''
end
if @ProductName<>''
begin
set @SqlWhere=@SqlWhere+' and ProductName like ''%'+@ProductName+'%'''
end
if @Inputer<>''
begin
set @SqlWhere=@SqlWhere+' and Inputer like ''%'+@Inputer+'%'''
end
if @ProductStandard<>''
begin
set @SqlWhere=@SqlWhere+' and ProductStandard like ''%'+@ProductStandard+'%'''
end
if @PriceOriginal<>''
begin
set @SqlWhere=@SqlWhere+' and PriceOriginal like ''%'+@PriceOriginal+'%'''
end
if @ISBN<>''
begin
set @SqlWhere=@SqlWhere+' and ISBN = '''+@ISBN+''''
end
if @CreateTimeStart <>''
begin
set @SqlWhere=@SqlWhere+' and CreateTime >= '''+@CreateTimeStart+''''
end
if @CreateTimeEnd <>''
begin
set @SqlWhere=@SqlWhere+' and CreateTime <= '''+@CreateTimeStart+''''
end
set @strSql='select temptbl.* from ( SELECT ROW_NUMBER() OVER (ORDER BY productId asc)AS Row,*
from My_Products '
set @strSql1=') as temptbl where temptbl.Row > '+cast(@start as varchar(10)) +' and temptbl.Row<='+cast(@end as varchar(10))
set @strSql=@strSql+@SqlWhere+@strSql1
exec(@strSql)
print @strSqlset @strSql='select count(productId) from My_Products '+@SqlWhere
exec(@strSql)
print @strSql
end
if(!string.IsNullOrEmpty(extname))
{
ft = ft.where(t=>t.extname.Equals(extname))
}
public IQueryable<Class> Query(Class model)
{
var query = db.Class.AsQueryable();
if (model != null)
{
if (!string.IsNullOrEmpty(model.LinkText))
{
query = query.Where(a=>a.LinkText.Contains(model.LinkText));
}
if (0 != model.LocationID)
{
query = query.Where(a=>a.LocationID == model.LocationID);
}
if (null != model.StartDate)
{
query = query.Where(a=>a.StartDate >= model.StartDate);
}
if (null != model.EndDate)
{
query = query.Where(a=>a.EndDate <= model.EndDate);
}
query = query.Where(a => a.IsDelete == 0);
}
return query;
}