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.