Parameter list

  • Thread starter Thread starter Roy Gourgi
  • Start date Start date
R

Roy Gourgi

Hi,

Is there a way to add a parameter list with only one statement as opposed to
having one statement for each parameter. For example if I have 20 parameters
(@p1, @p2 .... @p20) that I want to add to my parameter collection, could I
do it all in one statement more or less?

TIA
Roy
 
Could you make it more clear.
If you are saying about collection, many classes expose AddRange
method.
Note that Array also implement ICollection.
 
Ah yes, I didn't notice that.
So he just needs to write a helper method to do that.
 
Hi Truong,

What I mean is this. When I first add my parameters I do it like this (see
below), so I was wandering if there was a way to do this all in one
statement rather than 10 statements. I guess I could use a for loop and a
string builder to do it, but I was wandering if there was an inherent way to
do it.


oCommand.Parameters.AddWithValue("@par0", laEnum2Bk[0]);
oCommand.Parameters.AddWithValue("@par1", laEnum2Bk[1]);

oCommand.Parameters.AddWithValue("@par2", laEnum2Bk[2]);

oCommand.Parameters.AddWithValue("@par3", laEnum2Bk[3]);

oCommand.Parameters.AddWithValue("@par4", laEnum2Bk[4]);

oCommand.Parameters.AddWithValue("@par5", laEnum2Bk[5]);

oCommand.Parameters.AddWithValue("@par6", laEnum2Bk[6]);

oCommand.Parameters.AddWithValue("@par7", laEnum2Bk[7]);

oCommand.Parameters.AddWithValue("@par8", laEnum2Bk[8]);

oCommand.Parameters.AddWithValue("@par9", laEnum2Bk[9]);

oCommand.Parameters.AddWithValue("@par10", laEnum2Bk[10]);

Thanks

Roy
 
As Truong Hong Thi said, a helper function is probably the way to go... I'm
assuming (from the AddWithValue method) that the command is a SqlCommand,
not an IDbCommand, so how about the following:

public static void AddCommandParams(SqlCommand command, string prefix,
params object[] values) {
int index = 0;
foreach (object value in values) {
command.Parameters.AddWithValue(prefix+(index++).ToString(),value);
}
}

Then call
AddCommandParams(oCommand, "@par", laEnum2Bk);

The "params" is so that you could also call:
AddCommandParams(oCommand, "@par", "MyValue", 1, 0.2);

Haven't tested the code, but looks like it should work (famous last
words...)

Marc

Roy Gourgi said:
Hi Truong,

What I mean is this. When I first add my parameters I do it like this (see
below), so I was wandering if there was a way to do this all in one
statement rather than 10 statements. I guess I could use a for loop and a
string builder to do it, but I was wandering if there was an inherent way
to do it.


oCommand.Parameters.AddWithValue("@par0", laEnum2Bk[0]);
oCommand.Parameters.AddWithValue("@par1", laEnum2Bk[1]);

oCommand.Parameters.AddWithValue("@par2", laEnum2Bk[2]);

oCommand.Parameters.AddWithValue("@par3", laEnum2Bk[3]);

oCommand.Parameters.AddWithValue("@par4", laEnum2Bk[4]);

oCommand.Parameters.AddWithValue("@par5", laEnum2Bk[5]);

oCommand.Parameters.AddWithValue("@par6", laEnum2Bk[6]);

oCommand.Parameters.AddWithValue("@par7", laEnum2Bk[7]);

oCommand.Parameters.AddWithValue("@par8", laEnum2Bk[8]);

oCommand.Parameters.AddWithValue("@par9", laEnum2Bk[9]);

oCommand.Parameters.AddWithValue("@par10", laEnum2Bk[10]);

Thanks

Roy





Truong Hong Thi said:
Could you make it more clear.
If you are saying about collection, many classes expose AddRange
method.
Note that Array also implement ICollection.
Click to expand...
Click to expand...
 
Hi Marc,

I tried this and it seems to work fine for my needs. BTW would this be
faster if I used a string builder?

for (lpMisc = 1; lpMisc < 3; lpMisc++)

{

strC = "@par"+lpMisc.ToString();

oCommand.Parameters.AddWithValue(strC, laEnum2Bk[lpMisc]);

}

