C#存储过程(草稿)

1,try
        {

            SQLLeaveWord leav = new SQLLeaveWord();

            Console.WriteLine("测试 public List<LeaveWordInfo> GetLeaWordByShop(int index, int number, int shopId) ");

            leav.GetLeaWordByShop(2,23,3);
            Console.WriteLine(leav.pageNumbers);
            Console.WriteLine("测试SQLMajor.Delete suceess!");
        }
        catch (AppException ex)
        {
            Console.WriteLine(ex.ToString());
        }

 

 

 

public List<LeaveWordInfo> GetLeaWordByShop(int index, int number, int shopId)
    {
        //调用Search存储过程
        //@PageSize int = 20,                --分页大小
        //@CurrentPage int ,                 --第几页
        //@Columns varchar(1000) = '*',      --需要得到的字段
        //@TableName varchar(100),           --需要查询的表   
        //@Condition varchar(1000) = '',     --查询条件, 不用加where关键字
        //@OrderColumn varchar(100) = '',      --排序的字段名 (即 order by column asc/desc)
        //@OrderType bit = 0,             --排序的类型 (0为升序,1为降序)
        //@PkColumn varchar(50) = ''         --主键名称
        //@TotalCount int       -------输出总数

        SqlParameter[] paramSearch ={
            new SqlParameter("@PageSize", SqlDbType.Int) ,
            new SqlParameter("@CurrentPage", SqlDbType.Int),
            new SqlParameter("@Columns", SqlDbType.VarChar,1000),
            new SqlParameter("@TableName", SqlDbType.VarChar,100),
            new SqlParameter("@Condition ", SqlDbType.VarChar,1000),
            new SqlParameter("@OrderColumn", SqlDbType.VarChar,100),
            new SqlParameter("@OrderType", SqlDbType.Bit),
            new SqlParameter("@PkColumn",SqlDbType.VarChar,50),
            new SqlParameter("@TotalCount",SqlDbType.Int)

           
        };
        paramSearch[0].Value = number;
        paramSearch[1].Value = index;
        paramSearch[2].Value = "*";
        paramSearch[3].Value = "LeaveWord";
        paramSearch[4].Value = "LeaveWord.ShopID=" +shopId;
        paramSearch[5].Value = "LeaveDate";
        paramSearch[6].Value = true;
        paramSearch[7].Value = "LeaveWordID";
        paramSearch[8].Direction = ParameterDirection.Output;
   

        SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.StoredProcedure, "Search", paramSearch);

        List<LeaveWordInfo> leaWords=new List<LeaveWordInfo>();

        pageNumbers = SQLHelper.totalCount / number;
       //对leaWords赋值
        while(sdr.Read())
        {
             LeaveWordInfo leaWord = new LeaveWordInfo();

             leaWord.LeaveWordID = Convert.ToInt32(sdr["LeaveWordID"].ToString());
             leaWord.Account = sdr["Account"].ToString();
             leaWord.LeaveDate = Convert.ToDateTime(sdr["LeaveDate"].ToString());
             leaWord.ShopID = Convert.ToInt32(sdr["ShopID"].ToString());
             leaWord.Message = sdr["Message"].ToString();

             leaWords.Add(leaWord);

        }
        sdr.Close();
 
        return leaWords;
    }
  
    /// <summary>
    /// 返回ID号为leaveWordId的留言信息
    /// </summary>
    /// <param name="leaveWordId"></param>
    /// <returns>ID号为leaveWordId的留言信息</returns>
    public LeaveWordInfo GetInfo(int leaveWordId)
    {
        //调用Search存储过程
        //@PageSize int = 20,                --分页大小
        //@CurrentPage int ,                 --第几页
        //@Columns varchar(1000) = '*',      --需要得到的字段
        //@TableName varchar(100),           --需要查询的表   
        //@Condition varchar(1000) = '',     --查询条件, 不用加where关键字
        //@OrderColumn varchar(100) = '',      --排序的字段名 (即 order by column asc/desc)
        //@OrderType bit = 0,             --排序的类型 (0为升序,1为降序)
        //@PkColumn varchar(50) = ''         --主键名称

        SqlParameter[] paramSearch ={
            new SqlParameter("@PageSize", SqlDbType.Int) ,
            new SqlParameter("@CurrentPage", SqlDbType.Int),
            new SqlParameter("@Columns", SqlDbType.VarChar,1000),
            new SqlParameter("@TableName", SqlDbType.VarChar,100),
            new SqlParameter("@Condition ", SqlDbType.VarChar,1000),
            new SqlParameter("@OrderColumn", SqlDbType.VarChar,100),
            new SqlParameter("@OrderType", SqlDbType.Bit),
            new SqlParameter("@PkColumn",SqlDbType.VarChar,50)
           
        };
        paramSearch[0].Value = 20;
        paramSearch[1].Value = 0;
        paramSearch[2].Value = "*";
        paramSearch[3].Value = "LeaveWord";
        paramSearch[4].Value = "LeaveWordID='" + leaveWordId + "'";
        paramSearch[5].Value = "LeaveDate";
        paramSearch[6].Value = true;
        paramSearch[7].Value = "LeaveWordID";

        SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.StoredProcedure, "Search", paramSearch);
        LeaveWordInfo leaWord=null;

        //对shops赋值
        while (sdr.Read())
        {
            leaWord.LeaveWordID = Convert.ToInt32( sdr["LeaveWordID"].ToString() );
            leaWord.Account = sdr["Account"].ToString();
            leaWord.LeaveDate = Convert.ToDateTime(sdr["LeaveDate"].ToString());
            leaWord.ShopID =Convert.ToInt32(sdr["ShopID"].ToString());
            leaWord.Message = sdr["Message"].ToString();

         }
     
        sdr.Close();

        return leaWord;
    }

 

 

 

