不好意思...将.CSV文件导入C#/ASP.NET中的Access 2007DB [英] No Fun... Import a .CSV file into Access 2007DB in C#/ASP.NET

问题描述

我有一些顽固的代码，无法正常工作.
我有一个数据的CSV(逗号分隔)文本文件，我需要将其追加到现有的Access DB(当前为2007，但可能为2010)表中.

我无法每次都插入新行.

我想念什么???

我创建了一些代码...

I have a stubborn bit of code I am unable to get to work properly.
I have a CSV (comma delimited) text file of data I need to Append into an existing Access DB (Currently 2007 but could be 2010) table.

I am unable to get this to insert the new rows every time.

What am I missing???

I created a couple of bits of code...

// connections string to DB
string query = "INSERT INTO ";
if (DBTableName == "RAW_Transaction_Data")
{
    query = query + DBTableName + " ( post_tran_cust_id, message_type, tran_type, extended_tran_type, datetime_tran_gmt, recon_business_date, source_node_name, settle_amount_rsp, to_account_id, pan, agent_id, sub_agent_id, terminal_id, card_program, merchant_type ) SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10, F11, F12, F13, F14, F15 FROM [Text;HDR=NO;DATABASE=" + HttpContext.Current.Request.MapPath("~") + "FTP_DL\\" + ";].[" + FileName + "]";
}
int rowsAffected = myDC.NonQueryDataIns(query, 2);
return rowsAffected;

这是执行代码段...

Here is the Execute code segment...

public int NonQueryDataIns(string pSQLString, int pcmdType)
{
    try
    {
        string insSQL = pSQLString;
        CreateConnection();
        OleDbCommand cmd = new OleDbCommand();
        OleDbTransaction trans;

        // set the properties of the cmd Object
        if (pcmdType == 1)
            cmd.CommandType = CommandType.StoredProcedure;
        else if (pcmdType == 2)
            cmd.CommandType = CommandType.Text;
        else if (pcmdType == 3)
            cmd.CommandType = CommandType.TableDirect;

        cmd.CommandText = insSQL;
        cmd.Connection = dbConn;
        trans = dbConn.BeginTransaction();
        cmd.Transaction = trans;

        // add new parameter to command object & Execute it
        int rowsAffected = cmd.ExecuteNonQuery();
        cmd.Transaction.Commit();
        dbConn.Close();
        return rowsAffected;

    }
    catch (OleDbException oleEx)
    {

谢谢
Phill

Thanks
Phill

推荐答案

按顺序回答您的问题...

1.大约30％的时间将插入数据，这是irratick.
2.我有并且确实从文本文件中获取了行.
3.它将始终在"ExecuteNonQuery"行中保释.
4.您正确的代码如下.
5.我每次都检查过，并且每次都传递表名.

Hi,

to answer your questions in order...

1. It will insert the data about 30% of the time, it is irratick.
2. I have and I do get the rows back from the text file.
3. It would always bail at the "ExecuteNonQuery" line.
4. you are correct the code is below.
5. I have checked each time and the table name is being passed each time.

// Make the connection to the DB<br />
string a = varAppTitle + ".Properties.Settings." + varAppTitle + "ConnectionString";<br />
dbConn = new OleDbConnection(Settings.GetConnectionStringByName(a));<br />
dbConn.Open();<br />
<br />
// write to the Log File<br />
ExceptionLog("CreateConnection Success - " + a);<br />
return dbConn;<br />

我发现并且很奇怪的是，关闭DBConn的原因是BeginTransaction()或下一行trans；我已对所有交易"行进行了存根处理，但似乎工作正常.从我已阅读的内容中，需要Transaction行，但我不知道为什么它们会导致错误.

有任何建议吗?

谢谢

What I have found and it is strange is that the cause of the DBConn being closed is either the BeginTransaction() or the next line trans; I have stubbed all the Transaction lines and it seems to work fine. From what I have read the Transaction lines are needed but I do not know why they are causing the errors.

Got any suggestions?

Thanks

尝试将.Commit()函数与BeginTransaction()结合使用.这是 MSDN [

Try using the .Commit() function in conjunction with the BeginTransaction(). Here is an example from MSDN[^]

Modify the execution area of your code to mimic this example. The biggest differences I see are the explicit .Open() of the connection, the use of the .Commit(), and the specification of the isolation level. Let me know if this makes a difference.

这篇关于不好意思...将.CSV文件导入C#/ASP.NET中的Access 2007DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！