public void CheckData(List <NewFieldEngine> fieldsValuesList, AxHelper axHelperLocal)
{
KeyedObjectCollection <AxTableField> tableFields;
List <string> newFieldsList = new List <string>();
if (TableName.Contains(".") == false)
{
AxTable axTable = axHelperLocal.MetadataProvider.Tables.Read(TableName);
tableFields = axTable.Fields;
}
else
{
AxTableExtension axTableExtension = axHelperLocal.MetadataProvider.TableExtensions.Read(TableName);
tableFields = axTableExtension.Fields;
}
foreach (NewFieldEngine newFieldEngine in GetFieldsValues())
{
if (tableFields.Contains(newFieldEngine.FieldName))
{
throw new Exception($"Field {newFieldEngine.FieldName} already exists in the table {TableName}");
}
if (newFieldsList.Contains(newFieldEngine.FieldName))
{
throw new Exception($"Field {newFieldEngine.FieldName} specified several times");
}
newFieldsList.Add(newFieldEngine.FieldName);
}
}
public Boolean PostJsonToSql([FromBody] object JsonData)
{
try
{
JObject JsonObject = (JObject)JsonConvert.DeserializeObject(JsonData.ToString());
string TableName;
foreach (JToken token in JsonObject.SelectToken(""))
{
foreach (JToken Fields in token)
{
int LastIndex = 0;
if (Fields.Type.ToString() == "Array")
{
LastIndex = Fields.Count() - 1;
}
for (int i = 0; i <= LastIndex; i++)
{
JToken WorkFields;
if (Fields.Type.ToString() == "Array")
{
WorkFields = Fields.ToArray()[i];
}
else
{
WorkFields = Fields;
}
JsonInputSection PcrSection = new JsonInputSection();
foreach (JToken Field in WorkFields.Children())
{
var Property = Field as JProperty;
PcrSection[Property.Name] = Property.Value.ToString();
}
TableName = token.Path.ToString();
if (TableName.Contains("."))
{
TableName = TableName.Remove(0, TableName.IndexOf(".") + 1);
}
Assembly Asm = Assembly.Load(Assembly.GetExecutingAssembly().FullName);
Type PCRSectionType = Asm.GetTypes().First(t => token.Path.ToUpper().EndsWith(t.Name.ToUpper()));
dynamic objSectionOut = Activator.CreateInstance(PCRSectionType, TableName, PcrSection);
objSectionOut.HandleRecord();
}
}
}
Logger.LogAction(JsonObject.ToString(), "JSON_Posts");
return(true);
}
catch (Exception ex) { Logger.LogException(ex); return(false); }
}
public string SQLRenameTable(TableSchema newSchema)
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "RENAME TABLE `" + TableName + "` TO `" + newSchema.TableName + "`;\n";
}
else
{
sql = "RENAME TABLE `" + DatabaseName + "`.`" + TableName + "` TO `" + DatabaseName + "`.`" + newSchema.TableName + "`;\n";
}
return(sql);
}
public string SQLDropTable()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "DROP TABLE `" + TableName + "`;\n";
}
else
{
sql = "DROP TABLE `" + DatabaseName + "`.`" + TableName + "`;\n";
}
return(sql);
}
public string SQLTruncateTable()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "TRUNCATE `" + TableName + "`\n";
}
else
{
sql = "TRUNCATE `" + DatabaseName + "`.`" + TableName + "`;\n";
}
return(sql);
}
public string SQLDelete()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "DELETE FROM TABLE `" + TableName + "` WHERE #condition#;\n";
}
else
{
sql = "DELETE FROM TABLE `" + DatabaseName + "`.`" + TableName + "` WHERE #condition#;\n";
}
return(sql);
}
public string SQLUpdate()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "UPDATE `" + TableName + "` SET #column# = #value# WHERE #condition#;\n";
}
else
{
sql = "UPDATE `" + DatabaseName + "`.`" + TableName + "` SET #column# = #value# WHERE #condition#;\n";
}
return(sql);
}
public string SQLSelectTop()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "SELECT * FROM `" + TableName + "`;\n";
}
else
{
sql = "SELECT * FROM `" + DatabaseName + "`.`" + TableName + "` LIMIT 100;\n";
}
return(sql);
}
/// <summary>
/// Starts infinite loop to pull and push data. This is executed on a separate thread from within the calling code.
/// </summary>
internal void PushData()
{
Hashtable objDatasetVersions = new Hashtable();
string sErrorMessage = "";
PollPush objPollPush = new PollPush(UserCredential, ClientID);
while (true)
{
objPollPush.ConnectionString = ConnectionString;
objPollPush.PowerBIDatasetName = PowerBIDatasetName;
objPollPush.IsInitialLoad = IsIntialLoad;
objPollPush.datasetId = datasetId;
if (TableName.Contains(","))
{
string[] sarrTables = TableName.Split(',');
foreach (string sTableName in sarrTables)
{
objPollPush.CurrentVersion = CurrentVersion[sTableName].ToString();
string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + sTableName + "']").Attributes["Columns"].Value;
sErrorMessage = objPollPush.ValidatePushData(sTableName, sColumns, out RecordsAffected);
CurrentVersion[sTableName] = objPollPush.CurrentVersion;
if (RefreshJobDetailUI != null)
{
RefreshJobDetailUI(SelectedRow, sTableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected);
}
}
}
else
{
if (CurrentVersion[TableName] != null)
{
objPollPush.CurrentVersion = CurrentVersion[TableName].ToString();
string sColumns = JobDetails.SelectSingleNode("./JobMetadata[@Datasetname='" + TableName + "']").Attributes["Columns"].Value;
sErrorMessage = objPollPush.ValidatePushData(TableName, sColumns, out RecordsAffected);
CurrentVersion[TableName] = objPollPush.CurrentVersion;
if (RefreshJobDetailUI != null)
{
RefreshJobDetailUI(SelectedRow, TableName, objPollPush.CurrentVersion, sErrorMessage, RecordsAffected);
}
}
}
Thread.Sleep(Interval * 1000);
}
}
public override void RestoreBackup(string newTableName = null)
{
if (!TableName.Contains(backupSuffix))
{
throw new Exception("Table is not backup table!");
}
string originalTableName = TableName.Substring(0, TableName.Length - backupSuffix.Length);
StringBuilder commandText = new StringBuilder();
commandText.Append(string.Format("DROP TABLE IF EXISTS {0};", originalTableName));
commandText.Append(string.Format("CREATE TABLE {0} LIKE {1};", originalTableName, TableName));
commandText.Append(string.Format("INSERT {0} SELECT * FROM {1};", originalTableName, TableName));
var dbQueryExecutor = new DbQueryExecutor(dbManager);
dbQueryExecutor.ExecuteNonQuery(commandText);
}
public string SQLCreateTable()
{
string sql = "";
if (Columns != null)
{
if (TableName.Contains(".csv"))
{
sql = "CREATE TABLE `" + TableName.ToString() + "` (\n";
}
else
{
sql = "CREATE TABLE `" + DatabaseName + "`.`" + TableName + "` (\n";
}
foreach (ColumnSchema Column in Columns)
{
sql += "`" + Column.ColumnName + "` ";
sql += Column.DataType + " ";
if (Column.NotNull == true)
{
sql += "NOT NULL ";
}
else
{
sql += "NULL ";
}
if (Column.AutoIncrement == true)
{
sql += "AUTO INCREMENT ";
}
sql += ",\n";
}
if (hasPrimaryKey() == true)
{
sql += "PRIMARY KEY (`" + getPrimaryKey() + "`),\n";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ");\n";
}
return(sql);
}
public string SQLInsert()
{
string sql = "";
if (TableName.Contains(".csv"))
{
sql = "INSERT INTO TABLE `" + TableName + "` (";
foreach (ColumnSchema col in Columns)
{
sql += "`" + col.ColumnName + "`, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")\n";
sql += "VALUES(";
foreach (ColumnSchema col in Columns)
{
sql += "#" + col.ColumnName + "#, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ") ;\n";
}
else
{
sql = "INSERT INTO TABLE `" + DatabaseName + "`.`" + TableName + "` (";
foreach (ColumnSchema col in Columns)
{
sql += "`" + col.ColumnName + "`, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")\n";
sql += "VALUES(";
foreach (ColumnSchema col in Columns)
{
sql += "#" + col.ColumnName + "#, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ") ;\n";
}
return(sql);
}
public void InitFromSelectedElement(AddinDesignerEventArgs e)
{
if (_axHelper == null)
{
_axHelper = new AxHelper();
}
BaseField baseField = e.SelectedElements.OfType <BaseField>().First();
SelectedField = baseField.Name;
TableName = baseField.Table != null?baseField.Table.GetMetadataType().Name : baseField.TableExtension?.GetMetadataType().Name;
NewFieldEngine newFieldEngine = new NewFieldEngine();
newFieldEngine.GetSetHelper = _axHelper;
if (TableName.Contains(".") == false)
{
_axTable = _axHelper.MetadataProvider.Tables.Read(TableName);
AxTableField axTableField = _axTable.Fields[baseField.Name];
TableRelationForeignKey = newFieldEngine.AddTableRelation(axTableField, _axTable.Relations);
}
else
{
_axTableExtension = _axHelper.MetadataProvider.TableExtensions.Read(TableName);
AxTableField axTableField = _axTableExtension.Fields[baseField.Name];
TableRelationForeignKey = newFieldEngine.AddTableRelation(axTableField, _axTableExtension.Relations);
}
if (TableRelationForeignKey == null)
{
throw new System.Exception($"Field {baseField.Name} doesn't have an EDT relation");
}
RelationName = TableRelationForeignKey.Name;
}
protected void AddField(AxTableField field)
{
AxTableFieldGroup axTableFieldGroup;
AxTableFieldGroupField axTableFieldGroupField = new AxTableFieldGroupField();
axTableFieldGroupField.Name = field.Name;
axTableFieldGroupField.DataField = field.Name;
if (TableName.Contains(".") == false)
{
AxTable axTable = _axHelper.MetadataProvider.Tables.Read(TableName);
if (IsDisplayMethod)
{
AxMethod axMethod = new AxMethod();
axMethod.Name = field.Name;
axMethod.Source = $"public display {field.ExtendedDataType} {field.Name}() " +
Environment.NewLine + "{" + Environment.NewLine + " return '';" + Environment.NewLine + "}";
axTable.AddMethod(axMethod);
}
else
{
axTable.Fields.Add(field);
}
if (GroupName != String.Empty)
{
if (axTable.FieldGroups.Contains(GroupName))
{
axTableFieldGroup = axTable.FieldGroups.getObject(GroupName);
axTableFieldGroup.AddField(axTableFieldGroupField);
}
else
{
axTableFieldGroup = new AxTableFieldGroup {
Name = GroupName
};
axTableFieldGroup.AddField(axTableFieldGroupField);
axTable.AddFieldGroup(axTableFieldGroup);
}
}
if (!IsDisplayMethod)
{
AxTableRelationForeignKey axTableRelationForeignKey = AddTableRelation(field, axTable.Relations);
if (axTableRelationForeignKey != null)
{
axTable.AddRelation(axTableRelationForeignKey);
}
}
_axHelper.MetadataProvider.Tables.Update(axTable, _axHelper.ModelSaveInfo);
}
else
{
AxTableExtension axTableExtension = _axHelper.MetadataProvider.TableExtensions.Read(TableName);
if (!IsDisplayMethod)
{
axTableExtension.Fields.Add(field);
}
if (GroupName != String.Empty)
{
if (axTableExtension.FieldGroups.Contains(GroupName))
{
axTableFieldGroup = axTableExtension.FieldGroups.getObject(GroupName);
axTableFieldGroup.AddField(axTableFieldGroupField);
}
else
{
axTableFieldGroup = new AxTableFieldGroup {
Name = GroupName
};
axTableFieldGroup.AddField(axTableFieldGroupField);
axTableExtension.FieldGroups.Add(axTableFieldGroup);
}
}
if (!IsDisplayMethod)
{
AxTableRelationForeignKey axTableRelationForeignKey = AddTableRelation(field, axTableExtension.Relations);
if (axTableRelationForeignKey != null)
{
axTableExtension.Relations.Add(axTableRelationForeignKey);
}
}
_axHelper.MetadataProvider.TableExtensions.Update(axTableExtension, _axHelper.ModelSaveInfo);
}
}
private bool IsDraft(eDocumentObjectType pDocumentObjectType = eDocumentObjectType.oNone)
{
return(pDocumentObjectType == eDocumentObjectType.oDrafts ||
(TableName != null && TableName.Contains("DRF")));
}
void DoInsertForMySql(DbSource runtimeDatabase, SqlBulkCopyOptions currentOptions, IWarewolfListIterator parametersIteratorCollection, IWarewolfIterator batchItr, IWarewolfIterator timeoutItr, IDSFDataObject dataObject, ErrorResultTO errorResultTo, ErrorResultTO allErrors, ref bool addExceptionToErrorList)
{
MySqlBulkLoader sqlBulkCopy = new MySqlBulkLoader(new MySqlConnection(runtimeDatabase.ConnectionString));
TableName = TableName.Replace("[", "").Replace("]", "");
if (TableName.Contains("."))
{
TableName = TableName.Substring(TableName.IndexOf(".", StringComparison.Ordinal) + 1);
}
if (String.IsNullOrEmpty(BatchSize) && String.IsNullOrEmpty(Timeout))
{
sqlBulkCopy = new MySqlBulkLoader(new MySqlConnection(runtimeDatabase.ConnectionString))
{
TableName = TableName, FieldTerminator = ",", LineTerminator = "\n"
};
}
else
{
while (parametersIteratorCollection.HasMoreData())
{
sqlBulkCopy = SetupMySqlBulkCopy(batchItr, parametersIteratorCollection, timeoutItr, runtimeDatabase, currentOptions);
}
}
if (sqlBulkCopy != null)
{
var dataTableToInsert = BuildDataTableToInsertMySql();
if (InputMappings != null && InputMappings.Count > 0)
{
var iteratorCollection = new WarewolfListIterator();
var listOfIterators = GetIteratorsFromInputMappings(dataObject, iteratorCollection, out errorResultTo);
allErrors.MergeErrors(errorResultTo);
// oh no, we have an issue, bubble it out ;)
if (allErrors.HasErrors())
{
addExceptionToErrorList = false;
throw new Exception("Problems with Iterators for SQLBulkInsert");
}
// emit options to debug as per acceptance test ;)
if (dataObject.IsDebugMode())
{
AddBatchSizeAndTimeOutToDebug(dataObject.Environment);
AddOptionsDebugItems();
}
FillDataTableWithDataFromDataList(iteratorCollection, dataTableToInsert, listOfIterators);
foreach (var dataColumnMapping in InputMappings)
{
if (!String.IsNullOrEmpty(dataColumnMapping.InputColumn))
{
sqlBulkCopy.Columns.Add(dataColumnMapping.OutputColumn.ColumnName);
}
}
}
// Pass in wrapper now ;)
var wrapper = new MySqlBulkCopyWrapper(sqlBulkCopy);
var inserted = SqlBulkInserter.Insert(wrapper, dataTableToInsert);
var resultString = inserted ? "Success" : "Failure";
dataObject.Environment.Assign(Result, resultString);
if (dataObject.IsDebugMode())
{
AddDebugOutputItem(new DebugItemWarewolfAtomResult(resultString, Result, ""));
}
allErrors.MergeErrors(errorResultTo);
if (dataTableToInsert != null)
{
dataTableToInsert.Dispose();
}
}
}
