Exemple #1
0
 internal void ExecuteQuery(SQLQuery pQuery)
 {
     lock (mLock)
     {
         ExecuteQueryThreadSafe(pQuery);
     }
 }
Exemple #2
0
 public SQLQuery BuildQueryToRemoveChangesForTable(string schemaName, string tableName)
 {
     string sql = "delete from " + tableSet.FullyQualifiedSyncTableName + " where `schema` = ?schema AND `table` = ?table;";
     var query = new SQLQuery(sql);
     query.Parameters.Add("schema", schemaName);
     query.Parameters.Add("table", tableName);
     return query;
 }
Exemple #3
0
 private void AddColumnParametersToQuery(SQLQuery query, IEnumerable<ColumnInfo> columnInfos, List<Tuple<string, object>> data)
 {
     foreach (var columnInfo in columnInfos)
     {
         var tuple = data.First(t => t.Item1 == columnInfo.ColumnName);
         query.Parameters.Add(columnInfo.ColumnName, tuple.Item2);
     }
 }
Exemple #4
0
        /// <summary>
        /// Execute()
        /// </summary>
        /// <param name='context'>The context for the test, this holds state that is passed beteen tests</param>
        public override void Execute(Context context)
        {
            context.LogInfo("Using database connection string: {0}", ConnectionString);
            string sqlQueryToExecute = SQLQuery.GetFormattedSqlQuery(context);

            // Sleep for delay seconds...
            if (0 < DelayBeforeCheck)
            {
                context.LogInfo("Sleeping for: {0} seconds", DelayBeforeCheck);
                System.Threading.Thread.Sleep(DelayBeforeCheck * 1000);
            }

            context.LogInfo("Executing database query: {0}", sqlQueryToExecute);
            DataSet ds = FillDataSet(ConnectionString, sqlQueryToExecute);

            if (NumberOfRowsExpected != ds.Tables[0].Rows.Count)
            {
                throw new ApplicationException(string.Format("Number of rows expected to be returned by the query does not match the value specified in the teststep. Number of rows the NnumberOfRowsExpected were: {0}, actual: {1}", NumberOfRowsExpected, ds.Tables[0].Rows.Count));
            }

            context.LogInfo("NumberOfRowsExpected: {0}, actual number returned: {1}", NumberOfRowsExpected, ds.Tables[0].Rows.Count);

            if (0 == NumberOfRowsExpected)
            {
                return;
            }

            if (0 < DbRowsToValidate.Count)
            {
                int rowCount = 0;

                foreach (var dbRowToValidate in DbRowsToValidate)
                {
                    context.LogInfo("Validating row number: {0}", rowCount);

                    var resultRow = ds.Tables[0].Rows[rowCount];;
                    var cells     = dbRowToValidate.Cells;

                    foreach (DbCellToValidate cell in cells)
                    {
                        object dbData            = resultRow[cell.ColumnName];
                        var    dbDataStringValue = string.Empty;

                        if (0 == ValidateData(dbData, cell.ExpectedValue, ref dbDataStringValue))
                        {
                            context.LogInfo("Validation succeeded for field: {0}. Expected value: {1}", cell.ColumnName, dbDataStringValue);
                        }
                        else
                        {
                            throw new Exception(String.Format("Validation failed for field: {0}. Expected value: {1}, actual value: {2}", cell.ColumnName, cell.ExpectedValue, dbDataStringValue));
                        }
                    }

                    rowCount++;
                }
            }
        }
Exemple #5
0
        /// <summary>
        /// Creates and initializes a new instance of the QueryTransformer object.
        /// </summary>
        /// <param name="query">SQL Query to transform.</param>
        /// <returns>Returns instance of the QueryTransformer object.</returns>
        private QueryTransformer CreateQueryTransformer(SQLQuery query)
        {
            var qt = QueryTransformerStore.Create(instanceId);

            qt.QueryProvider = query;
            qt.AlwaysExpandColumnsInQuery = true;

            return(qt);
        }
Exemple #6
0
 private void button2_Click(object sender, EventArgs e)
 {
     for (int i = 0; i < dataGridView1.SelectedRows.Count; i++)
     {
         dataGridView1.Rows.RemoveAt(this.dataGridView1.SelectedRows[i].Index);
     }
     SQLQuery.UpdateTable("groups", dataGridView1);
     dataGridView1.DataSource = SQLQuery.ListGroups();
     dataGridView1.Update();
 }
Exemple #7
0
        private void QBuilder_OnLinkCreated(SQLQuery sender, Link link)
        {
            if (CbLinkCreated.IsChecked != true)
            {
                return;
            }

            BoxLogEvents.Text = "LinkCreated" +
                                Environment.NewLine + BoxLogEvents.Text;
        }
Exemple #8
0
        private void QBuilder_OnDataSourceAdded(SQLQuery sender, DataSource addedobject)
        {
            if (CbDataSourceAdded.IsChecked != true)
            {
                return;
            }

            BoxLogEvents.Text = "DataSourceAdded " + addedobject.NameInQuery +
                                Environment.NewLine + BoxLogEvents.Text;
        }
