在X记录之后，通过oledb从datatable写入foxpro失败 [英] writing from datatable to foxpro via oledb fails after X records
问题描述
未处理的异常：System.Data.OleDb.OleDbException：SQL：列'Q578P5'未找到...
Unhandled Exception: System.Data.OleDb.OleDbException: SQL: Column 'Q578P5' is not found...
我在VS2010中编写了一个应用程序C＃ .net将数据从SQL表读取为datatable，然后将其写入foxpro表。
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.
如果在记录578上如果失败，则在Inventory表上。在客户表它记录在Q617P78上失败
On the Inventory table if fails as above at record 578. On the Customer table it fails at record 'Q617P78'
我已经通过从SQL表中删除一些记录来测试数据问题，但是错误仍然发生在相同的记录号，尽管如此记录号不是相同的记录。
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.
我已经尝试将数据记录写入CSV，并且工作正常。这似乎是FoxPro表的一个问题。
I've tried writing the datatable records out to CSV and that works fine. It seems to be an issue with the FoxPro tables.
库存记录比客户记录短。因此，我怀疑是一个记忆问题。这一切都完全按预期达到记录数字X。
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.
任何建议赞赏
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();
}
}
}
推荐答案
从您提供的内容可能会从垃圾收集问题中崩溃。您正在重复创建命令和参数，可以通过预先创建命令和参数ONCE来简化命令和参数，然后，对于每个记录，只需通过...重新设置参数VALUE ...我已经重组并进行了通用，但是在一个SIMILAR接近你所拥有的通过这样做，我正在建立命令和参数ONCE，准备参数一次，然后循环记录。很少有垃圾收集问题/您可能遇到的内存泄漏问题...
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);
以这种方式，列按顺序排列为IDENTICAL，除了更新之外，其中WHERE列始终为LAST 。现在，在查询SQL数据库之后，获取一行作为示例... THEN，使用命令AND行调用函数来表示参数原始来源，例如
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
这篇关于在X记录之后，通过oledb从datatable写入foxpro失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
