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%5Fmembers%28BTS.10%29.aspx

I am declaring my database like this:

SqlDatabase db = new SqlDatabase(this.ConnectionString );

Answer 1

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.

Answer 2

If you are on SQL Server 2008 (many aren't yet...) you can use table variables! Check out: http://blogs.techrepublic.com.com/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.

Answer 3

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