Exemple #9
0
        public virtual XVar getCurrentRecordInternal()
        {
            dynamic fetchedArray = null, keysSet = null, orderClause = null, sql = XVar.Array();

            if (XVar.Pack(!(XVar)(this.data == null)))
            {
                return(this.data);
            }
            sql         = XVar.Clone(getSubsetSQLComponents());
            orderClause = XVar.Clone(getOrderByClause());
            keysSet     = XVar.Clone(checkKeysSet());
            if (XVar.Pack(keysSet))
            {
                sql.InitAndSetArrayItem(XVar.Array(), "optionalWhere");
                sql.InitAndSetArrayItem(XVar.Array(), "optionalHaving");
                sql.InitAndSetArrayItem(XVar.Array(), "mandatoryHaving");
                sql.InitAndSetArrayItem(XVar.Array(), "mandatoryWhere");
                sql.InitAndSetArrayItem(CommonFunctions.KeyWhere((XVar)(this.keys)), "mandatoryWhere", null);
                sql.InitAndSetArrayItem(SecuritySQL(new XVar("Search"), (XVar)(this.tName)), "mandatoryWhere", null);
            }
            GlobalVars.strSQL = XVar.Clone(SQLQuery.buildSQL((XVar)(sql["sqlParts"]), (XVar)(sql["mandatoryWhere"]), (XVar)(sql["mandatoryHaving"]), (XVar)(sql["optionalWhere"]), (XVar)(sql["optionalHaving"])));
            if (XVar.Pack(!(XVar)(keysSet)))
            {
                GlobalVars.strSQL = XVar.Clone(CommonFunctions.applyDBrecordLimit((XVar)(MVCFunctions.Concat(GlobalVars.strSQL, orderClause)), new XVar(1), (XVar)(this.connection.dbType)));
            }
            if (XVar.Pack(this.eventsObject.exists(new XVar("BeforeQueryView"))))
            {
                dynamic strSQLbak = null, strWhereClause = null, strWhereClauseBak = null;
                strWhereClause    = XVar.Clone(SQLQuery.combineCases((XVar)(sql["mandatoryWhere"]), new XVar("and")));
                strSQLbak         = XVar.Clone(GlobalVars.strSQL);
                strWhereClauseBak = XVar.Clone(strWhereClause);
                this.eventsObject.BeforeQueryView((XVar)(GlobalVars.strSQL), ref strWhereClause, this);
                if ((XVar)(strSQLbak == GlobalVars.strSQL) && (XVar)(strWhereClauseBak != strWhereClause))
                {
                    GlobalVars.strSQL = XVar.Clone(SQLQuery.buildSQL((XVar)(sql["sqlParts"]), (XVar)(new XVar(0, strWhereClause))));
                    if (XVar.Pack(!(XVar)(keysSet)))
                    {
                        GlobalVars.strSQL = XVar.Clone(CommonFunctions.applyDBrecordLimit((XVar)(MVCFunctions.Concat(GlobalVars.strSQL, orderClause)), new XVar(1), (XVar)(this.connection.dbType)));
                    }
                }
            }
            CommonFunctions.LogInfo((XVar)(GlobalVars.strSQL));
            fetchedArray = XVar.Clone(this.connection.query((XVar)(GlobalVars.strSQL)).fetchAssoc());
            this.data    = XVar.Clone(this.cipherer.DecryptFetchedArray((XVar)(fetchedArray)));
            if (XVar.Pack(!(XVar)(keysSet)))
            {
                this.keys = XVar.Clone(getKeysFromData((XVar)(this.data)));
                setKeysForJs();
            }
            if ((XVar)(MVCFunctions.count(this.data)) && (XVar)(this.eventsObject.exists(new XVar("ProcessValuesView"))))
            {
                this.eventsObject.ProcessValuesView((XVar)(this.data), this);
            }
            return(this.data);
        }
Exemple #10
0
        public static bool projectRefersToModels(UIProgress paramUIProgress, Session paramSession, long?paramLong)
        {
            paramUIProgress.Progress = UILanguage.get("tab.project.processing.model.check");
            SQLQuery sQLQuery = paramSession.createSQLQuery("SELECT count(1) from CNDON where PRJID = :id and TAKEOFFTYPE = :type");

            sQLQuery.setLong("id", paramLong.Value);
            sQLQuery.setString("type", "BIM");
            Number number = (Number)sQLQuery.list().get(0);

            return(number.intValue() != 0);
        }
 void checkSQLReqParameter(SQLQuery value)
 {
     if (value == null)
     {
         throw new Exception("The body is missing or not formatted correctly. Maybe just a comma is missing between two attributes.");
     }
     if (string.IsNullOrEmpty(value.SQL) && string.IsNullOrEmpty(value.userQuery))
     {
         throw new Exception("No SQL nor userQuery defined. Maybe your JSON is badly formatted. Use JSON.stringify");
     }
 }
        public void NonQueryTestNoParameters()
        {
            var query = new SQLQuery(sqlString, SQLQueryType.NonQuery);

            queryRunner.RunQuery(query);
            VerifyLogging(sqlString);
            Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText);
            MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
            MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
            MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1));
            Assert.AreEqual(345, query.RowCount);
        }
