X条记录后，通过oledb从数据表写入foxpro失败

Question

未处理的异常:找不到System.Data.OleDb.OleDbException: SQL:列'Q578P5‘...

我用VS2010 C#.net编写了一个应用程序，可以将SQL表中的数据读取到datatable中，然后将其写入foxpro表中。

在库存表if上，如上所述在记录578处失败。在Customer表上，在记录'Q617P78‘处失败

我已经通过从SQL表中删除一些记录来测试数据问题，但是错误仍然发生在相同的记录号上，尽管该记录号不是相同的记录。

我已经尝试将datatable记录写出到CSV，这很好用。这似乎是FoxPro表的一个问题。

库存记录比客户记录短。因此，我怀疑是内存问题。它完全像预期的那样工作，直到记录数字X。

非常感谢您的任何建议

namespace PLADO
{
class Program
{
    static void Main(string[] args)
    // CUSTOMERS
    {   // Create 2 tables - one for SQL and one for Vision
        DataTable VisionCustomerResultSet = new DataTable();
        DataTable SQLCustomerResultSet = new DataTable();

        // read data from INI
        string INIFilePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + "\\DBCS\\PLExe.ini";
        var ThisAppINIFile = new IniFile(INIFilePath.Trim());

        var SQLServer = ThisAppINIFile.Read("Glo:SQLServerInstance", "Preserved");
        var SQLDatabase = ThisAppINIFile.Read("Glo:SQLDatabase", "Preserved");
        var SQLTrustedConnection = ThisAppINIFile.Read("Glo:TrustedConnection", "Preserved");
        var SQLUsername = ThisAppINIFile.Read("Glo:SQLUsername", "Preserved");
        var SQLUserPassword = ThisAppINIFile.Read("Glo:SQLUserPassword", "Preserved");
        var SQLConnectionString = "Server=" + SQLServer + ";Database=" + SQLDatabase + ";User ID=" + SQLUsername + ";Password=" + SQLUserPassword + ";";
        var ADOConnectionString = ThisAppINIFile.Read("Glo:ADOConnectionString", "Preserved");

        // Open the SQL database
        SqlConnection sqlCon = new SqlConnection(SQLConnectionString);
        sqlCon.Open();

        // Open the Foxpro database
        OleDbConnection oleDbConnection1 = new OleDbConnection(ADOConnectionString);
        oleDbConnection1.Open();

        // read the SQL values into DataTAble
        string commandString = "SELECT [uniqueid],[ledgerno],[accountno],[sortcode],(clipped for readability)...[zgrouping],[zclegacy],[zmarket] FROM [PrimeLaundry].[dbo].[Vision_Customer]";
        SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
        SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
        sda.Fill(SQLCustomerResultSet);                     // read the select statement results into the dataTable


        // cycle through DataTable
        foreach (DataRow row in SQLCustomerResultSet.Rows)
        {   // read a matching record from Foxpro
            Console.WriteLine(row["AccountNo"]);
            string selectStatement = "select accountno from Customer where accountno = '" + row["AccountNo"] + "'";  
            string insertStatement = "INSERT INTO CUSTOMER ([uniqueid],[ledgerno],[accountno],[sortcode],[title], (clipped for readability)...,[zclegacy],[zmarket])"
            + " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            string updateStatement = "UPDATE CUSTOMER SET sortcode = ?,title = ?,periods = ?,groupno = ? (clipped for readability)... ?,ordnoreq = ?,zrunno = ?,zgrouping = ?,zclegacy = ?,zmarket = ? where Accountno = '" + row["AccountNo"] + "'";
            OleDbCommand selectCommand = new OleDbCommand(selectStatement, oleDbConnection1);
            OleDbCommand insertCommand = new OleDbCommand(insertStatement, oleDbConnection1);
            OleDbCommand updateCommand = new OleDbCommand(updateStatement, oleDbConnection1);
            String selectQueryResult = (String)selectCommand.ExecuteScalar();
            if (string.IsNullOrEmpty(selectQueryResult))
            {
                insertCommand.Parameters.Add("uniqueid", OleDbType.VarChar).Value = row["uniqueid"];
                insertCommand.Parameters.Add("ledgerno", OleDbType.Numeric).Value = row["ledgerno"];
                insertCommand.Parameters.Add("accountno", OleDbType.VarChar).Value = row["accountno"];
                insertCommand.Parameters.Add("sortcode", OleDbType.VarChar).Value = row["sortcode"];
                (Clipped for readability)
row["zgrouping"];
                insertCommand.Parameters.Add("zclegacy", OleDbType.VarChar).Value = row["zclegacy"];
                insertCommand.Parameters.Add("zmarket", OleDbType.VarChar).Value = row["zmarket"];

                int count = insertCommand.ExecuteNonQuery();
            }
            else
            {
                updateCommand.Parameters.Add("Sortcode", OleDbType.VarChar, 2).Value = row["sortcode"];
                updateCommand.Parameters.Add("title", OleDbType.VarChar).Value = row["title"];
                updateCommand.Parameters.Add("periods", OleDbType.Numeric).Value = row["periods"];
                updateCommand.Parameters.Add("groupno", OleDbType.Numeric).Value = row["groupno"];    (Clipped for readability)
     updateCommand.Parameters.Add("zclegacy", OleDbType.VarChar).Value = row["zclegacy"];
                updateCommand.Parameters.Add("zmarket", OleDbType.VarChar).Value = row["zmarket"];

                int count = updateCommand.ExecuteNonQuery();
            }       // end of if (string.IsNullOrEmpty...
        }       // end of foreach look


        // INVENTORY
        // Create 2 tables - one for SQL and one for Vision
        DataTable VisionInventoryResultSet = new DataTable();
        DataTable SQLInventoryResultSet = new DataTable();

        // read the SQL values into DataTAble
        commandString = "SELECT [uniqueid],[ledgerno],[accountno],[sortcode],[title],[periods],[groupno],[taxcode],[taxcode2],[leadtime],[reorder],[binno],[alternate],[remarks],[salesunit],[purchunit],[weight],[ctryorigin],[commodity],[spratio],[price1],(Clipped for readability)...[kitcomp],[useredit],[lastdeldat],[maxreorder],[zprodgroup] FROM [PrimeLaundry].[dbo].[Vision_Inventory]";
        sqlCmd = new SqlCommand(commandString, sqlCon);
        sda = new SqlDataAdapter(sqlCmd);
        sda.Fill(SQLInventoryResultSet);                     // read the select statement results into the dataTable


        // cycle through DataTable
        foreach (DataRow row in SQLInventoryResultSet.Rows)
        {   // read a matching record from Foxpro
            string selectStatement = "select accountno from Inventry where accountno = '" + row["AccountNo"] + "'"; 
            string insertStatement = "INSERT INTO INVENTRY ([uniqueid],[ledgerno],[accountno],[sortcode],[title],[periods],[groupno],[taxcode],[taxcode2],[leadtime],[reorder],[binno],[alternate],(Clipped for readability)...,[zprodgroup],[zilegacy])"
                                        + " Values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,' ')";
            string updateStatement = "UPDATE INVENTRY SET sortcode = ?,title = ?,periods = ?,groupno = ?,taxcode = ?,taxcode2 = ?,leadtime = ?,reorder = ?,binno = ?,alternate = ?,remarks = ?,salesunit = ?,purchunit = ?(Clipped for readability)...maxreorder = ?,zprodgroup = ? where Accountno = '" + row["AccountNo"] + "'";
            OleDbCommand selectCommand = new OleDbCommand(selectStatement, oleDbConnection1);
            OleDbCommand insertCommand = new OleDbCommand(insertStatement, oleDbConnection1);
            OleDbCommand updateCommand = new OleDbCommand(updateStatement, oleDbConnection1);

            string selectQueryResult = (String)selectCommand.ExecuteScalar();
            if (string.IsNullOrEmpty(selectQueryResult))
            {
                insertCommand.Parameters.Add("uniqueid", OleDbType.VarChar).Value = row["uniqueid"];
                insertCommand.Parameters.Add("ledgerno", OleDbType.Numeric).Value = row["ledgerno"];
                insertCommand.Parameters.Add("accountno", OleDbType.VarChar).Value = row["accountno"];
                insertCommand.Parameters.Add("sortcode", OleDbType.VarChar).Value = row["sortcode"]; (Clipped for readability)...
                insertCommand.Parameters.Add("maxreorder", OleDbType.Numeric).Value =     row["maxreorder"];
                insertCommand.Parameters.Add("zprodgroup", OleDbType.VarChar).Value = row["zprodgroup"];

                int count = insertCommand.ExecuteNonQuery();
            }
            else
            {
                updateCommand.Parameters.Add("Sortcode", OleDbType.VarChar, 2).Value = row["sortcode"];
                updateCommand.Parameters.Add("title", OleDbType.VarChar).Value = row["title"];
                updateCommand.Parameters.Add("periods", OleDbType.Numeric).Value = row["periods"]; (Clipped for readability)...
                updateCommand.Parameters.Add("zprodgroup", OleDbType.VarChar).Value = row["zprodgroup"];

                int count = updateCommand.ExecuteNonQuery();
            }


        }
        oleDbConnection1.Close();
        sqlCon.Close();
    }
}

}

