通过oledb从datatable向foxpro写入X条记录后失败

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

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

在库存表中，如果失败，记录为578。在Customer表中，它在记录'Q617P78'处失败

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

我试过把数据表记录写出来到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();
    }
}

}

根据您提供的信息，它可能由于垃圾收集问题而崩溃。您正在重复创建命令和参数，这可以通过一次预创建命令和参数来简化，然后，对于每个记录，只需通过…我已经重组和通用，但在类似的方法下，你有什么。按照我的方式，我构建命令和参数一次，准备参数一次，然后循环遍历记录。非常少的垃圾收集问题/内存泄漏问题，你可能会遇到…

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);

这样，除了where列总是LAST之外，列在顺序上是相同的。现在，在查询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"];
}

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

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

似乎不太可能基于您正在谈论的记录数量，但是您是否有机会在VFP中达到每个文件2GB的限制?

相关内容

最新更新

热门标签：