0

This is probably not possible, but I have a method that accept 'list', this method will execute multiple sql server stored procedure by looping the 'list' object.

public void ExecuteMultipleProc(string strcon, ref List<KeyValuePair<string, SqlParameter[]>> list)
{
    using (SqlConnection sqlcon = new SqlConnection(strcon)) {
        sqlcon.Open();
        SqlTransaction sqltran = default(SqlTransaction);
        sqltran = sqlcon.BeginTransaction();

        try {
            foreach (KeyValuePair<string, SqlParameter[]> kv in list) {
                SqlCommand sqlcmd = new SqlCommand(kv.Key, sqlcon);
                sqlcmd.CommandType = CommandType.StoredProcedure;

                if ((kv.Value == null) == false) {
                    foreach (SqlParameter x in kv.Value) {
                        sqlcmd.Parameters.Add(x);
                    }
                }

                sqlcmd.Transaction = sqltran;
                sqlcmd.ExecuteNonQuery();
            }

            sqltran.Commit();
        } catch (Exception ex) {
            sqltran.Rollback();
            throw;
        }
    }
}

I have a problem when I need to insert table with relation aka Header Detail table because my InsertDetail need output from InsertHeader for example :

List<KeyValuePair<string, SqlParameter[]>> list = new List<KeyValuePair<string, SqlParameter[]>>();

SqlParameter[] params = new SqlParameter[2];
params(0) = new SqlParameter("@test", SqlDbType.VarChar, 200);
params(0).Value = "TEST";
params(1) = new SqlParameter("@id", SqlDbType.Int);
params(1).Direction = ParameterDirection.Output;

list.Add(new KeyValuePair<string, SqlParameter[]>("spInsertHeader", params));

SqlParameter[] param = new SqlParameter[1];
param(0) = new SqlParameter("@id", SqlDbType.Int);
param(0).Value = Convert.ToInt32(params(1).Value); //THIS LINE WILL NEVER HAVE A VALUE BECAUSE THE CALL TO DATABASE ISN'T HAPPENED YET

list.Add(new KeyValuePair<string, SqlParameter[]>("spInsertDetail", param));

ExecuteMultipleProc(GetConnectionString(), list);

My question : is it possible to achieve this by detecting value change when my method call InsertHeader and pass the output value to InsertDetail without breaking the method functionality. Sorry for bad english, any help will be appreciated.

tickwave
  • 3,335
  • 6
  • 41
  • 82
  • not possible because this isn't a personal pet project – tickwave Jan 26 '15 at 07:41
  • 1
    How about using XML datatype to insert at one go? If that fits to your solution, you can refer for [sample](http://stackoverflow.com/questions/3600091/how-to-pass-xml-from-c-sharp-to-a-stored-procedure-in-sql-server-2008) – Hari Prasad Jan 26 '15 at 07:41
  • `if you’re writing ADO.Net code by hand, you’re stealing from your employer or client.` Jeremy Miller – aloisdg Jan 26 '15 at 08:03
  • Can you use an `event` that gets raised during the `InsertDetail` method? – Evil Dog Pie Jan 26 '15 at 09:07

0 Answers0