Elegant, clean and safe way of having multiple concurent parameters in a dbCommand

[arak123]

consider the following oversimplified and fictional code


public void CreateInvoices(Invoice[] invoices)
{
IDbCommand command=Util.CreateDbCommand();
foreach(Invoice invoice in invoices) //lets say you have 200
invoices
{
command.CommandText+="INSERT INTO Invoice(Amount)
VALUES(@Amount); ";
Util.AddParamToDbCommand("@Amount", invoice.Amount, command);
}

if(command.CommandText!=String.Empty)
{
try
{
command.ExecuteNonQuery();
}
catch(Exception ex)
{
//something here
}
}
}

/**************************************************************************
Problem: We can't add multiple parameters with the same name
Possible Solution: Change foreach loop with a for loop and assign a
number at the end of each parameter (which is not clean)
**************************************************************************/


public void CreateInvoices2(Invoice[] invoices)
{
IDbCommand command=Util.CreateDbCommand();
foreach(Invoice invoice in invoices) //lets say you have 200
invoices
{
command.CommandText+=String.Format("INSERT INTO
Invoice(Amount) VALUES({0}); ", invoice.Amount);
}

if(command.CommandText!=null)
{
try
{
command.ExecuteNonQuery();
}
catch(Exception ex)
{
//something here
}
}
}

/****************************************************************************
Problem: It seem that the ToString() function take into account the
culture info of the current thread
so in some cultures you have a different decimal number separator than
the one needed in the database
Possible solution: Set the culture info of the current thread to us-En
or do invoice.Amount.ToString(new CultureInfo("en-US")) which is not
clean either
****************************************************************************/


I was wondering if there was any cleaner and more 'natural' way to do
this than using one of the solutions described above.

 

[GhostInAK]

Hello (e-mail address removed),

First, the INSERT sql statement will only allow you to insert ONE (count
them.. ONE) record at a time.
So each time through the loop you MUST call .ExecuteNonQuery().

Second, if a parameter with the name you want to use already exists on the
command object then you should either clear all parameters and start over..
or just assign a new value to the existing parameter.

Third, numeric values are culture-independant. DO NOT turn them into strings.

-Boo

 

[Nicholas Paldino [.NET/C# MVP]]

I would recommend using a SqlDataAdapter for this and create a data set
which has the values you want to insert into the table. You can tell the
SqlDataAdapter (in .NET 2.0) to batch the commands, and it will take care of
putting everything together for you (the commands, the parameters, etc,
etc).

Hope this helps.

 

[Carl Daniel [VC++ MVP]]

consider the following oversimplified and fictional code
considered...


/**************************************************************************
Problem: We can't add multiple parameters with the same name
Possible Solution: Change foreach loop with a for loop and assign a
number at the end of each parameter (which is not clean)
**************************************************************************/

considered more...

/****************************************************************************
Problem: It seem that the ToString() function take into account the
culture info of the current thread
so in some cultures you have a different decimal number separator than
the one needed in the database
Possible solution: Set the culture info of the current thread to us-En
or do invoice.Amount.ToString(new CultureInfo("en-US")) which is not
clean either
****************************************************************************/


I was wondering if there was any cleaner and more 'natural' way to do
this than using one of the solutions described above.

Rather than using new CultureInfo(), use
invoice.Amount.ToString(CultureInfo.InvariantCulture) to always get the
culture-invariant number representation. Of course, converting parameters
to literal strings opens the potential for SQL injection attacks so you
should use caution. Numeric types don't pose any risk, but if you have any
text columns, you need to SQL-quote them before pasting them into your
generated SQL text.

On the SQL itself, you have a couple of options. First, you could generate
new parameter names each time through the loop, but you'll be subject to the
whim of the provider with regard to limits on the number of named parameters
allowed. SQL Server allows up to 2100 named parameters, but other DB
providers will vary. If you never have more than "a few" (whatever that
means) items, you'd probably be safe generating a new parameter name for
each iteration of the loop. I guess you don't consider this option to be
"clean" - it does have it's limitations, but it shouldn't be dismissed
outright IMO.

Regardless of whether you come up with a way to parameterize the SQL, you
can also improve the efficiency of your inserts by inserting more than one
row per statement. A couple of structures that can be used are:

insert into Table (c1, c2, c3)
select 1 as c1, 2 as c2, 3 as c3
union all select 4,5,6
union all select 7,8,9


You can cascade as many "union all select" clauses as you want, but the
"sweet spot" will be fairly small - probably 10 or fewer, otherwise query
compilation time gets to be too high.

Another possiblity is:

insert into Table(c1, c2, c3)
exec(@'
select 1, 2, 3
select 4, 5, 6
select 7, 8, 9
')

Again, you can add as many selects as you want, but the sweet spot will be
fairly small - but larger than the construct above. In one application,
using this construct improved insert speed by nearly a factor of 4 over
simple scalar inserts.

Of course, depending on how many database backends you need to target,
neither of these may work. Both will work with SQL server 2000 or 2005.

HTH

-cd

 

[Carl Daniel [VC++ MVP]]

I would recommend using a SqlDataAdapter for this and create a data set
which has the values you want to insert into the table. You can tell the
SqlDataAdapter (in .NET 2.0) to batch the commands, and it will take care
of putting everything together for you (the commands, the parameters, etc,
etc).

Out of curiousity - do you know what kind of SQL it generates for batched
inserts?

-cd