Exemple #13
0
        public void InsertDataBalance(SQLiteConnection AnyConnectionObject, ref int Choice2)
        {
            string Duration    = null;
            string ChooseTable = null;


            SQLiteCommand SQLQuery;

            SQLQuery = AnyConnectionObject.CreateCommand();


            if (Choice2 == 1)
            {
                Console.Write("Enter the leg raised (left/right): ");
                string LegRaised = Console.ReadLine();
                Console.WriteLine("Enter the duration: ");
                Duration    = Console.ReadLine();
                ChooseTable = "StandingOnOneFoot";


                SQLQuery.CommandText = "INSERT INTO " + ChooseTable + " (Date, LegRaisedToFront, Time) VALUES(strftime('%d/%m/%Y %H:%M', 'now', 'localtime'), @param1, @param2)";



                SQLQuery.Parameters.Add(new SQLiteParameter("@param1", LegRaised));
                SQLQuery.Parameters.Add(new SQLiteParameter("@param2", Duration));

                SQLQuery.ExecuteNonQuery();
            }
            if (Choice2 == 2)
            {
                Console.Write("*******GUIDELINES*******: For (Decimal), maximum number that can be entered is 9999.99. Max number of decimal places is 2, values like 9999.999 cannot be entered\n");
                Console.Write("*******END OF GUIDELINES**\n\n\n");

                Console.Write("Enter duration: ");
                Duration = Console.ReadLine();

                Console.Write("Enter the number of steps taken (Decimal): ");
                double StepsTaken = Convert.ToDouble(Console.ReadLine());
                ChooseTable = "HeelToToeWalk";



                SQLQuery.CommandText = "INSERT INTO " + ChooseTable + " (Date, Time, Steps) VALUES(strftime('%d/%m/%Y %H:%M', 'now', 'localtime'), @param1, @param2)";



                SQLQuery.Parameters.Add(new SQLiteParameter("@param1", Duration));
                SQLQuery.Parameters.Add(new SQLiteParameter("@param2", StepsTaken));

                SQLQuery.ExecuteNonQuery();
            }
        }
Exemple #14
0
        private SQLQuery GetUpdateQuery(SyncChange change, List <Tuple <string, object> > data)
        {
            var tableMap     = tableSet.GetTableMapping(change);
            var tableInfo    = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
            var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
            var updates      = tableInfo.Columns.Where(c => !c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
            var sql          = "update " + tableMap.FullyQualifiedDestinationTable + " set " + string.Join(", ", updates) + " where " + string.Join(" AND ", whereColumns) + ";";
            var query        = new SQLQuery(sql, SQLQueryType.NonQuery);

            AddColumnParametersToQuery(query, tableInfo.Columns, data);
            return(query);
        }
        public Form1()
        {
            SQLContext sqlContext = new SQLContext {
                LoadingOptions = { OfflineMode = true }
            };

            _query             = new SQLQuery(sqlContext);
            _query.SQLUpdated += Query_SQLUpdated;
            InitializeComponent();

            comboBoxSyntax.SelectedItem = "MS SQL Server";
        }
Exemple #16
0
        private void GetDataFromTable(SyncInfo reportInfo, TableInfo tableInfo, TableMapping tableMap, bool isSource, string tableName)
        {
            string sql;
            string parameterSymbol = isSource ? "?" : "@";
            bool   firstKey        = true;

            if (isSource && !string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly))
            {
                sql = tableMap.CustomSourceSQLForSyncOnly;
            }
            else
            {
                var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = " + parameterSymbol + c.ColumnName + ")");
                sql = "select * from " + tableName + " where " +
                      string.Join(" AND ", whereColumns) + ";";
            }
            var query = new SQLQuery(sql);

            foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID))
            {
                if (sql.Contains(parameterSymbol + column.ColumnName))
                {
                    query.Parameters.Add(column.ColumnName, firstKey ? reportInfo.PrimaryKey1 : reportInfo.PrimaryKey2);
                }
                firstKey = false;
            }

            if (isSource)
            {
                query.ProcessRow = reader =>
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        reportInfo.SourceDataList.Insert(i, ConvertDataTypes(reader[i]));
                    }
                    return(false);
                };
                sourceDB.RunQuery(query);
            }
            else
            {
                query.ProcessRow = reader =>
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        reportInfo.DestinationDataList.Insert(i, ConvertDataTypes(reader[i]));
                    }
                    return(false);
                };
                destinationDB.RunQuery(query);
            }
        }
 public SQLResult Get([FromBody] SQLQuery value)
 {
     try {
         if (!SAPB1.brokerConf.sql)
         {
             throw new Exception("SQL module was disabled in web.config for SQL Broker");
         }
         checkSQLReqParameter(value);
         return(SAPB1.SQLQuery(value, true, new SQLResult(Request)));
     } catch (Exception e) {
         return(handleException(e));
     }
 }
 public virtual void execute(Session param1Session, string param1String1, string param1String2)
 {
     l.info("Rebuilding index " + param1String2 + " on table " + param1String1);
     try
     {
         SQLQuery sQLQuery = param1Session.createSQLQuery("ALTER INDEX " + param1String2 + " ON " + param1String1 + " REBUILD");
         sQLQuery.executeUpdate();
     }
     catch (Exception exception)
     {
         l.error("Could not rebuild index", exception);
     }
 }
Exemple #19
0
 /// <summary>
 /// The user have decided to save or not to save the current file via the parameter
 /// Then are all building objects removed and the update of the GUI comes in
 /// Lastly all components are reset to how they were when the application started
 /// </summary>
 /// <param name="save"></param>
 public void NewFile(bool save)
 {
     if (save)
     {
         SaveFile();
     }
     SQLQuery.DeleteAllBuildings();
     buildingManager.DeleteAll();
     ClearTable();
     filePath      = null;
     savedUpToDate = true;
     buildingManager.SetDisplayList();
 }
