/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus PerformBulkInsert(BulkInsertRequest bulkInsertRequest)
{
// Initialize
questStatus status = null;
// Read Filter
FilterId filterId = new FilterId(bulkInsertRequest.FilterId);
Filter filter = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
status = filtersMgr.Read(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertRequest.Filter = filter;
// Determine if bulk insert filter procedure exists.
FilterProcedure filterProcedure = null;
status = _dbBulkInsertMgr.GetFilterProcedure(bulkInsertRequest, "Insert", out filterProcedure);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Perform bulk insert filter procedure if exists.
if (questStatusDef.IsSuccess(status))
{
return(PerformBulkInsertFilterProcedure(bulkInsertRequest, filterProcedure));
}
// Validate bulk insert request
BulkInsertResponse bulkInsertResponse = null;
status = ValidateRequest(bulkInsertRequest, out bulkInsertResponse);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Generate the SQL
status = _dbBulkInsertMgr.GenerateBulkInsertSQL(bulkInsertRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Perform bulk insert.
status = _dbBulkInsertMgr.PerformBulkInsert(bulkInsertRequest);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
protected async Task <List <uint> > BulkInsertSQL(string query, List <string> values)
{
List <uint> InsertedIds = new List <uint>();
BulkInsertResponse bulkInsertResponse = new BulkInsertResponse();
StringBuilder SQLquery = new StringBuilder(query);
using (MySqlConnection mConnection = new MySqlConnection(this.ConnectionString))
{
SQLquery.Append(string.Join(",", values));
SQLquery.Append("ON DUPLICATE KEY UPDATE Value = VALUES(Value), ModifiedDate = utc_timestamp() ; COMMIT; ");
//Create a data reader and Execute the command
//MySqlDataReader dataReader = myCmd.ExecuteReader();
mConnection.Open();
using (MySqlCommand cmd = mConnection.CreateCommand())
{
cmd.CommandText = SQLquery.ToString();
MySqlDataReader dataReader = null;
try
{
dataReader = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
mConnection.Dispose();
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
}
}
if (bulkInsertResponse != null)
{
for (int i = 0; i < bulkInsertResponse.RowCount; i++)
{
InsertedIds.Add(bulkInsertResponse.FirstId);
bulkInsertResponse.FirstId++;
}
}
return(InsertedIds);
}
public async Task <IActionResult> BulkInsert(BulkInsert model)
{
var res = new BulkInsertResponse();
res.DailyInserted = new List <DailyInsert>();
res.DailyNotInserted = new List <DailyInsert>();
res.PositiveNotInserted = new List <PositiveInsert>();
res.PositiveInserted = new List <PositiveInsert>();
foreach (var item in model.PositiveInserts)
{
if (ModelState.IsValid)
{
var dailyRes = await _da.PositiveInsertion(item);
if (dailyRes.StatusCode == 200)
{
res.PositiveInserted.Add(item);
}
else
{
res.PositiveNotInserted.Add(item);
}
}
else
{
res.PositiveNotInserted.Add(item);
}
}
foreach (var item in model.DailyInserts)
{
if (ModelState.IsValid)
{
var dailyRes = await _da.DailyInsertion(item);
if (dailyRes.StatusCode == 200)
{
res.DailyInserted.Add(item);
}
else
{
res.DailyNotInserted.Add(item);
}
}
else
{
res.DailyNotInserted.Add(item);
}
}
return(Ok(res));
}
protected async Task <List <uint> > BulkInsertMySQL(StringBuilder SQLquery, List <string> values)
{
List <uint> InsertedIds = new List <uint>();
BulkInsertResponse bulkInsertResponse = new BulkInsertResponse();
using (MySqlConnection mConnection = new MySqlConnection(this.ConnectionString))
{
//Create a data reader and Execute the command
//MySqlDataReader dataReader = myCmd.ExecuteReader();
mConnection.Open();
using (MySqlCommand cmd = mConnection.CreateCommand())
{
cmd.CommandText = SQLquery.ToString();
MySqlDataReader dataReader = null;
try
{
dataReader = cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
mConnection.Dispose();
}
if (!dataReader.IsClosed)
{
dataReader.Close();
}
}
}
}
if (bulkInsertResponse != null)
{
for (int i = 0; i < bulkInsertResponse.RowCount; i++)
{
InsertedIds.Add(bulkInsertResponse.FirstId);
bulkInsertResponse.FirstId++;
}
}
return(InsertedIds);
}
protected async Task <bool> BulkUpdateSQLGeneric(string query, List <string> values)
{
List <uint> InsertedIds = new List <uint>();
BulkInsertResponse bulkInsertResponse = new BulkInsertResponse();
StringBuilder SQLquery = new StringBuilder(query);
var returnValue = -1;
using (MySqlConnection mConnection = new MySqlConnection(this.ConnectionString))
{
//makes a comma seprated string our of List of Strings
SQLquery.Append(string.Join(" ", values));
//MySqlDataReader dataReader;
//Create a data reader and Execute the command
//MySqlDataReader dataReader = myCmd.ExecuteReader();
mConnection.Open();
using (MySqlCommand cmd = mConnection.CreateCommand())
{
cmd.CommandText = SQLquery.ToString();
MySqlDataReader dataReader = null;
try
{
returnValue = await cmd.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (mConnection.State == ConnectionState.Open)
{
mConnection.Close();
mConnection.Dispose();
}
}
}
}
return(true);
}
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));
}