Clever Geek Handbook

C # DbCommand in a loop

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

eg:

  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 great code sample. The problem is that the db does not have a collection of parameters 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 );
+2


source to share


3 answers 


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 can also just add a parameter once per loop and then change its value inside the loop. Half one, six dozen others.

+9


source


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

This way you can do the whole set of files in 1 command, saving you a lot of time.



Alternatively, you can join all the filenames with a delimiter character and then split them into an SQL stored procedure.

The advantage is that you minimize transactions in the DB, the disadvantage is that the programming is not that straightforward.

+3


source 


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


source






More articles:


All Articles