PDF.NET数据开发框架 之SQL-MAP使用存储过程

有关SQL-MAP的规范性介绍,请看下面的文章:

PDF.NET(PWMIS数据开发框架)之SQL-MAP目标和规范

在SQL-MAP中使用存储过程

1,存储过程的输出参数在SQL MAP 中的使用:

我们先创建一个存储过程CountUser ,它有一个输入参数和一个输出参数:

Create Procedure 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文件中生成下面的内容(可以使用工具生成):

<Select CommandName="GetCountByStroy" CommandType="StoredProcedure" Method="" Description="获取统计数据" ResultClass="ValueType">
            <![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 类型的输出参数:

CREATE  procedure [dbo].[NBF_GetZhuHeSYL]
(
 @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 的脚本书写方式,存储过程的返回值始终是整数:

public System.Object GetZhuHeSYL(String zdid  ,ref Double nowsyl   ) 
    { 
            //获取命令信息
            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 的命令配置项,改成下面的样子:

<Select CommandName="GetZhuHeSYL" CommandType="StoredProcedure" Description="获取组合收益率" ResultClass="ValueType">
<![CDATA[
NBF_GetZhuHeSYL #zdid:String,String,38,Input#,#nowsyl:Double,Decimal,18,Output,18,4#
]]></Select>

注意上面脚本中的红色部分,这样就为Decimal类型指定了查询参数的精度信息了。 注意:该特性仅仅在PDF.NET 4.0 版本受支持! 3,使用存储过程的返回值

如果你需要明确的使用存储过程的返回值(非“行结果集”),例如获取你自己定义的操作状态,则需要注意一些问题。

我们先看一个存储过程的定义:

代码
SET ANSI_NULLS ON
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脚本怎么写:

<Insert CommandName="BatchSettingReminds" Method="" CommandType="StoredProcedure" Description="批量插入提醒">
          <![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。

public partial class RemindSettingDAL
    {
        /// <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使用存储过程的问题就解决了,更为基础的示例教程,参看网友

jack_mjl 写的文章

PDF.NET框架学习篇之SQL-MAP使用存储过程

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏禹都一只猫博客

Python的flask:models.py来创建mysql数据库

1.2K9
来自专栏java系列博客

oracle 中start with ... connect by prior 子句的用法

1862
来自专栏Phoenix的Android之旅

Java 单例的五种写法

单例的目的是为了保证某个类只实例化一个对象。对于我们来说,理解这些单例写法的不同点,最好的方法是明白他们在什么情况下会失效。

1371
来自专栏Java后端技术栈

MySQL必知必会知识点总结一二

1、DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、 数据库、表、列、索引等数据库对象的定义。常用的...

1416
来自专栏运维技术迷

连仕彤博客[Python笔记] Python 一次性替换多个字符

在一个字符串中,一次性替换多个字符串,可以使用字符串的replace函数来处理(目前我想到的是这个方法,大神请指教!) 要求 把INSERT语句中的riqi、b...

3665
来自专栏北京马哥教育

10分钟学会理解和解决MySQL乱码问题

本文将详细介绍MySQL乱码的成因和具体的解决方案。在阅读本文之前,强烈建议对字符集编码概念还比较模糊的同学 阅读下博主之前对相关概念的一篇科普:十分钟搞清字符...

3088
来自专栏加米谷大数据

Hive的数据类型

本文介绍hive的数据类型,数据模型以及文件存储格式。这些知识大家可以类比关系数据库的相关知识。

1612
来自专栏影子

oracle行转列、列转行、连续日期数字实现方式及mybatis下实现方式

-- 行转列 SELECT * from ( SELECT tt1.SAP_ID,TT1.dt,TT1.EFF from ( SELECT t1.SAP...

4662
来自专栏前端儿

在PHP中使用MySQL Mysqli操作数据库 ,以及类操作方法

先来操作函数部分,普遍的MySQL 函数方法,但随着PHP5的发展,有些函数使用的要求加重了,有些则将废弃不用,有些则参数必填...

4053
来自专栏张善友的专栏

使用 SQL Server 2008 数据类型-xml 字段类型参数进行数据的批量选取或删除数据

我们经常有这样的需求,批量的删除或者选取大量的数据,有非常多的Id值,经常使用in条件查询,如果你使用拼接字符串的方式,可能遭遇SQL语句的长度限制4000个字...

2769

扫码关注云+社区

领取腾讯云代金券