我有一个问题。
我尝试在我的C#代码中复制多个sp(存储过程)调用,但我想以异步方式进行。
TSQL示例:
(执行sp @key = 15072000173475; 执行sp @key = 15072000173571; ... 执行sp @key = n;
)
[TestClass]
public class UnitTestNomenclature {
[TestMethod]
public void ParallelSQLMethod() {
Task scropeTasks = null;
//real amount is more then 1500
long[] keys = new long[] {15072000173475,15072000173571 ... n };
try {
var tasks = keys.Select( i => Task.Run(async () => { await RunStoredProc(i); }));
scropeTasks = Task.WhenAll(tasks);
scropeTasks.Wait();
} catch (Exception ex) {
Debug.WriteLine("Exception: " + ex.Message);
Debug.WriteLine("IsFaulted: " + scropeTasks.IsFaulted);
foreach (var inx in scropeTasks.Exception.InnerExceptions) {
Debug.WriteLine("Details: " + inx.Message);
}
}
Assert.AreEqual(1, 1);
}
public async Task RunStoredProc(long scollNumbParam) {
const string strStoredProcName = @"[dbo].[sp]";
using (SqlConnection conn = new SqlConnection(@"data source=SERVER;initial catalog=Db;integrated security=True;Trusted_Connection=Yes;")) {
await conn.OpenAsync();
Debug.WriteLine("============================================ Connection is open: ==============================================");
// info
Debug.WriteLine(String.Format("Connection: {0}", conn.ClientConnectionId));
Debug.WriteLine(String.Format("State: {0}", conn.State.ToString()));
using (SqlCommand cmd = new SqlCommand(strStoredProcName, conn) { CommandTimeout = 120, CommandType = CommandType.StoredProcedure }) {
SqlParameter scrParam = new SqlParameter() {
ParameterName = "@KEYKRT",
Value = scollNumbParam,
SqlDbType = SqlDbType.BigInt
};
cmd.Parameters.Add(scrParam);
Debug.WriteLine("Start of Proccesing: " + scollNumbParam);
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
Debug.WriteLine("End of Proccesing: " + scollNumbParam);
}
}
Debug.WriteLine("============================================ Connection is closed: ==============================================");
}
}
这是我在输出窗口中得到的内容:
========== Connection is open: ========
Connection: 5be9c681-6eb5-422f-a22c-b49689a2d912
State: Open
Start of Proccesing: 15072000173475
========== Connection is open: ==========
Connection: cfb66041-6646-4b56-be1c-2afb26a18cb8
State: Open
Start of Proccesing: 15072000173571
.....
End of Proccesing: 15072000173475
=========== Connection is closed: =========
End of Proccesing: 15072000173571
=========== Connection is closed: =========
....
A timeout occurred while waiting for memory resources to execute the query in resource pool 'default' (2). Rerun the query.
Actual error number: 8645
Actual line number: 98
同时调试信息显示连接池已满。我认为主要原因是连接没有被适当处理,但我如何使用异步实现呢?如果在声明异步任务之前尝试只打开一个连接并将其传递给RunStoredProc方法,则会出现连接不支持MultipleActiveResultSets的错误。
using (SqlConnection conn = new SqlConnection(@"data source=SERVER;initial catalog=Db;integrated security=True;Trusted_Connection=Yes;)) {
conn.OpenAsync();
var tasks = keys.Select(i => Task.Run(async () => { await RunStoredProc(i, conn); }));
scropeTasks = Task.WhenAll(tasks);
scropeTasks.Wait();
}
Debug.WriteLine("========== Connection is closed: ==========");
这是我在输出窗口中得到的内容:
Connection: 5be9c681-6eb5-422f-a22c-b49689a2d912
State: Open
Start of Proccesing: 15072000173475
======= Connection is open: =============
Connection: cfb66041-6646-4b56-be1c-2afb26a18cb8
State: Open
Start of Proccesing: 15072000173571
========= Connection is open: =========