我有一个 C# 程序需要对 SQL Server 表执行一组大规模更新（20k+）。由于其他用户可以通过内部网站逐个更新这些记录，我们需要构建带有锁定表格功能的 C# 程序。一旦表被锁定以防止其他用户进行任何更改/搜索，我们将需要执行所需的更新/插入。
由于我们正在处理如此多的记录，我们不能使用TransactionScope （起初似乎是最简单的方法），因为我们的事务最后会由MSDTC 服务处理。我们需要使用另一种方法。
根据我在互联网上的阅读，使用 SqlTransaction 对象似乎是最佳方法，但我无法使表格锁定。当程序运行并且我逐步通过下面的代码时，我仍然能够通过内部网站执行更新和搜索。
我的问题有两个方面。我是否正确使用了 SqlTransaction？如果是这样（甚至不是这样），是否有更好的方法来获得允许当前程序搜索和执行更新的表格锁定?
我希望在程序执行下面的代码时该表格被锁定。
C#
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 > 0)
{
Updates.Insert(_insertList, _errors);
}
if (_updateList.Count > 0)
{
_updates = Updates.Update(_updateList, _errors);
_issues += _updateList.Count - _updates;
}
if (_autotermList.Count > 0)
{
//_autotermed = Updates.Update(_autotermList, _errors);
_autotermed = Updates.UpdateWithReporting(_autotermList, _errors);
_issues += _autotermList.Count - _autotermed;
}
}
transaction.Commit();
}