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