c# - 如何在 C# 中使用 ODBC 在 SQLite 中执行 INSERT INTO

Question

应用程序的功能是从一个数据库(PSQL)中选择一些值并将其插入到另一个数据库(SQLite)中。但下面的代码不起作用，它在执行行停止并且不显示错误，但永远持续(如果我使用 SELECT TOP 1 ...)。

//... odbc conection to DSN, this works fine
odbc.dbsqlite.Open();
odbc.dbpsql.Open();

//sql command
OdbcCommand comsqlite = odbc.dbsqlite.CreateCommand();
OdbcCommand compsql = odbc.dbpsql.CreateCommand();

//SQL for select ... this works
compsql.CommandText = "SELECT DISTINCT ..." 

compsql.Parameters.AddWithValue("@sifra", "VP");
...

// from here is problem
try
{
    OdbcDataReader dbReader = compsql.ExecuteReader();
    OdbcTransaction transaction = odbc.dbsqlite.BeginTransaction();

    var ordinal = new
    {
       cenik = dbReader.GetOrdinal("sifra"),
       ident = dbReader.GetOrdinal("ident"),
       klasi = dbReader.GetOrdinal("klasi"),
       cena  = dbReader.GetOrdinal("cena"),
       eankoda = dbReader.GetOrdinal("eankoda"),
    };

    int count = 0;

    while (dbReader.Read())
    {
       //here single variable gets results 
       var cena = Convert.ToDouble(dbReader.GetDouble(ordinal.cena));
       var ident = Convert.ToString(dbReader.GetString(ordinal.ident));
       var cenik = Convert.ToString(dbReader.GetString(ordinal.cenik));
       var klasi = Convert.ToString(dbReader.GetString(ordinal.klasi));
       var eanko = Convert.ToString(dbReader.GetString(ordinal.eankoda));

       comsqlite.CommandText = "INSERT INTO ARTIKLI (KLASI, CENA, BARKODA, CENIK, IDENT) VALUES (?,?,?,?,?);";

       comsqlite.Parameters.AddWithValue("@KLASI", klasi);
       comsqlite.Parameters.AddWithValue("@CENA", cena);
       comsqlite.Parameters.AddWithValue("@BARKODA", eanko);
       comsqlite.Parameters.AddWithValue("@CENIK", cenik);
       comsqlite.Parameters.AddWithValue("@IDENT", ident);

        if (count % 1000 == 0)
        {
           transaction.Commit();
           transaction.Dispose();
           **comsqlite.ExecuteNonQuery(); //here it stops and give no results**
           transaction = odbc.dbsqlite.BeginTransaction();

         }
         count++;

         }

         comsqlite.Dispose();
         odbc.dbsqlite.Close();

         transaction.Commit();
         transaction.Dispose();

         dbReader.Close();
         compsql.Dispose();
         odbc.dbpsql.Close();

        }
        catch (Exception e)
        {
            Console.WriteLine("Error: "+ e);
            throw;
        }

Answer 1

我现在不确定您的 CommandText 是什么样子，但您应该尝试在数据库中的字符串/字符值周围设置一些单引号。

comsqlite.CommandText = "INSERT INTO ARTIKLI (KLASI, CENA, BARKODA, CENIK, IDENT) VALUES ('?','?','?','?','?');";