title: C# method to lock SQL Server table
body: <p>I have a C# program that needs to perform a group of mass updates (20k+) to a SQL Server table. Since other users can update these records one at a time via an intranet website, we need to build the C# program with the capability of locking the table down. Once the table is locked to prevent another user from making any alterations/searches we will then need to preform the requested updates/inserts. </p>
  
  <p>Since we are processing so many records, we cannot use <code>TransactionScope</code> (seemed the easiest way at first) due to the fact our transaction winds up being handled by the <a href="http://en.wikipedia.org/wiki/Microsoft_Distributed_Transaction_Coordinator">MSDTC service</a>. We need to use another method. </p>
  
  <p>Based on what I've read on the internet using a <code>SqlTransaction</code> object seemed to be the best method, however I cannot get the table to lock. When the program runs and I step through the code below, I'm still able to perform updates and search via the intranet site. </p>
  
  <p>My question is twofold. Am I using the <code>SqlTransaction</code> properly? If so (or even if not) is there a better method for obtaining a table lock that allows the current program running to search and preform updates? </p>
  
  <p>I would like for the table to be locked while the program executes the code below. </p>
  
  <p><strong>C#</strong></p>
  
  <pre><code>SqlConnection dbConnection = new SqlConnection(dbConn);
  
  dbConnection.Open();
  
  using (SqlTransaction transaction = dbConnection.BeginTransaction(IsolationLevel.Serializable))
  {
      //Instantiate validation object with zip and channel values
      _allRecords = GetRecords();
      validation = new Validation();
      validation.SetLists(_allRecords);
  
      while (_reader.Read())
      {
          try
          {
              record = new ZipCodeTerritory();
              _errorMsg = string.Empty;
  
              //Convert row to ZipCodeTerritory type
              record.ChannelCode = _reader[0].ToString();
              record.DrmTerrDesc = _reader[1].ToString();
              record.IndDistrnId = _reader[2].ToString();
              record.StateCode = _reader[3].ToString().Trim();
              record.ZipCode = _reader[4].ToString().Trim();
              record.LastUpdateId = _reader[7].ToString();
              record.ErrorCodes = _reader[8].ToString();
              record.Status = _reader[9].ToString();
              record.LastUpdateDate = DateTime.Now;
  
              //Handle DateTime types separetly
              DateTime value = new DateTime();
              if (DateTime.TryParse(_reader[5].ToString(), out value))
              {
                  record.EndDate = Convert.ToDateTime(_reader[5].ToString());
              }
              else
              {
                  _errorMsg += "Invalid End Date; ";
              }
              if (DateTime.TryParse(_reader[6].ToString(), out value))
              {
                  record.EffectiveDate = Convert.ToDateTime(_reader[6].ToString());
              }
              else
              {
                  _errorMsg += "Invalid Effective Date; ";
              }
  
              //Do not process if we're missing LastUpdateId
              if (string.IsNullOrEmpty(record.LastUpdateId))
              {
                  _errorMsg += "Missing last update Id; ";
              }
  
              //Make sure primary key is valid
              if (_reader[10] != DBNull.Value)
              {
                  int id = 0;
                  if (int.TryParse(_reader[10].ToString(), out id))
                  {
                      record.Id = id;
                  }
                  else
                  {
                      _errorMsg += "Invalid Id; ";
                  }
              }
  
              //Validate business rules if data is properly formatted
              if (string.IsNullOrWhiteSpace(_errorMsg))
              {
                  _errorMsg = validation.ValidateZipCode(record);
              }
  
              //Skip record if any errors found
              if (!string.IsNullOrWhiteSpace(_errorMsg))
              {
                  _issues++;
  
                  //Convert to ZipCodeError type in case we have data/formatting errors
                  _errors.Add(new ZipCodeError(_reader), _errorMsg);
                  continue;
              }
              else if (flag)
              {
                  //Separate updates to appropriate list
                  SendToUpdates(record);
              }
          }
          catch (Exception ex)
          {
              _errors.Add(new ZipCodeError(_reader), "Job crashed reading this record, please review all columns.");
              _issues++;
          }
      }//End while
  
  
      //Updates occur in one of three methods below. If I step through the code,
      //and stop the program here, before I enter any of the methods, and then 
      //make updates to the same records via our intranet site the changes
      //made on the site go through. No table locking has occured at this point. 
      if (flag)
      {
          if (_insertList.Count &gt; 0)
          {
              Updates.Insert(_insertList, _errors);
          }
          if (_updateList.Count &gt; 0)
          {
              _updates = Updates.Update(_updateList, _errors);
              _issues += _updateList.Count - _updates;
          }
          if (_autotermList.Count &gt; 0)
          {
              //_autotermed = Updates.Update(_autotermList, _errors);
              _autotermed = Updates.UpdateWithReporting(_autotermList, _errors);
              _issues += _autotermList.Count - _autotermed;
          }
      } 
  
      transaction.Commit();
  }
  </code></pre>