Thanks
Roy



Marc Gravell said:
As Truong Hong Thi said, a helper function is probably the way to go...
I'm assuming (from the AddWithValue method) that the command is a
SqlCommand, not an IDbCommand, so how about the following:

public static void AddCommandParams(SqlCommand command, string prefix,
params object[] values) {
int index = 0;
foreach (object value in values) {

command.Parameters.AddWithValue(prefix+(index++).ToString(),value);
}
}

Then call
AddCommandParams(oCommand, "@par", laEnum2Bk);

The "params" is so that you could also call:
AddCommandParams(oCommand, "@par", "MyValue", 1, 0.2);

Haven't tested the code, but looks like it should work (famous last
words...)

Marc

Roy Gourgi said:
Hi Truong,

What I mean is this. When I first add my parameters I do it like this
(see below), so I was wandering if there was a way to do this all in one
statement rather than 10 statements. I guess I could use a for loop and a
string builder to do it, but I was wandering if there was an inherent way
to do it.


oCommand.Parameters.AddWithValue("@par0", laEnum2Bk[0]);
oCommand.Parameters.AddWithValue("@par1", laEnum2Bk[1]);

oCommand.Parameters.AddWithValue("@par2", laEnum2Bk[2]);

oCommand.Parameters.AddWithValue("@par3", laEnum2Bk[3]);

oCommand.Parameters.AddWithValue("@par4", laEnum2Bk[4]);

oCommand.Parameters.AddWithValue("@par5", laEnum2Bk[5]);

oCommand.Parameters.AddWithValue("@par6", laEnum2Bk[6]);

oCommand.Parameters.AddWithValue("@par7", laEnum2Bk[7]);

oCommand.Parameters.AddWithValue("@par8", laEnum2Bk[8]);

oCommand.Parameters.AddWithValue("@par9", laEnum2Bk[9]);

oCommand.Parameters.AddWithValue("@par10", laEnum2Bk[10]);

Thanks

Roy





Truong Hong Thi said:
Could you make it more clear.
If you are saying about collection, many classes expose AddRange
method.
Note that Array also implement ICollection.
Click to expand...
Click to expand...
Click to expand...
 
Re StringBuilder, probably not;

