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

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

阅读量：4322 次

发布时间：2019-06-06

本文共 11941 字，大约阅读时间需要 39 分钟。

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

在SQL-MAP中使用存储过程

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

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

Create

Procedure

CountUser

@CountNum

int

output,

@Field

varchar

(

)

SELECT

@CountNum

result

FROM

Tb_UserCount

WHERE

Field

@Field

@CountNum

NULL

set

@CountNum

else

@CountNum

SELECT

@CountNum

MyCount

return

在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

(

myfundid

@nowsyl

decimal

(

) output

)

Select

@nowsyl

sum

(NowShuhuiyingkui)

sum

(NowBenjin)

from

TB_Product_MyFund_Open

where

MyFundID

@zdid

return

在SQL-MAP的DAL程序中，如果直接使用配置文件中命令向 GetZhuHeSYL 的脚本书写方式，存储过程的返回值始终是整数：

public

System.Object GetZhuHeSYL(String zdid ,

ref

Double nowsyl )

{

获取命令信息

CommandInfo cmdInfo

Mapper.GetCommandInfo(

GetZhuHeSYL

);

参数赋值，推荐使用该种方式；

cmdInfo.DataParameters[

].Value

zdid;

cmdInfo.DataParameters[

].Value

nowsyl;

参数赋值，使用命名方式；

cmdInfo.SetParameterValue("@zdid", zdid);

cmdInfo.SetParameterValue("@nowsyl", nowsyl);

执行查询

CurrentDataBase.ExecuteScalar(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);

nowsyl

(

double

)cmdInfo.DataParameters[

].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

SET

QUOTED_IDENTIFIER

Create

PROCEDURE

[

dbo

]

[

Batch_SettingReminds

]

Add the parameters for the stored procedure here

@WorkNo

varchar

(

)

@CustomerIDCardList

varchar

(

max

@JjdmList

varchar

(

max

)

@EventConetent

varchar

(

max

)

@Cycle

varchar

(

500

)

@StartDate

varchar

(

)

@RateTypeList

varchar

(

500

)

@ModelId

varchar

(

)

BEGIN

SET NOCOUNT ON added to prevent extra result sets from

interfering with SELECT statements.

SET

NOCOUNT

;

declare

table

(

[

StartDate

]

[

datetime

]

NULL

[

NowYield

]

[

float

]

NULL

[

FundCode

]

[

varchar

]

(

)

NULL

[

TradeType

]

[

varchar

]

(

)

NULL

[

TradeMoneyStart

]

[

float

]

NULL

[

CustomerIDCard

]

[

varchar

]

(

500

)

NULL

[

WorkNo

]

[

varchar

]

(

)

NULL

[

SettingType

]

[

int

]

NULL

[

SettingDate

]

[

datetime

]

NULL

[

SettingState

]

[

int

]

NULL

[

RemindTypeID

]

[

varchar

]

(

)

NULL

[

RemindEventID

]

[

varchar

]

(

)

NULL

[

Rate

]

[

float

]

NULL

[

zhxgrq

]

[

datetime

]

NULL

)

(

@ModelId

7886FC2E-5038-4A71-8477-121A207BD70F

)

begin

insert

into

select

distinct

@StartDate

,a.name,

,b.name,

@WorkNo

GETDATE

(),

@ModelId

select

name

from

dbo.Split(c.name,

)

where

)

KEY

select

name

from

dbo.Split(c.name,

)

where

)

Value

GETDATE

()

from

dbo.Split(

@JjdmList

) a

left

join

dbo.Split(

@CustomerIDCardList

) b

left

join

dbo.Split(

@EventConetent

) c

insert

into

select

distinct

@StartDate

,a.name,

,b.name,

@WorkNo

GETDATE

(),

@ModelId

,c.name,

GETDATE

()

from

dbo.Split(

@JjdmList

) a

left

join

dbo.Split(

@CustomerIDCardList

) b

left

join

dbo.Split(

@Cycle

) c

insert

into

select

distinct

@StartDate

,a.name,

,b.name,

@WorkNo

GETDATE

(),

@ModelId

select

name

from

dbo.Split(c.name,

)

where

)

KEY

select

name

from

dbo.Split(c.name,

)

where

)

Value

GETDATE

()

from

dbo.Split(

@JjdmList

) a

left

join

dbo.Split(

@CustomerIDCardList

) b

left

join

dbo.Split(

@RateTypeList

) c

insert

into

[

WFT_RemindSetting

]

select

NEWID

(),

from

return

@@rowcount

end

(

@ModelId

359A80EF-6769-401E-97A8-2EEEAE3C61C7

)

begin

declare

@t_1

table

(

CustomerIDCard

varchar

(

500

)

,JJDM

varchar

(

)

,BuyDate

varchar

(

)

insert

into

@t_1

select

a.name,c.JJDM,

@StartDate

from

dbo.Split(

@CustomerIDCardList

) a

left

join

WFT_Customer b

a.name

b.CustomerIDcard

left

join

WFT_CustomerFundTrade c

b.FundAccount

c.FundAccount

where

c.NowLot

(

@StartDate

1900-01-01

)

begin

update

@t_1

set

BuyDate

(

select

top

CONVERT

(

VARCHAR

,x.TradeDate,

)

from

WFT_CustomerFundTradeDetails x

left

join

WFT_Customer y

x.FundAccount

y.FundAccount

where

x.JJDM

JJDM

and

y.CustomerIDcard

CustomerIDcard

and

x.IsHistory

order

x.TradeDate)

end

insert

into

select

a.BuyDate,

,a.JJDM,

,a.CustomerIDCard,

@WorkNo

GETDATE

(),

@ModelId

select

name

from

dbo.Split(c.name,

)

where

)

KEY

select

name

from

dbo.Split(c.name,

)

where

)

Value

GETDATE

()

from

@t_1

left

join

dbo.Split(

@RateTypeList

) c

insert

into

[

WFT_RemindSetting

]

select

NEWID

(),

from

return

@@rowcount

end

(

@ModelId

C9D578B8-17D4-43A8-84B4-EB1BD44D8D9A

)

begin

insert

into

select

distinct

@StartDate

,b.name,

@WorkNo

GETDATE

(),

@ModelId

select

name

from

dbo.Split(c.name,

)

where

)

KEY

select

name

from

dbo.Split(c.name,

)

where

)

Value

GETDATE

()

from

dbo.Split(

@CustomerIDCardList

) b

left

join

dbo.Split(

@RateTypeList

) c

insert

into

select

distinct

@StartDate

,b.name,

@WorkNo

GETDATE

(),

@ModelId

,d.name

GETDATE

()

from

dbo.Split(

@CustomerIDCardList

) b

left

join

dbo.Split(

@Cycle

) d

insert

into

[

WFT_RemindSetting

]

select

NEWID

(),

from

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

请注意参数 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>

///

///

///

///

///

///

///

///

///

///

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[

].Value