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);
            }
        }
Beispiel #2
0
        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);
        }
Beispiel #9
0
        /// <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);
            }
        }
Beispiel #10
0
        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);
        }
Beispiel #13
0
        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);
            }
        }
Beispiel #15
0
 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();
                }
            }
        }