writing from datatable to foxpro via oledb fails a

问题:

Unhandled Exception: System.Data.OleDb.OleDbException: SQL: Column 'Q578P5' is not found...

I've written an app in VS2010 C#.net that reads data from an SQL table into datatable and then writes that into foxpro tables.

On the Inventory table if fails as above at record 578. On the Customer table it fails at record 'Q617P78'

I have tested for data issues by removing some records from the SQL table but the error still occurs at the same record number in spite of that record number not being the same record.

I've tried writing the datatable records out to CSV and that works fine. It seems to be an issue with the FoxPro tables.

The inventory records are shorter than the customer records. I therefore suspect a memory issue. It all works entirely as expected up to record number X.

Any suggestions appreciated

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

}

回答1:

From what you provided, it might be crashing from garbage collection issues. You are creating the command and parameters repeatedly which might be simplified by pre-creating the command and parameters ONCE, then, for each record, just reset the parameter VALUE for each time through... I have restructured and made generic but under a SIMILAR approach to what you had. By doing it in the fashion I have, I am building the commands and parameters ONCE, preparing the PARAMETERS once, then cycle through the records. Very little garbage collection issues / memory leak issues you might be encountering...

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

This way the columns are IDENTICAL in sequential order with exception of the update where the WHERE column is always LAST. Now, after you query the SQL Database, get a single row as a sample... THEN, call function with both the command AND row to represent the parameters origin source such as

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"];
}

Finally, I had created a function to prepare either of the insert or update commands in a similar fashion by passing the command and row so I don't oops the order, miss a column, etc

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"];

}

And my final loop to cycle through and process would be something like...

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

回答2:

Seems unlikely based on the number of records you're talking about, but any chance you're hitting the 2GB per file limit in VFP?