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