param array and stored procedure call

[M]

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 Ewing [MVP]]

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

 

[M]

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]

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 += "?,"
}
}

 

[Jay B. Harlow [MVP - Outlook]]

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

 

[Alvin Bruney]

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

 

[M]

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

 

[M]

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

Thanks for the tip.