public void Process(ETLInfo etlInfo, string oleDbConnstring, string destinationOleDbConnectionString, bool clearDataBeforeETL = false, Action <int> callback = null, int pageSize = 10000)
{
int pageIndex = 0;
int result = 1;
BulkCopy bcp = new BulkCopy(oleDbConnstring);
OleDbConnection sourceConn = new OleDbConnection(oleDbConnstring);
OleDbConnection dstConnection = new OleDbConnection(destinationOleDbConnectionString);
if (clearDataBeforeETL)
{
OleDbHelper.TruncateTable(dstConnection, etlInfo.DestinationTableName);
}
int success = 0;
while (result > 0)
{
result = BulkCopyByPage(etlInfo, pageSize, pageIndex, bcp, sourceConn, dstConnection);
if (result > 0)
{
pageIndex++;
success += result;
if (callback != null)
{
callback(success);
}
}
}
}
public static void BulkSqlInsert <T>(List <T> list, string tableName, string connectionString)
{
if (list == null)
{
throw new Exception("List of " + typeof(T).ToString() + " is null!");
}
BulkCopy.BulkSqlInsert(BulkCopy.ToDataTable <T>(list), tableName, connectionString);
}
public void WriteToServer()
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
BulkCopy.WriteToServer(InternalTable);
}
public void Insert()
{
BulkCopy.BulkCopyTimeout = BulkCopySettings.BulkCopyTimeout;
BulkCopy.BatchSize = BulkCopySettings.BatchSize;
BulkCopy.DestinationTableName = TableName;
Connection.Open();
BulkCopy.WriteToServer(DataTable);
Connection.Close();
}
private static void Test <T>(DbConnection connection)
{
using (var bcp = BulkCopy.TryCreate(connection))
{
Assert.NotNull(bcp);
Assert.IsType <T>(bcp.Wrapped);
bcp.EnableStreaming = true;
}
}
public async Task WriteToServerAsync()
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
await BulkCopy.WriteToServerAsync(InternalTable);
}
private static void Copy(Opts opts)
{
if (opts.Test)
{
return;
}
BulkCopy copier;
var forceOverwrite = !(opts.PreserveLocalChanges ?? true);
if (opts.ImportRelto.HasValue)
{
copier = opts.ExportRelto.HasValue
? BulkCopy.CreateAsync(opts.DekiApi, opts.ExportDocument, opts.ExportRelto.Value, opts.ImportRelto.Value, forceOverwrite, new Result <BulkCopy>()).Wait()
: BulkCopy.CreateAsync(opts.DekiApi, opts.ExportDocument, opts.ExportReltoPath, opts.ImportRelto.Value, forceOverwrite, new Result <BulkCopy>()).Wait();
}
else
{
copier = opts.ExportRelto.HasValue
? BulkCopy.CreateAsync(opts.DekiApi, opts.ExportDocument, opts.ExportRelto.Value, opts.ImportReltoPath, forceOverwrite, new Result <BulkCopy>()).Wait()
: BulkCopy.CreateAsync(opts.DekiApi, opts.ExportDocument, opts.ExportReltoPath, opts.ImportReltoPath, forceOverwrite, new Result <BulkCopy>()).Wait();
}
Result <BulkCopy> result = copier.CopyAsync(new Result <BulkCopy>());
int completed = 0;
Console.WriteLine("Exporting: {0}", opts.ExportPath);
while (!result.HasFinished)
{
Thread.Sleep(200);
if (copier.CompletedItems <= completed)
{
continue;
}
if (SysUtil.IsUnix)
{
Console.WriteLine(" {0} of {1} files ({2:0}%)", copier.CompletedItems, copier.TotalItems, 100.0 * copier.CompletedItems / copier.TotalItems);
}
else
{
Console.Write(" {0} of {1} files ({2:0}%) \r", copier.CompletedItems, copier.TotalItems, 100.0 * copier.CompletedItems / copier.TotalItems);
}
if (!SysUtil.IsUnix)
{
Console.WriteLine();
}
completed = copier.CompletedItems;
}
if (result.HasException)
{
throw new Exception(string.Format("Copy failed: {0}", result.Exception.Message), result.Exception);
}
}
public void BulkInsert(ICollection <T> entityCollection)
{
// Inicia o escopo de uma transação
using (SqlTransaction transaction = ((SqlConnection)_dbContext.Database.Connection).BeginTransaction())
{
using (var bulkCopy = new BulkCopy(transaction))
{
using (DataTable dataTable = GetDataTable(entityCollection))
{
// Performa o bulk insert
bulkCopy.BulkInsert(dataTable, SqlBulkCopyOptions.CheckConstraints);
}
}
}
}
static void Main(string[] args)
{
Console.WriteLine("Indicate the full path of the xml file:");
string xmlPath = Console.ReadLine();
Console.WriteLine("Indicate the database name");
string dbName = Console.ReadLine();
Console.WriteLine("Indicate the table name");
string tableName = Console.ReadLine();
Console.WriteLine("Indicate the server name");
string serverName = Console.ReadLine();
BulkCopy.BulKCopyXml(xmlPath, dbName, tableName, serverName);
Console.WriteLine("Process end.");
}
public FileLoader(SimpleLogger log, string sqlConnectionString, string fileName)
{
SqlConnectionString = sqlConnectionString;
FileName = fileName;
dt = new DataTable();
using (var reader = new CsvReader(fileName))
{
var newRow = dt.NewRow();
var firstRow = true;
var defaultColCount = 0;
tableName = fileName.Split('\\').Last().Replace(".csv", "");
foreach (string[] values in reader.RowEnumerator)
{
if (firstRow)
{
AddColumns(ref dt, values);
firstRow = false;
continue;
}
for (var i = 0; i < values.Length; i++)
{
newRow[defaultColCount + i] = values[i];
}
dt.Rows.Add(newRow);
newRow = dt.NewRow();
}
}
const string namePrefix = "temp_from_JamieHo_FileLoader_";
const string nameSuffix = ""; // "_JamieHo_Auto_Upload";
Sql.ExecuteSqlCommand(log, sqlConnectionString, $@"begin try drop table {namePrefix + tableName} end try begin catch end catch;");
Sql.ExecuteSqlCommand(log, sqlConnectionString, CreateTable.GetDDL(namePrefix + tableName, dt));
var bulkCopy = new BulkCopy(sqlConnectionString, namePrefix + tableName, dt);
Sql.ExecuteSqlCommand(log, sqlConnectionString, $@"begin try drop table {tableName + nameSuffix} end try begin catch end catch;");
Sql.ExecuteSqlCommand(log, sqlConnectionString, $@"exec sp_rename '{namePrefix + tableName}','{tableName + nameSuffix}'");
Sql.ExecuteSqlCommand(log, sqlConnectionString, $@"begin try drop table {namePrefix + tableName} end try begin catch end catch;");
}
private int BulkCopyByPage(ETLInfo etlInfo, int pageSize, int pageIndex, BulkCopy bcp, OleDbConnection sourceConnection, DbConnection DestinationConnection)
{
string sql = etlInfo.SourceTable.ToQuerySQL(pageSize, pageIndex);;
try
{
DataTable dt = OleDbHelper.ExecuteDataTable(sourceConnection, sql);
if (dt == null || dt.Rows.Count == 0)
{
return(0);
}
bcp.Insert(etlInfo.DestinationTableName, dt, etlInfo.ColumnMapping);
return(dt.Rows.Count);
}
catch (Exception ex)
{
string errorString = string.Format("转移失败,数据源SQL:{0}...", sql);
File.AppendAllText(@"bulkcopy.log", errorString);
throw;
}
}
public void InitializeBehavesAsExpected()
{
var initialized = false;
_connection
.SetupGet(m => m.Connected)
.Returns(() => true);
_connection
.SetupGet(m => m.Initialized)
.Returns(() => initialized);
_connection
.Setup(m => m.Initialize(AnyTableNameWillDo))
.Callback(() => initialized = true);
using (var sut = new BulkCopy())
{
sut.Initialize(AnyTableNameWillDo);
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.VerifyGet(m => m.Initialized, Times.Once);
_connection.Verify(m => m.Initialize(AnyTableNameWillDo), Times.Once);
}
public void ExecuteDirectThrowsExceptionWhenCommandTextIsNullOrEmpty()
{
using (var sut = new BulkCopy())
{
var bc = sut;
TestHelper.AssertThrows<ArgumentException>(() => bc.ExecuteDirect(null));
TestHelper.AssertThrows<ArgumentException>(() => bc.ExecuteDirect(string.Empty));
}
}
public void ExecuteDirectThrowsExceptionIfNotConnected()
{
_connection
.SetupGet(m => m.Connected)
.Returns(() => false);
using (var sut = new BulkCopy())
{
var bc = sut;
TestHelper.AssertThrows<InvalidOperationException>(() => bc.ExecuteDirect(AnyCommandTextWillDo));
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.Verify(m => m.ExecuteDirect(AnyCommandTextWillDo), Times.Never);
}
public void ExecuteDirectBehavesAsExpected()
{
_connection
.SetupGet(m => m.Connected)
.Returns(true);
using (var sut = new BulkCopy())
{
sut.ExecuteDirect(AnyCommandTextWillDo);
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.Verify(m => m.ExecuteDirect(AnyCommandTextWillDo), Times.Once);
}
public void ConnectThrowsExceptionIfAlreadyConnected()
{
_connection
.SetupGet(m => m.Connected)
.Returns(() => true);
using (var sut = new BulkCopy())
{
var bc = sut;
TestHelper.AssertThrows<InvalidOperationException>(() => bc.Connect(AnyConnectionStringWillDo));
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.Verify(m => m.Connect(AnyConnectionStringWillDo), Times.Never);
}
public void ConnectBehavesAsExpected()
{
var connected = false;
_connection
.SetupGet(m => m.Connected)
.Returns(() => connected);
_connection
.Setup(m => m.Connect(AnyConnectionStringWillDo))
.Callback(() => connected = true);
using (var sut = new BulkCopy())
{
sut.Connect(AnyConnectionStringWillDo);
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.Verify(m => m.Connect(AnyConnectionStringWillDo), Times.Once);
}
public static void BulkInsert(DbConnection conn, string providerName, string tableName, DataTable sourceData, Dictionary <string, string> columnMappings = null)
{
BulkCopy bulkCopy = new BulkCopy(conn.ConnectionString, BulkCopy.GetSupportedDBType(providerName));
bulkCopy.Insert(tableName, sourceData, columnMappings);
}
public ReturnValue sync([FromBody] RequestValue value)
{
if (value == null)
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
try
{
// User SN 정보 조회
ObjectParameter userSN = new ObjectParameter("UserSN", typeof(long));
ObjectParameter gUID = new ObjectParameter("GUID", typeof(string));
ObjectParameter hashPhone = new ObjectParameter("HashPhone", typeof(string));
ObjectParameter deviceID = new ObjectParameter("DeviceID", typeof(string));
ObjectParameter pushKey = new ObjectParameter("PushKey", typeof(string));
ObjectParameter createDate = new ObjectParameter("CreateDate", typeof(DateTime));
ObjectParameter updateDate = new ObjectParameter("UpdateDate", typeof(DateTime));
entity.UserGetInfoByToken(value.token, userSN, gUID, hashPhone, deviceID, pushKey, createDate, updateDate);
// 저장되 있는 연락처 정보와 업데이트 연락처 정보를 머징.
ContactsRequest syncdata = JsonConvert.DeserializeObject <ContactsRequest>(Convert.ToString(value.data));
DataTable newContacts = BulkCopy.MakeTable();
foreach (string hashphone in syncdata.contacts)
{
//entity.ContactsCreate((long)userSN.Value, hashphone);
DataRow row = newContacts.NewRow();
row["usersn"] = (long)userSN.Value;
row["hashphone"] = hashphone;
newContacts.Rows.Add(row);
}
newContacts.AcceptChanges();
DataRow[] rowArray = newContacts.Select();
BulkCopy bc = new BulkCopy();
bc.copy(rowArray);
returnValue.error = 0;
returnValue.message = "OK";
ObjectParameter totalCount = new ObjectParameter("TotalCount", typeof(int));
List <string> installusers = entity.ContactsInstallUserList((long)userSN.Value, totalCount).ToList <string>();
returnValue.data = new
{
totalcount = (int)totalCount.Value,
installusers
};
}
catch (InvalidCastException)
{
returnValue.error = 100;
returnValue.message = "USER_NOT_FOUND";
}
catch (Exception ex)
{
entity.ErrorLogCreate(HttpRequestMessageHelper.GetClientIpAddress(Request), Request.RequestUri.AbsoluteUri, ex.Source, ex.TargetSite.Name, ex.Message, ex.StackTrace, JsonConvert.SerializeObject(value));;
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
return(returnValue);
}
public void InitializeThrowsExceptionWhenTableNameIsNullOrEmpty()
{
using (var sut = new BulkCopy())
{
var bc = sut;
TestHelper.AssertThrows<ArgumentException>(() => bc.Initialize(null));
TestHelper.AssertThrows<ArgumentException>(() => bc.Initialize(string.Empty));
}
}
public void InitializesAndDisposesAsExpectedWhenUsingSharedEnvironment()
{
var environment = BulkCopy.CreateSharedEnvironment();
using (var sut = new BulkCopy(environment))
{
Assert.IsNotNull(sut.Columns);
}
// construction
_driver.Verify(m => m.CreateEnvironment(OdbcConnectionPooling.PerEnvironment), Times.Once);
_environment.Verify(m => m.CreateConnection(), Times.Once);
// destruction
_connection.Verify(m => m.Disconnect(), Times.Once);
_connection.Verify(m => m.Dispose(), Times.Once);
_environment.Verify(m => m.Dispose(), Times.Never);
environment.Dispose();
_environment.Verify(m => m.Dispose(), Times.Once);
}
public static void BulkInsert(OleDbConnection conn, string tableName, DataTable sourceData, Dictionary<string, string> columnMappings = null)
{
BulkCopy bulkCopy = new BulkCopy(conn);
bulkCopy.Insert(tableName, sourceData, columnMappings);
}
public static void BulkInsert(DbConnection conn, string providerName, string tableName, DataTable sourceData, Dictionary<string, string> columnMappings = null)
{
BulkCopy bulkCopy = new BulkCopy(conn.ConnectionString, BulkCopy.GetSupportedDBType(providerName));
bulkCopy.Insert(tableName, sourceData, columnMappings);
}
protected void ButtonSubirPArchivo_Click(object sender, EventArgs e)
{
List<ComparativoExcel> listadoComparativo = null;
List<Llamadas> listadoLlamadas = null;
List<Activos> listadoActivos = null;
List<SL> listadoSLP = null;
string mensaje;
if (FileUploadPrimerArchivo.HasFile)
{
try
{
int columnas = 0;
string folder = string.Format("{0}{1}{2}", Server.MapPath("~/App_Archivos/"), Guid.NewGuid(), FileUploadPrimerArchivo.FileName);
//guardo el archivo en la carpeta asignada
FileUploadPrimerArchivo.SaveAs(folder);
//Validación del número de campos contenidos en el archivo
using (StreamReader rd = new StreamReader(folder))
{
while (!rd.EndOfStream)
{
var splits = rd.ReadLine().Split(';');
columnas = splits.Count();
break;
}
}
TipoArchivoPorColumnas tipoArchivoPorColumnas;
Enum.TryParse(columnas.ToString(), out tipoArchivoPorColumnas);
//obtengo el tipo de archivo que estan subiendo
int tipoArchivo = Convert.ToInt32(cbxTipoArchivo.SelectedItem.Value);
switch (tipoArchivo)
{
//Activos
case 1:
int columnas1 = 0;
//Validación del número de campos contenidos en el archivo
using (StreamReader rd = new StreamReader(folder))
{
while (!rd.EndOfStream)
{
var splits = rd.ReadLine().Split(';');
columnas1 = splits.Count();
break;
}
}
TipoArchivoPorColumnas tipoArchivoPorColumnas1;
Enum.TryParse(columnas1.ToString(), out tipoArchivoPorColumnas1);
if (tipoArchivoPorColumnas1 == TipoArchivoPorColumnas.Activos)
{
listadoActivos = new List<Activos>(LectorCSV<Activos>.obtenerEntidadesMasivas(folder) as Activos[]);
if (listadoActivos == null)
mensaje = "columnas no validas";
else if (listadoActivos != null)
{
listadoActivos.RemoveAt(0);
//se sube a sesion los datos
//Session[ACTIVOS_SESSION] = listadoActivos;
Session[ACTIVOS_SESSION] = (from p in listadoActivos select p.Set(dm => { dm.Cedula = Utilidades.eliminarCeros(dm.Cedula); dm.Mes = cbxMes.SelectedItem.Value; dm.Año = cbxAño.SelectedItem.Value; })).ToList();
//Session[ACTIVOS_SESSION] = (from p in listadoActivos select p.Set(dm => { dm.Cedula = Utilidades.eliminarCeros(dm.Cedula); })).ToList();
CargueDatosExcel cargue = new CargueDatosExcel();
cargue.DataTablePrimerArchivo = Utilidades.ToDataTable<Activos>((List<Activos>)Session[ACTIVOS_SESSION], "Id", typeof(Guid), false);
//Carga en el bulk copy
BulkCopy sqlBulk = new BulkCopy();
sqlBulk.GuardarDataTableBD(cargue.DataTablePrimerArchivo, "ProcesosTemporales.Activos");
LabelStatus.Text = "subida de archivo exitoso";
}
}
break;
//comparativo excel
case 2:
if (tipoArchivoPorColumnas == TipoArchivoPorColumnas.Excel)
{
listadoComparativo = new List<ComparativoExcel>(LectorCSV<ComparativoExcel>.obtenerEntidadesMasivas(folder) as ComparativoExcel[]);
if (listadoComparativo == null)
mensaje = "columnas no validas";
else if (listadoComparativo != null)
{
listadoComparativo.RemoveAt(0);
//subo a sesion la lista de elementos de ComparativoExcel
Session[LISTA_EXCEL] = listadoComparativo;
LabelStatus.Text = "subida de archivo exitoso";
}
}
break;
//llamadas
case 3:
if (tipoArchivoPorColumnas == TipoArchivoPorColumnas.RegistrosCall)
{
listadoLlamadas = new List<Llamadas>(LectorCSV<Llamadas>.obtenerEntidadesMasivas(folder) as Llamadas[]);
if (listadoLlamadas == null)
mensaje = "columnas no validas";
else if (listadoLlamadas != null)
{
listadoLlamadas.RemoveAt(0);
//subo a sesion la lista de elementos de ComparativoExcel
Session[LISTA_LLAMADAS] = listadoLlamadas;
CargueDatosExcel cargue = new CargueDatosExcel();
cargue.DataTablePrimerArchivo = Utilidades.ToDataTable<Llamadas>((List<Llamadas>)Session[LISTA_LLAMADAS], "Id", typeof(Guid), false);
//Carga en el bulk copy
BulkCopy sqlBulk = new BulkCopy();
sqlBulk.GuardarDataTableBD(cargue.DataTablePrimerArchivo, "ProcesosTemporales.Llamadas");
LabelStatus.Text = "subida de archivo exitoso";
}
}
break;
//SLP
case 4:
int columnasSLP = 0;
//Validación del número de campos contenidos en el archivo
using (StreamReader rd = new StreamReader(folder))
{
while (!rd.EndOfStream)
{
var splits = rd.ReadLine().Split(';');
columnasSLP = splits.Count();
break;
}
}
TipoArchivoPorColumnas tipoArchivoPorColumnasSLP;
Enum.TryParse(columnasSLP.ToString(), out tipoArchivoPorColumnasSLP);
if (tipoArchivoPorColumnasSLP == TipoArchivoPorColumnas.Slp)
{
listadoSLP = new List<SL>(LectorCSV<SL>.obtenerEntidadesMasivas(folder) as SL[]);
if (listadoSLP == null)
mensaje = "columnas no validas";
else if (listadoSLP != null)
{
listadoSLP.RemoveAt(0);
//se sube a sesion los datos
Session[SLP] = (from p in listadoSLP select p.Set(dm => { dm.Identificacion = Utilidades.eliminarCeros(dm.Identificacion); dm.Mes = cbxMes.SelectedItem.Value; dm.Año = cbxAño.SelectedItem.Value; })).ToList();
//Session[ACTIVOS_SESSION] = (from p in listadoActivos select p.Set(dm => { dm.Cedula = Utilidades.eliminarCeros(dm.Cedula); })).ToList();
CargueDatosExcel cargue = new CargueDatosExcel();
cargue.DataTablePrimerArchivo = Utilidades.ToDataTable<SL>((List<SL>)Session[SLP], "Id", typeof(Guid), false);
//Carga en el bulk copy
BulkCopy sqlBulk = new BulkCopy();
sqlBulk.GuardarDataTableBD(cargue.DataTablePrimerArchivo, "ProcesosTemporales.SLP");
LabelStatus.Text = "subida de archivo exitoso";
}
}
break;
default:
mensaje = "no es ningun archivo reconocido";
break;
}
//eliminación del archivo del servidor
File.Delete(folder);
}
catch (Exception exc)
{
LabelStatus.Text = "Ha ocurrido un error:" + exc.Message;
}
}
}
public void InitializeThrowsExceptionIfNotConnected()
{
_connection
.SetupGet(m => m.Connected)
.Returns(() => false);
using (var sut = new BulkCopy())
{
var bc = sut;
TestHelper.AssertThrows<InvalidOperationException>(() => bc.Initialize(AnyTableNameWillDo));
}
_connection.VerifyGet(m => m.Connected, Times.Once);
_connection.Verify(m => m.Initialize(AnyTableNameWillDo), Times.Never);
}
private static void VerifyRoundtripsAsExpected(OdbcDriver driver)
{
/*
* Reasons this test might fail:
* 1. You need to execute SqlBulkCopy.sql (in the same directory as this test) to create the test database.
* 2. You need to update the connection string to specify the location of your instance of the test database.
* 3. The particular driver is not installed on your PC.
*/
const string connectionString = "Server=WORKSTATION\\SQL2012;Database=SqlBulkCopy;Trusted_Connection=yes;";
const string tableName = "SqlBulkCopyTest";
List<IBulkCopyBoundColumn> columns;
var data = TestData.GetSampleData();
BulkCopy.Driver = driver;
BulkCopy.Trace = true;
try
{
using (var sut = new BulkCopy())
{
sut.LoginTimeout = 2;
try
{
sut.Connect(string.Concat(sut.DriverOptionString, ";", connectionString));
}
catch (Exception ex)
{
const string message = "Please refer to this test for possible solutions." +
"\r\n\r\nTesting: {0}." +
"\r\n\r\nODBC exception text: {1}.";
Assert.Fail(message, sut.DriverOptionString, ex.Message);
}
sut.ConnectionTimeout = 2;
sut.ExecuteDirect("TRUNCATE TABLE SqlBulkCopyTest;");
sut.Initialize(tableName);
var importer = new TestDataImporter(sut);
columns = sut.Columns
.Where(c => c.Index > 0)
.Cast<IBulkCopyBoundColumn>()
.ToList();
LoadData(importer, data);
}
}
finally
{
BulkCopy.Trace = false;
}
List<object[]> rows;
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
rows = FetchData(cn, columns.Count);
}
VerifyFetchedData(columns, data, rows);
}
private BulkCopy GetInitializedSubjectUnderTest()
{
var connected = false;
var initialized = false;
_connection
.SetupGet(m => m.Connected)
.Returns(() => connected);
_connection
.SetupGet(m => m.Initialized)
.Returns(() => initialized);
_connection
.Setup(m => m.Connect(AnyConnectionStringWillDo))
.Callback(() => connected = true);
_connection
.Setup(m => m.Initialize(AnyTableNameWillDo))
.Callback(() => initialized = true);
var sut = new BulkCopy();
sut.Connect(AnyConnectionStringWillDo);
sut.Initialize(AnyTableNameWillDo);
return sut;
}
public static void BulkInsert(OleDbConnection conn, string tableName, DataTable sourceData, Dictionary <string, string> columnMappings = null)
{
BulkCopy bulkCopy = new BulkCopy(conn);
bulkCopy.Insert(tableName, sourceData, columnMappings);
}
