带参数方法和存储过程

调用方法：

 ds = an00021010l.getRiverData(strCz, strHl, strWaterItems, strFrequency, AspNetPager1.PageSize, page, intStartTime, intEndTime, out total);

方法：

public DataSet getRiverData(string stcode, string RCODE, string itemStr, string frequency, int PageSize, int PageIndex, int startTime, int endTime,out int total )         {             DataConnection dtCon = new DataConnection();             dtCon.OpenConnection();             total = 0;             try             {                 SqlCommand cmd = new SqlCommand("Pro_RiverData_query", dtCon.Connection);                 cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@stcode", stcode);                 cmd.Parameters.Add("@RCODE", RCODE);                 cmd.Parameters.Add("@itemStr", itemStr);                 cmd.Parameters.Add("@frequency", frequency);                 cmd.Parameters.Add("@PageSize", PageSize);                 cmd.Parameters.Add("@PageIndex", PageIndex);                 cmd.Parameters.Add("@lQssj", startTime);                 cmd.Parameters.Add("@lZzsj", endTime);                 SqlParameter prmName = new SqlParameter("@total", SqlDbType.Int, 4);                 prmName.Direction = ParameterDirection.Output;                 cmd.Parameters.Add(prmName);                 SqlDataAdapter da = new SqlDataAdapter(cmd);                 DataSet ds = new DataSet();                 da.Fill(ds);                 total = Convert.ToInt16(prmName.Value);                 return ds;             }             catch (Exception ex)             {                 ex.Message.ToString();                 return null;             }             finally             {                 dtCon.CloseConnection();             }         }

存储过程： /****************************************************************** * 名称: AN00021010L * 作者: 卢明田 * 时间: 2008-5-8 * 实现功能： *   河流信息查询 * ----------------------------------------------------------------- * 版本  时间   作者  备注 * * V1.00  2008-5-28     卢明田  创建 * ----------------------------------------------------------------- ******************************************************************/ ALTER PROCEDURE [dbo].[Pro_RiverData_query] @stcode varchar(80), --测站代码字符串 @RCODE  varchar(80), --河流代码字符串 @itemStr varchar(500), --指标字符串 @frequency varchar(10), --数据类别 @PageSize   int = 10,   -- 页尺寸 @PageIndex  int = 1, -- 页码 @lQssj int,--起始时间 @lZzsj int, --终止时间 @total int Output AS BEGIN DECLARE @sql VARCHAR(4000) DECLARE @tj nvarchar(1000) --条件 DECLARE @tempsql nvarchar(1000)   set @sql='SELECT TOP '+convert(nvarchar,@PageSize)+' dbo.T_Bas_StIndex.StName as 测站名称, dbo.T_Cod_River.RNAME as 河流名称, dbo.T_Bas_RiverData.YE as 年, dbo.T_Bas_RiverData.MON as 月, dbo.T_Bas_RiverData.[DAY]as 日,                       '+@itemStr+',dbo.T_Bas_RiverData.frequency as 数据类型 from dbo.T_Bas_RiverData left JOIN                       dbo.T_Bas_StIndex ON dbo.T_Bas_RiverData.STCODE = dbo.T_Bas_StIndex.STCODE and dbo.T_Bas_RiverData.ye = dbo.T_Bas_StIndex.ye left JOIN                       dbo.T_Cod_River ON dbo.T_Bas_RiverData.STCODE = dbo.T_Cod_River.STCODE  and dbo.T_Bas_RiverData.ye = dbo.T_Cod_River.ye  where (((T_Bas_RiverData.ye*100+dbo.T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)>='+CONVERT(CHAR,@lQssj) + ' and ((T_Bas_RiverData.ye*100+T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)<='+CONVERT(CHAR,@lZzsj)+')'

--测站代码字符串 if @stcode='' begin   SET @tj=' AND 1=1' end else begin   SET @tj=' AND CHARINDEX(dbo.T_Bas_RiverData.STCODE,'''+@stcode+''')>0' end --河流代码字符串 if @RCODE='' begin   SET @tj=@tj+' AND 1=1' end else begin   SET @tj=@tj+' AND CHARINDEX(dbo.T_Bas_RiverData.RCODE,'''+@RCODE+''')>0' end --数据类别 if @frequency='' begin   set @tj= @tj+' AND 1=1' end else begin  SET @tj=@tj+' AND dbo.T_Bas_RiverData.frequency='''+@frequency+'''' end

------返回 总行数  set @tempsql='SELECT @total=count(*)  from dbo.T_Bas_RiverData left JOIN                       dbo.T_Bas_StIndex ON dbo.T_Bas_RiverData.STCODE = dbo.T_Bas_StIndex.STCODE and dbo.T_Bas_RiverData.ye = dbo.T_Bas_StIndex.ye left JOIN                       dbo.T_Cod_River ON dbo.T_Bas_RiverData.STCODE = dbo.T_Cod_River.STCODE  and dbo.T_Bas_RiverData.ye = dbo.T_Cod_River.ye  where (((T_Bas_RiverData.ye*100+dbo.T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)>='+CONVERT(CHAR,@lQssj) + ' and ((T_Bas_RiverData.ye*100+T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)<='+CONVERT(CHAR,@lZzsj)+')'

set @tempsql=@tempsql+@tj --print @tempsql exec  sp_executesql @tempsql,N'@total int output',@total output

if @PageIndex != 1 begin  SET @tj=@tj+' AND dbo.T_Bas_RiverData.id >(select max (id) from (select top (('+convert(nvarchar,@PageIndex)+'-1)*'+convert(nvarchar,@PageSize)+') id from dbo.T_Bas_RiverData left JOIN                       dbo.T_Bas_StIndex ON dbo.T_Bas_RiverData.STCODE = dbo.T_Bas_StIndex.STCODE and dbo.T_Bas_RiverData.ye = dbo.T_Bas_StIndex.ye left JOIN                       dbo.T_Cod_River ON dbo.T_Bas_RiverData.STCODE = dbo.T_Cod_River.STCODE  and dbo.T_Bas_RiverData.ye = dbo.T_Cod_River.ye  where (((T_Bas_RiverData.ye*100+dbo.T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)>='+CONVERT(CHAR,@lQssj) + ' and ((T_Bas_RiverData.ye*100+T_Bas_RiverData.mon)*100+T_Bas_RiverData.day)<='+CONVERT(CHAR,@lZzsj)+')  '+@tj+' order by dbo.T_Bas_RiverData.id ) as T ) order by dbo.T_Bas_RiverData.id'  end

set @sql=@sql+@tj print @sql exec(@sql) END