title: Determine the new autoincrement primary key value "before" creating a new row
body: <p>I have a <strong>Winforms TreeView</strong> and I wish to store the node data into an SQLite table, so that I can restore the TreeView when the application starts up next time. I use the TreeView to drive most of the desktop application and use index pointer fields within a class attached to the TreeNodes to access other tables in the database when a user clicks on a node.  </p>
  
  <p>The problem I am trying to avoid is a lot of "round tripping" between the TreeView and the TreeNode_Table everytime a new TreeNode is created.  It would be best if I know ahead of time what the <strong>next autoincrement primary key will be</strong> in the TreeNode_Table <strong><em>before</em></strong> I create the TreeNode. That way I can create and add the node to the tree then copy the data into a new TreeNode_Table row in a single pass.  No round trips between the table and the TreeView to sync up values like the primary key, node level, node index, and other node and row values.</p>
  
  <p>I tried:</p>
  
  <pre><code>string strFindLastRowInserted = @"SELECT MAX([id]) FROM [TreeNode_Table];";
  var findLastRowInsertedCmd = new SQLiteCommand(strFindLastRowInserted, _cnn);
  object resultObj = findLastRowInsertedCmd.ExecuteScalar();
  findLastRowInsertedCmd.Dispose();
  if( resultObj != null)
  NodeTagDataObj.DataPtr = (long)resultObj + 1;    
  </code></pre>
  
  <p>But adding one to <strong>MAX([id])</strong> breaks down if some of the most recently created nodes/rows have been previously deleted. I'm using the <strong>System.Data.Sqlite</strong> library to access the data file.</p>
  
  <p>Thank you for your help. Any ideas?</p>