c# - 无法使用 C# 将数据从 Visual Studio 导入 MySQL

Question

我正在尝试将一个充满数据的 csv 文件导入到 MySQL 中的表中。用户连接的权限可以访问insert等所有命令。该代码工作正常，没有错误，并且每次插入后我都会打印确认信息。但仍然没有数据插入到表中。

StreamReader theFile = new StreamReader("C:\\Users\\Bob\\Documents\\NewInventory.csv");
{
    string aLine = null;

    while ((aLine = theFile.ReadLine()) != null)
    {
        try
        {
            string[] fields = aLine.Split(',');
            Item_ID = int.Parse(fields[0]);
            Invent_id = int.Parse(fields[1]);
            Itemsize = fields[2];
            Color = fields[3];
            Curr_price = decimal.Parse(fields[4]);
            Qoh = int.Parse(fields[5]);
        }
        catch (Exception ex)
        {

        }

        String sql = "SELECT * FROM item WHERE item_ID=?";
        OdbcCommand Command = new OdbcCommand(sql, connection);

        Command.Parameters.Add("@ID", OdbcType.Int).Value = Item_ID;

        //If query returns a row - there is already an item in the table.
        if (Command.ExecuteReader().HasRows)
        {
            Console.WriteLine("Item ID " + Item_ID + " is already in the database");
        }
        else
        {
            String sql3 = "INSERT INTO item (item_id, invent_id, itemsize, color, curr_price, qoh) VALUES( ?, ?, ?, ?, ?, ?) ";
            OdbcCommand Command3 = new OdbcCommand(sql3, connection);
            Command3.Parameters.Add("@ID", OdbcType.Int).Value = Item_ID;
            Command3.Parameters.Add("@INVID", OdbcType.Int).Value = Invent_id;
            Command3.Parameters.Add("@SZ", OdbcType.VarChar).Value = Itemsize;
            Command3.Parameters.Add("@COL", OdbcType.VarChar).Value = Color;
            Command3.Parameters.Add("@PR", OdbcType.Decimal).Value = (decimal)Curr_price;
            Command3.Parameters.Add("@QOH", OdbcType.Int).Value = Qoh;
            Console.WriteLine("Item ID " + Item_ID + " was added");
        }
    }
    Console.WriteLine("\n\n\nPress ENTER to continue");
    Console.ReadLine();
    connection.Close();
    theFile.Close();
}

更新(异常错误)

System.Data.Odbc.OdbcException occurred
  HResult=0x80131937
  Message=
  Source=
  StackTrace:
   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcParameter.Bind(OdbcStatementHandle hstmt, OdbcCommand command, Int16 ordinal, CNativeBuffer parameterBuffer, Boolean allowReentrance)
   at System.Data.Odbc.OdbcParameterCollection.Bind(OdbcCommand command, CMDWrapper cmdWrapper, CNativeBuffer parameterBuffer)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at ProjectLab1.Program.Main(String[] args) in C:\Users\Bob\source\repos\ProjectLab1\ProjectLab1\Program.cs:line 84

Answer 1

您没有执行 SQL 代码。您必须添加这一行:

Command3.ExecuteNonQuery();

无论如何，尝试始终使用 using 创建连接，因为这将确保所有资源都已被释放。这里有一个关于如何编码的很好的例子:

https://www.dotnetperls.com/odbcconnection