public void BulkCopyAndSave <T>(IEnumerable <T> source, bool isDispose = false, int?batchSize = null)
{
var mapping = TFundSolution.Utils.EntityFramworks.EfMappingFactory.GetMappingsForContext(this.ContextCis);
var currentType = typeof(T);
var typeMapping = mapping.TypeMappings[typeof(T)];
var tableMapping = typeMapping.TableMappings.First();
IList <ColumnMapping> properties = tableMapping.PropertyMappings
.Where(p => currentType.IsSubclassOf(p.ForEntityType) || p.ForEntityType == currentType)
.Select(p => new ColumnMapping {
NameInDatabase = p.ColumnName, NameOnObject = p.PropertyName
}).ToList();
if (tableMapping.TPHConfiguration != null)
{
properties.Add(new ColumnMapping
{
NameInDatabase = tableMapping.TPHConfiguration.ColumnName,
StaticValue = tableMapping.TPHConfiguration.Mappings[typeof(T)]
});
}
using (var reader = new EFDataReader <T>(source, properties))
{
var connection = new Oracle.DataAccess.Client.OracleConnection(ContextCis.Database.Connection.ConnectionString);
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
using (Oracle.DataAccess.Client.OracleBulkCopy copy = new Oracle.DataAccess.Client.OracleBulkCopy(connection, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
{
copy.BatchSize = Math.Min(reader.RecordsAffected, batchSize ?? 10000); //default batch size
copy.BulkCopyTimeout = 3600;
if (!string.IsNullOrWhiteSpace(tableMapping.Schema))
{
copy.DestinationTableName = string.Format("{0}.{1}", tableMapping.Schema, tableMapping.TableName);
}
else
{
copy.DestinationTableName = tableMapping.TableName;
}
copy.NotifyAfter = 0;
foreach (var i in Enumerable.Range(0, reader.FieldCount))
{
copy.ColumnMappings.Add(i, properties[i].NameInDatabase);
}
var sourceTable = source.AsDataTableWithContext(this.ContextCis);
copy.WriteToServer(sourceTable);
copy.Close();
}
if (isDispose)
{
this.ReNewContext();
}
}
}
public bool ImportaDados(CargaDados carga, DataTable dt)
{
bool ret = false;
using (Oracle.DataAccess.Client.OracleBulkCopy bulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(ConfigAplication.GetConnectString().Replace(";Unicode=True", "")))
{
bulkCopy.DestinationTableName = carga.de_para[0].tabela_destino;
string strColunas = "";
foreach (CargaDadosDePara cddBulkCol in carga.de_para)
{
bulkCopy.ColumnMappings.Add(cddBulkCol.origem_campo, cddBulkCol.destino_campo);
strColunas += cddBulkCol.origem_campo + ", ";
}
strColunas = strColunas.Substring(0, strColunas.Length - 2);
try
{
bulkCopy.WriteToServer(dt);
ret = true;
}
catch (Exception ex)
{
ret = false;
throw new Exception(ex.Message + "\\n\\nVerique se a planinha contém as colunas (" + strColunas + ")");
}
finally
{
bulkCopy.Close();
}
}
return(ret);
}
/// <summary>
/// OracleBulkCopy批量插入数据
/// 【*注：调用此方法DataTable中的字段必须与对应插入表的字段保持一致】
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="dataTableName">表名</param>
/// <param name="sourceDataTable">数据源</param>
/// <param name="batchSize">一次事务插入的行数</param>
/// <param name="copyTimeout">最长执行时间(默认定义为10分钟)</param>
/// <returns>返回是否成功</returns>
/// 创建者：蒋浩
/// 创建时间：2018-4-8
public static bool OracleBulkCopyByDataTable(string connectionString, string dataTableName, DataTable sourceDataTable, int batchSize = 100000, int copyTimeout = 600)
{
bool flag = false;
try
{
if (sourceDataTable == null || sourceDataTable.Rows.Count == 0)
{
return(flag);
}
using (var conn = new System.Data.OleDb.OleDbConnection(connectionString))
{
conn.Open();
using (Oracle.DataAccess.Client.OracleBulkCopy oracleBulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(connectionString, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
{
//服务器上目标表的名称
oracleBulkCopy.DestinationTableName = dataTableName;
oracleBulkCopy.BatchSize = batchSize;
oracleBulkCopy.BulkCopyTimeout = copyTimeout;
for (int i = 0; i < sourceDataTable.Columns.Count; i++)
{
//列映射定义数据源中的列和目标表中的列之间的关系
oracleBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
}
try
{
oracleBulkCopy.WriteToServer(sourceDataTable);
flag = true;
}
catch
{
return(false);
}
finally
{
conn.Close();
if (oracleBulkCopy != null)
{
oracleBulkCopy.Close();
}
}
}
}
}
catch
{
return(false);
}
return(flag);
}
public string InsertCovarianceMatrix(GlobalClass.CovarianceMatrix obj)
{
try
{
DataTable dt = (DataTable)JsonConvert.DeserializeObject(obj.ConvMatrix, (typeof(DataTable)));
using (var connection = new Oracle.DataAccess.Client.OracleConnection(connectionString))
{
connection.Open();
using (var bulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(connection, Oracle.DataAccess.Client.OracleBulkCopyOptions.UseInternalTransaction))
{
bulkCopy.DestinationTableName = "COVARIANCE_MATRIX";
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.WriteToServer(dt);
}
}
}
catch (Exception ex)
{
throw ex;
}
return("");
}
/// <summary>
/// 批量导入到oracle
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public ActionResult UploadMB(HttpPostedFileBase file)
{
string err = "";
OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(qpssyh);
if (file != null)
{
try
{
DataTable dt = NPOIHelper.ImportExceltoDt(file.InputStream, "Sheet1", 0);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string sql = "select count(1) tabcnt from user_tables where table_name = upper('xcrw_batch') ";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataReader dr = cmd.ExecuteReader();
int tabcnt = 0;
if (dr.Read())
{
tabcnt = Convert.ToInt32(dr["tabcnt"]);
}
dr.Close();
if (tabcnt > 0)
{
sql = "drop table xcrw_batch";
cmd = null;
cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
}
sql = @" create table xcrw_batch(
任务名称 NVARCHAR2(100),
来源 NVARCHAR2(100),
大类 NVARCHAR2(100),
小类 NVARCHAR2(100),
紧急程度 NVARCHAR2(100)
)";
cmd = null;
cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
OracleBulkCopy bulkCopy = new Oracle.DataAccess.Client.OracleBulkCopy(conn, Oracle.DataAccess.Client.OracleBulkCopyOptions.Default);
bulkCopy.BatchSize = 100000;
bulkCopy.BulkCopyTimeout = 260;
//targetTable目标表名
bulkCopy.DestinationTableName = "XCRW_BATCH";
if (dt.Columns.Count > 0)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i > 4)
{
continue;
}
if (dt.Columns[0].ColumnName != "任务名称")
{
err += "第一列列名应为任务名称";
}
if (dt.Columns[1].ColumnName != "来源")
{
err += "第二列列名应为来源";
}
if (dt.Columns[2].ColumnName != "大类")
{
err += "第三列列名应为大类";
}
if (dt.Columns[3].ColumnName != "小类")
{
err += "第四列列名应为小类";
}
if (dt.Columns[4].ColumnName != "紧急程度")
{
err += "第五列列名应为紧急程度";
}
bulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);//源列名->目标列名
}
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
if (dt != null && dt.Rows.Count != 0)
{
bulkCopy.WriteToServer(dt);
bulkCopy.Dispose();
}
sql = "insert into T_PATROL_TASK(S_NAME,S_SOURCE,S_CATEGORY,S_TYPE,S_EMERGENCY) select 任务名称,来源,大类,小类,紧急程度 from xcrw_batch";
cmd = null;
cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "drop table xcrw_batch";
cmd = null;
cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
return(Json(new Result {
code = "200", msg = "导入成功!"
}));
}
catch (Exception e)
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
return(Json(new Result {
code = "500", msg = e.Message + "," + err
}));
}
}
else
{
return(Json(new Result {
code = "404", msg = "请选择文件!"
}));
}
}
