PDF.NET數(shù)據(jù)開發(fā)框架之SQL-MAP使用存儲過程

SQL-MAP的目標(biāo)就是：集中管理SQL語句，所有SQL語句放在專門的配置文件中進行管理；通過替換SQL配置文件，達(dá)到平滑切換數(shù)據(jù)庫到另外一個數(shù)據(jù)庫，比如從Oracle的應(yīng)用移植到SQLSERVER；由DBA來寫程序，對于復(fù)雜的查詢，DBA寫的SQL語句和存儲過程更有保障更有效率，SQL-MAP工具讓DBA也能夠?qū)?NET程序；代碼自動生成，由于在SQL配置文件中指定了很多編程特性，所以可以使用專用工具將配置文件映射到.NET代碼；

有關(guān)SQL-MAP的規(guī)范性介紹，請看下面的文章：

PDF.NET（PWMIS數(shù)據(jù)開發(fā)框架）之SQL-MAP目標(biāo)和規(guī)范

在SQL-MAP中使用存儲過程

1，存儲過程的輸出參數(shù)在SQL MAP 中的使用：

我們先創(chuàng)建一個存儲過程CountUser ，它有一個輸入?yún)?shù)和一個輸出參數(shù)：

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文件中生成下面的內(nèi)容（可以使用工具生成）：

在SQL-MAP的命令類型屬性中，請指定 CommandType ="StoredProcedure" 表示查詢將使用一個存儲過程。

注意：在Select，Update，Insert，Delete 配置節(jié)中都可以使用存儲過程，這里使用的是Select ，選擇何種類型決定于你的存儲過程類型。

例如，你的存儲過程返回值是一個“行結(jié)果集”（存儲過程最后一行附近是 Select field1,field2... from table...），那么在SQL-MAP配置節(jié)中使用Select；

存儲過程返回值是其它值或者沒有返回值，則使用Update，Insert，Delete之一，具體選擇那個請根據(jù)存儲過程的語義來決定。

在要執(zhí)行的腳本內(nèi)容中，存儲過程的參數(shù)緊跟在存儲過程的名字之后，可以使用空格或者換行分隔，參數(shù)之間使用“逗號”或者換行分隔，請看下面的例子也是合法的：

SQL-MAP腳本

2，使用Decimal類型輸出參數(shù)的存儲過程注意事項：

請看下面的存儲過程， @nowsyl 是一個 decimal 類型的輸出參數(shù)：

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 的腳本書寫方式，存儲過程的返回值始終是整數(shù)：

public System.Object GetZhuHeSYL(String zdid  ,ref Double nowsyl   )
    {
            //獲取命令信息
            CommandInfo cmdInfo=Mapper.GetCommandInfo("GetZhuHeSYL");
            //參數(shù)賦值，推薦使用該種方式；
            cmdInfo.DataParameters[0].Value = zdid;
            cmdInfo.DataParameters[1].Value = nowsyl;
            //參數(shù)賦值，使用命名方式；
            //cmdInfo.SetParameterValue("@zdid", zdid);
            //cmdInfo.SetParameterValue("@nowsyl", nowsyl);
            //執(zhí)行查詢
            CurrentDataBase.ExecuteScalar(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);
            nowsyl = (double)cmdInfo.DataParameters[1].Value;
            return null;
        //
    }//End Function

注意上面代碼“紅色”的部分，目前代碼生成器不能自動生成處理存儲過程參數(shù)返回類型參數(shù)的代碼，這些代碼需要你手工添加。

--------------------------------
改進措施：
Decimal 類型是一個特殊的類型，在數(shù)據(jù)庫中你需要指定它的“精度”和“小數(shù)位數(shù)”，如 Decimal(14,4) 表示精度為14位，小數(shù)為4位。
經(jīng)過測試，我們在ADO.NET的命令參數(shù)中，必須將參數(shù)的小數(shù)位數(shù)設(shè)置成跟數(shù)據(jù)庫中一樣的Decimal類型小數(shù)位數(shù)，才可以獲得正確的返回值，數(shù)據(jù)的精度可以不一樣。
要解決這個問題，我們有兩個方案，
（1）在存儲過程中使用 real 類型替代 Decimal 類型，在程序中使用 double 類型對應(yīng)存儲過程的參數(shù)；
（2）改寫SQL-MAP 的命令配置項，改成下面的樣子：

注意上面腳本中的紅色部分，這樣就為Decimal類型指定了查詢參數(shù)的精度信息了。
注意：該特性僅僅在PDF.NET 4.0 版本受支持！

3，使用存儲過程的返回值

如果你需要明確的使用存儲過程的返回值（非“行結(jié)果集”），例如獲取你自己定義的操作狀態(tài)，則需要注意一些問題。

我們先看一個存儲過程的定義：

代碼

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

這是一個復(fù)雜的存儲過程，中間有多個Insert 語句，而且在存儲過程開頭使用了SET NOCOUNT ON 語法，所以存儲過程不會返回操作受影響的行數(shù)，但是存儲過程中已經(jīng)明確寫了下面的返回值語句：

return @@rowcount

所以我們需要一個“返回值”參數(shù)，但這個參數(shù)名并沒有定義，沒關(guān)系我們隨便用一個名字即可。我們先看看這個存儲過程對應(yīng)的SQL-MAP腳本怎么寫：

請注意參數(shù) result 的定義，它是一個整數(shù)類型，存儲過程的輸出類型是 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");
    //        //參數(shù)賦值，推薦使用該種方式；
    //        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;
    //        //參數(shù)賦值，使用命名方式；
    //        //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

上面的代碼可以正確的執(zhí)行，但是我們調(diào)用方法以后，沒有獲得結(jié)果 result ，因為它是值類型，需要明確標(biāo)注成引用類型，這里我們修改一下上面代碼，讓方法直接返回這個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");
            //參數(shù)賦值，推薦使用該種方式；
            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;//這個是返回值參數(shù)

            //參數(shù)賦值，使用命名方式；
            //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);
            //下面的代碼需要手工修改
            //執(zhí)行查詢
            int count = CurrentDataBase.ExecuteNonQuery(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText, cmdInfo.DataParameters);
            result = (int)cmdInfo.DataParameters[8].Value;
            return result;
            //
        }//End Function
    }

我們使用partial class 類文件方式，可以確保代碼生成器不會覆蓋了我們手工修改的代碼。

這樣，存儲過程使用返回值參數(shù)的問題也解決了。

------------------------------------------------------

到此為止，有關(guān)SQL-MAP使用存儲過程的問題就解決了，更為基礎(chǔ)的示例教程，參看網(wǎng)友

jack_mjl 寫的文章

PDF.NET框架學(xué)習(xí)篇之SQL-MAP使用存儲過程

posted on 2010-07-03 23:31 深藍(lán)醫(yī)生閱讀(4114) 評論(4) 收藏舉報

刷新頁面返回頂部

深藍(lán)醫(yī)生

PDF.NET數(shù)據(jù)開發(fā)框架之SQL-MAP使用存儲過程

PDF.NET框架學(xué)習(xí)篇之SQL-MAP使用存儲過程

導(dǎo)航

公告

深藍(lán)醫(yī)生

PDF.NET數(shù)據(jù)開發(fā)框架 之SQL-MAP使用存儲過程

PDF.NET框架學(xué)習(xí)篇之SQL-MAP使用存儲過程

導(dǎo)航

公告

PDF.NET數(shù)據(jù)開發(fā)框架之SQL-MAP使用存儲過程