Answer 1

从您提供的内容来看，它可能是由于垃圾收集问题而崩溃。您正在重复创建命令和参数，可以通过预先创建一次命令和参数来简化这些操作，然后，对于每条记录，只需通过...我已经重组并使其通用，但采用了类似于您所拥有的方法。通过使用我拥有的方式，我构建命令和参数一次，准备参数一次，然后循环记录一次。您可能会遇到很少的垃圾收集问题/内存泄漏问题...

string ins = "insert into MyTable ( ColA, ColB, ColC, ..., ColZ ) values ( ?, ?, ?, ..., ? )"
string upd = "update MyTable set ColA = ?, ColB = ?, ColC = ?, ..., ColZ = ?  where pkColumn = ?"

OleDbCommand insCmd = new OleDbCommand(ins, oleDbConnection1);
OleDbCommand updCmd = new OleDbCommand(upd, oleDbConnection1);

这样，除了update中where列始终是最后一列之外，其他列的顺序都是相同的。现在，在查询SQL数据库之后，获取一行作为示例...然后，使用命令和行调用函数来表示参数来源，例如

DataRow tmpRow = SQLCustomerResultSet.Rows[0];
prepParameters( insCmd, tmpRow, false );
prepParameters( updCmd, tmpRow, true );

private void prepParameters( OleDbCommand oCmd, DataRow oSampleRow, bool IsUpdate )
{
   oCmd.Parameters.Add("ColA", OleDbType.VarChar).Value = oSampleRow["ColA"];
   oCmd.Parameters.Add("ColB", OleDbType.Numeric).Value = oSampleRow["ColB"];
   oCmd.Parameters.Add("ColC", OleDbType.VarChar).Value = oSampleRow["ColC"];
   ...
   oCmd.Parameters.Add("ColZ", OleDbType.VarChar).Value = oSampleRow["ColZ"];

   if( IsUpdate )
      oCmd.Parameters.Add("PKCol", OleDbType.VarChar).Value = oSampleRow["PKCol"];
}