I'm going to take a liberty here and direct you at Jon Skeet's excellent
page on the subject (http://www.yoda.arachsys.com/csharp/stringbuilder.html)
(I hope you don't mind, Jon)

If you were constantly adding to a single string (i.e you were building up
some uber-command as a string (or a CSV or something) by enumerating through
a large-ish array) then maybe - but you're not. For concatenating 2 strings,
the + operator is almost certainly faster by a mile.

As a minor note, I'm sure your underlying code deals with this, but remember
that the array is 0-based (your code enumerates from 1, which is perfectly
valid, but sometimes a bug).

Marc

Roy Gourgi said:
Hi Marc,

I tried this and it seems to work fine for my needs. BTW would this be
faster if I used a string builder?

for (lpMisc = 1; lpMisc < 3; lpMisc++)

{

strC = "@par"+lpMisc.ToString();

oCommand.Parameters.AddWithValue(strC, laEnum2Bk[lpMisc]);

}

Thanks
Roy



Marc Gravell said:
As Truong Hong Thi said, a helper function is probably the way to go...
I'm assuming (from the AddWithValue method) that the command is a
SqlCommand, not an IDbCommand, so how about the following:

public static void AddCommandParams(SqlCommand command, string prefix,
params object[] values) {
int index = 0;
foreach (object value in values) {

command.Parameters.AddWithValue(prefix+(index++).ToString(),value);
}
}

Then call
AddCommandParams(oCommand, "@par", laEnum2Bk);

The "params" is so that you could also call:
AddCommandParams(oCommand, "@par", "MyValue", 1, 0.2);

Haven't tested the code, but looks like it should work (famous last
words...)

Marc

Roy Gourgi said:
Hi Truong,

What I mean is this. When I first add my parameters I do it like this
(see below), so I was wandering if there was a way to do this all in one
statement rather than 10 statements. I guess I could use a for loop and
a string builder to do it, but I was wandering if there was an inherent
way to do it.


oCommand.Parameters.AddWithValue("@par0", laEnum2Bk[0]);
oCommand.Parameters.AddWithValue("@par1", laEnum2Bk[1]);

oCommand.Parameters.AddWithValue("@par2", laEnum2Bk[2]);

oCommand.Parameters.AddWithValue("@par3", laEnum2Bk[3]);

oCommand.Parameters.AddWithValue("@par4", laEnum2Bk[4]);

oCommand.Parameters.AddWithValue("@par5", laEnum2Bk[5]);

oCommand.Parameters.AddWithValue("@par6", laEnum2Bk[6]);

oCommand.Parameters.AddWithValue("@par7", laEnum2Bk[7]);

oCommand.Parameters.AddWithValue("@par8", laEnum2Bk[8]);

oCommand.Parameters.AddWithValue("@par9", laEnum2Bk[9]);

oCommand.Parameters.AddWithValue("@par10", laEnum2Bk[10]);

Thanks

Roy





Could you make it more clear.
If you are saying about collection, many classes expose AddRange
method.
Note that Array also implement ICollection.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Marc.

Roy

Marc Gravell said:
Re StringBuilder, probably not;

I'm going to take a liberty here and direct you at Jon Skeet's excellent
page on the subject
(http://www.yoda.arachsys.com/csharp/stringbuilder.html) (I hope you don't
mind, Jon)

If you were constantly adding to a single string (i.e you were building up
some uber-command as a string (or a CSV or something) by enumerating
through a large-ish array) then maybe - but you're not. For concatenating
2 strings, the + operator is almost certainly faster by a mile.

As a minor note, I'm sure your underlying code deals with this, but
remember that the array is 0-based (your code enumerates from 1, which is
perfectly valid, but sometimes a bug).

Marc

Roy Gourgi said:
Hi Marc,

I tried this and it seems to work fine for my needs. BTW would this be
faster if I used a string builder?

for (lpMisc = 1; lpMisc < 3; lpMisc++)

{

strC = "@par"+lpMisc.ToString();

oCommand.Parameters.AddWithValue(strC, laEnum2Bk[lpMisc]);

}

Thanks
Roy



Marc Gravell said:
As Truong Hong Thi said, a helper function is probably the way to go...
I'm assuming (from the AddWithValue method) that the command is a
SqlCommand, not an IDbCommand, so how about the following:

public static void AddCommandParams(SqlCommand command, string prefix,
params object[] values) {
int index = 0;
foreach (object value in values) {

command.Parameters.AddWithValue(prefix+(index++).ToString(),value);
}
}

Then call
AddCommandParams(oCommand, "@par", laEnum2Bk);

The "params" is so that you could also call:
AddCommandParams(oCommand, "@par", "MyValue", 1, 0.2);

Haven't tested the code, but looks like it should work (famous last
words...)

Marc

Hi Truong,

What I mean is this. When I first add my parameters I do it like this
(see below), so I was wandering if there was a way to do this all in
one statement rather than 10 statements. I guess I could use a for loop
and a string builder to do it, but I was wandering if there was an
inherent way to do it.


oCommand.Parameters.AddWithValue("@par0", laEnum2Bk[0]);
oCommand.Parameters.AddWithValue("@par1", laEnum2Bk[1]);

oCommand.Parameters.AddWithValue("@par2", laEnum2Bk[2]);

oCommand.Parameters.AddWithValue("@par3", laEnum2Bk[3]);

oCommand.Parameters.AddWithValue("@par4", laEnum2Bk[4]);

oCommand.Parameters.AddWithValue("@par5", laEnum2Bk[5]);

oCommand.Parameters.AddWithValue("@par6", laEnum2Bk[6]);

oCommand.Parameters.AddWithValue("@par7", laEnum2Bk[7]);

oCommand.Parameters.AddWithValue("@par8", laEnum2Bk[8]);

oCommand.Parameters.AddWithValue("@par9", laEnum2Bk[9]);

oCommand.Parameters.AddWithValue("@par10", laEnum2Bk[10]);

Thanks

Roy





Could you make it more clear.
If you are saying about collection, many classes expose AddRange
method.
Note that Array also implement ICollection.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top