param array and stored procedure call

Is there any way to modify the following code so I can run it with any
number of paramNames as well as any number of paramValues? So far it
works with an array of paramValues, but since you can only pass a
variable number of parameters in the last parameter of a method, I'm
stumped.

Perhaps the paramValues could be multidimensional? And a (?) has to be
present for each paramName as well, correct?

Thanks in advance.


public DataSet returnStoredProc(string spName, string paramName, params
Object[] paramValues)
{
OdbcDataAdapter _da = new OdbcDataAdapter();
DataSet _ds = new DataSet();
OdbcConnection cn = new OdbcConnection(connectionString);
try
{


OdbcCommand _cmdSel = new OdbcCommand();
OdbcParameter prm = new OdbcParameter();

cn.Open();

_da.SelectCommand = _cmdSel;

_cmdSel.CommandText = "{CALL " + spName + " (?)}";
_cmdSel.CommandType = CommandType.StoredProcedure;
_cmdSel.Connection = cn;

for (int x = 0; x < paramValues.Length; x++)
_cmdSel.Parameters.Add("@" + paramName, paramValues[x]);


_da.Fill(_ds);
return _ds;


cn.Close();



}
}

Greg
Mon Sep 15 21:14:20 CDT 2003

M, how about creating a class which contains your ParamName and ParamValue,
then you can pass as many of those in to your method as you want.

public class Params
{
//ParamName property
//paramValue property.
}

--
Greg Ewing [MVP]
http://www.citidc.com

"M" <minnow31@hotmail.com> wrote in message
news:qiu9b.80953$mp.33816@rwcrnsc51.ops.asp.att.net...
> Is there any way to modify the following code so I can run it with any
> number of paramNames as well as any number of paramValues? So far it
> works with an array of paramValues, but since you can only pass a
> variable number of parameters in the last parameter of a method, I'm
> stumped.
>
> Perhaps the paramValues could be multidimensional? And a (?) has to be
> present for each paramName as well, correct?
>
> Thanks in advance.
>
>
> public DataSet returnStoredProc(string spName, string paramName, params
> Object[] paramValues)
> {
> OdbcDataAdapter _da = new OdbcDataAdapter();
> DataSet _ds = new DataSet();
> OdbcConnection cn = new OdbcConnection(connectionString);
> try
> {
>
>
> OdbcCommand _cmdSel = new OdbcCommand();
> OdbcParameter prm = new OdbcParameter();
>
> cn.Open();
>
> _da.SelectCommand = _cmdSel;
>
> _cmdSel.CommandText = "{CALL " + spName + " (?)}";
> _cmdSel.CommandType = CommandType.StoredProcedure;
> _cmdSel.Connection = cn;
>
> for (int x = 0; x < paramValues.Length; x++)
> _cmdSel.Parameters.Add("@" + paramName, paramValues[x]);
>
>
> _da.Fill(_ds);
> return _ds;
>
>
> cn.Close();
>
>
>
> }
> }
>

M
Mon Sep 15 21:29:06 CDT 2003

Greg Ewing [MVP] wrote:

> M, how about creating a class which contains your ParamName and ParamValue,
> then you can pass as many of those in to your method as you want.
>
> public class Params
> {
> //ParamName property
> //paramValue property.
> }
>

ah yes. objects, of course!

d'oh!

:)

I assume you mean like so:

public DataSet returnAnyStoredProc(string spName, param myParams)

{
//...snip essential dbstuff

foreach(param obj in myParams)
{
cmdSel.Parameters.Add("@" + myParam.paramName, myParam.paramValue);
}


//...snip essential catch/return stuff
}


******Curious about the parameterized (?) question mark stuff in the
call of the stored proc. how would you handle that?*************

M
Mon Sep 15 21:44:18 CDT 2003


For this part

cmdSel.CommandText = "{CALL " + spName + " (?)}";



I'm not really comfortable with the syntax, but I'm guessing if I have 3
parameters to pass into a storedproc, the call would go

cmdSel.CommandText = "{CALL " + spName + " (?,?,?)}";

So...

string questionMarkList;


foreach (paramObj p in myParams)
{
if(myParams.Count = p.Count)
{
questionMarkList += "?"
}else{
questionMarkList += "?,"
}
}

M wrote:

> Greg Ewing [MVP] wrote:
>
>> M, how about creating a class which contains your ParamName and
>> ParamValue,
>> then you can pass as many of those in to your method as you want.
>>
>> public class Params
>> {
>> //ParamName property
>> //paramValue property.
>> }
>>
>
> ah yes. objects, of course!
>
> d'oh!
>
> :)
>
> I assume you mean like so:
>
> public DataSet returnAnyStoredProc(string spName, param myParams)
>
> {
> //...snip essential dbstuff
>
> foreach(param obj in myParams)
> {
> cmdSel.Parameters.Add("@" + myParam.paramName, myParam.paramValue);
> }
>
>
> //...snip essential catch/return stuff
> }
>
>
> ******Curious about the parameterized (?) question mark stuff in the
> call of the stored proc. how would you handle that?*************
>

Jay
Mon Sep 15 22:07:42 CDT 2003

M,
Rather than concatenating together a lot of 'expensive' string objects,
consider using a StringBuilder instead.

> string questionMarkList;
System.Text.StringBuilder list = new StringBuilder();

> foreach (paramObj p in myParams)
> {
list.Append("?,")
> }
list.Length -= 1; // remove trailing comma
questionMarkList = list.ToString();

