param array and stored procedure call

  • Thread starter Thread starter M
  • Start date Start date
M

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();



}
}
 
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 said:
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.
}
Click to expand...

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?*************
 
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,
Rather than concatenating together a lot of 'expensive' string objects,
consider using a StringBuilder instead.
string questionMarkList;
Click to expand...
System.Text.StringBuilder list = new StringBuilder();
foreach (paramObj p in myParams)
{ list.Append("?,")
}
Click to expand...
list.Length -= 1; // remove trailing comma
questionMarkList = list.ToString();
}
Click to expand...

Hope this helps
Jay
 
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.
 
Back
Top