PDF.NET数据开发框架 之SQL-MAP使用存储过程
有关SQL-MAP的规范性介绍，请看下面的文章：
PDF.NET（PWMIS数据开发框架）之SQL-MAP目标和规范
在SQL-MAP中使用存储过程
1，存储过程的输出参数在SQL MAP 中的使用：
我们先创建一个存储过程CountUser ，它有一个输入参数和一个输出参数：
@CountNum int output,
@Field varchar(10)
AS
SELECT @CountNum=result FROM Tb_UserCount WHERE Field=@Field
if @CountNum is NULL
set @CountNum=-1
else
print @CountNum
SELECT @CountNum as MyCount
return 0
在SQL-MAP文件中生成下面的内容（可以使用工具生成）：
<![CDATA[
CountUser
#Field:String#
#CountNum:Int32,Int32,,Output#
]]>
</Select>
在SQL-MAP的命令类型属性中，请指定 CommandType ="StoredProcedure" 表示查询将使用一个存储过程。
注意：在Select，Update，Insert，Delete 配置节中都可以使用 存储过程，这里使用的是Select ，选择何种类型决定于你的存储过程类型。
例如，你的存储过程返回值是一个“行结果集”（存储过程最后一行附近是 Select field1,field2... from table...），那么在SQL-MAP配置节中使用Select；
存储过程返回值是其它值或者没有返回值，则使用Update，Insert，Delete之一，具体选择那个请根据存储过程的语义来决定。
在要执行的脚本内容中，存储过程的参数紧跟在存储过程的名字之后，可以使用空格或者换行分隔，参数之间使用“逗号”或者换行分隔，请看下面的例子也是合法的：
SQL-MAP脚本
<Select CommandName="GetZhuHeSYL" CommandType="StoredProcedure" Description="获取组合收益率" ResultClass="ValueType">
<![CDATA[
NBF_GetZhuHeSYL #zdid:String,String,38,Input#,#nowsyl:Double,Decimal,18,Output#
]]></Select>
2，使用Decimal类型输出参数的存储过程注意事项：
请看下面的存储过程， @nowsyl 是一个 decimal 类型的输出参数：
(
@zdid varchar(38),--myfundid
@nowsyl decimal(18,4) output
)
AS
Select @nowsyl = sum(NowShuhuiyingkui)/sum(NowBenjin) from TB_Product_MyFund_Open where MyFundID=@zdid
return 1
在SQL-MAP的DAL程序中，如果直接使用配置文件中命令向 GetZhuHeSYL 的脚本书写方式，存储过程的返回值始终是整数：
{
//获取命令信息
CommandInfo cmdInfo=Mapper.GetCommandInfo("GetZhuHeSYL");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = zdid;
cmdInfo.DataParameters[1].Value = nowsyl;
//参数赋值，使用命名方式；
//cmdInfo.SetParameterValue("@zdid", zdid);
//cmdInfo.SetParameterValue("@nowsyl", nowsyl);
//执行查询
CurrentDataBase.ExecuteScalar(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);
nowsyl = (double)cmdInfo.DataParameters[1].Value;
return null;
//
}//End Function
注意上面代码“红色”的部分，目前代码生成器不能自动生成处理存储过程参数返回类型参数的代码，这些代码需要你手工添加。
--------------------------------
改进措施：
Decimal 类型是一个特殊的类型，在数据库中你需要指定它的“精度”和“小数位数”，如 Decimal(14,4) 表示精度为14位，小数为4位。
经过测试，我们在ADO.NET的命令参数中，必须将参数的小数位数设置成跟数据库中一样的Decimal类型小数位数，才可以获得正确的返回值，数据的精度可以不一样。
要解决这个问题，我们有两个方案，
（1）在存储过程中使用 real 类型替代 Decimal 类型，在程序中使用 double 类型对应存储过程的参数；
（2）改写SQL-MAP 的命令配置项，改成下面的样子：
<![CDATA[
NBF_GetZhuHeSYL #zdid:String,String,38,Input#,#nowsyl:Double,Decimal,18,Output,18,4#
]]></Select>
注意上面脚本中的红色部分，这样就为Decimal类型指定了查询参数的精度信息了。
注意：该特性仅仅在PDF.NET 4.0 版本受支持！
3，使用存储过程的返回值
如果你需要明确的使用存储过程的返回值（非“行结果集”），例如获取你自己定义的操作状态，则需要注意一些问题。
我们先看一个存储过程的定义：
代码
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[Batch_SettingReminds]
-- Add the parameters for the stored procedure here
@WorkNo varchar(38)
,@CustomerIDCardList varchar(max
,@JjdmList varchar(max)
,@EventConetent varchar(max)
,@Cycle varchar(500)
,@StartDate varchar(21)
,@RateTypeList varchar(500)
,@ModelId varchar(38)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @t table
(
[StartDate] [datetime] NULL,
[NowYield] [float] NULL,
[FundCode] [varchar](10) NULL,
[TradeType] [varchar](50) NULL,
[TradeMoneyStart] [float] NULL,
[CustomerIDCard] [varchar](500) NULL,
[WorkNo] [varchar](38) NULL,
[SettingType] [int] NULL,
[SettingDate] [datetime] NULL,
[SettingState] [int] NULL,
[RemindTypeID] [varchar](38) NULL,
[RemindEventID] [varchar](38) NULL,
[Rate] [float] NULL,
[zhxgrq] [datetime] NULL
)
if(@ModelId='7886FC2E-5038-4A71-8477-121A207BD70F')
begin
insert into @t select distinct @StartDate,0,a.name,'--',0,b.name,@WorkNo,0,GETDATE(),1,@ModelId
,(select name from dbo.Split(c.name,':') where id=1)--KEY
,(select name from dbo.Split(c.name,':') where id=2)--Value
,GETDATE() from dbo.Split(@JjdmList,',') a left join dbo.Split(@CustomerIDCardList,',') b on 1=1 left join dbo.Split(@EventConetent,',') c on 1=1
insert into @t select distinct @StartDate,0,a.name,'--',0,b.name,@WorkNo,0,GETDATE(),1,@ModelId,c.name,0,GETDATE() from dbo.Split(@JjdmList,',') a left join dbo.Split(@CustomerIDCardList,',') b on 1=1 left join dbo.Split(@Cycle,',') c on 1=1
insert into @t select distinct @StartDate,0,a.name,'--',0,b.name,@WorkNo,0,GETDATE(),1,@ModelId
,(select name from dbo.Split(c.name,':') where id=1)--KEY
,(select name from dbo.Split(c.name,':') where id=2)--Value
,GETDATE() from dbo.Split(@JjdmList,',') a left join dbo.Split(@CustomerIDCardList,',') b on 1=1 left join dbo.Split(@RateTypeList,',') c on 1=1
insert into [WFT_RemindSetting] select NEWID(),* from @t
return @@rowcount
end
if(@ModelId='359A80EF-6769-401E-97A8-2EEEAE3C61C7')
begin
declare @t_1 table
(
CustomerIDCard varchar(500)
,JJDM varchar(10)
,BuyDate varchar(21)
)
insert into @t_1 select a.name,c.JJDM,@StartDate from dbo.Split(@CustomerIDCardList,',') a left join WFT_Customer b on a.name =b.CustomerIDcard left join WFT_CustomerFundTrade c on b.FundAccount=c.FundAccount where c.NowLot>0
if(@StartDate='1900-01-01')
begin
update @t_1 set BuyDate= (select top 1 CONVERT(VARCHAR,x.TradeDate,23) from WFT_CustomerFundTradeDetails x left join WFT_Customer y on x.FundAccount=y.FundAccount where x.JJDM=JJDM and y.CustomerIDcard=CustomerIDcard and x.IsHistory='0' order by x.TradeDate)
end
insert into @t select a.BuyDate,0,a.JJDM,'--',0,a.CustomerIDCard,@WorkNo,0,GETDATE(),1,@ModelId
,(select name from dbo.Split(c.name,':') where id=1)--KEY
,(select name from dbo.Split(c.name,':') where id=2)--Value
,GETDATE() from @t_1 a left join dbo.Split(@RateTypeList,',') c on 1=1
insert into [WFT_RemindSetting] select NEWID(),* from @t
return @@rowcount
end
if(@ModelId='C9D578B8-17D4-43A8-84B4-EB1BD44D8D9A')
begin
insert into @t select distinct @StartDate,0,'--','--',0,b.name,@WorkNo,0,GETDATE(),1,@ModelId
,(select name from dbo.Split(c.name,':') where id=1)--KEY
,(select name from dbo.Split(c.name,':') where id=2)--Value
,GETDATE() from dbo.Split(@CustomerIDCardList,',') b left join dbo.Split(@RateTypeList,',') c on 1=1
insert into @t select distinct @StartDate,0,'--','--',0,b.name,@WorkNo,0,GETDATE(),1,@ModelId
,d.name
,0
,GETDATE() from dbo.Split(@CustomerIDCardList,',') b left join dbo.Split(@Cycle,',') d on 1=1
insert into [WFT_RemindSetting] select NEWID(),* from @t
return @@rowcount
end
-- Insert statements for procedure here
END
这是一个复杂的存储过程，中间有多个Insert 语句，而且在存储过程开头使用了SET NOCOUNT ON 语法，所以存储过程不会返回操作受影响的行数，但是存储过程中已经明确写了下面的返回值语句：
return @@rowcount
所以我们需要一个“返回值”参数，但这个参数名并没有定义，没关系我们随便用一个名字即可。我们先看看这个存储过程对应的SQL-MAP脚本怎么写：
<![CDATA[
[Batch_SettingReminds]
#WorkNo:String#,
#CustomerIDCardList:String#,
#JjdmList:String#,
#EventConetent:String#,
#Cycle:String#,
#StartDate:String#,
#RateTypeList:String#,
#ModelId:String#,
#result:Int32,Int32,,ReturnValue#
]]>
</Insert>
</Insert>
请注意参数 result 的定义，它是一个整数类型，存储过程的输出类型是 ReturnValue 。
我们使用代码生成器来生成上面的代码，请注意目前代码生成器还没有这么“智能”的处理这类问题，所以需要你手工修改一下代码：
///// <summary>
///// 批量插入提醒
///// </summary>
///// <param name="WorkNo"></param>
///// <param name="CustomerIDCardList"></param>
///// <param name="JjdmList"></param>
///// <param name="EventConetent"></param>
///// <param name="Cycle"></param>
///// <param name="StartDate"></param>
///// <param name="RateTypeList"></param>
///// <param name="ModelId"></param>
///// <param name="result"></param>
///// <returns></returns>
//public Int32 BatchSettingReminds(String WorkNo , String CustomerIDCardList , String JjdmList , String EventConetent , String Cycle , String StartDate , String RateTypeList , String ModelId ,int result )
//{
// //获取命令信息
// CommandInfo cmdInfo=Mapper.GetCommandInfo("BatchSettingReminds");
// //参数赋值，推荐使用该种方式；
// cmdInfo.DataParameters[0].Value = WorkNo;
// cmdInfo.DataParameters[1].Value = CustomerIDCardList;
// cmdInfo.DataParameters[2].Value = JjdmList;
// cmdInfo.DataParameters[3].Value = EventConetent;
// cmdInfo.DataParameters[4].Value = Cycle;
// cmdInfo.DataParameters[5].Value = StartDate;
// cmdInfo.DataParameters[6].Value = RateTypeList;
// cmdInfo.DataParameters[7].Value = ModelId;
// cmdInfo.DataParameters[8].Value = result;
// //参数赋值，使用命名方式；
// //cmdInfo.SetParameterValue("@WorkNo", WorkNo);
// //cmdInfo.SetParameterValue("@CustomerIDCardList", CustomerIDCardList);
// //cmdInfo.SetParameterValue("@JjdmList", JjdmList);
// //cmdInfo.SetParameterValue("@EventConetent", EventConetent);
// //cmdInfo.SetParameterValue("@Cycle", Cycle);
// //cmdInfo.SetParameterValue("@StartDate", StartDate);
// //cmdInfo.SetParameterValue("@RateTypeList", RateTypeList);
// //cmdInfo.SetParameterValue("@ModelId", ModelId);
// //cmdInfo.SetParameterValue("@result", result);
// return CurrentDataBase.ExecuteNonQuery(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);
// //
//}//End Function
上面的代码可以正确的执行，但是我们调用方法以后，没有获得结果 result ，因为它是值类型，需要明确标注成引用类型，这里我们修改一下上面代码，让方法直接返回这个result。
{
/// <summary>
/// 批量插入提醒
/// </summary>
/// <param name="WorkNo"></param>
/// <param name="CustomerIDCardList"></param>
/// <param name="JjdmList"></param>
/// <param name="EventConetent"></param>
/// <param name="Cycle"></param>
/// <param name="StartDate"></param>
/// <param name="RateTypeList"></param>
/// <param name="ModelId"></param>
/// <param name="result"></param>
/// <returns></returns>
public Int32 BatchSettingReminds(String WorkNo, String CustomerIDCardList, String JjdmList, String EventConetent, String Cycle, String StartDate, String RateTypeList, String ModelId)
{
int result = 0;
//获取命令信息
CommandInfo cmdInfo = Mapper.GetCommandInfo("BatchSettingReminds");
//参数赋值，推荐使用该种方式；
cmdInfo.DataParameters[0].Value = WorkNo;
cmdInfo.DataParameters[1].Value = CustomerIDCardList;
cmdInfo.DataParameters[2].Value = JjdmList;
cmdInfo.DataParameters[3].Value = EventConetent;
cmdInfo.DataParameters[4].Value = Cycle;
cmdInfo.DataParameters[5].Value = StartDate;
cmdInfo.DataParameters[6].Value = RateTypeList;
cmdInfo.DataParameters[7].Value = ModelId;
cmdInfo.DataParameters[8].Value = result;//这个是返回值参数
//cmdInfo.SetParameterValue("@WorkNo", WorkNo);
//cmdInfo.SetParameterValue("@CustomerIDCardList", CustomerIDCardList);
//cmdInfo.SetParameterValue("@JjdmList", JjdmList);
//cmdInfo.SetParameterValue("@EventConetent", EventConetent);
//cmdInfo.SetParameterValue("@Cycle", Cycle);
//cmdInfo.SetParameterValue("@StartDate", StartDate);
//cmdInfo.SetParameterValue("@RateTypeList", RateTypeList);
//cmdInfo.SetParameterValue("@ModelId", ModelId);
//cmdInfo.SetParameterValue("@result", result);
//下面的代码需要手工修改
//执行查询
int count = CurrentDataBase.ExecuteNonQuery(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters);
result = (int)cmdInfo.DataParameters[8].Value;
return result;
//
}//End Function
}
我们使用partial class 类文件方式，可以确保代码生成器不会覆盖了我们手工修改的代码。
这样，存储过程使用返回值参数的问题也解决了。
------------------------------------------------------
到此为止，有关SQL-MAP使用存储过程的问题就解决了，更为基础的示例教程，参看网友
