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
