SQL Server连接在几百个循环后冻结

Question

. NET Core 5项目我使用System.Data.SQLClient4.8.2连接到SQL Server 11.

代码运行正常

public List<Contract> ReadDBView(Contract contract)
{
List<Contract> contracts = new List<Contract> { };
try
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "server01";
builder.UserID = "user1";
builder.Password = "SecretPassword";
builder.InitialCatalog = "archiv1";
String sql = "SELECT name, group_name, group_country_code, FROM[dbo].[GROUPS] where number like '%" + contract.Number.ToString() + "'";
using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
contract.DealerName = reader["name"].ToString();
contract.GroupName = reader["group_name"].ToString();
contract.Country = reader["group_country_code"].ToString();
contracts.Add(contract);
}
}
}
connection.Close();
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
return contracts;
}

但过了一会儿它就冻结了——你知道如何防止冻结吗?

返回值有时为空。由于我很长一段时间没有使用c#，因此非常感谢任何关于代码的提示。

Answer 1

考虑返回一个已命名的ValueTuple

下面的示例使用了一个与您的表不匹配的现有表，但演示了一种发现是否抛出异常的方法。使用控制台。WriteLine可以用于调试(最好使用Debug.WriteLine)，但为什么不能用于生产环境呢?

• 考虑按照@jeroen Mostert的建议添加日志
• 表不是设置索引明智的类似条件，所以它是不有效的。关键是要弄清楚是否有异常抛出。
• 使用c# 9， . net Core 5语法，例如using声明而不是语句体。
后端代码

public class SqlOperations
{
public static string ConnectionString =
"Data Source=.\SQLEXPRESS;Initial Catalog=PaginationExample;Integrated Security=True";

public static (List<Contract>, Exception exception) ReadDBView(string firstNameValue)
{
List<Contract> contracts = new();
var selectStatement = 
"SELECT Id, FirstName, LastName, Balance " + 
"FROM dbo.LotsOfData " + 
"WHERE FirstName LIKE @FirstNameLike;";
try
{
using var cn = new SqlConnection() { ConnectionString = ConnectionString };
using var cmd = new SqlCommand() { Connection = cn, CommandText = selectStatement };
cmd.Parameters.Add("@FirstNameLike", SqlDbType.NVarChar).Value = firstNameValue;

cn.Open();
var reader = cmd.ExecuteReader();
if (!reader.HasRows) return (contracts, null);
while (reader.Read())
{
contracts.Add(new Contract()
{
Id = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2),

Balance = reader.IsDBNull("Balance") ? 
(decimal?)null : 
reader.GetDecimal("Balance")

});
}
return (contracts, null);
}
catch (Exception exception)
{
return (null, exception);
}

}
}
// place in own file
public class Contract
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public decimal? Balance { get; set; }
public override string ToString() => $"{FirstName} {LastName}";
}

使用单元测试方法

[TestMethod]
public void LargeLike()
{
var (contracts, exception) = SqlOperations.ReadDBView("%nia");
if (contracts.Count > 0 && exception is null)
{
Debug.WriteLine(contracts.Count);
}
else
{
Debug.WriteLine(exception is not null ? exception.Message : "No matches");
}
}

using语法

public static void UsingExample()
{
// C#9, .NET Core 5 syntax
using var cn1 = new SqlConnection() { ConnectionString = ConnectionString };
// .NET 4.8 syntax
using (var cn2 = new SqlConnection() { ConnectionString = ConnectionString })
{

}

}