如何使用c#将csv数据导入Oracle [英] how to import csv data into Oracle using c #
本文介绍了如何使用c#将csv数据导入Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
你好，
如何使用c#将csv数据导入Oracle.其中要导入的数据大小为3GB，行数为7512263.我已经设法将csv数据导入到Oracle中，但是时间大约需要1个小时.如何加快将csv数据导入到oracle中所需的时间.谢谢.
这是我的代码:
Hello,
How to import csv data into Oracle using c #. Where data to be imported 3GB in size and number of rows 7512263. I''ve managed to import csv data into Oracle, but the time it takes about 1 hour. How to speed up the time it takes to import csv data into oracle. Thank you.
This is my code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Threading;
using System.Text.RegularExpressions;
using System.IO;
using FileHelpers;
using System.Data.OracleClient;
namespace sqlloader
{
class Program
{
static void Main(string[] args)
{
int jum;
int i;
bool isFirstLine = false;
FileHelperEngine engine = new FileHelperEngine(typeof(XL_XDR));
//Connect To Database
string constr = "Data Source=(DESCRIPTION=(ADDRESS_LIST="
+ "(ADDRESS=(PROTOCOL=TCP)(HOST= pt-9a84825594af )(PORT=1521 )))"
+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=o11g)));"
+ "User Id=xl;Password=rahasia;";
OracleConnection con = new OracleConnection(constr);
con.Open();
// To Read Use:
XL_XDR[] res = engine.ReadFile("DataOut.csv") as XL_XDR[];
jum = CountLinesInFile("DataOut.csv");
FileInfo f2 = new FileInfo("DataOut.csv");
long s2 = f2.Length;
int jmlRecord = jum - 1;
for (i = 0; i < jum; i++)
{
ShowPercentProgress("Processing...", i, jum);
Thread.Sleep(100);
if (isFirstLine == false)
{
isFirstLine = true;
}
else
{
string sql = "INSERT INTO XL_XDR (XDR_ID, XDR_TYPE, SESSION_START_TIME, SESSION_END_TIME, SESSION_LAST_UPDATE_TIME, " +
"SESSION_FLAG, VERSION, CONNECTION_ROW_COUNT, ERROR_CODE, METHOD, HOST_LEN, HOST, URL_LEN, URL, CONNECTION_START_TIME, " +
"CONNECTION_LAST_UPDATE_TIME, CONNECTION_FLAG, CONNECTION_ID, TOTAL_EVENT_COUNT, TUNNEL_PAIR_ID, RESPONSIVENESS_TYPE, " +
"CLIENT_PORT, PAYLOAD_TYPE, VIRTUAL_TYPE, VID_CLIENT, VID_SERVER, CLIENT_ADDR, SERVER_ADDR, CLIENT_TUNNEL_ADDR, " +
"SERVER_TUNNEL_ADDR, ERROR_CODE_2, IPID, C2S_PKTS, C2S_OCTETS, S2C_PKTS, S2C_OCTETS, NUM_SUCC_TRANS, CONNECT_TIME, " +
"TOTAL_RESP, TIMEOUTS, RETRIES, RAI, TCP_SYNS, TCP_SYN_ACKS, TCP_SYN_RESETS, TCP_SYN_FINS, EVENT_TYPE, FLAGS, TIME_STAMP, " +
"EVENT_ID, EVENT_CODE) VALUES (" +
"''" + res[i].XDR_ID + "'', ''" + res[i].XDR_TYPE + "'', ''" + res[i].SESSION_START_TIME + "'', ''" + res[i].SESSION_END_TIME + "'', " +
"''" + res[i].SESSION_LAST_UPDATE_TIME + "'', ''" + res[i].SESSION_FLAG + "'', ''" + res[i].VERSION + "'', ''" + res[i].CONNECTION_ROW_COUNT + "'', " +
"''" + res[i].ERROR_CODE + "'', ''" + res[i].METHOD + "'', ''" + res[i].HOST_LEN + "'', ''" + res[i].HOST + "'', " +
"''" + res[i].URL_LEN + "'', ''" + res[i].URL + "'', ''" + res[i].CONNECTION_START_TIME + "'', ''" + res[i].CONNECTION_LAST_UPDATE_TIME + "'', " +
"''" + res[i].CONNECTION_FLAG + "'', ''" + res[i].CONNECTION_ID + "'', ''" + res[i].TOTAL_EVENT_COUNT + "'', ''" + res[i].TUNNEL_PAIR_ID + "'', " +
"''" + res[i].RESPONSIVENESS_TYPE + "'', ''" + res[i].CLIENT_PORT + "'', ''" + res[i].PAYLOAD_TYPE + "'', ''" + res[i].VIRTUAL_TYPE + "'', " +
"''" + res[i].VID_CLIENT + "'', ''" + res[i].VID_SERVER + "'', ''" + res[i].CLIENT_ADDR + "'', ''" + res[i].SERVER_ADDR + "'', " +
"''" + res[i].CLIENT_TUNNEL_ADDR + "'', ''" + res[i].SERVER_TUNNEL_ADDR + "'', ''" + res[i].ERROR_CODE_2 + "'', ''" + res[i].IPID + "'', " +
"''" + res[i].C2S_PKTS + "'', ''" + res[i].C2S_OCTETS + "'', ''" + res[i].S2C_PKTS + "'', ''" + res[i].S2C_OCTETS + "'', " +
"''" + res[i].NUM_SUCC_TRANS + "'', ''" + res[i].CONNECT_TIME + "'', ''" + res[i].TOTAL_RESP + "'', ''" + res[i].TIMEOUTS + "'', " +
"''" + res[i].RETRIES + "'', ''" + res[i].RAI + "'', ''" + res[i].TCP_SYNS + "'', ''" + res[i].TCP_SYN_ACKS + "'', " +
"''" + res[i].TCP_SYN_RESETS + "'', ''" + res[i].TCP_SYN_FINS + "'', ''" + res[i].EVENT_TYPE + "'', ''" + res[i].FLAGS + "'', " +
"''" + res[i].TIME_STAMP + "'', ''" + res[i].EVENT_ID + "'', ''" + res[i].EVENT_CODE + "'')";
OracleCommand command = new OracleCommand(sql, con);
command.ExecuteNonQuery();
}
}
Console.WriteLine("Successfully Inserted");
Console.WriteLine();
Console.WriteLine("Number of Row Data: " + jmlRecord.ToString());
Console.WriteLine();
Console.WriteLine("The size of {0} is {1} bytes.", f2.Name, f2.Length);
con.Close();
}
static void ShowPercentProgress(string message, int currElementIndex, int totalElementCount)
{
if (currElementIndex < 0 || currElementIndex >= totalElementCount)
{
throw new InvalidOperationException("currElement out of range");
}
int percent = (100 * (currElementIndex + 1)) / totalElementCount;
Console.Write("\r{0}{1}% complete", message, percent);
if (currElementIndex == totalElementCount - 1)
{
Console.WriteLine(Environment.NewLine);
}
}
static int CountLinesInFile(string f)
{
int count = 0;
using (StreamReader r = new StreamReader(f))
{
string line;
while ((line = r.ReadLine()) != null)
{
count++;
}
}
return count;
}
}
[DelimitedRecord(",")]
public class XL_XDR
{
public string XDR_ID;
public string XDR_TYPE;
public string SESSION_START_TIME;
public string SESSION_END_TIME;
public string SESSION_LAST_UPDATE_TIME;
public string SESSION_FLAG;
public string VERSION;
public string CONNECTION_ROW_COUNT;
public string ERROR_CODE;
public string METHOD;
public string HOST_LEN;
public string HOST;
public string URL_LEN;
public string URL;
public string CONNECTION_START_TIME;
public string CONNECTION_LAST_UPDATE_TIME;
public string CONNECTION_FLAG;
public string CONNECTION_ID;
public string TOTAL_EVENT_COUNT;
public string TUNNEL_PAIR_ID;
public string RESPONSIVENESS_TYPE;
public string CLIENT_PORT;
public string PAYLOAD_TYPE;
public string VIRTUAL_TYPE;
public string VID_CLIENT;
public string VID_SERVER;
public string CLIENT_ADDR;
public string SERVER_ADDR;
public string CLIENT_TUNNEL_ADDR;
public string SERVER_TUNNEL_ADDR;
public string ERROR_CODE_2;
public string IPID;
public string C2S_PKTS;
public string C2S_OCTETS;
public string S2C_PKTS;
public string S2C_OCTETS;
public string NUM_SUCC_TRANS;
public string CONNECT_TIME;
public string TOTAL_RESP;
public string TIMEOUTS;
public string RETRIES;
public string RAI;
public string TCP_SYNS;
public string TCP_SYN_ACKS;
public string TCP_SYN_RESETS;
public string TCP_SYN_FINS;
public string EVENT_TYPE;
public string FLAGS;
public string TIME_STAMP;
public string EVENT_ID;
public string EVENT_CODE;
}
}
我希望有人能给我解决方案.谢谢:)
I hope someone can give me a solution. Thanks :)
推荐答案
尝试更改内容以使用参数.如果使用参数，则可以使用初始参数值执行一次字符串，然后从CSV读取下一行以更新参数，然后最后刷新命令. :)
Try to change things to use parameters. If you use parameters you can execute the string once with initial parameter values, then read next row from CSV to update your parameters and then finally refresh the command. :)
看看这篇文章
批量插入-oracle-using-ODP.Net [ ^ ]
http://www.oracle.com/technology/sample_code/tech/Windows/odpnet/howto/arraybind/index.html [ ^ ]
这里有趣的讨论在那里可以下载一个电子书链接.这可能会有所帮助.
http://forums.oracle.com/forums/thread.jspa?threadID=385089 [ ^ ]
Have a look on this article
Bulk-insert-to-oracle-using-ODP.Net[^]
http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html[^]
Interesting discussion here & download one ebook link is available there. it might be helpful.
http://forums.oracle.com/forums/thread.jspa?threadID=385089[^]
这篇关于如何使用c#将csv数据导入Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
