Calling multiple stored procedures in .net, how to do it?

Question

I need to call a stored procedure multiple times, I'm using informix. I would like to know if calling a procedure multiple times with the same connection is the same generating the string with the multiple calls to the stored procedure and executing that as a query.

this is an example of the code:

IfxCommand cmd = new IfxCommand("storeData", myconn);
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < lbim; i++)
{

  cmd.Parameters.Add("id", IBM.Data.Informix.IfxType.VarChar, 255).Value = info.id;
  cmd.Parameters.Add("descripcionDescuentoImpuesto", IBM.Data.Informix.IfxType.VarChar, 255).Value = info.data[i].value;
  try
  {
     IfxDataReader myreader = cmd.ExecuteReader();
     if (myreader.Read())
     {
        Boolean aux = (Boolean)myreader[0];
        myreturn = aux;
     }
     myreader.Close();
  }
  catch (IfxException ex)
  {
  }
  cmd.Parameters.Clear();
}

The problem is that each stored procedure returns true or false.

Thanks

Answer 1

You should create a new IfxCommand object for each call, so just move that part of the code into the for loop. Actually this is the case no matter what provider you are using.

Answer 2

For performance reasons the best approach is to prepare command before loop. Inside the loop you can set parameter values and execute the reader. I would also improve the code with 2 things:

• using factories; this way you can easily switch between OdbcDriver and IfxDriver or something else in the future;
• error handling: you should close reader in finally section or use "usings" clause which guarantees freeing resources in case of exception; I prefere usings because in more complex scenarios finally section becomes very complicated.

This changes would give following code:

DbProviderFactory dbfactory;
dbfactory = DbProviderFactories.GetFactory("IBM.Data.Informix");
using (myconn = dbfactory.CreateConnection())
{
    myconn.ConnectionString = " ... ";
    myconn.Open();
    DbCommand cmd = dbfactory.CreateCommand();
    cmd.Connection = myconn;
    cmd.CommandText = "storeData";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Clear();

    DbParameter parameter = dbfactory.CreateParameter();
    parameter.ParameterName = "id";
    parameter.DbType = DbType.String;
    parameter.Size = 255; // probably not necessary
    cmd.Parameters.Add(parameter);

    parameter = dbfactory.CreateParameter();
    parameter.ParameterName = "descripcionDescuentoImpuesto";
    parameter.DbType = DbType.String;
    parameter.Size = 255;
    cmd.Parameters.Add(parameter);

    cmd.Prepare();
    for (int i = 0; i < lbim; i++)
    {
        cmd.Parameters[0].Value = info.id;
        cmd.Parameters[1].Value = info.data[i].value;
        using (DbDataReader myreader = cmd.ExecuteReader()) {
            if (myreader.Read())
            {
                Boolean aux = (Boolean)myreader[0];
                myreturn = aux;
            }
        }
    }
}

Code is now much longer but I think that advantages are prevailing. Even better approach is to use Spring.NET (I'm just learning it) - half the size of the code, driver independend (similarly to factories approach), automatic disposal of resources in case of exception. Also I would use rather

myreturn = (bool)cmd.ExecuteScalar();

instead of data reader. Next thing is that I'm using command type text rather and "execute procedure storeData(?,?)". This is because of Informix bug in some scenarios which I've got long time ago. Possible that this is already fixed - so probably it is no longer necessary.