/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus DoBulkInsert(BulkInsertViewModel bulkInsertViewModel)
{
// Initialize
questStatus status = null;
// Build bulk insert request
BulkInsertRequest bulkInsertRequest = new BulkInsertRequest();
BufferMgr.TransferBuffer(bulkInsertViewModel, bulkInsertRequest, true);
bulkInsertRequest.TablesetId = bulkInsertViewModel.TablesetId;
foreach (BulkInsertRowDataViewModel RowData in bulkInsertViewModel.Rows)
{
BulkInsertRow bulkInsertRow = new BulkInsertRow();
foreach (NameValueViewModel nameValueViewModel in RowData.Columns)
{
BulkInsertColumnValue bulkInsertColumnValue = new BulkInsertColumnValue();
bulkInsertColumnValue.Name = nameValueViewModel.ColumnName;
if ((string.IsNullOrWhiteSpace(nameValueViewModel.ColumnValue)) && (nameValueViewModel.ColumnValue != null))
{
bulkInsertColumnValue.Value = nameValueViewModel.ColumnValue.Trim();
}
else
{
bulkInsertColumnValue.Value = nameValueViewModel.ColumnValue;
}
bulkInsertRow.Columns.Add(bulkInsertColumnValue);
}
bulkInsertRequest.Rows.Add(bulkInsertRow);
}
// Perform bulk insert.
BulkInsertMgr bulkInsertMgr = new BulkInsertMgr(this.UserSession);
status = bulkInsertMgr.PerformBulkInsert(bulkInsertRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus ValidateRequest(BulkInsertRequest bulkInsertRequest, out BulkInsertResponse bulkInsertResponse)
{
// Initialize
questStatus status = null;
bulkInsertResponse = new BulkInsertResponse();
bulkInsertResponse.BulkInsertRequest = bulkInsertRequest;
// Get the filter.
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
Filter filter = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
// Validate the request.
foreach (BulkInsertRow bulkInsertRow in bulkInsertRequest.Rows)
{
BulkInsertRow invalidRow = null;
for (int rdx = 0; rdx < bulkInsertRow.Columns.Count; rdx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[rdx];
string entityName = null;
string columnName = null;
if (filter.FilterTableList.Count == 1)
{
entityName = filter.FilterTableList[0].TablesetTable.Table.Name;
columnName = bulkInsertColumnValue.Name;
}
else
{
string[] pp = bulkInsertColumnValue.Name.Split('_');
entityName = pp[0];
columnName = pp[1];
}
FilterColumn filterColumn = null;
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(t.TablesetTable.Table.Name == entityName); });
if (filterTable != null)
{
filterColumn = filter.FilterColumnList.Find(delegate(FilterColumn c) {
return(c.TablesetColumn.Column.Name == columnName && c.TablesetColumn.Column.EntityTypeId == EntityType.Table && c.TablesetColumn.Column.EntityId == filterTable.TablesetTable.Table.Id);
});
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter column not found for bulk insert column {0}, row # {1}",
bulkInsertColumnValue.Name, (rdx + 1))));
}
}
else
{
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(v.TablesetView.View.Name == entityName); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter view not found for bulk insert column {0}, row # {1}",
bulkInsertColumnValue.Name, (rdx + 1))));
}
filterColumn = filter.FilterColumnList.Find(delegate(FilterColumn c) {
return(c.TablesetColumn.Column.Name == columnName && c.TablesetColumn.Column.EntityTypeId == EntityType.View && c.TablesetColumn.Column.EntityId == filterView.TablesetView.View.Id);
});
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter column not found for bulk insert column {0}, row # {1} (view search)",
bulkInsertColumnValue.Name, (rdx + 1))));
}
}
// Validate the column
status = ValidateBulkInsertColumn(bulkInsertColumnValue, filterColumn);
if (!questStatusDef.IsSuccess(status))
{
if (invalidRow == null)
{
invalidRow = new BulkInsertRow();
}
invalidRow.Columns.Add(bulkInsertColumnValue);
}
}
if (invalidRow != null)
{
bulkInsertResponse.ValidationErrors.Add(invalidRow);
}
}
// TODO: IF ANY INVALID FIELDS, RETURN AN ERROR.
if (bulkInsertResponse.ValidationErrors.Count > 0)
{
return(new questStatus(Severity.Error));
}
return(new questStatus(Severity.Success));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* SQL-based
*---------------------------------------------------------------------------------------------------------------------------------*/
public questStatus GenerateBulkInsertSQL(BulkInsertRequest bulkInsertRequest)
{
// Initialize
questStatus status = null;
Filter filter = null;
// If filter not included, get it.
if (bulkInsertRequest.Filter == null)
{
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
}
filter = bulkInsertRequest.Filter;
// TEMPORARY: IF MORE THAN ONE TABLE, NOT SUPPORTED
int numEntities = 0;
status = getNumEntities(bulkInsertRequest, out numEntities);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (numEntities != 1)
{
return(new questStatus(Severity.Error, "Single-table inserts only are supported via SQL generation. Use INSERT sproc."));
}
StringBuilder sbINSERTRows = new StringBuilder();
for (int rdx = 0; rdx < bulkInsertRequest.Rows.Count; rdx += 1)
{
BulkInsertRow bulkInsertRow = bulkInsertRequest.Rows[rdx];
StringBuilder sbINSERTSql = new StringBuilder("INSERT INTO ");
sbINSERTSql.AppendLine(String.Format(" [{0}].[{1}] ", filter.FilterTableList[0].TablesetTable.Table.Schema, filter.FilterTableList[0].TablesetTable.Table.Name));
// Build column list
sbINSERTSql.Append(" ( ");
for (int cidx = 0; cidx < bulkInsertRow.Columns.Count; cidx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[cidx];
status = getFilterColumn(bulkInsertColumnValue, filter);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(String.Format("ERROR: getting FilterColumn on row {0}: {1}", (rdx + 1), status.Message)));
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsIdentity)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsAutoIncrement)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsReadOnly)
{
continue;
}
// Append column to INSERT clause
if (filter.FilterTableList.Count == 1)
{
sbINSERTSql.AppendLine(String.Format(" [{0}]", bulkInsertColumnValue.Name));
}
else
{
string[] pp = bulkInsertColumnValue.Name.Split('_');
sbINSERTSql.AppendLine(String.Format(" [{0}]", pp[1]));
}
// Comma between columns
if (cidx + 1 < bulkInsertRow.Columns.Count)
{
sbINSERTSql.Append(", ");
}
else
{
sbINSERTSql.Append(" ");
}
}
sbINSERTSql.Append(" ) ");
// Build value list
sbINSERTSql.Append(" VALUES ( ");
for (int cidx = 0; cidx < bulkInsertRow.Columns.Count; cidx += 1)
{
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns[cidx];
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsIdentity)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsAutoIncrement)
{
continue;
}
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.bIsReadOnly)
{
continue;
}
// Append to VALUES clause
if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.DataTypeName.Contains("varchar"))
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(" '" + bulkInsertColumnValue.Value + "' ");
}
}
else if (bulkInsertColumnValue.FilterColumn.TablesetColumn.Column.DataTypeName.Contains("date"))
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(" '" + bulkInsertColumnValue.Value + "' ");
}
}
else
{
if (bulkInsertColumnValue.Value == null)
{
sbINSERTSql.Append(" NULL ");
}
else
{
sbINSERTSql.Append(bulkInsertColumnValue.Value);
}
}
// Comma between values
if (cidx + 1 < bulkInsertRow.Columns.Count)
{
sbINSERTSql.Append(", ");
}
else
{
sbINSERTSql.Append(" ");
}
}
sbINSERTSql.Append(" ) ");
// Append to job SQL.
sbINSERTRows.AppendLine(sbINSERTSql.ToString());
sbINSERTRows.AppendLine();
}
bulkInsertRequest.SQL = sbINSERTRows.ToString();
return(new questStatus(Severity.Success));
}
