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