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