多表联合查询sql存储过程带分页

最新推荐文章于 2021-01-28 08:37:13 发布

mofijeck

最新推荐文章于 2021-01-28 08:37:13 发布

阅读量3.5k

点赞数 1

分类专栏： C# 文章标签：数据库编程 sql c# 分页

本文链接：https://blog.csdn.net/mofijeck/article/details/44060283

版权

C# 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

note：分页可能不是特别好，把搜索的结果插入到临时表里面，然后再进行分页，感觉速度会慢，但是不知道怎么解决

sql代码：

USE [opussys_db]
GO
/****** Object:  StoredProcedure [dbo].[proc_DataSearch]    Script Date: 2015-03-04 14:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Mofijeck>
-- Create date: <2015-03-04>
-- Description:	<三张表联合搜索>
-- =============================================
ALTER PROCEDURE [dbo].[proc_DataSearch]
	-- Add the parameters for the stored procedure here
	(
	@words nvarchar(200) ,
	@type int ,
	@startIndex int ,
	@endIndex int
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	create table #Tmp  --创建临时表#Tmp
    (
      ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
	  searchid int,      
	  updateTime datetime,	  
	  stype int,
      primary key (ID)      --定义ID为临时表#Tmp的主键      
    )
	truncate table #Tmp


	if @type=0
	begin
	--新闻
	insert into #Tmp 
	select id,creattime,1 from table1 where col like '%'+rtrim(@words)+'%'
	--end
	--作家
	insert into #Tmp
	select id,creattime,2 from table2 where col like '%'+rtrim(@words)+'%'
	--end
	--作品
	insert into #Tmp
	select id,creattime,3 from table3 where col like '%'+rtrim(@words)+'%'
    --end
	end

	if @type=1
	begin
	--新闻
	insert into #Tmp 
	select id,creattime,@type from table1 where col like '%'+rtrim(@words)+'%'
	end

	if @type=2
	begin
	--作家
	insert into #Tmp
	select id,creatTime,@type from table2 where col like '%'+rtrim(@words)+'%'
	end

	if @type=3
	begin
	--作品
	insert into #Tmp
	select id,creattime,@type from table3 where col like '%'+rtrim(@words)+'%'
	end
	

	
	--返回结果
	SELECT * FROM (
	   SELECT ROW_NUMBER() OVER (
	      order by T.updateTime desc
	   ) AS Row, T.*  from #Tmp T
	) as TT where TT.Row between  @startIndex and @endIndex

	--计算记录数
    declare @count int
    select @count=count(ID) from #Tmp
    return @count

    -- Insert statements for procedure here
	
END

C#获取返回结果值和结果集：

#region
        /// <summary>
        /// 
        /// </summary>
        /// <param name="words">关键词 </param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DataSet searchByWords(string words, int type,int startIndex,int endIndex) { 
            IDataParameter[] parameter ={
                                            new SqlParameter("@words",SqlDbType.NVarChar,200),
                                            new SqlParameter("@type",SqlDbType.Int,4),
                                            new SqlParameter("@startIndex",SqlDbType.Int,4),
                                            new SqlParameter("@endIndex",SqlDbType.Int,4)
                                       };
            parameter[0].Value = words;
            parameter[1].Value = type;
            parameter[2].Value = startIndex;
            parameter[3].Value = endIndex;
            DataSet ds = DbHelperSQL.RunProcedure("proc_DataSearch", parameter, "search");
            return ds;
        }
        /// <summary>
        /// 返回结果
        /// </summary>
        /// <param name="words"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public static int searchByWordsCount(string words, int type, int startIndex, int endIndex)
        {
            IDataParameter[] parameter ={
                                            new SqlParameter("@words",SqlDbType.NVarChar,200),
                                            new SqlParameter("@type",SqlDbType.Int,4),
                                            new SqlParameter("@startIndex",SqlDbType.Int,4),
                                            new SqlParameter("@endIndex",SqlDbType.Int,4)
                                       };
            parameter[0].Value = words;
            parameter[1].Value = type;
            parameter[2].Value = startIndex;
            parameter[3].Value = endIndex;
            int results = 0;
            int returnCount = DbHelperSQL.RunProcedure("proc_DataSearch", parameter, out results);
            return returnCount;
        }
        #endregion