title: C# Equivalent of adodb recordsets
body: <p>I am converting a VB6 windows application to C# using VS2008, V3.5.  I have a SQL Server 2000 database I use for data storage and retrieval.  One table holds a single record that is of type Int and is used for generating a quote number, ie, 123456.  In VB6 I do the following:</p>
  
  <pre><code>OpenDBCon
  Set rst = New ADODB.Recordset
  rst.Open "SELECT idx From tblQuoteIdx", cn, adOpenDynamic, adLockPessimistic
  Select Case rst.BOF
  Case False
      rst.MoveFirst
          Me.txtRef = rst!idx
          tID = rst!idx + 1
      OKToContinue = True
  Case False
      'Do something
  End Select
  
  If OKToContinue = True Then
    Set rst = New ADODB.Recordset
    rst.Open "update tblQuoteIdx set idx = '" &amp; tID &amp; "' ", cn, adOpenDynamic, 
      adLockPessimistic
  End If
  CloseDBCon
  </code></pre>
  
  <p>In C# I currently am doing this:</p>
  
  <pre><code>        SqlConnection conn = new SqlConnection();
          conn.ConnectionString = Vars.connstring;
          conn.Open();
          SqlCommand sqlComm = new SqlCommand("Select idx from tblQuoteIdx", conn);
          Int32 tt = (Int32)sqlComm.ExecuteScalar();
          Int32 tt2 = tt++;
          SqlCommand sqlComm2 = new SqlCommand("Update tblQuoteIdx set " +
           "idx = " + tt2    + "", conn);
          sqlComm.ExecuteNonQuery();
          conn.Close();
  </code></pre>
  
  <p>Unfortunately I find now without a cursor lock of "adLockPessimistic" when several people hit the record at the same time multiple instances of the same index number can show up.  If anyone could either explain how to use an ADODB.Recordset in C# for this specific purpose and or use record locks so as to lock the db record when needed, OR a better way within the .Net framework and C# principals to accomplish the same thing, I would be greatful.  Many thanks in advance.</p>
  
  <p>Noel</p>