title: Using SqlTransaction in C#, how to get an output parameter from a stored procedure and insert into another table?
body: <p>I have two tables: Table1 and Table2. I need newly inserted Id (from <code>Table1</code>) as an output parameter which should be inserted into <code>Table2</code>. And I must use <code>SqlTransaction</code> or <code>TransactionScope</code> here.</p>
  
  <p>Stored procedure:</p>
  
  <pre><code>    CREATE PROC InsertTable1
      (
          @Name nvarchar(50),
          @OutputParam int out
      )
      AS
      BEGIN
          INSERT INTO Table1(Name) 
          VALUES(@Name)
  
          SELECT @OutputParam = SCOPE_IDENTITY() 
      END
  </code></pre>
  
  <p>C# code:</p>
  
  <pre><code>SqlTransaction transaction = db.sqlconn.BeginTransaction();
  
  SqlParameter[] param = new SqlParameter[2];
  param[0] = new SqlParameter("@Name", "TestName");
  param[1] = new SqlParameter("@OutputParam", SqlDbType.Int);
  param[1].Direction = ParameterDirection.Output;
  
  SqlHelper.ExecuteNonQuery(transaction, "InsertTable1", param);
  
  int outputId = (int)param[1].Value;
  </code></pre>
  
  <p>When I run and debug the project I get an error </p>
  
  <blockquote>
    <p>Object reference not set to an instance of an object</p>
  </blockquote>
  
  <p>just after execution of </p>
  
  <pre><code>int outputId = (int)param[1].Value;
  </code></pre>
  
  <p>I think the transaction is closing the connection or something. It works if I don't use transaction. But I have to use transaction anyhow. I am open to any alternative ways to achieve the same result.</p>