/// <summary>
/// Insert multiple records into the database transaction.
/// </summary>
/// <param name="estimateList"></param>
private void InsertMultipleRecords(IEnumerable <EstimateTransaction> transactionList)
{
//Create a datatable from the list.
var estimateRecords = ConvertToDataTable(transactionList);
//insert in DB
using (var connection = ConnectionStringService.GetConnection("Estimate"))
{
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
{
try
{
bulkCopy.DestinationTableName = "EstimateMain";
bulkCopy.WriteToServer(estimateRecords);
transaction.Commit();
connection.Close();
}
catch (Exception)
{
transaction.Rollback();
connection.Close();
}
}
}
}
}
public CostCodeDataService(string jobNumber, BudgetDataProvider dataProvider)
{
_jobNumber = jobNumber;
_dataProvider = dataProvider;
try
{
//Attempt to populate the connection strings.
estimateDatabaseString = ConnectionStringService.GetConnectionString("Estimate");
spectrumDatabaseString = ConnectionStringService.GetConnectionString("Spectrum");
//Initialize the external data cache service
externalDataCache = new ExternalCacheService(_jobNumber, _dataProvider);
}
catch (Exception e)
{
MessageBox.Show("There was a problem loading the file 'Estimating.ProgressReporter.CostCodeDataService' " + e.Message);
throw;
}
}
/// <summary>
/// Commits the SystemEstimate objects contained in the provided list to the database.
/// </summary>
/// <param name="systemEstimateList"></param>
public void Commit(List <SystemEstimate> systemEstimateList)
{
//Convert the systemEstimateLIst into EstimateTransaction form, ready for database commit.
List <EstimateTransaction> transactionData = ConvertToEstimateTransaction(systemEstimateList);
if (transactionData != null && transactionData.Count != 0)
{
try
{
//Insert a new header entry into the database.
SQLControl sql = new SQLControl(ConnectionStringService.GetConnectionString("Estimate"));
sql.AddParam("@jobNumber", _jobNumber);
sql.AddOutputParam("@estimateID", SqlDbType.Int, 4);
int nextEstimateID = (int)sql.GetReturnValueFromStoredProcedure("spInsertEstimateHeader", "@estimateID");
//If the new estimate header was successfully added, continue with the process.
if (nextEstimateID > 0)
{
//Assign the estimate ID to the transaction objects.
//int newEstimateID = 12;
foreach (EstimateTransaction t in transactionData)
{
t.EstimateID = nextEstimateID;
}
//Send the transaction list to be inserted in the database.
InsertMultipleRecords(transactionData);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message.ToString());
throw;
}
}
else
{
throw new Exception("Estimate commit aborted because transaction data list failed to populate correctly.");
}
}