public SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn) { SqlBulkCopyColumnMapping columnMapping = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn); return(Add(columnMapping)); }
public SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex) { SqlBulkCopyColumnMapping columnMapping = new SqlBulkCopyColumnMapping(sourceColumnIndex, destinationColumnIndex); return(Add(columnMapping)); }
public SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex) { AssertWriteAccess(); SqlBulkCopyColumnMapping column = new SqlBulkCopyColumnMapping(sourceColumnIndex, destinationColumnIndex); return(Add(column)); }
/// <summary> /// 往数据库中批量插入数据 /// </summary> /// <param name="sourceDt">数据源表</param> /// <param name="targetTable">服务器上目标表</param> public static void BulkToDB(DataTable sourceDt, string targetTable, SqlBulkCopyColumnMapping[] mapping) { SqlConnection conn = new SqlConnection(strConn); SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中 bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称 bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数 try { conn.Open(); if (sourceDt != null && sourceDt.Rows.Count != 0) { for (int i = 0; i < mapping.Length; i++) bulkCopy.ColumnMappings.Add(mapping[i]); bulkCopy.WriteToServer(sourceDt); //将提供的数据源中的所有行复制到目标表中 } } catch (Exception ex) { throw ex; } finally { conn.Close(); if (bulkCopy != null) bulkCopy.Close(); } }
public SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn) { AssertWriteAccess(); SqlBulkCopyColumnMapping column = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn); return(Add(column)); }
/// <summary> /// excel数据到数据库 /// </summary> /// <param name="excelFile">excel文件全路径</param> /// <param name="dbConnStr">数据库连接字符串</param> /// <param name="tableName">数据库表名</param> /// <param name="mappings">列映射</param> private static void InsertDBFromExcelData(string excelFile, string dbConnStr, string tableName, SqlBulkCopyColumnMapping[] mappings) { var connStr = GetExcelConnStr(excelFile); var cnnxls = new OleDbConnection(connStr); var allSheetNames = GetAllWorkSheets(excelFile); foreach (var sheetName in allSheetNames) { var myDa = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName.Replace("$", "")), cnnxls); var dt = new DataTable(); myDa.Fill(dt); if (dt.Rows.Count > 0) { using (var copy = new SqlBulkCopy(dbConnStr)) //与目标服务器连接 { copy.BulkCopyTimeout = 5000; copy.DestinationTableName = tableName; //导入到数据库的表名 if (mappings != null) { foreach (var item in mappings) { copy.ColumnMappings.Add(item); } } copy.WriteToServer(dt); } } } }
public SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex) { this.AssertWriteAccess(); SqlBulkCopyColumnMapping bulkCopyColumnMapping = new SqlBulkCopyColumnMapping(sourceColumnIndex, destinationColumnIndex); return(this.Add(bulkCopyColumnMapping)); }
public void InsertMany(string tableName, DataTable table, bool identityInsert, SqlBulkCopyColumnMapping[] mappings) { SqlBulkCopy bulkCopy; if (identityInsert) bulkCopy = new SqlBulkCopy(_connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null); else bulkCopy = new SqlBulkCopy(_connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null); if (mappings != null) foreach (var mapping in mappings) bulkCopy.ColumnMappings.Add(mapping); bulkCopy.BulkCopyTimeout = 20000; try { _connection.Open(); //using (var bulkCopy = new SqlBulkCopy(_connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction, null)) //{ bulkCopy.DestinationTableName = tableName; bulkCopy.WriteToServer(table); //} } catch (Exception ex) { throw ex; } finally { bulkCopy.Close(); _connection.Close(); } }
public SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn) { this.AssertWriteAccess(); SqlBulkCopyColumnMapping bulkCopyColumnMapping = new SqlBulkCopyColumnMapping(sourceColumn, destinationColumn); return(this.Add(bulkCopyColumnMapping)); }
public void Insert(int index, SqlBulkCopyColumnMapping value) { if (index < 0 || index > base.Count) { throw new ArgumentOutOfRangeException("Index is out of range"); } List.Insert(index, value); }
public SqlBulkCopyColumnMapping Add (SqlBulkCopyColumnMapping bulkCopyColumnMapping) { if (bulkCopyColumnMapping == null) throw new ArgumentNullException ("bulkCopyColumnMapping"); List.Add (bulkCopyColumnMapping); return bulkCopyColumnMapping; }
public SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping) { if (bulkCopyColumnMapping == null) { throw new ArgumentNullException("bulkCopyColumnMapping"); } List.Add(bulkCopyColumnMapping); return(bulkCopyColumnMapping); }
private SqlBulkCopyColumnMapping[] GetMapping() { SqlBulkCopyColumnMapping[] mappings = new SqlBulkCopyColumnMapping[4]; mappings[0] = new SqlBulkCopyColumnMapping("ID", "ID"); mappings[1] = new SqlBulkCopyColumnMapping("Name", "Name"); mappings[2] = new SqlBulkCopyColumnMapping("sex", "sex"); mappings[3] = new SqlBulkCopyColumnMapping("phone", "phone"); return mappings; }
public SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping) { this.AssertWriteAccess(); if ((ADP.IsEmpty(bulkCopyColumnMapping.SourceColumn) && (bulkCopyColumnMapping.SourceOrdinal == -1)) || (ADP.IsEmpty(bulkCopyColumnMapping.DestinationColumn) && (bulkCopyColumnMapping.DestinationOrdinal == -1))) { throw SQL.BulkLoadNonMatchingColumnMapping(); } base.InnerList.Add(bulkCopyColumnMapping); return bulkCopyColumnMapping; }
public SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping) { AssertWriteAccess(); Debug.Assert(ADP.IsEmpty(bulkCopyColumnMapping.SourceColumn) || bulkCopyColumnMapping._internalSourceColumnOrdinal == -1, "BulkLoadAmbigousSourceColumn"); if (((ADP.IsEmpty(bulkCopyColumnMapping.SourceColumn)) && (bulkCopyColumnMapping.SourceOrdinal == -1)) || ((ADP.IsEmpty(bulkCopyColumnMapping.DestinationColumn))&&(bulkCopyColumnMapping.DestinationOrdinal == -1))) { throw SQL.BulkLoadNonMatchingColumnMapping(); } InnerList.Add(bulkCopyColumnMapping); return bulkCopyColumnMapping; }
public SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping) { this.AssertWriteAccess(); if ((ADP.IsEmpty(bulkCopyColumnMapping.SourceColumn) && (bulkCopyColumnMapping.SourceOrdinal == -1)) || (ADP.IsEmpty(bulkCopyColumnMapping.DestinationColumn) && (bulkCopyColumnMapping.DestinationOrdinal == -1))) { throw SQL.BulkLoadNonMatchingColumnMapping(); } base.InnerList.Add(bulkCopyColumnMapping); return(bulkCopyColumnMapping); }
private static void InsertDataUsingSqlBulkCopy(IEnumerable<ContactInfo> people, SqlConnection connection) { var bulkCopy = new SqlBulkCopy(connection); bulkCopy.DestinationTableName = "Person"; // bulkCopy.ColumnMappings.Add("Name", "Name")lkCopy.ColumnMappings.Add("DateOfBirth", "DateOfBirth"); SqlBulkCopyColumnMapping mapping = new SqlBulkCopyColumnMapping(); using (var dataReader = new ObjectDataReader<ContactInfo>(people)) { bulkCopy.WriteToServer(dataReader); } }
public SqlBulkCopyColumnMapping Add(SqlBulkCopyColumnMapping bulkCopyColumnMapping) { AssertWriteAccess(); Debug.Assert(string.IsNullOrEmpty(bulkCopyColumnMapping.SourceColumn) || bulkCopyColumnMapping._internalSourceColumnOrdinal == -1, "BulkLoadAmbiguousSourceColumn"); if (((string.IsNullOrEmpty(bulkCopyColumnMapping.SourceColumn)) && (bulkCopyColumnMapping.SourceOrdinal == -1)) || ((string.IsNullOrEmpty(bulkCopyColumnMapping.DestinationColumn)) && (bulkCopyColumnMapping.DestinationOrdinal == -1))) { throw SQL.BulkLoadNonMatchingColumnMapping(); } InnerList.Add(bulkCopyColumnMapping); return(bulkCopyColumnMapping); }
public override void WriteToServer(string destinationTableName, DataTable table, int batchSize = 0, params BulkCopyColumnMapping[] columnMappings) { SqlBulkCopy bulkCopy = null; try { if (DbTransaction != null) { bulkCopy = new SqlBulkCopy((SqlConnection)DbTransaction.Connection, SqlBulkCopyOptions.Default, (SqlTransaction)DbTransaction); } else { bulkCopy = new SqlBulkCopy(_connectionString); } bulkCopy.DestinationTableName = destinationTableName; if (batchSize != 0) { bulkCopy.BatchSize = batchSize; } if (BulkCopyTimeout != (int)CommandTimeoutValue.None) { bulkCopy.BulkCopyTimeout = BulkCopyTimeout; } if (columnMappings != null) { foreach (var item in columnMappings) { var mapping = new SqlBulkCopyColumnMapping(); if (item.DestinationColumn != null) mapping.DestinationColumn = item.DestinationColumn; if (item.DestinationOrdinal != -1) mapping.DestinationOrdinal = item.DestinationOrdinal; if (item.SourceColumn != null) mapping.SourceColumn = item.SourceColumn; if (item.SourceOrdinal != -1) mapping.SourceOrdinal = item.SourceOrdinal; bulkCopy.ColumnMappings.Add(mapping); } } bulkCopy.WriteToServer(table); } finally { if (bulkCopy != null) { bulkCopy.Close(); } } }
static RemoteLogService() { try { schema.Columns.Add("MonitorAction", typeof(string)); schema.Columns.Add("MonitorTime", typeof(DateTime)); schema.Columns.Add("Host", typeof(string)); schema.Columns.Add("IP", typeof(string)); schema.Columns.Add("MAC", typeof(string)); schema.Columns.Add("AppPath", typeof(string)); schema.Columns.Add("AppName", typeof(string)); schema.Columns.Add("ProcessName", typeof(string)); schema.Columns.Add("FirstCag", typeof(string)); schema.Columns.Add("SecondCag", typeof(string)); schema.Columns.Add("ThirdCag", typeof(string)); schema.Columns.Add("MonitorValue", typeof(double)); schema.Columns.Add("Msg", typeof(string)); mappings[0] = new SqlBulkCopyColumnMapping("MonitorAction", "MonitorAction"); mappings[1] = new SqlBulkCopyColumnMapping("MonitorTime", "MonitorTime"); mappings[2] = new SqlBulkCopyColumnMapping("Host", "Host"); mappings[3] = new SqlBulkCopyColumnMapping("IP", "IP"); mappings[4] = new SqlBulkCopyColumnMapping("MAC", "MAC"); mappings[5] = new SqlBulkCopyColumnMapping("AppPath", "AppPath"); mappings[6] = new SqlBulkCopyColumnMapping("AppName", "AppName"); mappings[7] = new SqlBulkCopyColumnMapping("ProcessName", "ProcessName"); mappings[8] = new SqlBulkCopyColumnMapping("FirstCag", "FirstCag"); mappings[9] = new SqlBulkCopyColumnMapping("SecondCag", "SecondCag"); mappings[10] = new SqlBulkCopyColumnMapping("ThirdCag", "ThirdCag"); mappings[11] = new SqlBulkCopyColumnMapping("MonitorValue", "MonitorValue"); mappings[12] = new SqlBulkCopyColumnMapping("Msg", "Msg"); if (isDb) dac = new DAC(Config.LogConnectionName); schemaCache = schema.Clone(); } catch (Exception e) { Log.Error(e); } }
public void BulkInsertTo(DataTable schema, string tableName, IDataReader dataReader, string connectionString) { using (var bulkCopy = new SqlBulkCopy(connectionString)) { bulkCopy.BulkCopyTimeout = 9000000; bulkCopy.BatchSize = BatchSize; bulkCopy.DestinationTableName = string.Format("[{0}]", tableName); bulkCopy.EnableStreaming = true; for (int ordinal = 0; ordinal < schema.Columns.Count; ordinal++) { var mapping = new SqlBulkCopyColumnMapping { DestinationOrdinal = ordinal, SourceOrdinal = ordinal }; bulkCopy.ColumnMappings.Add(mapping); } bulkCopy.WriteToServer(dataReader); } }
public void WriteData(DataTable dt, string tableName) { SqlConnection conn = null; try { try { if (!this.IsInTransaction && !this.keepConnectionOpen) { conn = new SqlConnection(this.DBConnString); conn.Open(); } else { conn = this.sqlConn; if (conn.State != ConnectionState.Open) conn.Open(); } if (string.IsNullOrEmpty(tableName)) { if (string.IsNullOrEmpty(dt.TableName)) dt.TableName = "#tempTable"; this.CreateTmpTable(dt); tableName = dt.TableName; } SqlBulkCopy sbc; if (!this.IsInTransaction) { sbc = new SqlBulkCopy(conn); } else { sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, this.sqltrans); } sbc.DestinationTableName = tableName; // Set up the column mappings by name. foreach (DataColumn dcItem in dt.Columns) { SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping(dcItem.ColumnName, dcItem.ColumnName); sbc.ColumnMappings.Add(mapID); } sbc.WriteToServer(dt); sbc.Close(); } catch (Exception e) { if (this.needLog) Log.LogErr(e); throw; } } finally { if (!this.IsInTransaction && !this.KeepConnectionOpen) { conn.Close(); } } }
public virtual bool SqlBulkInsertDataTable(string targetTable, DataTable dt, SqlBulkCopyColumnMapping[] mappings) { long t = DateTime.Now.Ticks; try { bool n = dac.SqlBulkInsertDataTable(targetTable, dt, mappings); Log.Write(LogAction.Dac, className, "SqlBulkInsertDataTable2", _connString, targetTable, DateTime.Now.Ticks - t, "执行成功"); return n; } catch (Exception e) { Log.Write(LogAction.Error, className, "SqlBulkInsertDataTable2", _connString, targetTable, DateTime.Now.Ticks - t, "执行出错:targetTable:" + targetTable + ";" + e.Message); throw; } }
// Mapping of each Column while Exporting to database server private Boolean ExportBulkInsert(DataTable objBulkDT, String strExportType,String optionaldoBulkTransfer) { int intCount; if (objBulkDT.Rows.Count == 0) { MessageBox.Show("data is not available to Transter for the period of " + intMonth.ToString() + "/" + intYear.ToString() + " to Live Server ", "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } if (optionaldoBulkTransfer == "0") { DataColumn colMonth = new DataColumn("MONTH", typeof(System.Int16)); DataColumn colYear = new DataColumn("YEAR", typeof(System.Int16)); DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colMonth.DefaultValue = intMonth; colYear.DefaultValue = intYear; colType.DefaultValue = "HL"; objBulkDT.Columns.Add(colMonth); objBulkDT.Columns.Add(colYear); objBulkDT.Columns.Add(colType); return false; } try { if (objConLivedatabase.State == ConnectionState.Open) { objConLivedatabase.Close(); } objConLivedatabase.Open(); if (strExportType == "HL") { // 1. Instantiate a new command with a query and connection //cmd = new SqlCommand("SELECT YEAR , MONTH FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED WHERE COUNTRY = '" + StrCountry +"' AND MONTH = " + intMonth + " AND YEAR = " + intYear + " GROUP BY YEAR,MONTH", objConLivedatabase); cmd = new SqlCommand("SELECT YEAR , MONTH FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED WHERE COUNTRY = '" + StrCountry + "' AND MONTH = " + intMonth + " AND YEAR = " + intYear + " GROUP BY YEAR,MONTH", objConLivedatabase); } else if (strExportType == "HX") { cmd = new SqlCommand("SELECT HX_BOOKINGS = SUM(HX_BOOKINGS) FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED WHERE COUNTRY = '" + StrCountry + "' AND MONTH = " + intMonth + " AND YEAR = " + intYear + " GROUP BY YEAR,MONTH HAVING ISNULL(SUM(HX_BOOKINGS) ,0) !=0 ", objConLivedatabase); } else if (strExportType == "CODD") { cmd = new SqlCommand("SELECT CODD = SUM(CODD) FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED WHERE COUNTRY = '" + StrCountry + "' AND MONTH = " + intMonth + " AND YEAR = " + intYear + " GROUP BY YEAR,MONTH HAVING ISNULL(SUM(CODD),0) !=0 ", objConLivedatabase); } // 2. Call Execute reader to get query results SqlDataReader rdr = cmd.ExecuteReader(); if (rdr.HasRows == false) { if (objConLivedatabase.State == ConnectionState.Open) { objConLivedatabase.Close(); } objConLivedatabase.Open(); if (strExportType == "HL") { DataColumn colMonth = new DataColumn("MONTH", typeof(System.Int16)); DataColumn colYear = new DataColumn("YEAR", typeof(System.Int16)); DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colMonth.DefaultValue = intMonth; colYear.DefaultValue = intYear; colType.DefaultValue = "HL"; objBulkDT.Columns.Add(colMonth); objBulkDT.Columns.Add(colYear); objBulkDT.Columns.Add(colType); intCount = objBulkDT.Rows.Count; if (intCount > 1) { this.toolStripProgressBar1.Minimum = 0; this.toolStripProgressBar1.Maximum = intCount; this.toolStripProgressBar1.Value = 1; this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1; } SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("YEAR", "YEAR"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("MONTH", "MONTH"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LCODE", "LCODE"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("CHAIN_CODE", "CHAIN_CODE"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("CHAIN_NAME", "CHAIN_NAME"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("OFFICEID", "OFFICEID"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("COUNTRY", "COUNTRY"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("PRODUCTIVITY_CODD_PK_HX", "PRODUCTIVITY_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("INTL", "INTL"); SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("TTL_HL", "TTL_HL"); SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("HL_INTL", "HL_INTL"); SqlBulkCopyColumnMapping mapping12 = new SqlBulkCopyColumnMapping("S2_DOM_CODD_PK_HX", "S2_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping13 = new SqlBulkCopyColumnMapping("S2_HL_NETSTATUS", "S2_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping14 = new SqlBulkCopyColumnMapping("IC_DOM_CODD_PK_HX", "IC_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping15 = new SqlBulkCopyColumnMapping("IC_HL_NETSTATUS", "IC_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping16 = new SqlBulkCopyColumnMapping("9W_DOM_CODD_PK_HX", "9W_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping17 = new SqlBulkCopyColumnMapping("9W_HL_NETSTATUS", "9W_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping18 = new SqlBulkCopyColumnMapping("AI_DOM_CODD_PK_HX", "AI_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping19 = new SqlBulkCopyColumnMapping("AI_HL_NETSTATUS", "AI_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping20 = new SqlBulkCopyColumnMapping("IT_DOM_CODD_PK_HX", "IT_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping21 = new SqlBulkCopyColumnMapping("IT_HL_NETSTATUS", "IT_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping22 = new SqlBulkCopyColumnMapping("ITRED_DOM_CODD_PK_HX", "ITRED_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping23 = new SqlBulkCopyColumnMapping("ITRED_HL_NETSTATUS", "ITRED_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping24 = new SqlBulkCopyColumnMapping("I7_DOM_CODD_PK_HX", "I7_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping25 = new SqlBulkCopyColumnMapping("I7_HL_NETSTATUS", "I7_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping26 = new SqlBulkCopyColumnMapping("TOTALPK", "TOTALPK"); SqlBulkCopyColumnMapping mapping27 = new SqlBulkCopyColumnMapping("DOM_PK_IC", "DOM_PK_IC"); SqlBulkCopyColumnMapping mapping28 = new SqlBulkCopyColumnMapping("DOM_PK_IT", "DOM_PK_IT"); SqlBulkCopyColumnMapping mapping29 = new SqlBulkCopyColumnMapping("DOM_PK_AI", "DOM_PK_AI"); SqlBulkCopyColumnMapping mapping30 = new SqlBulkCopyColumnMapping("DOM_PK_9W", "DOM_PK_9W"); SqlBulkCopyColumnMapping mapping31 = new SqlBulkCopyColumnMapping("CODD", "CODD"); SqlBulkCopyColumnMapping mapping32 = new SqlBulkCopyColumnMapping("ROI", "ROI"); SqlBulkCopyColumnMapping mapping33 = new SqlBulkCopyColumnMapping("S2_HX", "S2_HX"); SqlBulkCopyColumnMapping mapping34 = new SqlBulkCopyColumnMapping("IC_HX", "IC_HX"); SqlBulkCopyColumnMapping mapping35 = new SqlBulkCopyColumnMapping("9W_HX", "9W_HX"); SqlBulkCopyColumnMapping mapping36 = new SqlBulkCopyColumnMapping("AI_HX", "AI_HX"); SqlBulkCopyColumnMapping mapping37 = new SqlBulkCopyColumnMapping("IT_HX", "IT_HX"); SqlBulkCopyColumnMapping mapping38 = new SqlBulkCopyColumnMapping("I7_HX", "I7_HX"); SqlBulkCopyColumnMapping mapping39 = new SqlBulkCopyColumnMapping("HX_BOOKINGS", "HX_BOOKINGS"); SqlBulkCopyColumnMapping mapping40 = new SqlBulkCopyColumnMapping("DOM_PK_S2", "DOM_PK_S2"); // newly added as on dated 09/03/2015 SqlBulkCopyColumnMapping mapping41 = new SqlBulkCopyColumnMapping("UK_DOM_CODD_PK_HX", "UK_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping42 = new SqlBulkCopyColumnMapping("UK_HL_NETSTATUS", "UK_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping43 = new SqlBulkCopyColumnMapping("DOM_PK_UK", "DOM_PK_UK"); SqlBulkCopyColumnMapping mapping44 = new SqlBulkCopyColumnMapping("UK_HX", "UK_HX"); SqlBulkCopyColumnMapping mapping45 = new SqlBulkCopyColumnMapping("UPLOAD_TYPE", "UPLOAD_TYPE"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.ColumnMappings.Add(mapping10); objSqlbulkCopy.ColumnMappings.Add(mapping11); objSqlbulkCopy.ColumnMappings.Add(mapping12); objSqlbulkCopy.ColumnMappings.Add(mapping13); objSqlbulkCopy.ColumnMappings.Add(mapping14); objSqlbulkCopy.ColumnMappings.Add(mapping15); objSqlbulkCopy.ColumnMappings.Add(mapping16); objSqlbulkCopy.ColumnMappings.Add(mapping17); objSqlbulkCopy.ColumnMappings.Add(mapping18); objSqlbulkCopy.ColumnMappings.Add(mapping19); objSqlbulkCopy.ColumnMappings.Add(mapping20); objSqlbulkCopy.ColumnMappings.Add(mapping21); objSqlbulkCopy.ColumnMappings.Add(mapping22); objSqlbulkCopy.ColumnMappings.Add(mapping23); objSqlbulkCopy.ColumnMappings.Add(mapping24); objSqlbulkCopy.ColumnMappings.Add(mapping25); objSqlbulkCopy.ColumnMappings.Add(mapping26); objSqlbulkCopy.ColumnMappings.Add(mapping27); objSqlbulkCopy.ColumnMappings.Add(mapping28); objSqlbulkCopy.ColumnMappings.Add(mapping29); objSqlbulkCopy.ColumnMappings.Add(mapping30); objSqlbulkCopy.ColumnMappings.Add(mapping31); objSqlbulkCopy.ColumnMappings.Add(mapping32); objSqlbulkCopy.ColumnMappings.Add(mapping33); objSqlbulkCopy.ColumnMappings.Add(mapping34); objSqlbulkCopy.ColumnMappings.Add(mapping35); objSqlbulkCopy.ColumnMappings.Add(mapping36); objSqlbulkCopy.ColumnMappings.Add(mapping37); objSqlbulkCopy.ColumnMappings.Add(mapping38); objSqlbulkCopy.ColumnMappings.Add(mapping39); objSqlbulkCopy.ColumnMappings.Add(mapping40); // newly added as on dated 09/03/2015 objSqlbulkCopy.ColumnMappings.Add(mapping41); objSqlbulkCopy.ColumnMappings.Add(mapping42); objSqlbulkCopy.ColumnMappings.Add(mapping43); objSqlbulkCopy.ColumnMappings.Add(mapping44); objSqlbulkCopy.ColumnMappings.Add(mapping45); //do enable the below lines for Live Working //objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { DisplayStatus("Transfering HL data..."); objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); this.toolStripProgressBar1.Value = intCount; objSqlTransaction = null; DisplayStatus("HL data Transfered..."); return true; } else if (strExportType == "HX") { DataColumn colMonth = new DataColumn("MONTH", typeof(System.Int16)); DataColumn colYear = new DataColumn("YEAR", typeof(System.Int16)); DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colMonth.DefaultValue = intMonth; colYear.DefaultValue = intYear; colType.DefaultValue = "HX"; objBulkDT.Columns.Add(colMonth); objBulkDT.Columns.Add(colYear); objBulkDT.Columns.Add(colType); intCount = objBulkDT.Rows.Count; toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = intCount; SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("YEAR", "YEAR"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("MONTH", "MONTH"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LCODE", "LCODE"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("CHAIN_CODE", "CHAIN_CODE"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("CHAIN_NAME", "CHAIN_NAME"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("OFFICEID", "OFFICEID"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("COUNTRY", "COUNTRY"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("HX_BOOKINGS", "HX_BOOKINGS"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("UPLOAD_TYPE", "UPLOAD_TYPE"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); objSqlTransaction = null; return true; } else if (strExportType == "CODD") { DataColumn colMonth = new DataColumn("MONTH", typeof(System.Int16)); DataColumn colYear = new DataColumn("YEAR", typeof(System.Int16)); DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colMonth.DefaultValue = intMonth; colYear.DefaultValue = intYear; colType.DefaultValue = "CODD"; objBulkDT.Columns.Add(colMonth); objBulkDT.Columns.Add(colYear); objBulkDT.Columns.Add(colType); intCount = objBulkDT.Rows.Count; toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = intCount; SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("[YEAR]", "[YEAR]"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("[MONTH]", "[MONTH]"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("[LCODE]", "[LCODE]"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("[CHAIN_CODE]", "[CHAIN_CODE]"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("[CHAIN_NAME]", "[CHAIN_NAME]"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("[OFFICEID]", "[OFFICEID]"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("[COUNTRY]", "[COUNTRY]"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("[CODD]", "[CODD]"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("[UPLOAD_TYPE]", "[UPLOAD_TYPE]"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); objSqlTransaction = null; return true; } } else { DataColumn colMonth = new DataColumn("MONTH", typeof(System.Int16)); DataColumn colYear = new DataColumn("YEAR", typeof(System.Int16)); colMonth.DefaultValue = intMonth; colYear.DefaultValue = intYear; objBulkDT.Columns.Add(colMonth); objBulkDT.Columns.Add(colYear); if (strExportType == "HL") { DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colType.DefaultValue = "HL"; objBulkDT.Columns.Add(colType); } else if (strExportType == "HX") { DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colType.DefaultValue = "HX"; objBulkDT.Columns.Add(colType); } else if (strExportType == "CODD") { DataColumn colType = new DataColumn("UPLOAD_TYPE", typeof(string)); colType.DefaultValue = "CODD"; objBulkDT.Columns.Add(colType); } intCount = objBulkDT.Rows.Count; toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = intCount; DialogResult dlgResult = MessageBox.Show("NIDT data for the month/year [" + intMonth.ToString() + "/" + intYear.ToString() + "] already exists in Live Server database."+ "\n\n" + "do you want to continue Upload data into Live Server ? ", "AAMS Admin", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (dlgResult == DialogResult.Yes) { Cursor.Current = Cursors.WaitCursor; if (objConLivedatabase.State == ConnectionState.Open) { objConLivedatabase.Close(); } objConLivedatabase.Open(); if (strExportType == "HL") { // INSERT LOG BEFORE DELETE FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED TABLE // 1. Instantiate a new command with a query and connection // HL DATA OF INDIA CONTAINS THREE COUNTRY DATA INCLUDING HL,HX,CODD 'India,Bhutan,Tba' //cmd1 = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED OUTPUT DELETED.[YEAR],DELETED.[MONTH],DELETED.[LCODE],DELETED.[CHAIN_CODE],DELETED.[CHAIN_NAME],DELETED.[OFFICEID],DELETED.[COUNTRY],DELETED.[PRODUCTIVITY_CODD_PK_HX],DELETED.[INTL],DELETED.[TTL_HL],DELETED.[HL_INTL],DELETED.[S2_DOM_CODD_PK_HX],DELETED.[S2_HL_NETSTATUS],DELETED.[IC_DOM_CODD_PK_HX],DELETED.[IC_HL_NETSTATUS],DELETED.[9W_DOM_CODD_PK_HX],DELETED.[9W_HL_NETSTATUS],DELETED.[AI_DOM_CODD_PK_HX],DELETED.[AI_HL_NETSTATUS],DELETED.[IT_DOM_CODD_PK_HX],DELETED.[IT_HL_NETSTATUS],DELETED.[ITRED_DOM_CODD_PK_HX],DELETED.[ITRED_HL_NETSTATUS],DELETED.[I7_DOM_CODD_PK_HX],DELETED.[I7_HL_NETSTATUS],DELETED.[TOTALPK],DELETED.[DOM_PK_IC],DELETED.[DOM_PK_IT],DELETED.[DOM_PK_AI],DELETED.[DOM_PK_9W],DELETED.[CODD],DELETED.[ROI],DELETED.[S2_HX],DELETED.[IC_HX],DELETED.[9W_HX],DELETED.[AI_HX],DELETED.[IT_HX],DELETED.[I7_HX],DELETED.[HX_BOOKINGS],DELETED.[DOM_PK_S2] INTO T_INC_NIDT_PRODUCTIVITY_EXPORTED_LOG ([YEAR],[MONTH],[LCODE],[CHAIN_CODE],[CHAIN_NAME],[OFFICEID],[COUNTRY],[PRODUCTIVITY_CODD_PK_HX],[INTL],[TTL_HL],[HL_INTL],[S2_DOM_CODD_PK_HX],[S2_HL_NETSTATUS],[IC_DOM_CODD_PK_HX],[IC_HL_NETSTATUS],[9W_DOM_CODD_PK_HX],[9W_HL_NETSTATUS],[AI_DOM_CODD_PK_HX],[AI_HL_NETSTATUS],[IT_DOM_CODD_PK_HX],[IT_HL_NETSTATUS],[ITRED_DOM_CODD_PK_HX],[ITRED_HL_NETSTATUS],[I7_DOM_CODD_PK_HX],[I7_HL_NETSTATUS],[TOTALPK],[DOM_PK_IC],[DOM_PK_IT],[DOM_PK_AI],[DOM_PK_9W],[CODD],[ROI],[S2_HX],[IC_HX],[9W_HX],[AI_HX],[IT_HX],[I7_HX],[HX_BOOKINGS],[DOM_PK_S2]) WHERE COUNTRY IN ('India','Bhutan','Tba') AND MONTH = " + intMonth + " AND YEAR = " + intYear, objConLivedatabase); cmd1 = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED OUTPUT DELETED.[YEAR],DELETED.[MONTH],DELETED.[LCODE],DELETED.[CHAIN_CODE],DELETED.[CHAIN_NAME],DELETED.[OFFICEID],DELETED.[COUNTRY],DELETED.[PRODUCTIVITY_CODD_PK_HX],DELETED.[INTL],DELETED.[TTL_HL],DELETED.[HL_INTL],DELETED.[S2_DOM_CODD_PK_HX],DELETED.[S2_HL_NETSTATUS],DELETED.[IC_DOM_CODD_PK_HX],DELETED.[IC_HL_NETSTATUS],DELETED.[9W_DOM_CODD_PK_HX],DELETED.[9W_HL_NETSTATUS],DELETED.[AI_DOM_CODD_PK_HX],DELETED.[AI_HL_NETSTATUS],DELETED.[IT_DOM_CODD_PK_HX],DELETED.[IT_HL_NETSTATUS],DELETED.[ITRED_DOM_CODD_PK_HX],DELETED.[ITRED_HL_NETSTATUS],DELETED.[I7_DOM_CODD_PK_HX],DELETED.[I7_HL_NETSTATUS],DELETED.[TOTALPK],DELETED.[DOM_PK_IC],DELETED.[DOM_PK_IT],DELETED.[DOM_PK_AI],DELETED.[DOM_PK_9W],DELETED.[CODD],DELETED.[ROI],DELETED.[S2_HX],DELETED.[IC_HX],DELETED.[9W_HX],DELETED.[AI_HX],DELETED.[IT_HX],DELETED.[I7_HX],DELETED.[HX_BOOKINGS],DELETED.[DOM_PK_S2],DELETED.[UK_DOM_CODD_PK_HX],DELETED.[UK_HL_NETSTATUS],DELETED.[DOM_PK_UK],DELETED.[UK_HX],DELETED.UPLOAD_TYPE ,'" + strIpaddress + "' INTO T_INC_NIDT_PRODUCTIVITY_EXPORTED_LOG ([YEAR],[MONTH],[LCODE],[CHAIN_CODE],[CHAIN_NAME],[OFFICEID],[COUNTRY],[PRODUCTIVITY_CODD_PK_HX],[INTL],[TTL_HL],[HL_INTL],[S2_DOM_CODD_PK_HX],[S2_HL_NETSTATUS],[IC_DOM_CODD_PK_HX],[IC_HL_NETSTATUS],[9W_DOM_CODD_PK_HX],[9W_HL_NETSTATUS],[AI_DOM_CODD_PK_HX],[AI_HL_NETSTATUS],[IT_DOM_CODD_PK_HX],[IT_HL_NETSTATUS],[ITRED_DOM_CODD_PK_HX],[ITRED_HL_NETSTATUS],[I7_DOM_CODD_PK_HX],[I7_HL_NETSTATUS],[TOTALPK],[DOM_PK_IC],[DOM_PK_IT],[DOM_PK_AI],[DOM_PK_9W],[CODD],[ROI],[S2_HX],[IC_HX],[9W_HX],[AI_HX],[IT_HX],[I7_HX],[HX_BOOKINGS],[DOM_PK_S2],[UK_DOM_CODD_PK_HX],[UK_HL_NETSTATUS],[DOM_PK_UK],[UK_HX],UPLOAD_TYPE,IPADDRESS) WHERE UPLOAD_TYPE = 'HL' AND MONTH = " + intMonth + " AND YEAR = " + intYear, objConLivedatabase); // 2. Call Execute query int intRowaffected = cmd1.ExecuteNonQuery(); //INSERT HL DATA AFTER DELETE QUERY intCount = objBulkDT.Rows.Count; if (intCount > 1) { this.toolStripProgressBar1.Minimum = 0; this.toolStripProgressBar1.Maximum = intCount; this.toolStripProgressBar1.Value = 1; this.toolStripProgressBar1.Value = this.toolStripProgressBar1.Value + 1; } SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("YEAR", "YEAR"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("MONTH", "MONTH"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LCODE", "LCODE"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("CHAIN_CODE", "CHAIN_CODE"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("CHAIN_NAME", "CHAIN_NAME"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("OFFICEID", "OFFICEID"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("COUNTRY", "COUNTRY"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("PRODUCTIVITY_CODD_PK_HX", "PRODUCTIVITY_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("INTL", "INTL"); SqlBulkCopyColumnMapping mapping10 = new SqlBulkCopyColumnMapping("TTL_HL", "TTL_HL"); SqlBulkCopyColumnMapping mapping11 = new SqlBulkCopyColumnMapping("HL_INTL", "HL_INTL"); SqlBulkCopyColumnMapping mapping12 = new SqlBulkCopyColumnMapping("S2_DOM_CODD_PK_HX", "S2_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping13 = new SqlBulkCopyColumnMapping("S2_HL_NETSTATUS", "S2_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping14 = new SqlBulkCopyColumnMapping("IC_DOM_CODD_PK_HX", "IC_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping15 = new SqlBulkCopyColumnMapping("IC_HL_NETSTATUS", "IC_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping16 = new SqlBulkCopyColumnMapping("9W_DOM_CODD_PK_HX", "9W_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping17 = new SqlBulkCopyColumnMapping("9W_HL_NETSTATUS", "9W_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping18 = new SqlBulkCopyColumnMapping("AI_DOM_CODD_PK_HX", "AI_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping19 = new SqlBulkCopyColumnMapping("AI_HL_NETSTATUS", "AI_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping20 = new SqlBulkCopyColumnMapping("IT_DOM_CODD_PK_HX", "IT_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping21 = new SqlBulkCopyColumnMapping("IT_HL_NETSTATUS", "IT_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping22 = new SqlBulkCopyColumnMapping("ITRED_DOM_CODD_PK_HX", "ITRED_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping23 = new SqlBulkCopyColumnMapping("ITRED_HL_NETSTATUS", "ITRED_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping24 = new SqlBulkCopyColumnMapping("I7_DOM_CODD_PK_HX", "I7_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping25 = new SqlBulkCopyColumnMapping("I7_HL_NETSTATUS", "I7_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping26 = new SqlBulkCopyColumnMapping("TOTALPK", "TOTALPK"); SqlBulkCopyColumnMapping mapping27 = new SqlBulkCopyColumnMapping("DOM_PK_IC", "DOM_PK_IC"); SqlBulkCopyColumnMapping mapping28 = new SqlBulkCopyColumnMapping("DOM_PK_IT", "DOM_PK_IT"); SqlBulkCopyColumnMapping mapping29 = new SqlBulkCopyColumnMapping("DOM_PK_AI", "DOM_PK_AI"); SqlBulkCopyColumnMapping mapping30 = new SqlBulkCopyColumnMapping("DOM_PK_9W", "DOM_PK_9W"); SqlBulkCopyColumnMapping mapping31 = new SqlBulkCopyColumnMapping("CODD", "CODD"); SqlBulkCopyColumnMapping mapping32 = new SqlBulkCopyColumnMapping("ROI", "ROI"); SqlBulkCopyColumnMapping mapping33 = new SqlBulkCopyColumnMapping("S2_HX", "S2_HX"); SqlBulkCopyColumnMapping mapping34 = new SqlBulkCopyColumnMapping("IC_HX", "IC_HX"); SqlBulkCopyColumnMapping mapping35 = new SqlBulkCopyColumnMapping("9W_HX", "9W_HX"); SqlBulkCopyColumnMapping mapping36 = new SqlBulkCopyColumnMapping("AI_HX", "AI_HX"); SqlBulkCopyColumnMapping mapping37 = new SqlBulkCopyColumnMapping("IT_HX", "IT_HX"); SqlBulkCopyColumnMapping mapping38 = new SqlBulkCopyColumnMapping("I7_HX", "I7_HX"); SqlBulkCopyColumnMapping mapping39 = new SqlBulkCopyColumnMapping("HX_BOOKINGS", "HX_BOOKINGS"); SqlBulkCopyColumnMapping mapping40 = new SqlBulkCopyColumnMapping("DOM_PK_S2", "DOM_PK_S2"); // newly added as on dated 09/03/2015 SqlBulkCopyColumnMapping mapping41 = new SqlBulkCopyColumnMapping("UK_DOM_CODD_PK_HX", "UK_DOM_CODD_PK_HX"); SqlBulkCopyColumnMapping mapping42 = new SqlBulkCopyColumnMapping("UK_HL_NETSTATUS", "UK_HL_NETSTATUS"); SqlBulkCopyColumnMapping mapping43 = new SqlBulkCopyColumnMapping("DOM_PK_UK", "DOM_PK_UK"); SqlBulkCopyColumnMapping mapping44 = new SqlBulkCopyColumnMapping("UK_HX", "UK_HX"); SqlBulkCopyColumnMapping mapping45 = new SqlBulkCopyColumnMapping("UPLOAD_TYPE", "UPLOAD_TYPE"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.ColumnMappings.Add(mapping10); objSqlbulkCopy.ColumnMappings.Add(mapping11); objSqlbulkCopy.ColumnMappings.Add(mapping12); objSqlbulkCopy.ColumnMappings.Add(mapping13); objSqlbulkCopy.ColumnMappings.Add(mapping14); objSqlbulkCopy.ColumnMappings.Add(mapping15); objSqlbulkCopy.ColumnMappings.Add(mapping16); objSqlbulkCopy.ColumnMappings.Add(mapping17); objSqlbulkCopy.ColumnMappings.Add(mapping18); objSqlbulkCopy.ColumnMappings.Add(mapping19); objSqlbulkCopy.ColumnMappings.Add(mapping20); objSqlbulkCopy.ColumnMappings.Add(mapping21); objSqlbulkCopy.ColumnMappings.Add(mapping22); objSqlbulkCopy.ColumnMappings.Add(mapping23); objSqlbulkCopy.ColumnMappings.Add(mapping24); objSqlbulkCopy.ColumnMappings.Add(mapping25); objSqlbulkCopy.ColumnMappings.Add(mapping26); objSqlbulkCopy.ColumnMappings.Add(mapping27); objSqlbulkCopy.ColumnMappings.Add(mapping28); objSqlbulkCopy.ColumnMappings.Add(mapping29); objSqlbulkCopy.ColumnMappings.Add(mapping30); objSqlbulkCopy.ColumnMappings.Add(mapping31); objSqlbulkCopy.ColumnMappings.Add(mapping32); objSqlbulkCopy.ColumnMappings.Add(mapping33); objSqlbulkCopy.ColumnMappings.Add(mapping34); objSqlbulkCopy.ColumnMappings.Add(mapping35); objSqlbulkCopy.ColumnMappings.Add(mapping36); objSqlbulkCopy.ColumnMappings.Add(mapping37); objSqlbulkCopy.ColumnMappings.Add(mapping38); objSqlbulkCopy.ColumnMappings.Add(mapping39); objSqlbulkCopy.ColumnMappings.Add(mapping40); // newly added as on dated 09/03/2015 objSqlbulkCopy.ColumnMappings.Add(mapping41); objSqlbulkCopy.ColumnMappings.Add(mapping42); objSqlbulkCopy.ColumnMappings.Add(mapping43); objSqlbulkCopy.ColumnMappings.Add(mapping44); objSqlbulkCopy.ColumnMappings.Add(mapping45); objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { DisplayStatus("Transfering HL data..."); objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); this.toolStripProgressBar1.Value = intCount; objSqlTransaction = null; DisplayStatus("HL data Transfered..."); MessageBox.Show("HL data for the period of " + intMonth.ToString() + "/" + intYear.ToString() + " successfully transfered to Live Server database ", "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Information); //END INSERT HL DATA } else if (strExportType == "HX") { cmd1 = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED OUTPUT DELETED.[YEAR],DELETED.[MONTH],DELETED.[LCODE],DELETED.[CHAIN_CODE],DELETED.[CHAIN_NAME],DELETED.[OFFICEID],DELETED.[COUNTRY],DELETED.[PRODUCTIVITY_CODD_PK_HX],DELETED.[INTL],DELETED.[TTL_HL],DELETED.[HL_INTL],DELETED.[S2_DOM_CODD_PK_HX],DELETED.[S2_HL_NETSTATUS],DELETED.[IC_DOM_CODD_PK_HX],DELETED.[IC_HL_NETSTATUS],DELETED.[9W_DOM_CODD_PK_HX],DELETED.[9W_HL_NETSTATUS],DELETED.[AI_DOM_CODD_PK_HX],DELETED.[AI_HL_NETSTATUS],DELETED.[IT_DOM_CODD_PK_HX],DELETED.[IT_HL_NETSTATUS],DELETED.[ITRED_DOM_CODD_PK_HX],DELETED.[ITRED_HL_NETSTATUS],DELETED.[I7_DOM_CODD_PK_HX],DELETED.[I7_HL_NETSTATUS],DELETED.[TOTALPK],DELETED.[DOM_PK_IC],DELETED.[DOM_PK_IT],DELETED.[DOM_PK_AI],DELETED.[DOM_PK_9W],DELETED.[CODD],DELETED.[ROI],DELETED.[S2_HX],DELETED.[IC_HX],DELETED.[9W_HX],DELETED.[AI_HX],DELETED.[IT_HX],DELETED.[I7_HX],DELETED.[HX_BOOKINGS],DELETED.[DOM_PK_S2],DELETED.[UK_DOM_CODD_PK_HX],DELETED.[UK_HL_NETSTATUS],DELETED.[DOM_PK_UK],DELETED.[UK_HX],DELETED.UPLOAD_TYPE ,'" + strIpaddress + "' INTO T_INC_NIDT_PRODUCTIVITY_EXPORTED_LOG ([YEAR],[MONTH],[LCODE],[CHAIN_CODE],[CHAIN_NAME],[OFFICEID],[COUNTRY],[PRODUCTIVITY_CODD_PK_HX],[INTL],[TTL_HL],[HL_INTL],[S2_DOM_CODD_PK_HX],[S2_HL_NETSTATUS],[IC_DOM_CODD_PK_HX],[IC_HL_NETSTATUS],[9W_DOM_CODD_PK_HX],[9W_HL_NETSTATUS],[AI_DOM_CODD_PK_HX],[AI_HL_NETSTATUS],[IT_DOM_CODD_PK_HX],[IT_HL_NETSTATUS],[ITRED_DOM_CODD_PK_HX],[ITRED_HL_NETSTATUS],[I7_DOM_CODD_PK_HX],[I7_HL_NETSTATUS],[TOTALPK],[DOM_PK_IC],[DOM_PK_IT],[DOM_PK_AI],[DOM_PK_9W],[CODD],[ROI],[S2_HX],[IC_HX],[9W_HX],[AI_HX],[IT_HX],[I7_HX],[HX_BOOKINGS],[DOM_PK_S2],[UK_DOM_CODD_PK_HX],[UK_HL_NETSTATUS],[DOM_PK_UK],[UK_HX],UPLOAD_TYPE,IPADDRESS) WHERE UPLOAD_TYPE = 'HX' AND COUNTRY = '" + StrCountry + "' AND MONTH = " + intMonth + " AND YEAR = " + intYear, objConLivedatabase); // 2. Call Execute query int intRowaffected = cmd1.ExecuteNonQuery(); //INSERT HX DATA AFTER DELETE QUUERY intCount = objBulkDT.Rows.Count; toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = intCount; SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("YEAR", "YEAR"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("MONTH", "MONTH"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("LCODE", "LCODE"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("CHAIN_CODE", "CHAIN_CODE"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("CHAIN_NAME", "CHAIN_NAME"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("OFFICEID", "OFFICEID"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("COUNTRY", "COUNTRY"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("HX_BOOKINGS", "HX_BOOKINGS"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("UPLOAD_TYPE", "UPLOAD_TYPE"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); objSqlTransaction = null; MessageBox.Show("HX data for the period of " + intMonth.ToString() + "/" + intYear.ToString() + " successfully transfered to Live Server database ", "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Information); //END INSERT HX DATA } else if (strExportType == "CODD") { cmd1 = new SqlCommand("DELETE FROM T_INC_NIDT_PRODUCTIVITY_EXPORTED OUTPUT DELETED.[YEAR],DELETED.[MONTH],DELETED.[LCODE],DELETED.[CHAIN_CODE],DELETED.[CHAIN_NAME],DELETED.[OFFICEID],DELETED.[COUNTRY],DELETED.[PRODUCTIVITY_CODD_PK_HX],DELETED.[INTL],DELETED.[TTL_HL],DELETED.[HL_INTL],DELETED.[S2_DOM_CODD_PK_HX],DELETED.[S2_HL_NETSTATUS],DELETED.[IC_DOM_CODD_PK_HX],DELETED.[IC_HL_NETSTATUS],DELETED.[9W_DOM_CODD_PK_HX],DELETED.[9W_HL_NETSTATUS],DELETED.[AI_DOM_CODD_PK_HX],DELETED.[AI_HL_NETSTATUS],DELETED.[IT_DOM_CODD_PK_HX],DELETED.[IT_HL_NETSTATUS],DELETED.[ITRED_DOM_CODD_PK_HX],DELETED.[ITRED_HL_NETSTATUS],DELETED.[I7_DOM_CODD_PK_HX],DELETED.[I7_HL_NETSTATUS],DELETED.[TOTALPK],DELETED.[DOM_PK_IC],DELETED.[DOM_PK_IT],DELETED.[DOM_PK_AI],DELETED.[DOM_PK_9W],DELETED.[CODD],DELETED.[ROI],DELETED.[S2_HX],DELETED.[IC_HX],DELETED.[9W_HX],DELETED.[AI_HX],DELETED.[IT_HX],DELETED.[I7_HX],DELETED.[HX_BOOKINGS],DELETED.[DOM_PK_S2],DELETED.[UK_DOM_CODD_PK_HX],DELETED.[UK_HL_NETSTATUS],DELETED.[DOM_PK_UK],DELETED.[UK_HX],DELETED.UPLOAD_TYPE ,'" + strIpaddress + "' INTO T_INC_NIDT_PRODUCTIVITY_EXPORTED_LOG ([YEAR],[MONTH],[LCODE],[CHAIN_CODE],[CHAIN_NAME],[OFFICEID],[COUNTRY],[PRODUCTIVITY_CODD_PK_HX],[INTL],[TTL_HL],[HL_INTL],[S2_DOM_CODD_PK_HX],[S2_HL_NETSTATUS],[IC_DOM_CODD_PK_HX],[IC_HL_NETSTATUS],[9W_DOM_CODD_PK_HX],[9W_HL_NETSTATUS],[AI_DOM_CODD_PK_HX],[AI_HL_NETSTATUS],[IT_DOM_CODD_PK_HX],[IT_HL_NETSTATUS],[ITRED_DOM_CODD_PK_HX],[ITRED_HL_NETSTATUS],[I7_DOM_CODD_PK_HX],[I7_HL_NETSTATUS],[TOTALPK],[DOM_PK_IC],[DOM_PK_IT],[DOM_PK_AI],[DOM_PK_9W],[CODD],[ROI],[S2_HX],[IC_HX],[9W_HX],[AI_HX],[IT_HX],[I7_HX],[HX_BOOKINGS],[DOM_PK_S2],[UK_DOM_CODD_PK_HX],[UK_HL_NETSTATUS],[DOM_PK_UK],[UK_HX],UPLOAD_TYPE,IPADDRESS) WHERE UPLOAD_TYPE = 'CODD' AND COUNTRY = '" + StrCountry + "' AND MONTH = " + intMonth + " AND YEAR = " + intYear, objConLivedatabase); // 2. Call Execute query int intRowaffected = cmd1.ExecuteNonQuery(); //INSERT CODD DATA AFTER DELETE QUERY intCount = objBulkDT.Rows.Count; toolStripProgressBar1.Minimum = 0; toolStripProgressBar1.Maximum = intCount; SqlBulkCopyColumnMapping mapping1 = new SqlBulkCopyColumnMapping("[YEAR]", "[YEAR]"); SqlBulkCopyColumnMapping mapping2 = new SqlBulkCopyColumnMapping("[MONTH]", "[MONTH]"); SqlBulkCopyColumnMapping mapping3 = new SqlBulkCopyColumnMapping("[LCODE]", "[LCODE]"); SqlBulkCopyColumnMapping mapping4 = new SqlBulkCopyColumnMapping("[CHAIN_CODE]", "[CHAIN_CODE]"); SqlBulkCopyColumnMapping mapping5 = new SqlBulkCopyColumnMapping("[CHAIN_NAME]", "[CHAIN_NAME]"); SqlBulkCopyColumnMapping mapping6 = new SqlBulkCopyColumnMapping("[OFFICEID]", "[OFFICEID]"); SqlBulkCopyColumnMapping mapping7 = new SqlBulkCopyColumnMapping("[COUNTRY]", "[COUNTRY]"); SqlBulkCopyColumnMapping mapping8 = new SqlBulkCopyColumnMapping("[CODD]", "[CODD]"); SqlBulkCopyColumnMapping mapping9 = new SqlBulkCopyColumnMapping("[UPLOAD_TYPE]", "[UPLOAD_TYPE]"); objSqlTransaction = objConLivedatabase.BeginTransaction(IsolationLevel.RepeatableRead); objSqlbulkCopy = new SqlBulkCopy(objConLivedatabase, SqlBulkCopyOptions.CheckConstraints, objSqlTransaction); objSqlbulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(objSqlbulkCopy_SqlRowsCopied); objSqlbulkCopy.ColumnMappings.Add(mapping1); objSqlbulkCopy.ColumnMappings.Add(mapping2); objSqlbulkCopy.ColumnMappings.Add(mapping3); objSqlbulkCopy.ColumnMappings.Add(mapping4); objSqlbulkCopy.ColumnMappings.Add(mapping5); objSqlbulkCopy.ColumnMappings.Add(mapping6); objSqlbulkCopy.ColumnMappings.Add(mapping7); objSqlbulkCopy.ColumnMappings.Add(mapping8); objSqlbulkCopy.ColumnMappings.Add(mapping9); objSqlbulkCopy.DestinationTableName = "T_INC_NIDT_PRODUCTIVITY_EXPORTED"; objSqlbulkCopy.BatchSize = 1000; objSqlbulkCopy.NotifyAfter = 5; //objSqlbulkCopy.WriteToServer(objBulkDT); DataTableReader reader = objBulkDT.CreateDataReader(); using (ValidatingDataReader validator = new ValidatingDataReader(reader, objConLivedatabase, objSqlbulkCopy, objSqlTransaction)) { objSqlbulkCopy.WriteToServer(validator); } objSqlTransaction.Commit(); objSqlTransaction = null; MessageBox.Show("CODD data for the period of " + intMonth.ToString() + "/" + intYear.ToString() + " successfully transfered to Live Server database ", "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Information); //END INSERT CODD DATA } } else if (dlgResult == DialogResult.No) { Cursor.Current = Cursors.Default; return false; } Cursor.Current = Cursors.Default; return true; } } catch (Exception e1) { MessageBox.Show("Problem in BulkInserting." + "\n\n" + "Contact to Admin and send them error message : " + e1.Message + " \n\n" + e1.StackTrace, "AAMS Admin", MessageBoxButtons.OK, MessageBoxIcon.Stop); if (objConLivedatabase.State != ConnectionState.Closed) { if (objSqlTransaction != null) { objSqlTransaction.Rollback(); MessageBox.Show(e1.Message); } objConLivedatabase.Close(); } return false; } finally { //myTable1 = null; //myTable2 = null; //myTable3 = null; //myTable4 = null; //myTable4_1 = null; //gblGrpds = null; if (objConLivedatabase.State == ConnectionState.Open) { objConLivedatabase.Close(); } } return true; }
public SqlBulkCopyColumnMapping Add(int sourceColumnIndex, int destinationColumnIndex) { AssertWriteAccess(); SqlBulkCopyColumnMapping column = new SqlBulkCopyColumnMapping (sourceColumnIndex, destinationColumnIndex); return Add(column); }
public bool Contains(SqlBulkCopyColumnMapping value) { return(List.Contains(value)); }
public bool SqlBulkInsertDataTable(string connString, string targetTable, DataTable dt, SqlBulkCopyColumnMapping[] mappings) { long t = DateTime.Now.Ticks; try { DAC dac = new DAC(connString); bool rtn = dac.SqlBulkInsertDataTable(targetTable, dt, mappings); Log.Write(LogAction.Svc, className, "SqlBulkInsertDataTabl2", connString, targetTable, DateTime.Now.Ticks - t, "执行成功:"); return rtn; } catch (Exception e) { Log.Write(LogAction.Error, className, "SqlBulkInsertDataTable2", connString, targetTable, DateTime.Now.Ticks - t, "执行出错targetTable:" + targetTable + ";" + e.Message); } return false; }
public static void SaveTracksToDatabase(Dictionary<Int64, SpotifyTrack> Tracks, WorkTableState oWorkTableState) { DataTable dtArtists = new DataTable(); dtArtists.Columns.Add(new DataColumn("TrackID", typeof(long))); dtArtists.Columns.Add(new DataColumn("Name", typeof(string))); dtArtists.Columns.Add(new DataColumn("SpotifyID", typeof(string))); foreach (KeyValuePair<long, SpotifyTrack> oKVP in Tracks) { DataRow drArtist = dtArtists.NewRow(); drArtist["TrackID"] = oKVP.Key; drArtist["Name"] = oKVP.Value.Name; drArtist["SpotifyID"] = oKVP.Value.SpotifyID; dtArtists.Rows.Add(drArtist); } SqlBulkCopyColumnMapping cmID = new SqlBulkCopyColumnMapping("TrackID", "TrackID"); SqlBulkCopyColumnMapping cmName = new SqlBulkCopyColumnMapping("Name", "Name"); SqlBulkCopyColumnMapping cmSpotifyID = new SqlBulkCopyColumnMapping("SpotifyID", "SpotifyID"); RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmID, cmName, cmSpotifyID); RelationalDatabase.ExecuteNonQuery("AddSpotifyTracks", CommandType.StoredProcedure); }
public static void SaveStatesToDatabase(List<SpotifyState> SpotifyStates, WorkTableState oWorkTableState) { DataTable dtArtists = new DataTable(); dtArtists.Columns.Add(new DataColumn("UserID", typeof(long))); dtArtists.Columns.Add(new DataColumn("PlaylistID", typeof(long))); dtArtists.Columns.Add(new DataColumn("TrackID", typeof(long))); dtArtists.Columns.Add(new DataColumn("Position", typeof(long))); dtArtists.Columns.Add(new DataColumn("ArtistID", typeof(long))); foreach (SpotifyState oSpotifyState in SpotifyStates) { DataRow drArtist = dtArtists.NewRow(); drArtist["UserID"] = oSpotifyState.UserID; drArtist["PlaylistID"] = oSpotifyState.PlaylistID; drArtist["TrackID"] = oSpotifyState.TrackID; drArtist["Position"] = oSpotifyState.Position; drArtist["ArtistID"] = oSpotifyState.ArtistID; dtArtists.Rows.Add(drArtist); } SqlBulkCopyColumnMapping cmUserID = new SqlBulkCopyColumnMapping("UserID", "UserID"); SqlBulkCopyColumnMapping cmPlaylistID = new SqlBulkCopyColumnMapping("PlaylistID", "PlaylistID"); SqlBulkCopyColumnMapping cmTrackID = new SqlBulkCopyColumnMapping("TrackID", "TrackID"); SqlBulkCopyColumnMapping cmPosition = new SqlBulkCopyColumnMapping("Position", "Position"); SqlBulkCopyColumnMapping cmArtistID = new SqlBulkCopyColumnMapping("ArtistID", "ArtistID"); RelationalDatabase.BulkInsert(dtArtists, oWorkTableState, cmUserID, cmPlaylistID, cmTrackID, cmPosition, cmArtistID); RelationalDatabase.ExecuteNonQuery("UpsertSpotifyState", CommandType.StoredProcedure); }
protected override void RunBulkCopy(Common.CommonDataLayer.ICdlReader reader) { var ts = reader.Structure; bool forceInserts = false; if (ts.Columns.Count == 1) { // SqlBulkCopy has problems when running on tables with one column forceInserts = true; } if (DestinationTable.Columns.Any(x => x.DataType?.ToLower()?.Contains("geo") ?? false)) { // SqlBulkCopy deosn't support spatial types forceInserts = true; } if (forceInserts) { RunInserts(reader); return; } var dialect = Factory.CreateDialect(); using (SqlBulkCopy bcp = new SqlBulkCopy((SqlConnection)Connection, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null)) { bcp.DestinationTableName = dialect.QuoteFullName(DestinationTable.FullName); foreach (var item in _columnMap.Items) { var map = new SqlBulkCopyColumnMapping(item.Source, item.Target); bcp.ColumnMappings.Add(map); } //var dst_ts = DestinationTable; //if (ts.Columns.Count < dst_ts.Columns.Count) //{ // int srcindex = 0; // foreach (var src in ts.Columns) // { // SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(srcindex, dst_ts.Columns.IndexOfIf(col => col.Name == src.Name)); // bcp.ColumnMappings.Add(map); // srcindex++; // } //} //int srcindex = 0; //foreach (var src in ts.Columns) //{ // int dstIndex = dst_ts.Columns.IndexOfIf(col => col.Name == src.Name); // if (dstIndex < 0) continue; // SqlBulkCopyColumnMapping map = new SqlBulkCopyColumnMapping(srcindex, dstIndex); // bcp.ColumnMappings.Add(map); // srcindex++; //} var readerAda = new CdlReaderAdapter(); readerAda.Reader = reader; try { bcp.BulkCopyTimeout = 0; bcp.WriteToServer(readerAda); LogInfo(String.Format("{0} rows inserted into table {1}", readerAda.ReadedRows, DestinationTable.FullName)); //ProgressInfo.LogMessage("INSERT", LogLevel.Info, Texts.Get("s_inserted_into_table$table$rows", "table", DestinationTable.FullName, "rows", readerAda.ReadedRows)); } catch (Exception err) { LogError($"DBSH-00200 Error inserting into table {DestinationTable.FullName}:{err.Message}"); _log.Error($"DBSH-00201 Error inserting into table {DestinationTable.FullName}", err); //ILogger logger = ProgressInfo; //if (err is QueueClosedError) logger = Logging.Root; //logger.LogMessageDetail( // "INSERT", LogLevel.Error, // String.Format("{0}", Texts.Get("s_error_inserting_into_table$table", "table", DestinationTable.FullName)), err.ToString()); //throw; } finally { readerAda.Close(); } } }
public void CopyTo(SqlBulkCopyColumnMapping[] array, int index) { InnerList.CopyTo(array, index); }
public int IndexOf(SqlBulkCopyColumnMapping value) { return InnerList.IndexOf(value); }
public void Insert(int index, SqlBulkCopyColumnMapping value) { AssertWriteAccess(); InnerList.Insert(index, value); }
public void Remove(SqlBulkCopyColumnMapping value) { AssertWriteAccess(); InnerList.Remove(value); }
public bool Contains(SqlBulkCopyColumnMapping value) { return(-1 != InnerList.IndexOf(value)); }
/// <summary> /// Static method which we be called from Program.Main. /// This method will extract the data from the PASNGR DB and copy it to /// the StagingDB using BulkCopy. /// </summary> /// <returns> /// bool - success or failure, true for errors, false for no errors /// </returns> public static bool ExtractData(bool testing = false) { string customer = ConfigurationManager.AppSettings["Customer"]; String conn = ""; if (testing == true) { conn = ConfigurationManager.ConnectionStrings["sqlConnString" + customer + "TEST"].ConnectionString; } else { conn = ConfigurationManager.ConnectionStrings["sqlConnString" + customer].ConnectionString; } string sp = "GetData"; using (SqlConnection con = new SqlConnection(conn)) { Log.Info("Starting Extract of Data"); SqlCommand cmd = new SqlCommand(sp, con); cmd.CommandTimeout = 10000; DateTime enddate; try { if (testing == true) { if (ConfigurationManager.AppSettings["RetrieveEndDateTEST"] != "") { enddate = Convert.ToDateTime(ConfigurationManager.AppSettings["RetrieveEndDateTEST"]); } else { enddate = System.DateTime.Now; } } else if (ConfigurationManager.AppSettings["RetrieveEndDate"] != "") { enddate = Convert.ToDateTime(ConfigurationManager.AppSettings["RetrieveEndDate"]); } else { enddate = System.DateTime.Now; } } catch (System.FormatException) { Log.ErrorFormat("ERROR: Invalid DateTime set in Config for RetrieveEndDate, will used current datetime"); enddate = System.DateTime.Now; } //parameters for the stored procedure int recipientID = 0; if (testing == true) { recipientID = GetLastRecipientID(true); } else { recipientID = GetLastRecipientID(); } SqlParameter recipientIDp = new SqlParameter("@recipientID", recipientID); SqlParameter enddatep = new SqlParameter("@enddate", enddate); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add(recipientIDp); cmd.Parameters.Add(enddatep); con.Open(); Log.Info("Executing Stored Procedure: " + sp); SqlDataReader rdr = cmd.ExecuteReader(); Log.Info("Finished Executing Stored Procedure: " + sp); // Initializing an SqlBulkCopy object string sdbConnStr = ""; if (testing == true) { sdbConnStr = ConfigurationManager.ConnectionStrings["sqlConnStringSDBTEST"].ConnectionString; } else { sdbConnStr = ConfigurationManager.ConnectionStrings["sqlConnStringSDB"].ConnectionString; } SqlBulkCopy sbc = new SqlBulkCopy(sdbConnStr); sbc.BulkCopyTimeout = 10000; #region Column Mappings // Copying data to destination sbc.DestinationTableName = "StagingTable"; //fact table data SqlBulkCopyColumnMapping mapID1 = new SqlBulkCopyColumnMapping("recipientid", "recipientid"); sbc.ColumnMappings.Add(mapID1); SqlBulkCopyColumnMapping mapID2 = new SqlBulkCopyColumnMapping("extractID", "extractid"); sbc.ColumnMappings.Add(mapID2); SqlBulkCopyColumnMapping mapID3 = new SqlBulkCopyColumnMapping("data_sourceid", "datasourceid"); sbc.ColumnMappings.Add(mapID3); SqlBulkCopyColumnMapping mapID4 = new SqlBulkCopyColumnMapping("notificationid", "notificationid"); sbc.ColumnMappings.Add(mapID4); SqlBulkCopyColumnMapping mapID5 = new SqlBulkCopyColumnMapping("paxID", "paxid"); sbc.ColumnMappings.Add(mapID5); SqlBulkCopyColumnMapping mapID6 = new SqlBulkCopyColumnMapping("flight_legid", "flightid"); sbc.ColumnMappings.Add(mapID6); SqlBulkCopyColumnMapping mapID7 = new SqlBulkCopyColumnMapping("templateID", "templateid"); sbc.ColumnMappings.Add(mapID7); //recipient data SqlBulkCopyColumnMapping mapID79 = new SqlBulkCopyColumnMapping("createDate", "NotificationSendDate"); sbc.ColumnMappings.Add(mapID79); SqlBulkCopyColumnMapping mapID9 = new SqlBulkCopyColumnMapping("notificationSentDate", "RecipientSentDateTime"); sbc.ColumnMappings.Add(mapID9); SqlBulkCopyColumnMapping mapID11 = new SqlBulkCopyColumnMapping("notificationSent", "notificationsent"); sbc.ColumnMappings.Add(mapID11); SqlBulkCopyColumnMapping mapID12 = new SqlBulkCopyColumnMapping("notificationSentTo", "notificationsentto"); sbc.ColumnMappings.Add(mapID12); SqlBulkCopyColumnMapping mapID13 = new SqlBulkCopyColumnMapping("notificationSentType", "notificationSentType"); sbc.ColumnMappings.Add(mapID13); SqlBulkCopyColumnMapping mapID14 = new SqlBulkCopyColumnMapping("sendTo", "sendto"); sbc.ColumnMappings.Add(mapID14); SqlBulkCopyColumnMapping mapID16 = new SqlBulkCopyColumnMapping("PasngrRenderDate", "PasngrRenderDate"); sbc.ColumnMappings.Add(mapID16); //extracts data SqlBulkCopyColumnMapping mapID17 = new SqlBulkCopyColumnMapping("pnr", "pnr"); sbc.ColumnMappings.Add(mapID17); SqlBulkCopyColumnMapping mapID18 = new SqlBulkCopyColumnMapping("booking_date", "booking_date"); sbc.ColumnMappings.Add(mapID18); SqlBulkCopyColumnMapping mapID19 = new SqlBulkCopyColumnMapping("language_code", "language_code"); sbc.ColumnMappings.Add(mapID19); SqlBulkCopyColumnMapping mapID20 = new SqlBulkCopyColumnMapping("address_name", "address_name"); sbc.ColumnMappings.Add(mapID20); SqlBulkCopyColumnMapping mapID21 = new SqlBulkCopyColumnMapping("address1", "address1"); sbc.ColumnMappings.Add(mapID21); SqlBulkCopyColumnMapping mapID22 = new SqlBulkCopyColumnMapping("address2", "address2"); sbc.ColumnMappings.Add(mapID22); SqlBulkCopyColumnMapping mapID23 = new SqlBulkCopyColumnMapping("address3", "address3"); sbc.ColumnMappings.Add(mapID23); SqlBulkCopyColumnMapping mapID24 = new SqlBulkCopyColumnMapping("city", "city"); sbc.ColumnMappings.Add(mapID24); SqlBulkCopyColumnMapping mapID25 = new SqlBulkCopyColumnMapping("postcode", "postcode"); sbc.ColumnMappings.Add(mapID25); SqlBulkCopyColumnMapping mapID26 = new SqlBulkCopyColumnMapping("country", "country"); sbc.ColumnMappings.Add(mapID26); SqlBulkCopyColumnMapping mapID27 = new SqlBulkCopyColumnMapping("email_address", "emailaddress"); sbc.ColumnMappings.Add(mapID27); SqlBulkCopyColumnMapping mapID28 = new SqlBulkCopyColumnMapping("home_phone", "homephone"); sbc.ColumnMappings.Add(mapID28); SqlBulkCopyColumnMapping mapID29 = new SqlBulkCopyColumnMapping("other_phone", "otherphone"); sbc.ColumnMappings.Add(mapID29); SqlBulkCopyColumnMapping mapID30 = new SqlBulkCopyColumnMapping("total_passengers", "total_passengers"); sbc.ColumnMappings.Add(mapID30); SqlBulkCopyColumnMapping mapID31 = new SqlBulkCopyColumnMapping("Date_Added", "dateadded"); sbc.ColumnMappings.Add(mapID31); SqlBulkCopyColumnMapping mapID33 = new SqlBulkCopyColumnMapping("PasngrCreateDate", "PasngrCreateDate"); sbc.ColumnMappings.Add(mapID33); //notifications data SqlBulkCopyColumnMapping mapID34 = new SqlBulkCopyColumnMapping("notification_desc", "notification_desc"); sbc.ColumnMappings.Add(mapID34); SqlBulkCopyColumnMapping mapID35 = new SqlBulkCopyColumnMapping("language_type", "language_type"); sbc.ColumnMappings.Add(mapID35); SqlBulkCopyColumnMapping mapID36 = new SqlBulkCopyColumnMapping("notificationSendOption", "notificationSendOption"); sbc.ColumnMappings.Add(mapID36); SqlBulkCopyColumnMapping mapID37 = new SqlBulkCopyColumnMapping("createDate", "createDate"); sbc.ColumnMappings.Add(mapID37); SqlBulkCopyColumnMapping mapID38 = new SqlBulkCopyColumnMapping("sentDate", "sentDate"); sbc.ColumnMappings.Add(mapID38); //flight data SqlBulkCopyColumnMapping mapID39 = new SqlBulkCopyColumnMapping("flight_leg", "flight_leg"); sbc.ColumnMappings.Add(mapID39); SqlBulkCopyColumnMapping mapID40 = new SqlBulkCopyColumnMapping("flight_number", "flight_number"); sbc.ColumnMappings.Add(mapID40); SqlBulkCopyColumnMapping mapID41 = new SqlBulkCopyColumnMapping("flight_code", "flight_code"); sbc.ColumnMappings.Add(mapID41); SqlBulkCopyColumnMapping mapID78 = new SqlBulkCopyColumnMapping("dept_date", "dept_date"); sbc.ColumnMappings.Add(mapID78); SqlBulkCopyColumnMapping mapID45 = new SqlBulkCopyColumnMapping("dept_time", "dept_time"); sbc.ColumnMappings.Add(mapID45); SqlBulkCopyColumnMapping mapID46 = new SqlBulkCopyColumnMapping("dept_city", "dept_city"); sbc.ColumnMappings.Add(mapID46); SqlBulkCopyColumnMapping mapID47 = new SqlBulkCopyColumnMapping("dept_country", "dept_country"); sbc.ColumnMappings.Add(mapID47); SqlBulkCopyColumnMapping mapID48 = new SqlBulkCopyColumnMapping("arrv_date", "arrv_date"); sbc.ColumnMappings.Add(mapID48); SqlBulkCopyColumnMapping mapID52 = new SqlBulkCopyColumnMapping("arrv_time", "arrv_time"); sbc.ColumnMappings.Add(mapID52); SqlBulkCopyColumnMapping mapID53 = new SqlBulkCopyColumnMapping("arrv_city", "arrv_city"); sbc.ColumnMappings.Add(mapID53); SqlBulkCopyColumnMapping mapID54 = new SqlBulkCopyColumnMapping("arrv_country", "arrv_country"); sbc.ColumnMappings.Add(mapID54); SqlBulkCopyColumnMapping mapID55 = new SqlBulkCopyColumnMapping("schedule_change", "schedulechange"); sbc.ColumnMappings.Add(mapID55); SqlBulkCopyColumnMapping mapID56 = new SqlBulkCopyColumnMapping("ServiceClass", "ServiceClass"); sbc.ColumnMappings.Add(mapID56); SqlBulkCopyColumnMapping mapID57 = new SqlBulkCopyColumnMapping("deptTerminal", "deptterminal"); sbc.ColumnMappings.Add(mapID57); SqlBulkCopyColumnMapping mapID58 = new SqlBulkCopyColumnMapping("CheckInTime", "CheckInTime"); sbc.ColumnMappings.Add(mapID58); SqlBulkCopyColumnMapping mapID59 = new SqlBulkCopyColumnMapping("AircraftType", "aircrafttype"); sbc.ColumnMappings.Add(mapID59); SqlBulkCopyColumnMapping mapID60 = new SqlBulkCopyColumnMapping("FlightFacilities", "FlightFacilities"); sbc.ColumnMappings.Add(mapID60); SqlBulkCopyColumnMapping mapID61 = new SqlBulkCopyColumnMapping("duration", "duration"); sbc.ColumnMappings.Add(mapID61); SqlBulkCopyColumnMapping mapID62 = new SqlBulkCopyColumnMapping("arrival_terminal", "arrival_terminal"); sbc.ColumnMappings.Add(mapID62); SqlBulkCopyColumnMapping mapID63 = new SqlBulkCopyColumnMapping("flightStatus", "flightstatus"); sbc.ColumnMappings.Add(mapID63); SqlBulkCopyColumnMapping mapID64 = new SqlBulkCopyColumnMapping("number_of_stops", "numberofstops"); sbc.ColumnMappings.Add(mapID64); //pax data SqlBulkCopyColumnMapping mapID65 = new SqlBulkCopyColumnMapping("pax_first", "pax_first"); sbc.ColumnMappings.Add(mapID65); SqlBulkCopyColumnMapping mapID66 = new SqlBulkCopyColumnMapping("pax_last", "pax_last"); sbc.ColumnMappings.Add(mapID66); SqlBulkCopyColumnMapping mapID67 = new SqlBulkCopyColumnMapping("pax_middle", "pax_middle"); sbc.ColumnMappings.Add(mapID67); SqlBulkCopyColumnMapping mapID68 = new SqlBulkCopyColumnMapping("pax_title", "pax_title"); sbc.ColumnMappings.Add(mapID68); SqlBulkCopyColumnMapping mapID69 = new SqlBulkCopyColumnMapping("PaxGender", "PaxGender"); sbc.ColumnMappings.Add(mapID69); SqlBulkCopyColumnMapping mapID70 = new SqlBulkCopyColumnMapping("PaxType", "PaxType"); sbc.ColumnMappings.Add(mapID70); SqlBulkCopyColumnMapping mapID71 = new SqlBulkCopyColumnMapping("pax_dob", "pax_dob"); sbc.ColumnMappings.Add(mapID71); //templates data SqlBulkCopyColumnMapping mapID72 = new SqlBulkCopyColumnMapping("template_desc", "template_desc"); sbc.ColumnMappings.Add(mapID72); SqlBulkCopyColumnMapping mapID73 = new SqlBulkCopyColumnMapping("template_style", "template_style"); sbc.ColumnMappings.Add(mapID73); //data sources data SqlBulkCopyColumnMapping mapID74 = new SqlBulkCopyColumnMapping("data_source_name", "data_source_name"); sbc.ColumnMappings.Add(mapID74); SqlBulkCopyColumnMapping mapID75 = new SqlBulkCopyColumnMapping("data_source_type", "data_source_type"); sbc.ColumnMappings.Add(mapID75); SqlBulkCopyColumnMapping mapID76 = new SqlBulkCopyColumnMapping("upload_complete", "upload_complete"); sbc.ColumnMappings.Add(mapID76); SqlBulkCopyColumnMapping mapID77 = new SqlBulkCopyColumnMapping("createDate", "dscreateDate"); sbc.ColumnMappings.Add(mapID77); #endregion try { Log.Info("Writing to Destination"); sbc.WriteToServer(rdr); } catch (Exception Ex) { Log.Error("ERROR: error occured during writing data to StagingDB"); Log.Error(Ex); //return error return true; } sbc.Close(); rdr.Close(); con.Close(); Log.Info("Finished Extracting of Data"); } return false; }
public async Task<HttpResponseMessage> PostEnrollment() { string body = await Request.Content.ReadAsStringAsync(); List<Enrollment> enrollments = JsonConvert.DeserializeObject<List<Enrollment>>(body); DataTable table = new DataTable(); using(var reader = ObjectReader.Create(enrollments)) { table.Load(reader); } table.Columns.Remove("Student"); table.Columns.Remove("Session"); table.Columns.Remove("EnrollmentId"); string cnString = ConfigurationManager.ConnectionStrings["ArtDayConnection"].ConnectionString; using (SqlConnection destinationConnection = new SqlConnection(cnString)) { destinationConnection.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.DestinationTableName = "Enrollment"; SqlBulkCopyColumnMapping studentMap = new SqlBulkCopyColumnMapping("studentId", "StudentID"); bulkCopy.ColumnMappings.Add(studentMap); SqlBulkCopyColumnMapping sessionMap = new SqlBulkCopyColumnMapping("sessionId", "SessionID"); bulkCopy.ColumnMappings.Add(sessionMap); SqlBulkCopyColumnMapping periodMap = new SqlBulkCopyColumnMapping("period", "Period"); bulkCopy.ColumnMappings.Add(periodMap); try { bulkCopy.WriteToServer(table); } catch (Exception ex) { Debug.Write(ex.Message); } } } // this is very slow: // db.Enrollments.AddRange(enrollments); // await db.SaveChangesAsync(); // save changes once. Hopefully this makes a single db call. return this.Request.CreateResponse(HttpStatusCode.Created); }
public bool Contains(SqlBulkCopyColumnMapping value) { return (-1 != InnerList.IndexOf(value)); }
public int IndexOf(SqlBulkCopyColumnMapping value) { return(List.IndexOf(value)); }
public int IndexOf(SqlBulkCopyColumnMapping value) => InnerList.IndexOf(value);
public void Remove(SqlBulkCopyColumnMapping value) { List.Remove(value); }
public bool SqlBulkInsertDataTable(string targetTable, DataTable dt, SqlBulkCopyColumnMapping[] mappings) { using (SqlBulkCopy sbc = new SqlBulkCopy(_connectionString)) { sbc.DestinationTableName = targetTable; foreach (SqlBulkCopyColumnMapping map in mappings) sbc.ColumnMappings.Add(map); sbc.WriteToServer(dt); return true; } }
public void Remove(SqlBulkCopyColumnMapping value) { AssertWriteAccess(); InnerList.Remove(value); }
public static void ExtractData() { String conn = ConfigurationManager.ConnectionStrings["sqlConnStringS"].ConnectionString; string sp = "GetData"; using (SqlConnection con = new SqlConnection(conn)) { Log.Info("Starting Extract of Data"); Log.Info("Executing Stored Procedure: " + sp); SqlCommand cmd = new SqlCommand(sp, con); cmd.CommandTimeout = 10000; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); Log.Info("Finished Executing Stored Procedure: " + sp); // Initializing an SqlBulkCopy object SqlBulkCopy sbc = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["sqlConnStringD"].ConnectionString); sbc.BulkCopyTimeout = 10000; #region Column Mappings // Copying data to destination sbc.DestinationTableName = "StagingTable"; SqlBulkCopyColumnMapping mapID1 = new SqlBulkCopyColumnMapping("recipientid", "recipientid"); sbc.ColumnMappings.Add(mapID1); SqlBulkCopyColumnMapping mapID2 = new SqlBulkCopyColumnMapping("extractID", "extractid"); sbc.ColumnMappings.Add(mapID2); SqlBulkCopyColumnMapping mapID3 = new SqlBulkCopyColumnMapping("data_sourceid", "datasourceid"); sbc.ColumnMappings.Add(mapID3); SqlBulkCopyColumnMapping mapID4 = new SqlBulkCopyColumnMapping("notificationid", "notificationid"); sbc.ColumnMappings.Add(mapID4); SqlBulkCopyColumnMapping mapID5 = new SqlBulkCopyColumnMapping("paxid", "paxid"); sbc.ColumnMappings.Add(mapID5); SqlBulkCopyColumnMapping mapID6 = new SqlBulkCopyColumnMapping("flight_legid", "flightid"); sbc.ColumnMappings.Add(mapID6); SqlBulkCopyColumnMapping mapID7 = new SqlBulkCopyColumnMapping("templateID", "templateid"); sbc.ColumnMappings.Add(mapID7); SqlBulkCopyColumnMapping mapID79 = new SqlBulkCopyColumnMapping("createDate", "NotificationSendDate"); sbc.ColumnMappings.Add(mapID79); SqlBulkCopyColumnMapping mapID9 = new SqlBulkCopyColumnMapping("notificationSentDate", "RecipientSentDateTime"); sbc.ColumnMappings.Add(mapID9); SqlBulkCopyColumnMapping mapID11 = new SqlBulkCopyColumnMapping("notificationSent", "notificationsent"); sbc.ColumnMappings.Add(mapID11); SqlBulkCopyColumnMapping mapID12 = new SqlBulkCopyColumnMapping("notificationSentTo", "notificationsentto"); sbc.ColumnMappings.Add(mapID12); SqlBulkCopyColumnMapping mapID13 = new SqlBulkCopyColumnMapping("notificationSentType", "notificationSentType"); sbc.ColumnMappings.Add(mapID13); SqlBulkCopyColumnMapping mapID14 = new SqlBulkCopyColumnMapping("sendTo", "sendto"); sbc.ColumnMappings.Add(mapID14); SqlBulkCopyColumnMapping mapID16 = new SqlBulkCopyColumnMapping("PasngrRenderDate", "PasngrRenderDate"); sbc.ColumnMappings.Add(mapID16); SqlBulkCopyColumnMapping mapID17 = new SqlBulkCopyColumnMapping("pnr", "pnr"); sbc.ColumnMappings.Add(mapID17); SqlBulkCopyColumnMapping mapID18 = new SqlBulkCopyColumnMapping("booking_date", "booking_date"); sbc.ColumnMappings.Add(mapID18); SqlBulkCopyColumnMapping mapID19 = new SqlBulkCopyColumnMapping("language_code", "language_code"); sbc.ColumnMappings.Add(mapID19); SqlBulkCopyColumnMapping mapID20 = new SqlBulkCopyColumnMapping("address_name", "address_name"); sbc.ColumnMappings.Add(mapID20); SqlBulkCopyColumnMapping mapID21 = new SqlBulkCopyColumnMapping("address1", "address1"); sbc.ColumnMappings.Add(mapID21); SqlBulkCopyColumnMapping mapID22 = new SqlBulkCopyColumnMapping("address2", "address2"); sbc.ColumnMappings.Add(mapID22); SqlBulkCopyColumnMapping mapID23 = new SqlBulkCopyColumnMapping("address3", "address3"); sbc.ColumnMappings.Add(mapID23); SqlBulkCopyColumnMapping mapID24 = new SqlBulkCopyColumnMapping("city", "city"); sbc.ColumnMappings.Add(mapID24); SqlBulkCopyColumnMapping mapID25 = new SqlBulkCopyColumnMapping("postcode", "postcode"); sbc.ColumnMappings.Add(mapID25); SqlBulkCopyColumnMapping mapID26 = new SqlBulkCopyColumnMapping("country", "country"); sbc.ColumnMappings.Add(mapID26); SqlBulkCopyColumnMapping mapID27 = new SqlBulkCopyColumnMapping("email_address", "emailaddress"); sbc.ColumnMappings.Add(mapID27); SqlBulkCopyColumnMapping mapID28 = new SqlBulkCopyColumnMapping("home_phone", "homephone"); sbc.ColumnMappings.Add(mapID28); SqlBulkCopyColumnMapping mapID29 = new SqlBulkCopyColumnMapping("other_phone", "otherphone"); sbc.ColumnMappings.Add(mapID29); SqlBulkCopyColumnMapping mapID30 = new SqlBulkCopyColumnMapping("total_passengers", "total_passengers"); sbc.ColumnMappings.Add(mapID30); SqlBulkCopyColumnMapping mapID31 = new SqlBulkCopyColumnMapping("Date_Added", "dateadded"); sbc.ColumnMappings.Add(mapID31); SqlBulkCopyColumnMapping mapID33 = new SqlBulkCopyColumnMapping("PasngrCreateDate", "PasngrCreateDate"); sbc.ColumnMappings.Add(mapID33); SqlBulkCopyColumnMapping mapID34 = new SqlBulkCopyColumnMapping("notification_desc", "notification_desc"); sbc.ColumnMappings.Add(mapID34); SqlBulkCopyColumnMapping mapID35 = new SqlBulkCopyColumnMapping("language_type", "language_type"); sbc.ColumnMappings.Add(mapID35); SqlBulkCopyColumnMapping mapID36 = new SqlBulkCopyColumnMapping("notificationSendOption", "notificationSendOption"); sbc.ColumnMappings.Add(mapID36); SqlBulkCopyColumnMapping mapID37 = new SqlBulkCopyColumnMapping("createDate", "createDate"); sbc.ColumnMappings.Add(mapID37); SqlBulkCopyColumnMapping mapID38 = new SqlBulkCopyColumnMapping("sentDate", "sentDate"); sbc.ColumnMappings.Add(mapID38); SqlBulkCopyColumnMapping mapID39 = new SqlBulkCopyColumnMapping("flight_leg", "flight_leg"); //sbc.ColumnMappings.Add(mapID39); SqlBulkCopyColumnMapping mapID40 = new SqlBulkCopyColumnMapping("flight_number", "flight_number"); //sbc.ColumnMappings.Add(mapID40); SqlBulkCopyColumnMapping mapID41 = new SqlBulkCopyColumnMapping("flight_code", "flight_code"); //sbc.ColumnMappings.Add(mapID41); SqlBulkCopyColumnMapping mapID78 = new SqlBulkCopyColumnMapping("dept_date", "dept_date"); //sbc.ColumnMappings.Add(mapID78); SqlBulkCopyColumnMapping mapID42 = new SqlBulkCopyColumnMapping("dept_day", "dept_day"); //sbc.ColumnMappings.Add(mapID42); SqlBulkCopyColumnMapping mapID43 = new SqlBulkCopyColumnMapping("dept_month", "dept_month"); //sbc.ColumnMappings.Add(mapID43); SqlBulkCopyColumnMapping mapID44 = new SqlBulkCopyColumnMapping("dept_year", "dept_year"); //sbc.ColumnMappings.Add(mapID44); SqlBulkCopyColumnMapping mapID45 = new SqlBulkCopyColumnMapping("dept_time", "dept_time"); //sbc.ColumnMappings.Add(mapID45); SqlBulkCopyColumnMapping mapID46 = new SqlBulkCopyColumnMapping("dept_city", "dept_city"); //sbc.ColumnMappings.Add(mapID46); SqlBulkCopyColumnMapping mapID47 = new SqlBulkCopyColumnMapping("dept_country", "dept_country"); //sbc.ColumnMappings.Add(mapID47); SqlBulkCopyColumnMapping mapID48 = new SqlBulkCopyColumnMapping("arrv_date", "arrv_date"); //sbc.ColumnMappings.Add(mapID48); SqlBulkCopyColumnMapping mapID49 = new SqlBulkCopyColumnMapping("arrv_day", "arrv_day"); //sbc.ColumnMappings.Add(mapID49); SqlBulkCopyColumnMapping mapID50 = new SqlBulkCopyColumnMapping("arrv_month", "arrv_month"); //sbc.ColumnMappings.Add(mapID50); SqlBulkCopyColumnMapping mapID51 = new SqlBulkCopyColumnMapping("arrv_year", "arrv_year"); //sbc.ColumnMappings.Add(mapID51); SqlBulkCopyColumnMapping mapID52 = new SqlBulkCopyColumnMapping("arrv_time", "arrv_time"); //sbc.ColumnMappings.Add(mapID52); SqlBulkCopyColumnMapping mapID53 = new SqlBulkCopyColumnMapping("arrv_city", "arrv_city"); //sbc.ColumnMappings.Add(mapID53); SqlBulkCopyColumnMapping mapID54 = new SqlBulkCopyColumnMapping("arrv_country", "arrv_country"); //sbc.ColumnMappings.Add(mapID54); SqlBulkCopyColumnMapping mapID55 = new SqlBulkCopyColumnMapping("schedule_change", "schedulechange"); //sbc.ColumnMappings.Add(mapID55); SqlBulkCopyColumnMapping mapID56 = new SqlBulkCopyColumnMapping("ServiceClass", "ServiceClass"); //sbc.ColumnMappings.Add(mapID56); SqlBulkCopyColumnMapping mapID57 = new SqlBulkCopyColumnMapping("deptTerminal", "deptterminal"); //sbc.ColumnMappings.Add(mapID57); SqlBulkCopyColumnMapping mapID58 = new SqlBulkCopyColumnMapping("CheckInTime", "CheckInTime"); //sbc.ColumnMappings.Add(mapID58); SqlBulkCopyColumnMapping mapID59 = new SqlBulkCopyColumnMapping("AircraftType", "aircrafttype"); //sbc.ColumnMappings.Add(mapID59); SqlBulkCopyColumnMapping mapID60 = new SqlBulkCopyColumnMapping("FlightFacilities", "FlightFacilities"); //sbc.ColumnMappings.Add(mapID60); SqlBulkCopyColumnMapping mapID61 = new SqlBulkCopyColumnMapping("duration", "duration"); //sbc.ColumnMappings.Add(mapID61); SqlBulkCopyColumnMapping mapID62 = new SqlBulkCopyColumnMapping("arrival_terminal", "arrival_terminal"); //sbc.ColumnMappings.Add(mapID62); SqlBulkCopyColumnMapping mapID63 = new SqlBulkCopyColumnMapping("flightStatus", "flightstatus"); //sbc.ColumnMappings.Add(mapID63); SqlBulkCopyColumnMapping mapID64 = new SqlBulkCopyColumnMapping("number_of_stops", "numberofstops"); //sbc.ColumnMappings.Add(mapID64); SqlBulkCopyColumnMapping mapID65 = new SqlBulkCopyColumnMapping("pax_first", "pax_first"); //sbc.ColumnMappings.Add(mapID65); SqlBulkCopyColumnMapping mapID66 = new SqlBulkCopyColumnMapping("pax_last", "pax_last"); //sbc.ColumnMappings.Add(mapID66); SqlBulkCopyColumnMapping mapID67 = new SqlBulkCopyColumnMapping("pax_middle", "pax_middle"); //sbc.ColumnMappings.Add(mapID67); SqlBulkCopyColumnMapping mapID68 = new SqlBulkCopyColumnMapping("pax_title", "pax_title"); //sbc.ColumnMappings.Add(mapID68); SqlBulkCopyColumnMapping mapID69 = new SqlBulkCopyColumnMapping("PaxGender", "PaxGender"); //sbc.ColumnMappings.Add(mapID69); SqlBulkCopyColumnMapping mapID70 = new SqlBulkCopyColumnMapping("PaxType", "PaxType"); //sbc.ColumnMappings.Add(mapID70); SqlBulkCopyColumnMapping mapID71 = new SqlBulkCopyColumnMapping("pax_dob", "pax_dob"); //sbc.ColumnMappings.Add(mapID71); SqlBulkCopyColumnMapping mapID72 = new SqlBulkCopyColumnMapping("template_desc", "template_desc"); sbc.ColumnMappings.Add(mapID72); SqlBulkCopyColumnMapping mapID73 = new SqlBulkCopyColumnMapping("template_style", "template_style"); sbc.ColumnMappings.Add(mapID73); SqlBulkCopyColumnMapping mapID74 = new SqlBulkCopyColumnMapping("data_source_name", "data_source_name"); sbc.ColumnMappings.Add(mapID74); SqlBulkCopyColumnMapping mapID75 = new SqlBulkCopyColumnMapping("data_source_type", "data_source_type"); sbc.ColumnMappings.Add(mapID75); SqlBulkCopyColumnMapping mapID76 = new SqlBulkCopyColumnMapping("upload_complete", "upload_complete"); sbc.ColumnMappings.Add(mapID76); SqlBulkCopyColumnMapping mapID77 = new SqlBulkCopyColumnMapping("createDate", "dscreateDate"); sbc.ColumnMappings.Add(mapID77); #endregion try { Log.Info("Writing to Destination"); sbc.WriteToServer(rdr); } catch (Exception Ex) { Log.Error(Ex); } sbc.Close(); rdr.Close(); con.Close(); Log.Info("Finished Extracting of Data"); } }
public bool Contains(SqlBulkCopyColumnMapping value) => InnerList.Contains(value);
/// <summary> /// Emit a batch of log events, running asynchronously. /// </summary> /// <param name="events">The events to emit.</param> /// <remarks> /// Override either <see cref="PeriodicBatchingSink.EmitBatch" /> or <see cref="PeriodicBatchingSink.EmitBatchAsync" /> /// , /// not both. /// </remarks> protected override async Task EmitBatchAsync(IEnumerable<LogEvent> events) { // Copy the events to the data table FillDataTable(events); try { using (var cn = new SqlConnection(_connectionString)) { await cn.OpenAsync(_token.Token).ConfigureAwait(false); using (var copy = new SqlBulkCopy(cn)) { copy.DestinationTableName = _tableName; foreach (var column in _eventsTable.Columns) { var columnName = ((DataColumn)column).ColumnName; var mapping = new SqlBulkCopyColumnMapping(columnName, columnName); copy.ColumnMappings.Add(mapping); } await copy.WriteToServerAsync(_eventsTable, _token.Token).ConfigureAwait(false); } } } catch (Exception ex) { SelfLog.WriteLine("Unable to write {0} log events to the database due to following error: {1}", events.Count(), ex.Message); } finally { // Processed the items, clear for the next run _eventsTable.Clear(); } }
public void Insert(int index, SqlBulkCopyColumnMapping value) { AssertWriteAccess(); InnerList.Insert(index, value); }
public SqlBulkCopyColumnMapping Add(string sourceColumn, string destinationColumn) { AssertWriteAccess(); SqlBulkCopyColumnMapping column = new SqlBulkCopyColumnMapping (sourceColumn, destinationColumn); return Add(column); }