Exemple #20
0
        //TODO: get foreign key info...
        public static TableInfo GetTableInfo(this QueryRunner database, string schema, string tableName)
        {
            var sql = @"SELECT distinct ISNULL(sch.name, '') 'SchemaName', tables.name 'TableName', c.name 'ColumnName', c.column_id 'ColumnID',
            t.Name 'Datatype', c.max_length 'MaxLength',  c.precision 'Precision' ,
            c.scale 'Scale', c.is_nullable 'Nullable',
            ISNULL((select is_primary_key from sys.indexes i inner join sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
            where ic.object_id = c.object_id AND ic.column_id = c.column_id and is_primary_key = 1),0) as 'PrimaryKey',
            ISNULL(c.is_identity, 0) 'IsIdentity'
             FROM  sys.columns c INNER JOIN
            sys.types t ON c.user_type_id = t.user_type_id
            INNER JOIN sys.tables tables ON (tables.object_id = c.object_id)
            LEFT OUTER JOIN sys.schemas sch on (sch.schema_id = tables.schema_id)
            WHERE c.object_id = OBJECT_ID(@tableName) order by TableName, ColumnID;";

            var tableInfo = tableInfoCache.FirstOrDefault(t => t.ConnectionString == database.ConnectionString && t.SchemaName == schema && t.TableName == tableName);

            if (tableInfo == null)
            {
                var columnList = new List<ColumnInfo>();
                string actualSchemaName = string.Empty;
                string actualTableName  = string.Empty;

                var query = new SQLQuery(sql);
                var schemaTableName = string.IsNullOrEmpty(schema) ? tableName : schema + "." + tableName;
                query.Parameters.Add("tableName", schemaTableName);
                query.ProcessRow = reader =>
                {
                    columnList.Add(new ColumnInfo(
                        (string) reader["ColumnName"],
                        Convert.ToInt32(reader["ColumnID"]),
                        (string) reader["DataType"],
                        Convert.ToInt32(reader["MaxLength"]),
                        Convert.ToInt32(reader["Precision"]),
                        Convert.ToInt32(reader["Scale"]),
                        (bool) reader["Nullable"],
                        (bool) reader["PrimaryKey"],
                        (bool) reader["IsIdentity"]));
                    actualSchemaName = (string) reader["SchemaName"];
                    actualTableName = (string) reader["TableName"];
                    return true;
                };
                database.RunQuery(query);
                if (columnList.Count == 0)
                {
                    throw new ApplicationException("Couldn't find any columns for table " + schemaTableName);
                }
                tableInfo = new TableInfo(database.ConnectionString, actualTableName, actualSchemaName, columnList.ToArray());
                tableInfoCache.Add(tableInfo);
            }
            return tableInfo;
        }
Exemple #21
0
        //TODO: get foreign key info...

        public static TableInfo GetTableInfo(this QueryRunner database, string schema, string tableName)
        {
            var sql = @"SELECT distinct ISNULL(sch.name, '') 'SchemaName', tables.name 'TableName', c.name 'ColumnName', c.column_id 'ColumnID', 
    t.Name 'Datatype', c.max_length 'MaxLength',  c.precision 'Precision' , 
	c.scale 'Scale', c.is_nullable 'Nullable', 
	ISNULL((select is_primary_key from sys.indexes i inner join sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id 
			where ic.object_id = c.object_id AND ic.column_id = c.column_id and is_primary_key = 1),0) as 'PrimaryKey',
	ISNULL(c.is_identity, 0) 'IsIdentity'
	 FROM  sys.columns c INNER JOIN 
	sys.types t ON c.user_type_id = t.user_type_id  
	INNER JOIN sys.tables tables ON (tables.object_id = c.object_id)
	LEFT OUTER JOIN sys.schemas sch on (sch.schema_id = tables.schema_id)
	WHERE c.object_id = OBJECT_ID(@tableName) order by TableName, ColumnID;"    ;

            var tableInfo = tableInfoCache.FirstOrDefault(t => t.ConnectionString == database.ConnectionString && t.SchemaName == schema && t.TableName == tableName);

            if (tableInfo == null)
            {
                var    columnList       = new List <ColumnInfo>();
                string actualSchemaName = string.Empty;
                string actualTableName  = string.Empty;

                var query           = new SQLQuery(sql);
                var schemaTableName = string.IsNullOrEmpty(schema) ? tableName : schema + "." + tableName;
                query.Parameters.Add("tableName", schemaTableName);
                query.ProcessRow = reader =>
                {
                    columnList.Add(new ColumnInfo(
                                       (string)reader["ColumnName"],
                                       Convert.ToInt32(reader["ColumnID"]),
                                       (string)reader["DataType"],
                                       Convert.ToInt32(reader["MaxLength"]),
                                       Convert.ToInt32(reader["Precision"]),
                                       Convert.ToInt32(reader["Scale"]),
                                       (bool)reader["Nullable"],
                                       (bool)reader["PrimaryKey"],
                                       (bool)reader["IsIdentity"]));
                    actualSchemaName = (string)reader["SchemaName"];
                    actualTableName  = (string)reader["TableName"];
                    return(true);
                };
                database.RunQuery(query);
                if (columnList.Count == 0)
                {
                    throw new ApplicationException("Couldn't find any columns for table " + schemaTableName);
                }
                tableInfo = new TableInfo(database.ConnectionString, actualTableName, actualSchemaName, columnList.ToArray());
                tableInfoCache.Add(tableInfo);
            }
            return(tableInfo);
        }
