如何创建参数化查询 [英] How do I create parameterized queries

问题描述

我有一个接受数据的方法，大约每秒被调用一次.我尝试创建一个参数化查询，该查询将结果插入到MySQL数据库表中，但是我不断收到错误消息，告诉我该参数已经定义.有人可以解释一下它是如何工作的以及我在做什么错吗?我已经阅读了有关使用预准备语句的mysql文档，但对使用预准备语句的了解还不够深入.

I have a method that accepts data and and is being called approx every second. I tried creating a parameterized query which inserts the results into a MySQL database table but I keep getting error messages telling me that the parameter has already been defined. Can someone please explain how this works and what I''m doing wrong? I''ve read the mysql doc on using prepared statements and it doesn''t go in depth enough on using prepared statements.

private void (string _fname, string _lname, string _age) 
{ 
string sql = "Insert Into myTbl (firstname,lastname,age) Values(@fname,@lname,@age)"; 
cmd.CommandText = sql; 
cmd.PrePare(); 
cmd.Parameters.AddWithValue("@fname", _fname); 
cmd.Parameters.AddWithValue("@lname", _lname); 
cmd.Parameters.AddWithValue("@age" , _age ); 

cmd.ExecuteNonQuery(); 
}

推荐答案

在重新添加参数之前，请调用cmd.Parameters.Clear().或添加一个标志，以便只添加一次(无需每次都重新添加它们).

Call cmd.Parameters.Clear() before you re-add the parameters. Or add a flag so that you only add them once (there is no need to re-add them each time).

if(!parametersCreated)
{
  // add parameters here

  parametersCreated = true;
}

[基于我们通过注释进行的讨论而更新的代码]

[Updated code based on our discussion via comments]

private bool parametersCreated;

private void Foo(string _fname, string _lname, string _age)
{
  string sql = ". . .";
  cmd.CommandText = sql;

  cmd.Prepare();

  if(!parametersCreated)
  {
    // cmd.Parameters.Clear(); <-- No need for this now
    cmd.Parameters.AddWithValue("@fname", _fname);
    . . .

    parametersCreated = true;
  }
  else
  {
    cmd.Parameters["@fname"].Value = _fname;
    . . .
  }

  cmd.ExecuteNonQuery();
}

这篇关于如何创建参数化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！