> }

Hope this helps
Jay

"M" <minnow31@hotmail.com> wrote in message
news:6Ou9b.361682$cF.107691@rwcrnsc53...
>
> For this part
>
> cmdSel.CommandText = "{CALL " + spName + " (?)}";
>
>
>
> I'm not really comfortable with the syntax, but I'm guessing if I have 3
> parameters to pass into a storedproc, the call would go
>
> cmdSel.CommandText = "{CALL " + spName + " (?,?,?)}";
>
> So...
>
> string questionMarkList;
>
>
> foreach (paramObj p in myParams)
> {
> if(myParams.Count = p.Count)
> {
> questionMarkList += "?"
> }else{
> questionMarkList += "?,"
> }
> }
>
> M wrote:
>
> > Greg Ewing [MVP] wrote:
> >
> >> M, how about creating a class which contains your ParamName and
> >> ParamValue,
> >> then you can pass as many of those in to your method as you want.
> >>
> >> public class Params
> >> {
> >> //ParamName property
> >> //paramValue property.
> >> }
> >>
> >
> > ah yes. objects, of course!
> >
> > d'oh!
> >
> > :)
> >
> > I assume you mean like so:
> >
> > public DataSet returnAnyStoredProc(string spName, param myParams)
> >
> > {
> > //...snip essential dbstuff
> >
> > foreach(param obj in myParams)
> > {
> > cmdSel.Parameters.Add("@" + myParam.paramName, myParam.paramValue);
> > }
> >
> >
> > //...snip essential catch/return stuff
> > }
> >
> >
> > ******Curious about the parameterized (?) question mark stuff in the
> > call of the stored proc. how would you handle that?*************
> >
>

Alvin
Mon Sep 15 22:25:58 CDT 2003

prefer for loops to foreach statements as well, 3x faster roughly
"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow@email.msn.com> wrote in message
news:#lVez#$eDHA.2328@TK2MSFTNGP09.phx.gbl...
> M,
> Rather than concatenating together a lot of 'expensive' string objects,
> consider using a StringBuilder instead.
>
> > string questionMarkList;
> System.Text.StringBuilder list = new StringBuilder();
>
> > foreach (paramObj p in myParams)
> > {
> list.Append("?,")
> > }
> list.Length -= 1; // remove trailing comma
> questionMarkList = list.ToString();
>
> > }
>
> Hope this helps
> Jay
>
> "M" <minnow31@hotmail.com> wrote in message
> news:6Ou9b.361682$cF.107691@rwcrnsc53...
> >
> > For this part
> >
> > cmdSel.CommandText = "{CALL " + spName + " (?)}";
> >
> >
> >
> > I'm not really comfortable with the syntax, but I'm guessing if I have 3
> > parameters to pass into a storedproc, the call would go
> >
> > cmdSel.CommandText = "{CALL " + spName + " (?,?,?)}";
> >
> > So...
> >
> > string questionMarkList;
> >
> >
> > foreach (paramObj p in myParams)
> > {
> > if(myParams.Count = p.Count)
> > {
> > questionMarkList += "?"
> > }else{
> > questionMarkList += "?,"
> > }
> > }
> >
> > M wrote:
> >
> > > Greg Ewing [MVP] wrote:
> > >
> > >> M, how about creating a class which contains your ParamName and
> > >> ParamValue,
> > >> then you can pass as many of those in to your method as you want.
> > >>
> > >> public class Params
> > >> {
> > >> //ParamName property
> > >> //paramValue property.
> > >> }
> > >>
> > >
> > > ah yes. objects, of course!
> > >
> > > d'oh!
> > >
> > > :)
> > >
> > > I assume you mean like so:
> > >
> > > public DataSet returnAnyStoredProc(string spName, param myParams)
> > >
> > > {
> > > //...snip essential dbstuff
> > >
> > > foreach(param obj in myParams)
> > > {
> > > cmdSel.Parameters.Add("@" + myParam.paramName,
myParam.paramValue);
> > > }
> > >
> > >
> > > //...snip essential catch/return stuff
> > > }
> > >
> > >
> > > ******Curious about the parameterized (?) question mark stuff in the
> > > call of the stored proc. how would you handle that?*************
> > >
> >
>
>

M
Mon Sep 15 23:33:28 CDT 2003


Great tip, thanks! My general purpose stored proc caller works like a
dream.

Jay B. Harlow [MVP - Outlook] wrote:

> M,
> Rather than concatenating together a lot of 'expensive' string objects,
> consider using a StringBuilder instead.
>
>
>>string questionMarkList;
>
> System.Text.StringBuilder list = new StringBuilder();
>
>
>>foreach (paramObj p in myParams)
>>{
>
> list.Append("?,")
>
>>}
>
> list.Length -= 1; // remove trailing comma
> questionMarkList = list.ToString();
>
>
>>}
>
>
> Hope this helps
> Jay
>
>

M
Mon Sep 15 23:38:59 CDT 2003

Yeah, I better change that. If a real programmer ever looked at my code
I might get spit on with disgust ;)

And offtopic...I've noticed that as I do more real OOP that I am getting
better at caring about details like that. It is an interesting
phenomena that it is easier to ignore the pressure to complete projects
faster when I am the one who has to support and maintain it :p

Thanks for the tip.

Alvin Bruney wrote:

> prefer for loops to foreach statements as well, 3x faster roughly