最后，我创建了一个函数，通过传递命令和行，以类似的方式准备insert或update命令，这样我就不会错过顺序、遗漏一列等等

private void AssignParameters( OleDbCommand oCmd, DataRow oSampleRow, bool IsUpdate )
{
   oCmd.Parameters[0].Value = oSampleRow["ColA"];
   oCmd.Parameters[1].Value = oSampleRow["ColB"];
   oCmd.Parameters[2].Value = oSampleRow["ColC"];
   ...
   oCmd.Parameters[n].Value = oSampleRow["ColZ"];

   if( IsUpdate )
      oCmd.Parameters[extra].Value = oSampleRow["PKColumn"];

}

我要循环处理的最后一个循环是这样的：

foreach (DataRow row in SQLCustomerResultSet.Rows)
{  // read a matching record from Foxpro
   Console.WriteLine(row["AccountNo"]);

   // Just update the respective command parameter for the select...
   selectCommand.Parameters[0].Value = row["AccountNo"];

   // NOW, execute since we changed the parameter above before executing it.      
   String selectQueryResult = (String)selectCommand.ExecuteScalar();
   if (string.IsNullOrEmpty(selectQueryResult))
   {
      // with my simplified approach...
      AssignParameters( insCmd, row, false );
      // and now execute it...
      int count = insCmd.ExecuteNonQuery();
   }
   else
   {
      // with my simplified approach...
      AssignParameters( updCmd, row, true );
      // and now execute it...
      int count = updCmd.ExecuteNonQuery();
   }  // end of if (string.IsNullOrEmpty...
}  // end of foreach look

Answer 2

根据您正在讨论的记录数量，似乎不太可能，但是在VFP中有可能达到每个文件2 2GB的限制吗？