public virtual DataSet FuzzyQueries(string StoredProcedureName, string KeyWord, SqlParameter[] sqlParameters, string Sort, int PageIndex, int PageSize) { DataSet ds = new DataSet(); using (SqlCommand comm = Connection.CreateCommand()) { comm.Transaction = Transaction; comm.CommandType = CommandType.StoredProcedure; comm.CommandText = "usp_Common_Search"; StringBuilder sb = new StringBuilder(); if (sqlParameters != null) { foreach (SqlParameter item in sqlParameters) { if (sb.Length != 0) { sb.Append(","); } if (item.Value == DBNull.Value) { sb.AppendFormat("{0}={1}", item.ParameterName, "null"); } else { sb.AppendFormat("{0}='{1}'", item.ParameterName, item.Value); } } } comm.Parameters.AddWithValue("@AllKeyWords", KeyWord); comm.Parameters.AddWithValue("@uspName", StoredProcedureName); comm.Parameters.AddWithValue("@uspParas", sb.ToString()); comm.Parameters.AddWithValue("@DBConnStr", AppConfig.ConnectionString); comm.Parameters.AddWithValue("@PageIndex", PageIndex); comm.Parameters.AddWithValue("@PageSize", PageSize); if (!string.IsNullOrEmpty(Sort)) { comm.Parameters.AddWithValue("@OrderKey", Sort); } using (SqlDataAdapter adapter = new SqlDataAdapter(comm)) { adapter.Fill(ds); } } return ds; }
/****************************************************************************** ** Name: usp_Common_Search ** Desc: 搜索 ** ** ** Return Values: ** ** Parameters: ** Auth: ** Date:2008-10-21 *******************************************************************************/ ALTER proc usp_Common_Search @AllKeyWords varchar(max), @uspName varchar(500), @uspParas varchar(max)=null, @DBConnStr varchar(max), @PageIndex int = 0, --当前页索引(索引从0开始) @PageSize int = 200, --每页显示的记录数 @OrderKey varchar(300) = '__Keyword' as begin declare @sql nvarchar(max),@where nvarchar(max) declare @csr CURSOR,@word varchar(500),@temp varchar(1000); --replace chinses space set @AllKeyWords = Replace(@AllKeyWords,' ',' ') set @csr=cursor for select sValue from dbo.ufn_Split(@AllKeyWords,' ') where sValue <> ' ' open @csr fetch next from @csr into @word while(@@FETCH_STATUS <> -1) begin if @where is null or @where = '' begin set @where = '__KeyWord like ''%' + @word + '%'''; end else begin set @where = @where + ' or __KeyWord like ''%' + @word + '%'''; end fetch next from @csr into @word end --keyword is empty if len(@AllKeyWords)=0 set @where = '__Keyword like ''%%''' CLOSE @csr DEALLOCATE @csr set @uspParas = replace(@uspParas,'''',''''''); --set @DBConnStr = replace(@DBConnStr,'''',''''''); set @temp = '''SET FMTONLY OFF;set nocount on;exec ' + @uspName + ' ' + IsNull(@uspParas,'') + ' ''' set @sql = 'select * into #xxxxxx from openrowset(''SQLOLEDB'', ''' + @DBConnStr + ''',' + + @temp + ') a ;' set @sql = @sql + '; ' --以下是查询 --set @sql = @sql + 'select * from #xxxxxx where ' + @where + ' ' --print @sql --set @conn = 'SERVER=(local);uid=sa;pwd=Pass@word;Database=' + db_name() --exec sp_executesql @sql --执行第一遍取总页数;执行第二遍传入总页数,分页 declare @rtn int,@TotalNum int,@strSQLAll nvarchar(max),@strSel varchar(max),@TotalRecord int ,@ParmDefinition nvarchar(50) -- set @strSQLAll = @sql + 'select @TotalNumoutput = count(*) from #xxxxxx where ' + @where + ' -- drop table #xxxxxx' -- -- print @strSqlAll -- SET @ParmDefinition = N'@TotalNumoutput int OUTPUT'; -- EXEC dbo.sp_executesql @strSQLAll,@ParmDefinition,@TotalNumoutput=@TotalNum OUTPUT; -- -- set @TotalRecord = @TotalNum -- -- set @TotalNum = ceiling(cast(@TotalNum as float)/@PageSize) --返回记录 set @strSel = @sql + ' ; with Employee as ( SELECT a.*,ROW_NUMBER() OVER (ORDER BY ' + @OrderKey + ') AS ROW_NUMBER FROM #xxxxxx a ' declare @strSelLater varchar(300) set @strSelLater = ' ) select *,@TotalNum1 as PageCount FROM Employee WHERE ROW_NUMBER > @PageSize1/1*@PageIndex1 AND ROW_NUMBER <= @PageSize1/1*(@PageIndex1+1) ORDER BY ' + @OrderKey set @strSQLAll = @strSel + ' where ' + @where + @strSelLater + ' select count(*) TotalRecord from #xxxxxx where ' + @where + '; drop table #xxxxxx'; PRINT @strSQLAll SET @ParmDefinition = N'@PageSize1 int,@PageIndex1 int,@TotalNum1 int' EXEC dbo.sp_executesql @strSQLAll,@ParmDefinition,@PageSize1 = @PageSize,@PageIndex1 = @PageIndex,@TotalNum1= @TotalNum --select @TotalRecord TotalRecord end
/****************************************************************************** ** Name: ufn_Split ** Desc: 拆分字符串 ** ** ** Return Values: ** ** Parameters: ** Auth: ** Date:2008-10-24 *******************************************************************************/ ALTER function [dbo].[ufn_Split] ( @String varchar(max), -- 要拆分的字符串 @Key varchar(50) -- 关键字 ) returns @sValues table(sIndex int identity(1,1), sValue varchar(max) ) as begin -- 索引及当前值 declare @KeyIndex int declare @CurrentValue varchar(500) set @string = RTrim(LTrim(@String)) -- 拆分 set @KeyIndex = charindex(@Key,@string) while @KeyIndex <> 0 begin set @CurrentValue = substring(@String,1,@KeyIndex-1) insert into @sValues(sValue) values (@CurrentValue) set @String = substring(@String, @KeyIndex+1, len(@String)- @KeyIndex) set @KeyIndex = charindex(@Key, @String) end insert into @sValues(sValue) values (@String) -- 返回拆份结果 return end
/****************************************************************************** ** Name: usp_base_PersonSearch ** Desc: 搜索人员 ** ** ** Return Values: ** ** Parameters: ** Auth: ** Date:2008-10-21 usp_cfg_GetProjectFileByCode @status = 0 *******************************************************************************/ ALTER proc usp_base_PersonSearch as begin select PersonName __KeyWord,C.UserName,dbo.uf_GetOUName(a.OUID) OUName,A.* from tbl_base_PersonInfo a left join tbl_base_userMapping b on a.PersonID = b.PersonID left join tbl_base_user c on b.UserID = c.UserID end