Exemple #22
0
 static void SQLADONETQuery(SQLQuery q, SQLResult result, ConnectionParams cp, bool rollbackOnly)
 {
     using (SqlConnection t = new SqlConnection(cp.getConnectionString()))             //{ t.Open();
         using (SqlCommand sqlCommand = new SqlCommand {
             Connection = t
         }) {
             sqlCommand.Connection.Open();
             if (!string.IsNullOrEmpty(q.userQuery))
             {
                 if (!string.IsNullOrEmpty(q.SQL))
                 {
                     throw new Exception("SQL must be empty for a user query request");
                 }
                 q.SQL = getUserQuerySQL(t, q, cp);
                 checkRollbackOnlyConditions(result.rollbackOnly, q.SQL);
             }
             result.SQL             = q.SQL;
             result.userQuery       = !string.IsNullOrEmpty(q.userQuery) ? q.uqCategory + "." + q.userQuery : null;
             result.extendedUQ      = q.uqCategory != null && q.uqCategory.EndsWith(UQCATEGORY_EXTENSION);
             sqlCommand.CommandText = q.SQL;
             using (SqlDataReader rs = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
                 result.statusCode = System.Net.HttpStatusCode.OK;
                 var data = new Newtonsoft.Json.Linq.JArray();
                 do
                 {
                     while (rs.Read())
                     {
                         var row = new Newtonsoft.Json.Linq.JObject();
                         for (int i = 0; i < rs.FieldCount; i++)
                         {
                             row.Add(rs.GetName(i), rs.GetValue(i).ToString());
                         }
                         data.Add(row);
                     }
                     result.data = data;
                     if (q.columnInfo)
                     {
                         int cc = rs.FieldCount;
                         for (int i = 0; i < cc; i++)
                         {
                             SQLResult.Column column = new SQLResult.Column();
                             column.name     = rs.GetName(i);
                             column.dataType = rs.GetDataTypeName(i);
                             //column.subType = ;//Hmm, What to give here?
                             result.columns.Add(column);
                         }
                     }
                 } while (rs.NextResult());
             }
         }
 }
Exemple #23
0
        public void TruncateDestinationTables()
        {
            var destinationQueryRunner = new QueryRunner(tableSet.DestinationConnectionStringName);
            var destinationQueries     = new List <SQLQuery>();

            foreach (var tableMap in tableSet.Mappings.Where(ts => ts.TruncateDestinationAndBulkLoadFromSource).OrderByDescending(ts => ts.Ordinal))
            {
                Logging.WriteMessageToApplicationLog("About to delete all rows in table " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information);
                var destinationSql   = "delete from " + tableMap.FullyQualifiedDestinationTable + ";";
                var destinationQuery = new SQLQuery(destinationSql, SQLQueryType.NonQuery);
                destinationQueries.Add(destinationQuery);
            }
            destinationQueryRunner.RunQuery(destinationQueries, true);
        }
Exemple #24
0
        public virtual XVar getFullWhereComponent(dynamic _param_pageObj)
        {
            #region pass-by-value parameters
            dynamic pageObj = XVar.Clone(_param_pageObj);
            #endregion

            dynamic searchWhere = XVar.Array(), whereComponents = XVar.Array();
            whereComponents = XVar.Clone(pageObj.getWhereComponents());
            searchWhere     = XVar.Clone(XVar.Array());
            searchWhere.InitAndSetArrayItem(whereComponents["searchWhere"], null);
            searchWhere.InitAndSetArrayItem(pageObj.getCurrentTabWhere(), null);
            whereComponents.InitAndSetArrayItem(SQLQuery.combineCases((XVar)(searchWhere), new XVar("and")), "searchWhere");
            return(whereComponents);
        }
Exemple #25
0
 public void UpdateStatusOfChanges(IEnumerable<SyncChange> changes, RowChangeStatus newStatus)
 {
     string sql = "update " + tableSet.FullyQualifiedSyncTableName + " set status = ?status where id = ?id;";
     var queryList = new List<SQLQuery>();
     //TODO:this can be a single query...
     foreach (var change in changes)
     {
         var query = new SQLQuery(sql, SQLQueryType.NonQuery);
         query.Parameters.Add("status", (int) newStatus);
         query.Parameters.Add("id", change.RowId);
         queryList.Add(query);
     }
     SourceDb.RunQuery(queryList, true);
 }
Exemple #26
0
        public Customer GetCustomerNoOrders(int customerId)
        {
            Customer customer = null;
            var      query    = new SQLQuery(SQLSelectSingleCustomer);

            query.ProcessRow = dr =>
            {
                customer = CreateCustomerFromDataReader(dr);
                return(true);
            };
            query.Parameters.Add("CustomerId", customerId);
            queryRunner.RunQuery(query);
            return(customer);
        }
Exemple #27
0
    /// <summary>
    /// parses the entire workload
    /// </summary>
    /// <param name="Workload"></param>
    /// <returns></returns>
    public static SQLQuery[] Parse(String[] Workload)
    {
        var toreturn = new List <SQLQuery>();

        for (int i = 2; i < Workload.Length; i++)
        {
            SQLQuery sqlQuery = ParseLine(Workload[i]);
            if (sqlQuery != null)
            {
                toreturn.Add(sqlQuery);
            }
        }
        return(toreturn.ToArray());
    }
Exemple #28
0
    public void SetPagedDataSource(SQLQuery s, BindingNavigator bnav)
    {
        this.s = s;
        int count = DataProvider.ExecuteCount(s.CountQuery);

        pg = new Paging(count, 5);
        bnav.BindingSource = pg.BindingSource;
        pg.BindingSource.PositionChanged += new EventHandler(bs_PositionChanged);
        //first page
        string    q  = s.GetPagingQuery(pg.GetStartRowNum(1), pg.GetEndRowNum(1), true);
        DataTable dt = DataProvider.ExecuteDt(q);

        DataSource = dt;
    }
