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