CODEWITHSUNDEEP
stored-proceduresdatamapperibatis.net
nwpie
nwpie

Reputation: 735

ibatis datamapper scripts a stored-procedure with some default parameters

How can I make a robust datamapper script in xml if the target stored-procedure have some parameters added but indeed with default values ??

For instance, I designed a stored-procedure with some default parameters like this,

CREATE PROCEDURE [dbo].[SP_Test_1]
    @mode int = 1 -- skippable
AS
RETURN 1

MyBatis datamapper in xml like this,

<procedure id="myDBService.exeSPTest1">
    SP_Test_1
</procedure>

The way I called this statement,

IList<myStruct> list = myDataSource.QueryForList<myStruct>("myDBService.exeSPTest1", null);

But always got errors like this,

[ArgumentOutOfRangeException: index]
   IBatisNet.DataMapper.Configuration.ParameterMapping.ParameterPropertyCollection.get_Item(Int32 index) +88
   IBatisNet.DataMapper.Configuration.ParameterMapping.ParameterMap.GetProperty(Int32 index) +76
   IBatisNet.DataMapper.Commands.DefaultPreparedCommand.ApplyParameterMap(ISqlMapSession session, IDbCommand command, RequestScope request, IStatement statement, Object parameterObject) +395
   IBatisNet.DataMapper.Commands.DefaultPreparedCommand.Create(RequestScope request, ISqlMapSession session, IStatement statement, Object parameterObject) +439
   IBatisNet.DataMapper.MappedStatements.MappedStatement.ExecuteQueryForList(ISqlMapSession session, Object parameterObject) +125
   IBatisNet.DataMapper.SqlMapper.QueryForList(String statementName, Object parameterObject) +251

until I gave a parameterMap tag and then works,

<procedure id="myDBService.exeSPTest1" parameterMap="myDBService.params-exeSPTest1">
    SP_Test_1
</procedure> 

<parameterMap id="myDBService.params-exeSPTest1" class="Hashtable">
  <parameter column="mode" property="mode" dbType="int" type="int" />
</parameterMap>

Hashtable ht = new Hashtable();
ht.Add("mode", 1);
IList<myStruct> list = myDataSource.QueryForList<myStruct>("myDBService.exeSPTest1", ht);

Although it worked afterwards, I actually want flexible parameters inputted by lots of procedure calls. For example, in the same procedure, I can make it have multi parameters without change any front-tier code, like this,

CREATE PROCEDURE [dbo].[SP_Test_1]
    @mode int = 1, -- skippable
    @reserved int = 1 -- used in the future, still skippable
AS
RETURN 1

The point is do NOT change and front-tier code or xml settings if I add skippable parameters in the stored-procedure. Any idea will be appreciated. Thank you.

Upvotes: 0

Views: 843

Answers (1)

nwpie
nwpie

Reputation: 735

I think I got an idea from this page.

for example, 

<statement id="myDBService.exeSPTest1" parameterClass="myDBService.params-exeSPTest1" >  
 <dynamic>  
  // EXEC SP_Test_1 @mode = #mode#
  // or
  // EXEC SP_Test_1
  // as ur wish
 </dynamic>  
</statement>

Now, it can work with a SP with any number of default parameters.

Upvotes: 0

Related Questions