Exemple #29
0
 private void ExecuteQueryThreadSafe(SQLQuery pQuery)
 {
     /* try
     {
         OpenConnection(pQuery.GetDatabaseName());
         var data = ExecuteRequest(pQuery.GetQuery());
         pQuery.Result = ReadResult(data);
         CloseConnection();
     }
     catch(Exception exception)
     {
         Error.Create(exception.Message, EErrorSeverity.MAJOR);
     }*/
 }
Exemple #30
0
        public async Task <IHttpActionResult> Login(Credentials cred)
        {
            var processingResult = new ServiceProcessingResult <LoginReturn> {
                IsSuccessful = true
            };


            try
            {
                var sqlQuery = "SELECT schcode,schname,invno FROM InvoiceInfo WHERE Schcode=@Schcode AND Invno=@Invno";
                MySqlParameter[] parameters = new MySqlParameter[] { new MySqlParameter("@Invno", cred.password), new MySqlParameter("@Schcode", cred.schcode) };
                var sqlQueryService         = new SQLQuery();
                var getLoginResult          = await sqlQueryService.ExecuteReaderAsync <LoginReturn>(CommandType.Text, sqlQuery, parameters);

                if (!getLoginResult.IsSuccessful)
                {
                    processingResult.IsSuccessful = false;
                    processingResult.Error        = new ProcessingError("Failed to login.", "Failed to login.", true, false);
                    ExceptionlessClient.Default.CreateLog(typeof(InvoiceController).FullName, "Login failed", "Error").AddObject(cred).AddTags("Controller Error").Submit();
                    return(Ok(processingResult));
                }
                var retval = (List <LoginReturn>)getLoginResult.Data;
                if (retval == null)
                {
                    processingResult.IsSuccessful = false;
                    processingResult.Error        = new ProcessingError("Improper Credeintials.", "Improper Credentials.", true, false);
                    return(Ok(processingResult));
                }
                if (retval.Count == 1)
                {
                    processingResult.Data = retval[0];
                }
                else
                {
                    processingResult.IsSuccessful = false;
                    processingResult.Error        = new ProcessingError("Improper Credeintials.", "Improper Credentials.", true, false);
                }
                return(Ok(processingResult));
            }
            catch (Exception ex)
            {
                processingResult.IsSuccessful = false;
                processingResult.Error        = new ProcessingError("Failed to login.", "Failed to login.", true, false);
                ex.ToExceptionless()
                .SetMessage("Login Failed")
                .Submit();
                return(Ok(processingResult));
            }
        }
Exemple #31
0
        public ActionResult PostBid(AuctionItemPage currentPage, BidStuff BidThing)
        {
            string idStr = currentPage.PageLink.ToString();
            int    id    = Convert.ToInt32(idStr);

            BidVM bid = new BidVM();

            bid.ItemId    = id;
            bid.BidAmount = BidThing.BidAmount;
            bid.NewBidder = BidThing.NewBidder;

            string message = SQLQuery.AddBidToDB(bid);

            return(RedirectToAction("Index"));
        }
Exemple #32
0
        public void UpdateStatusOfChanges(IEnumerable <SyncChange> changes, RowChangeStatus newStatus)
        {
            string sql       = "update " + tableSet.FullyQualifiedSyncTableName + " set status = ?status where id = ?id;";
            var    queryList = new List <SQLQuery>();

            //TODO:this can be a single query...
            foreach (var change in changes)
            {
                var query = new SQLQuery(sql, SQLQueryType.NonQuery);
                query.Parameters.Add("status", (int)newStatus);
                query.Parameters.Add("id", change.RowId);
                queryList.Add(query);
            }
            SourceDb.RunQuery(queryList, true);
        }
Exemple #33
0
        public MainWindow()
        {
            InitializeComponent();

            var sqlContext = new SQLContext {
                LoadingOptions = { OfflineMode = true }
            };

            _query             = new SQLQuery(sqlContext);
            _query.SQLUpdated += _query_SQLUpdated;
            InitializeComponent();

            ComboBoxSyntax.SelectedItem = "MS SQL Server";
            Loaded += MainWindow_Loaded;
        }
