/// <summary> /// Creates the SQL table. /// </summary> /// <param name="connectionString">The connection string.</param> /// <param name="table">The table to create.</param> /// <returns></returns> private bool CreateSQLTable(string connectionString, DataTable table) { string tblName = System.Configuration.ConfigurationManager.AppSettings.Get("SapStagingTablePrefix") + _table.TableName; SqlUtilities sqlUtil = new SqlUtilities(); bool tblExist = sqlUtil.IsTableExists(tblName, connectionString); if (!tblExist) { Logger.Instance.Log.Trace("Table '"+tblName+"' does not exist @ " + DateTime.Now.ToString()); lock (table) { Logger.Instance.Log.Trace("Table locked. @ " + DateTime.Now.ToString()); if (!_tableCreated) { Logger.Instance.Log.Trace("Preparing to Create table '" + tblName + "' @ " + DateTime.Now.ToString()); if(table.Rows != null) Logger.Instance.Log.Trace("Rows not null @ " + DateTime.Now.ToString()); else Logger.Instance.Log.Trace("Rows are null @ " + DateTime.Now.ToString()); Logger.Instance.Log.Trace("Row count = " + table.Rows.Count + " @ " + DateTime.Now.ToString()); if (table.Rows != null && table.Rows.Count > 0) { Logger.Instance.Log.Trace("Table rows not null and rows > 1 @ " + DateTime.Now.ToString()); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlTable stc = new SqlTable(); stc.Connection = connection; stc.DestinationTableName = tblName; Logger.Instance.Log.Trace("Creating Table '" + tblName + "' @ " + DateTime.Now.ToString()); int result = stc.CreateFromDataTable(table, true); Logger.Instance.Log.Trace("Result " + result + " @ " + DateTime.Now.ToString()); if (result == -1) { _tableCreated = true; } connection.Close(); } } } } } Logger.Instance.Log.Trace("Table created " + _tableCreated + " @ " + DateTime.Now.ToString()); return _tableCreated; }
/// <summary> /// A check is performed to ensure that the table to be written to does not exist already. /// If the table already exists, a BULK SQL insert is performed. /// If the table does not exist, it is first created, with the relevant columns as required. A BULK /// SQL insert is then performed. /// </summary> public void write() { Logger.Instance.Log.Trace(" Write method called @ " + DateTime.Now.ToString()); using (SqlBulkCopy bulkCopy = new SqlBulkCopy (_connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { bulkCopy.BatchSize = BatchSize; bulkCopy.DestinationTableName = System.Configuration.ConfigurationManager.AppSettings.Get("SapStagingTablePrefix") + _table.TableName; if (CreateSQLTable(_connectionString, _table)) { #region CreateSQLTable Logger.Instance.Log.Trace("CreateSQLTable method True @ " + DateTime.Now.ToString()); lock (_table.Columns.SyncRoot) { foreach (DataColumn column in _table.Columns) { bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString()); } } lock (_table.Rows.SyncRoot) { bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); bulkCopy.NotifyAfter = _notifyAfter; try { bulkCopy.WriteToServer(_table, DataRowState.Added); _table.AcceptChanges(); } catch (Exception ex) { _table.RejectChanges(); throw ex; } } #endregion CreateSQLTable } else { #region Bulk Copy Logger.Instance.Log.Trace("CreateSQLTable method False @ " + DateTime.Now.ToString()); Logger.Instance.Log.Trace("Bulk Copy @ " + DateTime.Now.ToString()); #region Perform the deletion of ContractLines // =========================================================== // There exists a scenario, in which certain Contract Lines within an order will be // deleted. This has to be catered for by first checking if ContractLines exist // for an incoming Contract. If so, remove these contract lines first, then add // the new Contract Lines. string performClear = System.Configuration.ConfigurationManager.AppSettings.Get("DeleteTemporyTableDataFirst"); if (_table.TableName.Equals("CONTRACTLINE")) { if (performClear.Equals("true")) { try { SqlUtilities sqlUtil = new SqlUtilities(); if (sqlUtil.SelectiveTruncation(_connectionString, ToListDataTable(_table))) Logger.Instance.Log.Trace("Successfully Truncated. @ " + DateTime.Now.ToString()); else Logger.Instance.Log.Trace("Could not Truncate @ " + DateTime.Now.ToString()); } catch (Exception ex) { Logger.Instance.Log.Trace("Could not Truncate Contract Lines @ " + DateTime.Now.ToString()); Logger.Instance.Log.Trace("The following exception occurred: " + ex.Message + " @ " + DateTime.Now.ToString()); } } } // =========================================================== #endregion Perform the deletion of ContractLines lock (_table.Columns.SyncRoot) { foreach (DataColumn column in _table.Columns) { Logger.Instance.Log.Trace("Column: " + column.ColumnName + " @ " + DateTime.Now.ToString()); bulkCopy.ColumnMappings.Add(column.ToString(), column.ToString()); } } lock (_table.Rows.SyncRoot) { bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); bulkCopy.NotifyAfter = _notifyAfter; try { bulkCopy.WriteToServer(_table, DataRowState.Added); _table.AcceptChanges(); Logger.Instance.Log.Trace("Accepted changes. @ " + DateTime.Now.ToString()); } catch (Exception ex) { Logger.Instance.Log.Trace("Exception: " + ex.Message + " @ " + DateTime.Now.ToString()); _table.RejectChanges(); // Suppress the SQL Exception. // This is for the following error: // "Violation of PRIMARY KEY constraint 'PK_SAP_Staging_PRODUCTMASTER'. Cannot insert duplicate key in object 'dbo.SAP_Staging_PRODUCTMASTER' // Is ProductMasterId field still needed? Now that the CODE_MATR identifies publications for MediaProducts. //throw ex; } } #endregion Bulk Copy } } GC.Collect(); }