正文

asp.net C#操作存储过程读取存储过程输出参数值

IP云
IP云V管理员/5 阅读
09/02
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

这段时间在做一个价格平台的项目时候，同事让我写一个存储过程。该存储过程是根据查询条件得出一组新数据，并且返回该组数据的总条数，此处的存储过程我用到了分页，其中主要知识点和难点是之前做项目的时候没有用到过存储过程输出参数，更没有在C#调用存储过程的时候得到输出参数的值，因此在网上搜了一会很快找到答案。

此处难点一：输出参数的定义

-- Add the parameters for the stored procedure here
@title varchar(100),
@pageindex int,
@pagesize int,
@result_value int out -- 输入出参数

难点二：在数据库中执行（exec）存储过程输出参数怎么用

--首先定义输出参数
DECLARE @result_value int
exec [searchdata] '三',2,2,@result_value output
--搜索输出参数值
SELECT @result_value

难点三：改造数据库操作类

在下面的操作方法参数中定义了一个out类型的输出参数，目的是在调用下面的方法的时候给数据总条数赋值。

/// <summary>
/// 执行存储过程取得数据
/// </summary>
/// <param name="storeName">存储过程名字</param>
/// <param name="parameters">存储过程参数</param>
/// <returns></returns>
public static DataTable ExecuteStoredPro(string storeName, out int resultcount, string title, int pageindex, int pagesize)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = storeName;
SqlParameter[] para ={
new SqlParameter("@title",SqlDbType.Int),
new SqlParameter("@pageindex",SqlDbType.Int),
new SqlParameter("@pagesize",SqlDbType.Int),
new SqlParameter("@result_value",SqlDbType.Int) }; para[].Value = title;
para[].Value = pageindex;
para[].Value = pagesize;
para[].Direction = ParameterDirection.Output; //设定参数的输出方向 cmd.Parameters.AddRange(para);// 将参数加入命令对象
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt); resultcount = Convert.ToInt32(cmd.Parameters[].Value); return dt; }
} }

总结：技术难点分解完成顺便把存储过程公布一下

ALTER PROCEDURE [dbo].[searchdata]
-- Add the parameters for the stored procedure here
@title varchar(100),
@pageindex int,
@pagesize int,
@result_value int out -- 输入出参数
AS
BEGIN
declare @resultcount int
--创建临时数据库表开始
if exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[#tmp_linshi]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
drop table #tmp_linshi
end
else
begin
CREATE TABLE #tmp_linshi (id int identity(1,1),nid int,channel_id int,company_id int,title varchar(150),retail_price nvarchar(20))
end
--创建临时数据库表结束
--超市商品数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,retail_price from dbo.eazy_life where title like '%'+@title+'%'
--家用电气数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,retail_price from dbo.eazy_appliances where title like '%'+@title+'%'
--汽车数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,retail_price from dbo.eazy_car where title like '%'+@title+'%'
--农贸数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_farm where title like '%'+@title+'%'
--医药数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,retail_price from dbo.eazy_medicine where title like '%'+@title+'%'
--客运数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_amtrack where title like '%'+@title+'%'
--景点数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_scenic where title like '%'+@title+'%'
--旅行社数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_travel where title like '%'+@title+'%'
--酒店住宿数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_hotel where title like '%'+@title+'%'
--文化娱乐数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_culture where title like '%'+@title+'%'
--餐饮美食数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,price from dbo.eazy_food where title like '%'+@title+'%'
--电力电缆数据查询
insert into #tmp_linshi (nid,channel_id,company_id,title,retail_price) select id,channel_id,company_id,title,retail_price from dbo.eazy_electric where title like '%'+@title+'%'
select @result_value=COUNT(*) from #tmp_linshi
select * from (select *,ROW_NUMBER() over(order by id) rownum from #tmp_linshi) t where t.rownum>=(@pageindex-1)*@pagesize+1 and t.rownum<=@pageindex*@pagesize
END