Exemple #34
0
        public Dictionary <OrderStatus, int> GetOrderCountForAllStatus(DateTime startDate, DateTime endDate)
        {
            var returnDict = new Dictionary <OrderStatus, int>();
            var query      = new SQLQuery(SQLCountOrderAllStatusByDate, SQLQueryType.DataReader);

            query.ProcessRow = new Func <DbDataReader, bool>(dr =>
            {
                returnDict.Add((OrderStatus)(int)dr["Status"], (int)dr["count"]);
                return(true);
            });
            query.Parameters.Add("StartDate", startDate);
            query.Parameters.Add("EndDate", endDate);
            queryRunner.RunQuery(query);
            return(returnDict);
        }
        public static DataView GetDataView(string sqlCommand, SQLQuery sqlQuery)
        {
            if (string.IsNullOrEmpty(sqlCommand))
            {
                return(null);
            }

            if (sqlQuery.SQLContext.MetadataProvider == null)
            {
                return(null);
            }

            if (!sqlQuery.SQLContext.MetadataProvider.Connected)
            {
                sqlQuery.SQLContext.MetadataProvider.Connect();
            }

            if (string.IsNullOrEmpty(sqlCommand))
            {
                return(null);
            }

            if (!sqlQuery.SQLContext.MetadataProvider.Connected)
            {
                sqlQuery.SQLContext.MetadataProvider.Connect();
            }

            var command = CreateSqlCommand(sqlCommand, sqlQuery);

            DataTable table = new DataTable("result");

            using (var dbReader = command.ExecuteReader())
            {
                for (int i = 0; i < dbReader.FieldCount; i++)
                {
                    table.Columns.Add(dbReader.GetName(i));
                }

                while (dbReader.Read())
                {
                    object[] values = new object[dbReader.FieldCount];
                    dbReader.GetValues(values);
                    table.Rows.Add(values);
                }

                return(table.DefaultView);
            }
        }
        private IList <string> getTables(Session paramSession, int paramInt, string paramString)
        {
            List <object> arrayList = new List <object>();

            if (paramInt == ProjectServerDBUtil.SQLSERVER_DBMS)
            {
                string   str                  = string.Format("SELECT QUOTENAME( TABLE_SCHEMA )+'.'+QUOTENAME( TABLE_NAME ) AS tableName FROM INFORMATION_SCHEMA.tables WHERE TABLE_CATALOG='{0}';", new object[] { paramString });
                SQLQuery sQLQuery             = paramSession.createSQLQuery(str);
                System.Collections.IList list = sQLQuery.list();
                foreach (string str1 in list)
                {
                    arrayList.Add(str1);
                }
            }
            return(arrayList);
        }
Exemple #37
0
        private List<SyncChange> GetChanges()
        {
            string sql = "select * from " + tableSet.FullyQualifiedSyncTableName + " where status = ?status order by id;";
            var changes = new List<SyncChange>();

            var query = new SQLQuery(sql);
            query.Parameters.Add("status", (int)RowChangeStatus.none);
            query.ProcessRow = reader =>
            {
                changes.Add(SyncChangeFromDataReader(reader));
                return true;
            };

            SourceDb.RunQuery(query);
            return changes;
        }
Exemple #38
0
        public void Select()
        {
            var query = new SQLQuery<Order>()
                .Select.Add(x => x.CustomerID).Add(x => x.OrderDate)
                .Return();

            string sql = query.Build().SQL;
            Assert.IsTrue(sql.Contains("a.[CustomerID]"));
            Assert.IsTrue(sql.Contains("a.[OrderDate]"));
        }
Exemple #39
0
        public void TruncateDestinationTables()
        {
            var destinationQueryRunner = new QueryRunner(tableSet.DestinationConnectionStringName);
            var destinationQueries = new List<SQLQuery>();

            foreach (var tableMap in tableSet.Mappings.Where(ts=>ts.TruncateDestinationAndBulkLoadFromSource).OrderByDescending(ts => ts.Ordinal))
            {
                Logging.WriteMessageToApplicationLog("About to delete all rows in table " + tableMap.FullyQualifiedDestinationTable, EventLogEntryType.Information);
                var destinationSql = "delete from " + tableMap.FullyQualifiedDestinationTable + ";";
                var destinationQuery = new SQLQuery(destinationSql, SQLQueryType.NonQuery);
                destinationQueries.Add(destinationQuery);
            }
            destinationQueryRunner.RunQuery(destinationQueries, true);
        }
Exemple #40
0
 public void From()
 {
     string sql = new SQLQuery<Product>()
                 .Build()
                 .SQL;
     Assert.IsTrue(sql.Contains("FROM [Product]"));
 }
Exemple #41
0
        private IEnumerable<SyncInfo> GetSyncChangedList()
        {
            string sql = "select distinct `schema`, `table`, `pk1`, `pk2` from "
                + tableSet.FullyQualifiedSyncTableName + " where `datetime` >= DATE_SUB(curdate(), INTERVAL 1 DAY);";
            var syncChangeList = new List<SyncInfo>();
            var syncChangesQuery = new SQLQuery(sql)
            {
                ProcessRow = reader =>
                {
                    syncChangeList.Add(PopulateChangeListFromDataReader(reader));
                    return true;
                }
            };

            sourceDB.RunQuery(syncChangesQuery);
            return syncChangeList;
        }
Exemple #42
0
        private SQLQuery GetUpdateQuery(SyncChange change, List<Tuple<string, object>> data)
        {
            var tableMap = tableSet.GetTableMapping(change);
            var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
            var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
            var updates = tableInfo.Columns.Where(c => !c.PrimaryKey).Select(c => "[" + c.ColumnName + "] = @" + c.ColumnName);
            var sql = "update " + tableMap.FullyQualifiedDestinationTable + " set " + string.Join(", ", updates) + " where " + string.Join(" AND ", whereColumns) + ";";
            var query = new SQLQuery(sql, SQLQueryType.NonQuery);

            AddColumnParametersToQuery(query, tableInfo.Columns, data);
            return query;
        }
Exemple #43
0
 private List<Tuple<string, object>> GetDataFromSource(SyncChange change)
 {
     var tableMap = tableSet.GetTableMapping(change);
     var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
     if (change.Operation == Operation.delete)
     {
         return GetDataForDelete(change);
     }
     string sql = null;
     if (!string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly))
     {
         sql = tableMap.CustomSourceSQLForSyncOnly;
     }
     else
     {
         var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = ?" + c.ColumnName + ")");
         sql = "select * from " + tableMap.FullyQualifiedSourceTable + " where " + string.Join(" AND ", whereColumns) + ";";
     }
     var query = new SQLQuery(sql);
     bool firstKey = true; //yes, it's hacky
     foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID))
     {
         if (sql.Contains("?" + column.ColumnName)) //get rid of this and just throw.....
         {
             query.Parameters.Add(column.ColumnName, firstKey ? change.PrimaryKey1 : change.PrimaryKey2);
         }
         firstKey = false;
     }
     var dataList = new List<Tuple<string, object>>();
     query.ProcessRow = reader =>
     {
         for (int i = 0; i < reader.FieldCount; i++)
         {
             dataList.Add(new Tuple<string, object>(reader.GetName(i), reader[i]));
         }
         return false;
         //return true here, but have a separate variable to see if this gets called again, if so, throw error...
     };
     sourceDb.RunQuery(query);
     return ConvertDataTypes(dataList);
 }
