如何在单击同一按钮时更新和读取 SQL 列的值

Question

我正在做的是单击一个按钮，我正在通过向其添加 +1 来更新 SQL 列。我这样做没有遇到任何问题。现在我想要的是，对于相同的按钮单击事件，我想读取已更新的该列的值并将其以查询字符串的形式发送到另一个页面。这是代码-

int i=0;
protected void btnSubmit_Click(object sender, EventArgs e)
{
sql.Open();
string r = "update counter set m_id=m_id+1 where id=1";
SqlCommand com = new SqlCommand(r, sql);
com.ExecuteNonQuery();
sql.Close();
{
SendHTMLMail();
}


void SendHTMLMail()
{
StreamReader reader = new StreamReader(dd1.SelectedItem.Value);
string readFile = reader.ReadToEnd();
Regex regx = new Regex("(?<!src=")http(s)?://([\w+?\.\w+])+([a-zA-Z0-9\~\!\@\#\$\%\^\&amp;\*\(\)_\-\=\+\\\/\?\.\:\;\'\,]*([a-zA-Z0-9\?\#\=\/]){1})?", RegexOptions.IgnoreCase);
string output = regx.ToString();    
string count = 0.ToString();
output = readFile;
string username = Server.UrlEncode(this.txtUsername.Text);
//here i want to read the value of m_id that i have updated as shown above and setting it to 'i' and passing this i  to the query string//
sql.Open();
string re = "select m_id from counter where id=1";
SqlCommand ccmd = new SqlCommand(re, sql);
com.ExecuteNonQuery();
SqlDataReader read = ccmd.ExecuteReader();
{
while (read.Read())
i =int.Parse(read["m_id"].ToString());
}
sql.Close();
output = regx.Replace(output, new MatchEvaluator((match) =>
{   
var url = Uri.EscapeDataString(match.Value.ToString());
return $"http://localhost:61187/two?sender={username}&link={url}&count={count}&mailer_id={i}";
}));

现在，问题不是从 5 更新到 6，而是m_id更新到 7

Answer 1

您可以将命令批处理在一起并只执行一个命令，而不是对数据库调用两次

protected void btnSubmit_Click(object sender, EventArgs e)
{
sql.Open();
string r = @"update counter set m_id=m_id+1 where id=1;
select m_id from counter where id=1;";
SqlCommand com = new SqlCommand(r, sql);
int result = (int)com.ExecuteScalar();
sql.Close();
{
SendHTMLMail(result);
}
}

此时，SendHTMLMail 方法(嵌套或非嵌套(可以接收结果变量并避免对数据库引擎的任何调用。请注意，在本例中，我将 ExecuteNonQuery 更改为 ExecuteScalar 以返回选择返回的唯一值。

但是，如果想要仍然保持东西分开，那么您需要将SendHTMLMail中的代码更改为如下所示的内容

// just change the CommandText to avoid reexecuting the INSERT
com.CommandText = "select m_id from counter where id=1";
// No more needed
// SqlCommand ccmd = new SqlCommand(re, sql);
// Get the reader from the SqlCommand
SqlDataReader read = com.ExecuteReader();
{
.... go on with reading