title: Turn off IDENTITY_INSERT for Dataset insert
body: <p>I am using a dataset to insert data being converted from an older database. The requirement is to maintain the current Order_ID numbers.</p>
  
  <p>I've tried using:</p>
  
  <pre><code>SET IDENTITY_INSERT orders ON;
  </code></pre>
  
  <p>This works when I'm in SqlServer Management Studio, I am able to successfully</p>
  
  <pre><code>INSERT INTO orders (order_Id, ...) VALUES ( 1, ...);
  </code></pre>
  
  <p>However, it does not allow me to do it via the dataset insert that I'm using in my conversion script. Which looks basically like this:</p>
  
  <pre><code>dsOrders.Insert(oldorderId, ...);
  </code></pre>
  
  <p>I've run the SQL (SET IDENTITY_INSERT orders ON) during the process too. I know that I can only do this against one table at a time and I am.</p>
  
  <p>I keep getting this exception:</p>
  
  <blockquote>
    <p>Exception when attempting to insert a value into the orders table
    System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'orders' when IDENTITY_INSERT is set to OFF.</p>
  </blockquote>
  
  <p>Any ideas?</p>
  
  <p><strong>Update</strong> </p>
  
  <p>AlexS &amp; AlexKuznetsov have mentioned that Set Identity_Insert is a connection level setting, however, when I look at the SQL in SqlProfiler, I notice several commands.</p>
  
  <ul>
  <li>First - <code>SET IDENTITY_INSERT DEAL ON</code></li>
  <li>Second - <code>exec sp_reset_connection</code></li>
  <li>Third to n - my various sql commands including select &amp; insert's</li>
  </ul>
  
  <p>There is always an <code>exec sp_reset_connection</code> between the commands though, I believe that this is responsible for the loss of value on the Identity_Insert setting.</p>
  
  <p>Is there a way to stop my dataset from doing the connection reset?</p>