Exemple #44
0
        public void OrderBy()
        {
            var query = new SQLQuery<Order>()
                .From
                    .InnerJoin<Customer>(x => x.CustomerID, x => x.ID)
                    .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID, false)
                .OrderBy.Ascending<Customer>(x => x.Name)
                    .Descending(x => x.OrderDate)
                .Return();

            string sql = query.Build().SQL;
            Assert.IsTrue(sql.Contains("b.[Name]"));
            Assert.IsTrue(sql.Contains("a.[OrderDate] DESC"));
        }
Exemple #45
0
        public void InnerJoin()
        {
            var query = new SQLQuery<Order>()
                .From
                    .InnerJoin<Customer>(x => x.CustomerID, x => x.ID)
                .Return();

            QueryStatement statement = query.Build();
        }
Exemple #46
0
        private SQLQuery GetInsertQuery(SyncChange change, List<Tuple<string, object>> rowData)
        {
            var tableMap = tableSet.GetTableMapping(change);
            var tableInfo = destinationDB.GetTableInfo(tableMap.DestinationSchema, tableMap.DestinationTable);
            var columnList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "[" + c.ColumnName + "]"));
            var parameterList = string.Join(",", tableInfo.Columns.OrderBy(c => c.ColumnID).Select(c => "@" + c.ColumnName));

            var sql = "insert into " + tableMap.FullyQualifiedDestinationTable + " ( " + columnList + ") values (" + parameterList + ");";
            if (tableInfo.Columns.Any(c => c.Identity))
            {
                sql = "set identity_insert " + tableMap.FullyQualifiedDestinationTable + " ON; " + sql + " set identity_insert " + tableMap.FullyQualifiedDestinationTable + " OFF;";
            }

            var query = new SQLQuery(sql, SQLQueryType.NonQuery);
            foreach (var columnInfo in tableInfo.Columns)
            {
                var tuple = rowData.First(t => t.Item1 == columnInfo.ColumnName);
                query.Parameters.Add("@" + columnInfo.ColumnName, tuple.Item2);
            }
            return query;
        }
Exemple #47
0
        public void Where()
        {
            var query = new SQLQuery<Order>()
                .From
                    .InnerJoin<Customer>(x => x.CustomerID, x => x.ID)
                    .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID, false)
                .Where.Equal<Customer>(x => x.Name, "Tim")
                    .Or
                    .START
                        .Equal<Customer, string>(x => x.Name, "Julia")
                        .And
                        .Equal(x => x.ID, 5)
                    .END
                .Return();

            QueryStatement statement = query.Build();

            Assert.AreEqual(3, statement.Parameters.Length);
        }
Exemple #48
0
        public void LeftJoin()
        {
            var query = new SQLQuery<Order>()
                .From.InnerJoin<Customer>(x => x.CustomerID, x => x.ID, false)
                    .LeftOuterJoin<OrderItem>(x => x.ID, x => x.OrderID)
                    .LeftOuterJoin<OrderItem, Product, int>(x => x.ProductID, x => x.ID)
                .Return();

            QueryStatement statement = query.Build();

            Assert.IsTrue(statement.SQL.Contains("LEFT OUTER JOIN"));
        }
Exemple #49
0
        private void GetDataFromTable(SyncInfo reportInfo, TableInfo tableInfo, TableMapping tableMap, bool isSource, string tableName)
        {
            string sql;
            string parameterSymbol = isSource ? "?" : "@";
            bool firstKey = true;

            if (isSource && !string.IsNullOrEmpty(tableMap.CustomSourceSQLForSyncOnly))
            {
                sql = tableMap.CustomSourceSQLForSyncOnly;
            }
            else
            {
                var whereColumns = tableInfo.Columns.Where(c => c.PrimaryKey).Select(c => "(" + c.ColumnName + " = " + parameterSymbol + c.ColumnName + ")");
                sql = "select * from " + tableName + " where " +
                      string.Join(" AND ", whereColumns) + ";";
            }
            var query = new SQLQuery(sql);

            foreach (var column in tableInfo.Columns.Where(c => c.PrimaryKey).OrderBy(c => c.ColumnID))
            {
                if (sql.Contains(parameterSymbol + column.ColumnName))
                {
                    query.Parameters.Add(column.ColumnName, firstKey ? reportInfo.PrimaryKey1 : reportInfo.PrimaryKey2);
                }
                firstKey = false;
            }

            if (isSource)
            {
                query.ProcessRow = reader =>
                {

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        reportInfo.SourceDataList.Insert(i, ConvertDataTypes(reader[i]));
                    }
                    return false;
                };
                sourceDB.RunQuery(query);
            }
            else
            {
                query.ProcessRow = reader =>
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        reportInfo.DestinationDataList.Insert(i, ConvertDataTypes(reader[i]));
                    }
                    return false;
                };
                destinationDB.RunQuery(query);
            }
        }