--创建存储过程
ALTER PROCEDURE Search
  @PageSize int = 20,                --分页大小
  @CurrentPage int ,                 --第几页
  @Columns varchar(1000) = '*',      --需要得到的字段
  @TableName varchar(100),           --需要查询的表   
  @Condition varchar(1000) = '',     --查询条件, 不用加where关键字
  @OrderColumn varchar(100) = '',      --排序的字段名 (即 order by column asc/desc)
  @OrderType bit = 0,             --排序的类型 (0为升序,1为降序)
  @PkColumn varchar(50) = '' ,       --主键名称
  @TotalCount int  output
AS
BEGIN                                                                                    --存储过程开始
  DECLARE @strTemp varchar(300)
  DECLARE @strSql varchar(5000)         --该存储过程最后执行的语句
  DECLARE @strOrderType varchar(1000)   --排序类型语句 (order by column asc或者order by column desc)
  DECLARE @strRowCount varchar(1000)
 

  BEGIN
    IF @OrderType = 1                --降序
      BEGIN
        SET @strOrderType = ' ORDER BY '+@OrderColumn+' DESC'
        SET @strTemp = '<(SELECT min'
      END
    ELSE                                --升序
      BEGIN
        SET @strOrderType = ' ORDER BY '+@OrderColumn+' ASC'
        SET @strTemp = '>(SELECT max'
      END
       -----------返回记录总数
    if @Condition!=''
    begin
     set @strRowCount = 'select @TotalCount=count('+@PkColumn+') from ' + @TableName+' where '+@Condition
    end
   else
     begin
        set @strRowCount = 'select @TotalCount=count('+@PkColumn+') from  ' + @TableName
     end
    
    exec sp_executesql @strRowCount,N'@TotalCount int out',@TotalCount out

    IF @CurrentPage = 1            --第一页
      BEGIN
        IF @Condition != ''
          SET @strSql = 'SELECT TOP '+STR(@PageSize)+' '+@Columns+' FROM '+@TableName+
            ' WHERE '+@Condition+@strOrderType
        ELSE
          SET @strSql = 'SELECT TOP '+STR(@PageSize)+' '+@Columns+' FROM '+@TableName+@strOrderType
      END

    ELSE  IF( @CurrentPage >1)                -- 其他页
      BEGIN
        IF @Condition !=''
          SET @strSql = 'SELECT TOP '+STR(@PageSize)+' '+@Columns+' FROM '+@TableName+
          ' WHERE '+@Condition+' AND '+@PkColumn+@strTemp+'('+@PkColumn+')'+' FROM (SELECT TOP '+STR((@CurrentPage-1)*@PageSize)+
          ' '+@PkColumn+' FROM '+@TableName+' WHERE '+@Condition+@strOrderType+') AS TabTemp)'+@strOrderType  
        ELSE
          SET @strSql = 'SELECT TOP '+STR(@PageSize)+' '+@Columns+' FROM '+@TableName+
          ' WHERE '+@PkColumn+@strTemp+'('+@PkColumn+')'+' FROM (SELECT TOP '+STR((@CurrentPage-1)*@PageSize)+' '+@PkColumn+
          ' FROM '+@TableName+@strOrderType+') AS TabTemp)'+@strOrderType 
         
      END
     ---非分页查询
     ElSE
       BEGIN
        IF @Condition !=''
          SET @strSql = 'SELECT  '+@Columns+' FROM '+@TableName+' WHERE '+@Condition+' '+@strOrderType
        ELSE
          SET @strSql = 'SELECT  '+@Columns+' FROM '+@TableName+' '+@strOrderType
       END
    
  END  
    EXEC (@strSql)
   
 
END