You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Garth Keesler <ga...@gdcjk.com> on 2007/02/25 18:59:18 UTC
SQL Serer 2005 Stored Procedures (calling)
I've googled and searched the PDF file but still have not figured out
how to invoke a simple stored procedure in c# using iBatisNet. The
stored procedure could be as follows:
create procedure fnIsValidSVN @svn varchar(128) as
set nocount on
declare @cnt int;
select @cnt = count(*) from tblVendors
where (ShortVN = @svn);
return @cnt
Pretty straight forward stuff. Following is the xml I think I need
except for the actual call which I'm not sure of.
<procedure id="IsValidSVN" parameterMap="SVNParams">
--not sure what would go here--
</procedure>
<parameterMap id="SVNParams">
<parameter property="--not sure what goes here--"
direction="Output" dbType="Int" type="int"/>
<parameter property="@svn" dbType="VarChar" size="128"
type="string" direction="Input"/>
</parameterMap>
As to how to actually use the above in c# I've not a clue. Somehow I
need to invoke IsValidSVN and pass it at least one string param and then
access the return value of the stored procedure. Can someone add a bit
of c# in reply?
Thanx much,
Garth
Re: SQL Serer 2005 Stored Procedures (calling)
Posted by Garth Keesler <ga...@gdcjk.com>.
Last clarification, I promise!
Delete the "declare @cnt int;" from the storedproc as it was left in
error. It should be:
create procedure doit @str varchar(128) as
set nocount on
if LEN(@str) > 0
return 1
else
return 0
Thanx,
Garth
Garth Keesler wrote:
> My apologies. I spent way too many words trying to define the problem.
> Let me try a simple example.
>
> Given the c# boolean function:
>
> public bool doit(string str)
> {
> return (str.length > 0);
> }
>
> The equivalent SQL 2005 storedproc might be:
>
> create procedure doit @str varchar(128) as
> set nocount on
> declare @cnt int;
> if LEN(@str) > 0
> return 1
> else
> return 0
>
> Question: What would the ParameterMap and <procedure> xml look like
> and what would the c# code be to invoke it?
>
> Thanx,
> Garth
> Garth Keesler wrote:
>> I thank you both for the replies. Unfortunately, it appears that my
>> original posting got badly mangled during creation/transmission,
>> possibly because I was cutting and pasting the message from other
>> sources.
>>
>> To clarify, given an SQL Server 2005 stored procedure that accepts a
>> single VarChar parameter called @svn and that returns a single int
>> (not a list) as the unnamed return code of the stored procedure, what
>> would the ParameteraMap entries look like and secondly, what would
>> the c# call be? In particular, since there is no c# Class defined
>> specifically for this ParameterMap instance, how do I pass in the
>> VarChar and get back the int return code of the stored proc? Do I
>> need to declare a matching c# class for each stored procedure's
>> ParameterMap? In the book, "iBatis In Action", which is written for
>> Java, the authors make frequent use of the Java Map to create the
>> appropriate data structure dynamically, fill it with values, and then
>> make the call to the sp. In my case, there is no predefined class
>> other than String as input and unnamed Int32 as sp return code, and
>> the result of the call is not a list of values, just a single int
>> return code.
>>
>> More generally, I typically use the return code of stored procedures
>> to give some indication of the success or failure of the sp. In many
>> cases, the return code of the sp is the only thing that gets
>> returned. Is it possible to access this return code when using
>> iBatisNet? In c# without iBatis, I create the first parameter to the
>> SP as an unnamed int with ParameterDirection of "ReturnValue" as
>> follows:
>>
>> SqlParameter p = cmd.Parameters.Add(new
>> SqlParameter("",SqlDbType.Int));
>> p.Direction = ParameterDirection.ReturnValue;
>>
>> Is this possible in iBatisNet or do I need to provide a named output
>> parameter (and matching c# class definition) to return this value? I
>> can do that but it means modifying several hundred existing stored
>> procedures and creating larges numbers of new Class definitions,
>> something I would probably just avoid.
>>
>> Hopefully, I'm just missing something really easy here!
>>
>> Thanx,
>> Garth
>>
>>
>> Guilhem Berthalon wrote:
>>>
>>> Hi,
>>>
>>> You can read this basic article :
>>>
>>> http://www.supinfo-projects.com/fr/2005/ibatisneten/
>>>
>>>
>>>
>>> an example of stored procedure call :
>>>
>>>
>>>
>>> public IEntityList<Adresse> GetEntityList()
>>>
>>> {
>>>
>>> try
>>>
>>> {
>>>
>>> AdresseList list =
>>> (AdresseList)DataAccess.Mapper.QueryForList<Adresse>("SelectAdresse",
>>> new Adresse());
>>>
>>> return (list);
>>>
>>> }
>>>
>>> catch(System.Exception exception)
>>>
>>> {
>>>
>>> throw exception;
>>>
>>> }
>>>
>>> }
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Guilhem Berthalon
>>>
>>> AROBAN
>>>
>>> 28 rue des teinturiers
>>>
>>> 84 000 AVIGNON
>>>
>>> tel : 04 32 76 23 60
>>>
>>> Hotline : 04 90 85 89 29
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> *De :* Prosper [mailto:mprospa@yahoo.com]
>>> *Envoyé :* lundi 26 février 2007 10:04
>>> *À :* user-cs@ibatis.apache.org
>>> *Objet :* Re: SQL Serer 2005 Stored Procedures (calling)
>>>
>>>
>>>
>>> Hi Garth
>>>
>>>
>>>
>>> <procedure id="IsValidSVN" parameterMap="SVNParams">
>>> fnIsValidSVN --Name of your sp.
>>>
>>> </procedure>
>>>
>>> <parameterMap id="SVNParams" class="">
>>>
>>> <parameter property="Name of your property/param from c# code"
>>> column ="Name of the corresponding param in the sp body"
>>> direction="Output" dbType="Int" type="int"/>
>>> </parameterMap>
>>>
>>>
>>> */Garth Keesler <ga...@gdcjk.com>/* wrote:
>>>
>>> I've googled and searched the PDF file but still have not
>>> figured out
>>> how to invoke a simple stored procedure in c# using iBatisNet. The
>>> stored procedure could be as follows:
>>>
>>> create procedure fnIsValidSVN @svn varchar(128) as
>>> set nocount on
>>> declare @cnt int;
>>> select @cnt = count(*) from tblVendors
>>> where (ShortVN = @svn);
>>> return @cnt
>>>
>>> Pretty straight forward stuff. Following is the xml I think I need
>>> except for the actual call which I'm not sure of.
>>>
>>>
>>> --not sure what would go here--
>>>
>>>
>>>
>>>
>>> direction="Output" dbType="Int" type="int"/>
>>>
>>> type="string" direction="Input"/>
>>>
>>>
>>> As to how to actually use the above in c# I've not a clue.
>>> Somehow I
>>> need to invoke IsValidSVN and pass it at least one string param
>>> and then
>>> access the return value of the stored procedure. Can someone add a
>>> bit
>>> of c# in reply?
>>>
>>> Thanx much,
>>> Garth
>>>
>>>
>>>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>>
>>> Don't be flakey. Get Yahoo! Mail for Mobile
>>> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> and
>>> always stay connected
>>> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> to
>>> friends.
>>>
>>
>>
>> .
>>
>
>
> .
>
Re: SQL Serer 2005 Stored Procedures (calling)
Posted by Garth Keesler <ga...@gdcjk.com>.
My apologies. I spent way too many words trying to define the problem.
Let me try a simple example.
Given the c# boolean function:
public bool doit(string str)
{
return (str.length > 0);
}
The equivalent SQL 2005 storedproc might be:
create procedure doit @str varchar(128) as
set nocount on
declare @cnt int;
if LEN(@str) > 0
return 1
else
return 0
Question: What would the ParameterMap and <procedure> xml look like and
what would the c# code be to invoke it?
Thanx,
Garth
Garth Keesler wrote:
> I thank you both for the replies. Unfortunately, it appears that my
> original posting got badly mangled during creation/transmission,
> possibly because I was cutting and pasting the message from other
> sources.
>
> To clarify, given an SQL Server 2005 stored procedure that accepts a
> single VarChar parameter called @svn and that returns a single int
> (not a list) as the unnamed return code of the stored procedure, what
> would the ParameteraMap entries look like and secondly, what would the
> c# call be? In particular, since there is no c# Class defined
> specifically for this ParameterMap instance, how do I pass in the
> VarChar and get back the int return code of the stored proc? Do I need
> to declare a matching c# class for each stored procedure's
> ParameterMap? In the book, "iBatis In Action", which is written for
> Java, the authors make frequent use of the Java Map to create the
> appropriate data structure dynamically, fill it with values, and then
> make the call to the sp. In my case, there is no predefined class
> other than String as input and unnamed Int32 as sp return code, and
> the result of the call is not a list of values, just a single int
> return code.
>
> More generally, I typically use the return code of stored procedures
> to give some indication of the success or failure of the sp. In many
> cases, the return code of the sp is the only thing that gets returned.
> Is it possible to access this return code when using iBatisNet? In c#
> without iBatis, I create the first parameter to the SP as an unnamed
> int with ParameterDirection of "ReturnValue" as follows:
>
> SqlParameter p = cmd.Parameters.Add(new
> SqlParameter("",SqlDbType.Int));
> p.Direction = ParameterDirection.ReturnValue;
>
> Is this possible in iBatisNet or do I need to provide a named output
> parameter (and matching c# class definition) to return this value? I
> can do that but it means modifying several hundred existing stored
> procedures and creating larges numbers of new Class definitions,
> something I would probably just avoid.
>
> Hopefully, I'm just missing something really easy here!
>
> Thanx,
> Garth
>
>
> Guilhem Berthalon wrote:
>>
>> Hi,
>>
>> You can read this basic article :
>>
>> http://www.supinfo-projects.com/fr/2005/ibatisneten/
>>
>>
>>
>> an example of stored procedure call :
>>
>>
>>
>> public IEntityList<Adresse> GetEntityList()
>>
>> {
>>
>> try
>>
>> {
>>
>> AdresseList list =
>> (AdresseList)DataAccess.Mapper.QueryForList<Adresse>("SelectAdresse",
>> new Adresse());
>>
>> return (list);
>>
>> }
>>
>> catch(System.Exception exception)
>>
>> {
>>
>> throw exception;
>>
>> }
>>
>> }
>>
>>
>>
>> Regards,
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> Guilhem Berthalon
>>
>> AROBAN
>>
>> 28 rue des teinturiers
>>
>> 84 000 AVIGNON
>>
>> tel : 04 32 76 23 60
>>
>> Hotline : 04 90 85 89 29
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> *De :* Prosper [mailto:mprospa@yahoo.com]
>> *Envoyé :* lundi 26 février 2007 10:04
>> *À :* user-cs@ibatis.apache.org
>> *Objet :* Re: SQL Serer 2005 Stored Procedures (calling)
>>
>>
>>
>> Hi Garth
>>
>>
>>
>> <procedure id="IsValidSVN" parameterMap="SVNParams">
>> fnIsValidSVN --Name of your sp.
>>
>> </procedure>
>>
>> <parameterMap id="SVNParams" class="">
>>
>> <parameter property="Name of your property/param from c# code"
>> column ="Name of the corresponding param in the sp body"
>> direction="Output" dbType="Int" type="int"/>
>> </parameterMap>
>>
>>
>> */Garth Keesler <ga...@gdcjk.com>/* wrote:
>>
>> I've googled and searched the PDF file but still have not figured
>> out
>> how to invoke a simple stored procedure in c# using iBatisNet. The
>> stored procedure could be as follows:
>>
>> create procedure fnIsValidSVN @svn varchar(128) as
>> set nocount on
>> declare @cnt int;
>> select @cnt = count(*) from tblVendors
>> where (ShortVN = @svn);
>> return @cnt
>>
>> Pretty straight forward stuff. Following is the xml I think I need
>> except for the actual call which I'm not sure of.
>>
>>
>> --not sure what would go here--
>>
>>
>>
>>
>> direction="Output" dbType="Int" type="int"/>
>>
>> type="string" direction="Input"/>
>>
>>
>> As to how to actually use the above in c# I've not a clue. Somehow I
>> need to invoke IsValidSVN and pass it at least one string param
>> and then
>> access the return value of the stored procedure. Can someone add a
>> bit
>> of c# in reply?
>>
>> Thanx much,
>> Garth
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------
>>
>> Don't be flakey. Get Yahoo! Mail for Mobile
>> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> and
>> always stay connected
>> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> to
>> friends.
>>
>
>
> .
>
Re: SQL Serer 2005 Stored Procedures (calling)
Posted by Garth Keesler <ga...@gdcjk.com>.
I thank you both for the replies. Unfortunately, it appears that my
original posting got badly mangled during creation/transmission,
possibly because I was cutting and pasting the message from other sources.
To clarify, given an SQL Server 2005 stored procedure that accepts a
single VarChar parameter called @svn and that returns a single int (not
a list) as the unnamed return code of the stored procedure, what would
the ParameteraMap entries look like and secondly, what would the c# call
be? In particular, since there is no c# Class defined specifically for
this ParameterMap instance, how do I pass in the VarChar and get back
the int return code of the stored proc? Do I need to declare a matching
c# class for each stored procedure's ParameterMap? In the book, "iBatis
In Action", which is written for Java, the authors make frequent use of
the Java Map to create the appropriate data structure dynamically, fill
it with values, and then make the call to the sp. In my case, there is
no predefined class other than String as input and unnamed Int32 as sp
return code, and the result of the call is not a list of values, just a
single int return code.
More generally, I typically use the return code of stored procedures to
give some indication of the success or failure of the sp. In many cases,
the return code of the sp is the only thing that gets returned. Is it
possible to access this return code when using iBatisNet? In c# without
iBatis, I create the first parameter to the SP as an unnamed int with
ParameterDirection of "ReturnValue" as follows:
SqlParameter p = cmd.Parameters.Add(new
SqlParameter("",SqlDbType.Int));
p.Direction = ParameterDirection.ReturnValue;
Is this possible in iBatisNet or do I need to provide a named output
parameter (and matching c# class definition) to return this value? I can
do that but it means modifying several hundred existing stored
procedures and creating larges numbers of new Class definitions,
something I would probably just avoid.
Hopefully, I'm just missing something really easy here!
Thanx,
Garth
Guilhem Berthalon wrote:
>
> Hi,
>
> You can read this basic article :
>
> http://www.supinfo-projects.com/fr/2005/ibatisneten/
>
>
>
> an example of stored procedure call :
>
>
>
> public IEntityList<Adresse> GetEntityList()
>
> {
>
> try
>
> {
>
> AdresseList list =
> (AdresseList)DataAccess.Mapper.QueryForList<Adresse>("SelectAdresse",
> new Adresse());
>
> return (list);
>
> }
>
> catch(System.Exception exception)
>
> {
>
> throw exception;
>
> }
>
> }
>
>
>
> Regards,
>
>
>
>
>
>
>
>
>
> Guilhem Berthalon
>
> AROBAN
>
> 28 rue des teinturiers
>
> 84 000 AVIGNON
>
> tel : 04 32 76 23 60
>
> Hotline : 04 90 85 89 29
>
>
>
> ------------------------------------------------------------------------
>
> *De :* Prosper [mailto:mprospa@yahoo.com]
> *Envoyé :* lundi 26 février 2007 10:04
> *À :* user-cs@ibatis.apache.org
> *Objet :* Re: SQL Serer 2005 Stored Procedures (calling)
>
>
>
> Hi Garth
>
>
>
> <procedure id="IsValidSVN" parameterMap="SVNParams">
> fnIsValidSVN --Name of your sp.
>
> </procedure>
>
> <parameterMap id="SVNParams" class="">
>
> <parameter property="Name of your property/param from c# code"
> column ="Name of the corresponding param in the sp body"
> direction="Output" dbType="Int" type="int"/>
> </parameterMap>
>
>
> */Garth Keesler <ga...@gdcjk.com>/* wrote:
>
> I've googled and searched the PDF file but still have not figured out
> how to invoke a simple stored procedure in c# using iBatisNet. The
> stored procedure could be as follows:
>
> create procedure fnIsValidSVN @svn varchar(128) as
> set nocount on
> declare @cnt int;
> select @cnt = count(*) from tblVendors
> where (ShortVN = @svn);
> return @cnt
>
> Pretty straight forward stuff. Following is the xml I think I need
> except for the actual call which I'm not sure of.
>
>
> --not sure what would go here--
>
>
>
>
> direction="Output" dbType="Int" type="int"/>
>
> type="string" direction="Input"/>
>
>
> As to how to actually use the above in c# I've not a clue. Somehow I
> need to invoke IsValidSVN and pass it at least one string param
> and then
> access the return value of the stored procedure. Can someone add a
> bit
> of c# in reply?
>
> Thanx much,
> Garth
>
>
>
>
>
> ------------------------------------------------------------------------
>
> Don't be flakey. Get Yahoo! Mail for Mobile
> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> and
> always stay connected
> <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> to
> friends.
>
RE: SQL Serer 2005 Stored Procedures (calling)
Posted by Guilhem Berthalon <gu...@aroban.com>.
Hi,
You can read this basic article :
http://www.supinfo-projects.com/fr/2005/ibatisneten/
an example of stored procedure call :
public IEntityList<Adresse> GetEntityList()
{
try
{
AdresseList list =
(AdresseList)DataAccess.Mapper.QueryForList<Adresse>("SelectAdresse", new
Adresse());
return (list);
}
catch(System.Exception exception)
{
throw exception;
}
}
Regards,
Guilhem Berthalon
AROBAN
28 rue des teinturiers
84 000 AVIGNON
tel : 04 32 76 23 60
Hotline : 04 90 85 89 29
_____
De : Prosper [mailto:mprospa@yahoo.com]
Envoyé : lundi 26 février 2007 10:04
À : user-cs@ibatis.apache.org
Objet : Re: SQL Serer 2005 Stored Procedures (calling)
Hi Garth
<procedure id="IsValidSVN" parameterMap="SVNParams">
fnIsValidSVN --Name of your sp.
</procedure>
<parameterMap id="SVNParams" class="">
<parameter property="Name of your property/param from c# code" column
="Name of the corresponding param in the sp body" direction="Output"
dbType="Int" type="int"/>
</parameterMap>
Garth Keesler <ga...@gdcjk.com> wrote:
I've googled and searched the PDF file but still have not figured out
how to invoke a simple stored procedure in c# using iBatisNet. The
stored procedure could be as follows:
create procedure fnIsValidSVN @svn varchar(128) as
set nocount on
declare @cnt int;
select @cnt = count(*) from tblVendors
where (ShortVN = @svn);
return @cnt
Pretty straight forward stuff. Following is the xml I think I need
except for the actual call which I'm not sure of.
--not sure what would go here--
direction="Output" dbType="Int" type="int"/>
type="string" direction="Input"/>
As to how to actually use the above in c# I've not a clue. Somehow I
need to invoke IsValidSVN and pass it at least one string param and then
access the return value of the stored procedure. Can someone add a bit
of c# in reply?
Thanx much,
Garth
_____
Don't be flakey. Get Yahoo!
<http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> Mail for
Mobile and
always <http://us.rd.yahoo.com/evt=43909/*http:/mobile.yahoo.com/mail> stay
connected to friends.
Re: SQL Serer 2005 Stored Procedures (calling)
Posted by Prosper <mp...@yahoo.com>.
Hi Garth
<procedure id="IsValidSVN" parameterMap="SVNParams">
fnIsValidSVN --Name of your sp.
</procedure>
<parameterMap id="SVNParams" class="">
<parameter property="Name of your property/param from c# code" column ="Name of the corresponding param in the sp body" direction="Output" dbType="Int" type="int"/>
</parameterMap>
Garth Keesler <ga...@gdcjk.com> wrote:
I've googled and searched the PDF file but still have not figured out
how to invoke a simple stored procedure in c# using iBatisNet. The
stored procedure could be as follows:
create procedure fnIsValidSVN @svn varchar(128) as
set nocount on
declare @cnt int;
select @cnt = count(*) from tblVendors
where (ShortVN = @svn);
return @cnt
Pretty straight forward stuff. Following is the xml I think I need
except for the actual call which I'm not sure of.
--not sure what would go here--
direction="Output" dbType="Int" type="int"/>
type="string" direction="Input"/>
As to how to actually use the above in c# I've not a clue. Somehow I
need to invoke IsValidSVN and pass it at least one string param and then
access the return value of the stored procedure. Can someone add a bit
of c# in reply?
Thanx much,
Garth
---------------------------------
Don't be flakey. Get Yahoo! Mail for Mobile and
always stay connected to friends.