C# DbCommand in a loop

Question

I have a collection of files, for each file I am going to call an SP using Dbcommand in a transaction.

for example:

  DbCommand insert = db.GetStoredProcCommand("Insert");
  db.AddInParameter(insert, "FileName", System.Data.DbType.String, 
      ID + ".xml");
  db.ExecuteNonQuery(insert, transaction);

My question is how do I put this in a loop?

The answer below doesn't work, but thanks for the otherwise great code sample. Problem is db doesn't have a Parameters collection that you can manipulate. check...

http://msdn.microsoft.com/en-us/library/microsoft.practices.enterpriselibrary.data.sql.sqldatabase_members%28BTS.10%29.aspx

I am declaring my database like this:

SqlDatabase db = new SqlDatabase(this.ConnectionString );

What C# type is the collection of files?

Mark Bell
– Mark Bell

2009-09-30 15:27:51 +00:00
Commented Sep 30, 2009 at 15:27 — Mark Bell
– Mark Bell, Commented Sep 30, 2009 at 15:27

JL. · Accepted Answer · 2009-09-30 16:23:34Z

9

DbCommand insert = db.GetStoredProcCommand("Insert");
foreach (string ID in myIDs)
{
    insert.Parameters.Clear();
    db.AddInParameter(insert, "FileName", System.Data.DbType.String, 
        ID + ".xml");
    db.ExecuteNonQuery(insert, transaction);
}

You could also just add the Parameter once outside the loop, and then change its Value inside the loop. Half of one, six dozen of the other.

edited Sep 30, 2009 at 16:23

JL.

81.7k134 gold badges318 silver badges466 bronze badges

answered Sep 30, 2009 at 15:29

MusiGenesis

75.6k41 gold badges199 silver badges340 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MusiGenesis Over a year ago

Is someone actually downvoting this?

JL. Over a year ago

Just a note - the parameters need to be cleared on the DbCommand, answer updated, rest looks good, thanks

MusiGenesis Over a year ago

@JL: I wasn't sure what classes you were using in your sample - I usually use the System.Data.SqlClient namespace. I'd never seen an "AddInParameter" method on anything before.

JL. Over a year ago

Cool stuff, good guess :) and who ever downvoted, no reason to downvote, the code works!

Rob Fonseca-Ensor Over a year ago

This might work but the performance will get much worse as the number of files goes up. Once you find you have performance problems, take a look at Erich's answer

Community · Accepted Answer · 2015-08-20 04:40:52Z

3

If you are on SQL Server 2008 (many aren't yet...) you can use table variables! Check out: https://web.archive.org/web/1/http://blogs.techrepublic%2ecom%2ecom/datacenter/?p=168

That way, you can do the entire set of files in 1 command, saving a bunch of time.

Alternatively, you could join all of the file names with a separator character, then split them on the SQL stored procedure.

The advantage is you minimize the transactions to the DB, the downside is the programming isn't as straight forward.

edited Aug 20, 2015 at 4:40

CommunityBot

11 silver badge

answered Sep 30, 2009 at 15:31

Erich

3,9721 gold badge25 silver badges21 bronze badges

1 Comment

ChrisLively Over a year ago

okay, this is just awesome. I have several apps which would immediately benefit from this. Thanks!

brainimus · Accepted Answer · 2011-03-16 22:10:53Z

0

db.AddInParameter(cmd, "xxx", DbType.Int32);
db.AddInParameter(cmd, "xxx", DbType.Int32);
db.AddInParameter(cmd, "xxx", DbType.Int32);
int id = 0;
db.AddOutParameter(cmd, "ccc", DbType.Int32, id);

foreach (xxx item in xxxx)
{
    db.SetParameterValue(cmd, "xxx", item.InvestmentProgramId);
    db.SetParameterValue(cmd, "xxx", item.CompanyId);
    db.ExecuteNonQuery(cmd);
    id = (int)db.GetParameterValue(cmd, "ccc");
    item.Id = id;
}

edited Mar 16, 2011 at 22:10

brainimus

11.1k14 gold badges44 silver badges65 bronze badges

answered Feb 3, 2010 at 11:48

sanjeevsah.1986

1

Collectives™ on Stack Overflow

C# DbCommand in a loop

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related