public IList <long> BulkInsert(string fullFilePath, string tableName)
{
IList <long> rejectedRowNumbers = null;
using (var verticaConnection = new VerticaConnection(_connectionString))
{
try
{
verticaConnection.Open();
using (var verticaTransaction = verticaConnection.BeginTransaction())
{
try
{
FileStream inputFileStream = File.OpenRead(fullFilePath);
string copy = @"COPY extr_manual." + tableName + @" FROM STDIN RECORD TERMINATOR E'\r\n' DELIMITER E'|' ENFORCELENGTH NO COMMIT";
VerticaCopyStream stream = new VerticaCopyStream(verticaConnection, copy);
stream.Start();
stream.AddStream(inputFileStream, false);
stream.Execute();
long insertedRowsCount = stream.Finish();
rejectedRowNumbers = stream.Rejects;
if (rejectedRowNumbers.Count == 0)
{
verticaTransaction.Commit();
}
else
{
verticaTransaction.Rollback();
}
}
catch (Exception ex)
{
verticaTransaction.Rollback();
// TASK : BulkInsert Exception details may be logged for further analysis
throw;
}
}
}
finally
{
verticaConnection.Close();
}
}
return(rejectedRowNumbers);
}
/// <summary>
/// 数据复制
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <param name="strTableName"></param>
public static void BulkCopy <T>(DataTable dt, string strTableName)
{
MemoryStream ms = null;
NHibernate.ITransaction txn = null;
try
{
if (dt == null || dt.Rows.Count == 0)
{
return;
}
if (dt.Columns.Count == 0)
{
throw new Exception("The length of column cannot be zero.");
}
//从datatable中获取列名
List <string> lstField = new List <string>();
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
lstField.Add(dt.Columns[colIndex].ColumnName);
}
string strFiledList = string.Format("({0})", string.Join(",", lstField.ToArray()));
//拼写copy语句
const char RowSplit = '\n';
const char ColSplit = '\t';
string strCopyStatement = string.Format("copy {0}{1} from stdin record terminator E'{2}' delimiter E'{3}' enforcelength no commit",
strTableName, strFiledList, RowSplit, ColSplit);
//按照copy语句中的分隔符，分隔数据源
StringBuilder sbText = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
bool bFirstField = true;
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
string strVal = GetDataString(dr, colIndex);
if (bFirstField)
{
sbText.Append(strVal);
bFirstField = false;
}
else
{
sbText.AppendFormat("{0}{1}", ColSplit, strVal);
}
}
sbText.Append(RowSplit);
}
//数据源写入内存流
string strTemp = sbText.ToString();
byte[] buff = Encoding.UTF8.GetBytes(strTemp);
using (ms = new MemoryStream())
{
ms.Write(buff, 0, buff.Length);
ms.Flush();
ms.Position = 0;
//建立vertica数据库连接
using (var session = NHibernateHelper <T> .OpenSession())
{
txn = session.BeginTransaction();
var vcs = new VerticaCopyStream((VerticaConnection)session.Connection, strCopyStatement);
vcs.Start();
vcs.AddStream(ms, false);
vcs.Execute();
long insertedCount = vcs.Finish();
IList <long> lstRejected = vcs.Rejects;
if (lstRejected.Count > 0)
{
txn.Rollback();
// Maybe need more detail info to show
throw new Exception("Bulk copy failure.");
}
else
{
txn.Commit();
}
}
ms.Close();
}
}
catch (Exception ex)
{
txn.Rollback();
ms.Close();
throw (ex);
}
}
