最近一直在忙其它项目,Monorail和IBatis.Net系列很长时间没有更新了,今天正好有朋友问到在IBatis.Net中调用存储过程的问题。
其实调用方式比较简单,主要也就是两种类型的存储过程:
1、更新类型的存储过程
2、查询类型的存储过程
下面就来看看具体的调用方式:
1、更新类型的存储过程
sp_InsertAccount:
CREATE
PROCEDURE
[
dbo
]
.
[
sp_InsertAccount
]
-- Add the parameters for the stored procedure here
@Account_ID int ,
@Account_FirstName varchar ( 32 ),
@Account_LastName varchar ( 32 ) AS
BEGIN
insert into accounts (account_id, account_firstname, account_lastname)
values ( @Account_ID , @Account_FirstName , @Account_LastName )
END
Map配置文件:-- Add the parameters for the stored procedure here
@Account_ID int ,
@Account_FirstName varchar ( 32 ),
@Account_LastName varchar ( 32 ) AS
BEGIN
insert into accounts (account_id, account_firstname, account_lastname)
values ( @Account_ID , @Account_FirstName , @Account_LastName )
END
<
procedure
id
="InsertAccountViaStoreProcedure"
parameterMap
="insert-params_new"
>
sp_InsertAccount
</ procedure >
< parameterMap id ="insert-params_new" class ="Account" >
< parameter property ="Id" />
< parameter property ="FirstName" />
< parameter property ="LastName" />
</ parameterMap >
sp_InsertAccount
</ procedure >
< parameterMap id ="insert-params_new" class ="Account" >
< parameter property ="Id" />
< parameter property ="FirstName" />
< parameter property ="LastName" />
</ parameterMap >
这里要注意的就是ParameterMap中的参数个数和顺序要和sp_InsertAccount存储过程中的一致
Ado中的调用代码:
public
void
InsertAccountViaStoreProcedure(Account account)
{
try
{
sqlMap.Insert("InsertAccountViaStoreProcedure", account);
}
catch (DataAccessException ex)
{
throw new DataAccessException("Error executing InsertAccountViaStoreProcedure. Cause :" + ex.Message, ex);
}
}
{
try
{
sqlMap.Insert("InsertAccountViaStoreProcedure", account);
}
catch (DataAccessException ex)
{
throw new DataAccessException("Error executing InsertAccountViaStoreProcedure. Cause :" + ex.Message, ex);
}
}
这里使用的是sqlMap.Insert的方法,为了看起来直观一点,其实使用sqlMap.QueryForObject方法的话效果也是一样的:)
2、查询类型的存储过程
GetAccountByName:
CREATE
PROCEDURE
[
dbo
]
.
[
GetAccountByName
]
@name varchar ( 32 )
AS
BEGIN
select * from accounts where Account_FirstName like ' % ' + @name + ' % '
END
@name varchar ( 32 )
AS
BEGIN
select * from accounts where Account_FirstName like ' % ' + @name + ' % '
END
Map配置文件:
<
procedure
id
="GetAccountByNameViaStoreProcedure"
resultMap
="account-result"
parameterMap
="selectpro-params"
>
GetAccountByName
</ procedure >
< parameterMap id ="selectpro-params" class ="string" >
< parameter property ="name" />
</ parameterMap >
这里parameterMap也是和上面的要求一样,至于property的名字在这里没有实际作用,可以任意取名的GetAccountByName
</ procedure >
< parameterMap id ="selectpro-params" class ="string" >
< parameter property ="name" />
</ parameterMap >
Ado中的调用代码:
public
ArrayList GetAccountByNameViaStoreProcedure(
string
strName)
{
try
{
ArrayList list = (ArrayList)sqlMap.QueryForList("GetAccountByNameViaStoreProcedure", strName);
return list;
}
catch (DataAccessException ex)
{
throw new DataAccessException("Error executing SqlAccountViaSqlMapDao.GetAccountById. Cause :" + ex.Message, ex);
}
}
{
try
{
ArrayList list = (ArrayList)sqlMap.QueryForList("GetAccountByNameViaStoreProcedure", strName);
return list;
}
catch (DataAccessException ex)
{
throw new DataAccessException("Error executing SqlAccountViaSqlMapDao.GetAccountById. Cause :" + ex.Message, ex);
}
}
本文转自永春博客园博客,原文链接:
http://www.cnblogs.com/firstyi/archive/2008/01/25/1053208.html
,如需转载请自行联系原作者