英文:
Is there any way to cancel executing query and close database in C#?
问题
我正在制作一个asp.net应用程序，用于对数据库查询进行测量。我的网页上有两个按钮，分别是“开始”和“停止”。开始按钮的功能是连接到PostgreSQL数据库，然后执行100个查询，然后关闭连接：
Connect();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = Connection;
for (int i = 0; i < 100; i++)
{
cmd.CommandText = queries[i];
cmd.ExecuteReader().Close();
}
Close();
这个函数通常需要7秒钟。当我在函数执行过程中点击停止按钮，即Connection.Close()函数时，我会收到错误提示，指出有正在执行的查询，因此无法关闭连接。有没有办法在执行循环时取消正在执行的查询，然后关闭连接？
我尝试通过查询的pid来取消查询，但不起作用（可能是实现有问题，因为具有该pid的查询已经完成）。
英文:
I am making asp.net app which makes database query measurement. There are two buttons on my webpage start and stop. Start button function connects to PostgreSQL database, then execute 100 queries and then close the connection:
Connect();
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.Connection = Connection;
for (int i = 0; i < 100; i++)
{
cmd.CommandText = queries[i];
cmd.ExecuteReader().Close();
}
Close();
This function usually takes 7 seconds.
When I click to stop button during function execution which function is Connection.Close(), I am getting error that there is executing query, so connection cannot be closed. Is there any way how can I cancel executing query and then close connection while there is execution loop?
I tried somehow to cancel query by his pid, but doesn't works (probably bad implementation because query with that pid already done)
答案1
得分: 1
我认为我会放弃尝试“取消”正在运行的查询的想法。这有点像向服务器扔一根炸药棒。
这里的“真正”目标实际上不是停止运行的查询，而是“温和”地停止我们的查询处理循环。
换句话说，让服务器只有大约半秒的处理时间。它可能做5个查询，也可能做10个。然而，如果我们决定停止查询处理循环，那么浏览器将在大约半秒内至少作出响应 - 不会更多。
因此，更好的想法是“停止”处理循环。所以，当我们按下停止按钮时，可能会再运行5-10个查询 - 但那不到1秒，我们不在乎。
因此，我建议我们将这个“挑战”分解为以下几个部分：
调用服务器开始运行一些查询 - 但只允许它运行大约半秒钟。不在乎它只完成了1个查询，还是说完成了10个查询。
然后，当完成这个“块”时，我们可以更新进度。而“奖励”是我们现在可以跟踪/看到/显示到目前为止完成了多少个查询！
在这一点上？
好吧，如果用户按下停止按钮，那么我们就停止。
但是，还有更多查询要运行吗？然后简单地继续！（并从上一次完成的查询+1继续）。
因此，这种方法不仅解决了终止运行的查询的“问题”（我们试图向服务器扔的炸药棒），而且意味着我们的停止按钮运行良好，正如我们已经注意到的那样，我们可以看到到目前为止完成了多少个查询。
因此，我们的服务器端Web方法可能是这样的：
（再次，我没有100个查询，但无关紧要 - 警告：示例代码！）。
服务器端代码：
public class MyStatInfo
{
public int NumQuery = 112;
public int QueryDone = 0;
}
[WebMethod]
public static MyStatInfo RunQuery(int StartQuery)
{
MyStatInfo cQueryinfo= new MyStatInfo();
// run queries untill at "least" 0.5 seocnds has passed.
double RunTime = 500; // adjiust a bit up or down
double sStart = DateTime.Now.TimeOfDay.TotalMilliseconds;
while (StartQuery <= cQueryinfo.NumQuery)
{
// fake query
// do query (StartQuery)
cQueryinfo.QueryDone++;
System.Threading.Thread.Sleep(100); // Fake query delay time
double sNow = DateTime.Now.TimeOfDay.TotalMilliseconds;
if (sNow >= RunTime) {
// return to client
return cQueryinfo;
}
StartQuery++;
}
return cQueryinfo;
}
所以，该例程将传递“起始”查询号，从我们的112个（或多少）中开始（执行每个查询），但仅直到我们用尽超过半秒的处理时间。
因此，现在我们的客户端标记：
2个仪表盘和2个按钮（启动和停止）。
因此，标记：
<div id="gauge0" style="float:left;margin-left:25px;width:120px"></div>
<div id="gauge1" style="float:left;margin-left:30px;width:120px;"></div>
<div style="clear:both"></div>
<div>
<asp:Button ID="cmdStart" runat="server" Text="Start Query Test"
style="float:left;margin-left:20px" CssClass="btn btn-info"
OnClientClick="mystart();return false"
/>
<asp:Button ID="cmdStop" runat="server" Text="Stop Query Test"
style="float:left;margin-left:30px" CssClass="btn btn-danger"
OnClientClick="mystop();return false"
/>
</div>
所以，2个按钮 + 2个仪表盘。
而在上述标记正下方，我们只是不断地调用服务器端例程，直到全部完成或按下停止按钮。
<script>
var gauge0;
var gauge1;
var MyTimer;
var ElapsedMSecs = 0.0
var StopLoop = false
var qPtr = 1
var QueryDone = 0
var QueryRemain = 0
function mystart() {
ElapsedMSecs = 0
gauge0.refresh(0, 15, 0)
gauge1.refresh(0, 50, 0)
qPtr = 1 // query to start at
qQueryDone = 0 // number of query done so far
qRemain = 0
StopLoop = false
startloop()
}
function mystop() {
StopLoop = true
}
function startloop() {
var ETime = new Date();
$.ajax({
type: "POST",
url: "GuageTest3.aspx/RunQuery",
data: JSON.stringify({StartQuery : qPtr}),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (MyStats) {
var ETime2 = new Date();
ElapsedMSecs += (ETime2 - ETime)
gauge0.refresh((ElapsedMSecs / 1000))
qQueryDone += MyStats.d.QueryDone
gauge1.refresh(qQueryDone,MyStats.d.NumQuery, 0)
// check for stop button value
if (StopLoop)
return
// more query to run???
if ( (MyStats.d.NumQuery - qQueryDone) > 0) {
qPtr = qQueryDone + 1
startloop()
}
},
error: function (xhr, status, error) {
var errorMessage = xhr.status + ': ' + xhr.statusText
alert('Error - ' + errorMessage)
}
});
}
</script>
当然，就在上面的下方，是仪表盘设置代码：
window.onload = function () {
gauge0 = new JustGage({
id: "g
<details>
<summary>英文:</summary>
I think I would "dump" the idea of trying to "cancel" a running query. It's kind of like throwing a stick of dynamite at the server anyway.
The "real" goal here is not really to STOP a running query, but in fact to "nice" and "gentle" stop our query processing loop.
In other words, let's give the server ONLY say a 1/2 second of processing time. It might do 5 query, maybe 10. However, THEN if we decide to stop the query process loop, then the browser will respond at least within about 1/2 second - not much more.
So, better idea is to "stop" the process loop. So, when we hit stop button, maybe 5-10 more queries run - but that's less then 1 second, and we don't care.
So, I suggest we break down this "challenge" to that of
Call the server to start running some queries - but ONLY let it run for say about 1/2 second. Don't care if it only gets 1 query done, or say 10 done.
Then, when that "chunk" is done, we can update the progress. And "bonus" here is we can then track/see/display HOW many queries are done so far!!!
At this point then?
Well, if user hit stop button, then we stop.
However, more queries to run? Then simple keep going!!! (and CONTINUE from +1 last query done).
So, this approach not only removes the "problem" of killing a running query (that stick of dynamite we're trying to throw at the server), but it also means our stop button works well, and we as noted also then get how many queries have been done so far.
So, our server side web method could be this:
(Again, I don't have the 100 queries, but don't matter - air code warning!!).
Server side code:
public class MyStatInfo
{
public int NumQuery = 112;
public int QueryDone = 0;
}
[WebMethod]
public static MyStatInfo RunQuery(int StartQuery)
{
MyStatInfo cQueryinfo= new MyStatInfo();
// run queries untill at "least" 0.5 seocnds has passed.
double RunTime = 500; // adjiust a bit up or down
double sStart = DateTime.Now.TimeOfDay.TotalMilliseconds;
while (StartQuery <= cQueryinfo.NumQuery)
{
// fake query
// do query (StartQuery)
cQueryinfo.QueryDone++;
System.Threading.Thread.Sleep(100); // Fake query delay time
double sNow = DateTime.Now.TimeOfDay.TotalMilliseconds;
if (sNow >= RunTime) {
// return to client
return cQueryinfo;
}
StartQuery++;
}
return cQueryinfo;
}
So, that routine is to be passed the "starting" query number out of our 112 (or however many). it will start execute of each query - but ONLY until such time wew used up more then 1/2 second of processing.
So, now our client side markup:
2 gauge(s), and 2 buttons (start and stop).
So, markup:
<div id="gauge0" style="float:left;margin-left:25px;width:120px"></div>
<div id="gauge1" style="float:left;margin-left:30px;width:120px;"></div>
<div style="clear:both"></div>
<div>
<asp:Button ID="cmdStart" runat="server" Text="Start Query Test"
style="float:left;margin-left:20px" CssClass="btn btn-info"
OnClientClick="mystart();return false"
/>
<asp:Button ID="cmdStop" runat="server" Text="Stop Query Test"
style="float:left;margin-left:30px" CssClass="btn btn-danger"
OnClientClick="mystop();return false"
/>
</div>
So, 2 buttons + 2 gauge.
And right below above markup, then we simple "keep" calling the server side routine over and over until all done, or stop button press.
<script>
var gauge0;
var gauge1;
var MyTimer;
var ElapsedMSecs = 0.0
var StopLoop = false
var qPtr = 1
var QueryDone = 0
var QueryRemain = 0
function mystart() {
ElapsedMSecs = 0
gauge0.refresh(0, 15, 0)
gauge1.refresh(0, 50, 0)
qPtr = 1 // query to start at
qQueryDone = 0 // number of query done so far
qRemain = 0
StopLoop = false
startloop()
}
function mystop() {
StopLoop = true
}
function startloop() {
var ETime = new Date();
$.ajax({
type: "POST",
url: "GuageTest3.aspx/RunQuery",
data: JSON.stringify({StartQuery : qPtr}),
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (MyStats) {
var ETime2 = new Date();
ElapsedMSecs += (ETime2 - ETime)
gauge0.refresh((ElapsedMSecs / 1000))
qQueryDone += MyStats.d.QueryDone
gauge1.refresh(qQueryDone,MyStats.d.NumQuery, 0)
// check for stop button value
if (StopLoop)
return
// more query to run???
if ( (MyStats.d.NumQuery - qQueryDone) > 0) {
qPtr = qQueryDone + 1
startloop()
}
},
error: function (xhr, status, error) {
var errorMessage = xhr.status + ': ' + xhr.statusText
alert('Error - ' + errorMessage)
}
});
}
And of course right below above, the gauge setup code:
window.onload = function () {
gauge0 = new JustGage({
id: "gauge0",
value: 0,
min: 0,
max: 50,
label: "Query Time",
showInnerShadow: true,
shadowOpacity: 2,
shadowSize: 5,
donut: true,
counter:false,
decimals:2,
levelColors: [
"#00fff6",
"#ff00fc",
"#1200ff"
]
});
gauge1 = new JustGage({
id: "gauge1",
value: 0,
min: 0,
max: 10,
label: "Query Count",
showInnerShadow: true,
shadowOpacity: 2,
shadowSize: 5,
donut: true,
refreshAnimationType: "bounce",
startAnimationType: ">",
counter: true
});
}
so, now when we run, we get see this:
[![enter image description here][1]][1]
[1]: https://i.stack.imgur.com/1rzP1.gif
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论