C#执行带参数的mysql语句

本教程用C# Connector/NET中的MySql.Data.dll连接mysql数据库，请做好相关配置后代码内引入：

using MySql.Data.MySqlClient;

最近在项目用C#调用mysql，执行的sql语句需要传输参数，搞了好久终于搞定了，记录下中间踩的坑，变量如下：

public static long fileid;
public static string type;
public static string police_name;
public static string linux_path;
public static int year;
public static int month;
public static int day;
public static int uploadtime;
///这些变量需要自己初始化

一.开启sql语句参数支持

 public static string mysql_url = "server=192.168.83.134;
 userid=root;database=hehe;
port=3306;password=test;Charset=utf8;
Allow User Variables=True";//连接mysql的字符串

注意这里的“Allow User Variables=True”，只有加上这句，后面才能执行带参数的sql语句。

二.用问号标识变量

MySqlConnection myConnnect = new MySqlConnection(mysql_url);  
myConnnect.Open();
string insert_str = "insert into        police_file(file_id,police_name,file_type,year,month,day,file_path,upload_time) values(?fileid,?police_name,?type,?year,?month,?day,?linux_path,?uploadtime) ";

MySqlCommand myCmd = new MySqlCommand(insert_str, myConnnect);

三.AddWithValue参数赋值：

myCmd.Parameters.AddWithValue("@fileid", fileid);
myCmd.Parameters.AddWithValue("@police_name", police_name);
myCmd.Parameters.AddWithValue("@type", type);
myCmd.Parameters.AddWithValue("@year", year);
myCmd.Parameters.AddWithValue("@month", month);
myCmd.Parameters.AddWithValue("@day", day);
myCmd.Parameters.AddWithValue("@linux_path", linux_path);
myCmd.Parameters.AddWithValue("@uploadtime", uploadtime);

四.执行sql语句

try
    {
        myCmd.ExecuteNonQuery();
    }
catch (System.Exception e)
    {
        MessageBox.Show(e.Message);
    }