コード例 #1
0
ファイル: AppDBMySQL.cs プロジェクト: martinvd/QueryDesk
        public long SaveQuery(AppDBQueryLink query)
        {
            MySqlCommand qry;

            if (query.id > 0)
            {
                // update
                qry = new MySqlCommand("update query set name=?name, sqltext=?sqltext, connection_id=?connection_id where id=?id", db);
                qry.Parameters.AddWithValue("?id", query.id);
            }
            else
            {
                // insert
                qry = new MySqlCommand("insert into query ( connection_id, name, sqltext) values (?connection_id,?name,?sqltext); select last_insert_id();", db);
            }

            qry.Parameters.AddWithValue("?connection_id", query.connection_id);
            qry.Parameters.AddWithValue("?name", query.name);
            qry.Parameters.AddWithValue("?sqltext", query.sqltext);

            qry.ExecuteNonQuery();

            if (query.id <= 0)
            {
                query.id = qry.LastInsertedId;
            }

            return(query.id);
        }
コード例 #2
0
ファイル: AppDBSQLite.cs プロジェクト: martinvd/QueryDesk
        public long SaveQuery(AppDBQueryLink query)
        {
            SQLiteCommand qry;

            if (query.id > 0)
            {
                // update
                qry = new SQLiteCommand("update query set name=@name, sqltext=@sqltext, connection_id=@connection_id where id=@id", db);
                qry.Parameters.AddWithValue("@id", query.id);
            }
            else
            {
                // insert
                qry = new SQLiteCommand("insert into query ( connection_id, name, sqltext) values (@connection_id,@name,@sqltext);", db);
            }

            qry.Parameters.AddWithValue("@connection_id", query.connection_id);
            qry.Parameters.AddWithValue("@name", query.name);
            qry.Parameters.AddWithValue("@sqltext", query.sqltext);

            qry.ExecuteNonQuery();

            if (query.id <= 0)
            {
                qry.CommandText = "select last_insert_rowid()";
                query.id        = (long)qry.ExecuteScalar();
            }

            return(query.id);
        }
コード例 #3
0
        public void Initialize(AppDBQueryLink linkQueryRow, IQueryableConnection connection)
        {
            currentQuery = linkQueryRow;
            completionHelper = QueryComposerResources.ComposerHelper(connection);

            Reset();
        }
コード例 #4
0
        public void Initialize(AppDBQueryLink linkQueryRow, IQueryableConnection connection)
        {
            currentQuery     = linkQueryRow;
            completionHelper = QueryComposerResources.ComposerHelper(connection);

            Reset();
        }
コード例 #5
0
ファイル: AppDBMySQL.cs プロジェクト: GDKsoftware/QueryDesk
        public void DelQuery(AppDBQueryLink query)
        {
            MySqlCommand qry;

            if (query.id > 0)
            {
                qry = new MySqlCommand("delete from query where id=?id", db);
                qry.Parameters.AddWithValue("?id", query.id);

                qry.ExecuteNonQuery();

                query.id = 0;
            }
            else
            {
                // ???
            }
        }
コード例 #6
0
ファイル: AppDBSQLite.cs プロジェクト: martinvd/QueryDesk
        public void DelQuery(AppDBQueryLink query)
        {
            SQLiteCommand qry;

            if (query.id > 0)
            {
                qry = new SQLiteCommand("delete from query where id=@id", db);
                qry.Parameters.AddWithValue("@id", query.id);

                qry.ExecuteNonQuery();

                query.id = 0;
            }
            else
            {
                // @@@
            }
        }
コード例 #7
0
ファイル: AppDBMySQL.cs プロジェクト: martinvd/QueryDesk
        public void DelQuery(AppDBQueryLink query)
        {
            MySqlCommand qry;

            if (query.id > 0)
            {
                qry = new MySqlCommand("delete from query where id=?id", db);
                qry.Parameters.AddWithValue("?id", query.id);

                qry.ExecuteNonQuery();

                query.id = 0;
            }
            else
            {
                // ???
            }
        }
コード例 #8
0
ファイル: AppDBSQLite.cs プロジェクト: GDKsoftware/QueryDesk
        public void DelQuery(AppDBQueryLink query)
        {
            SQLiteCommand qry;

            if (query.id > 0)
            {
                qry = new SQLiteCommand("delete from query where id=@id", db);
                qry.Parameters.AddWithValue("@id", query.id);

                qry.ExecuteNonQuery();

                query.id = 0;
            }
            else
            {
                // @@@
            }
        }
コード例 #9
0
        private void btnAddQuery_Click(object sender, RoutedEventArgs e)
        {
            var link = new AppDBQueryLink(new AppDBDummyQuery(0, connectionId, "New Query", string.Empty));
            var frm  = new frmQueryEdit();

            frm.Initialize(link, DBConnection);

            bool?b = frm.ShowDialog();

            if (b == true)
            {
                btnRefreshQuery.IsEnabled = false;

                var editable = (IAppDBEditableQueries)appDB;
                editable.SaveQuery(link);

                Reload();
            }
        }
コード例 #10
0
        private void btnDelQuery_Click(object sender, RoutedEventArgs e)
        {
            var row = cmbQueries.SelectedItem;

            if (row != null)
            {
                var link = new AppDBQueryLink(row);

                // ask to be sure user hit the right button
                var r = MessageBox.Show("Are you sure you want to delete this query?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question);
                if (r == MessageBoxResult.Yes)
                {
                    btnRefreshQuery.IsEnabled = false;

                    // AppDB needs to be editable in order to have saveQuery and delQuery functions,
                    //  but this button will be disabled if it's not editable, so we can just blindly do a typecast it here
                    var editable = (IAppDBEditableQueries)appDB;
                    editable.DelQuery(link);

                    Reload();
                }
            }
        }
コード例 #11
0
        private void btnEditQuery_Click(object sender, RoutedEventArgs e)
        {
            var row = cmbQueries.SelectedItem;

            if (row != null)
            {
                var link = new AppDBQueryLink(row);

                var frm = new frmQueryEdit();
                frm.Initialize(link, DBConnection);

                bool?b = frm.ShowDialog();
                if (b == true)
                {
                    btnRefreshQuery.IsEnabled = false;

                    var editable = (IAppDBEditableQueries)appDB;
                    editable.SaveQuery(link);

                    // we don't have to do Reload() here, the object (row) should be edited directly, but we still need to reset CurrentQuery (used by GoQuery button)
                    cmbQueries_SelectionChanged(cmbQueries, null);
                }
            }
        }
コード例 #12
0
        private void cmbQueries_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            btnRefreshQuery.IsEnabled = false;

            var row = cmbQueries.SelectedItem;

            if (row != null)
            {
                var link = new AppDBQueryLink(row);
                currentQuery = null;
                edSQL.Text   = link.sqltext;

                // Disable query action buttons
                enableQueryActionControls(true);
            }
            else
            {
                currentQuery = null;
                edSQL.Text   = string.Empty;

                // Disable query action buttons
                enableQueryActionControls(false);
            }
        }
コード例 #13
0
        private void btnEditQuery_Click(object sender, RoutedEventArgs e)
        {
            var row = cmbQueries.SelectedItem;
            if (row != null)
            {
                var link = new AppDBQueryLink(row);

                var frm = new frmQueryEdit();
                frm.Initialize(link, DBConnection);

                bool? b = frm.ShowDialog();
                if (b == true)
                {
                    btnRefreshQuery.IsEnabled = false;

                    var editable = (IAppDBEditableQueries)appDB;
                    editable.SaveQuery(link);

                    // we don't have to do Reload() here, the object (row) should be edited directly, but we still need to reset CurrentQuery (used by GoQuery button)
                    cmbQueries_SelectionChanged(cmbQueries, null);
                }
            }
        }
コード例 #14
0
ファイル: AppDBSQLite.cs プロジェクト: GDKsoftware/QueryDesk
        public long SaveQuery(AppDBQueryLink query)
        {
            SQLiteCommand qry;

            if (query.id > 0)
            {
                // update
                qry = new SQLiteCommand("update query set name=@name, sqltext=@sqltext, connection_id=@connection_id where id=@id", db);
                qry.Parameters.AddWithValue("@id", query.id);
            }
            else
            {
                // insert
                qry = new SQLiteCommand("insert into query ( connection_id, name, sqltext) values (@connection_id,@name,@sqltext);", db);
            }

            qry.Parameters.AddWithValue("@connection_id", query.connection_id);
            qry.Parameters.AddWithValue("@name", query.name);
            qry.Parameters.AddWithValue("@sqltext", query.sqltext);

            qry.ExecuteNonQuery();

            if (query.id <= 0)
            {
                qry.CommandText = "select last_insert_rowid()";
                query.id = (long)qry.ExecuteScalar();
            }

            return query.id;
        }
コード例 #15
0
        private void btnGoQuery_Click(object sender, RoutedEventArgs e)
        {
            btnRefreshQuery.IsEnabled = false;

            // parse query parameters
            var row = cmbQueries.SelectedItem;

            if (row != null)
            {
                var link = new AppDBQueryLink(row);
                if (currentQuery == null)
                {
                    currentQuery = new StoredQuery(link.sqltext);
                }

                // Aks for query parameters and save them
                if (!AskForParameters(currentQuery))
                {
                    // Return if parameter input was canceled
                    return;
                }

                currentQuery.RewriteParameters(DBConnection.GetParamPrefixChar());

                // Processs query parameters
                edSQL.Text = ProcessParameters(currentQuery);

                barQuery.Items.Clear();

                var hyjackquery = false;

                CExplainableQuery expl = QueryExplanationFactory.NewExplain(DBConnection, currentQuery);
                if (expl != null)
                {
                    if (expl.HasErrors())
                    {
                        MessageBox.Show(expl.GetErrorMsg(), "Query error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }
                    else if (!expl.IsAllIndexed())
                    {
                        if (MessageBox.Show("This query does not fully make use of indexes, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                    else if (expl.IsUsingBadStuff())
                    {
                        if (MessageBox.Show("This query could take a long time to run, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                    else if (expl.GetMaxResults() >= 65535)
                    {
                        if (MessageBox.Show("This query could possibly return a lot of rows, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                }

                if (hyjackquery)
                {
                    // execute the explain instead (only works for mysql queries)
                    var qryExplain = expl._get();

                    DBConnection.Query(qryExplain);
                }
                else
                {
                    // execute query and get result set
                    DBConnection.Query(currentQuery);
                }

                DataTable dt;
                try
                {
                    // todo: datatable contains all results, no cursor/rowtravel/stream
                    dt = DBConnection.ResultsAsDataTable();

                    // Enable Refresh query button
                    btnRefreshQuery.IsEnabled = true;

                    // Enable Feed to action
                    btnFeedToAction.IsEnabled = true;
                }
                catch (Exception x)
                {
                    // todo: handle query errors in a better way
                    MessageBox.Show(x.Message);
                    return;
                }

                // display results in datagrid
                gridQueryResults.AutoGenerateColumns = true;

                if (dt != null)
                {
                    gridQueryResults.ItemsSource = dt.DefaultView;

                    string s = "Results: " + dt.Rows.Count;
                    barQuery.Items.Add(s);
                }
                else
                {
                    gridQueryResults.ItemsSource = null;
                    string s = "No results";
                    barQuery.Items.Add(s);
                }
            }
        }
コード例 #16
0
        private void cmbQueries_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            btnRefreshQuery.IsEnabled = false;

            var row = cmbQueries.SelectedItem;
            if (row != null)
            {
                var link = new AppDBQueryLink(row);
                currentQuery = null;
                edSQL.Text = link.sqltext;

                // Disable query action buttons
                enableQueryActionControls(true);
            }
            else
            {
                currentQuery = null;
                edSQL.Text = string.Empty;

                // Disable query action buttons
                enableQueryActionControls(false);
            }
        }
コード例 #17
0
        private void btnGoQuery_Click(object sender, RoutedEventArgs e)
        {
            btnRefreshQuery.IsEnabled = false;

            // parse query parameters
            var row = cmbQueries.SelectedItem;
            if (row != null)
            {
                var link = new AppDBQueryLink(row);
                if (currentQuery == null)
                {
                    currentQuery = new StoredQuery(link.sqltext);
                }

                // Aks for query parameters and save them
                if (!AskForParameters(currentQuery))
                {
                    // Return if parameter input was canceled
                    return;
                }

                currentQuery.RewriteParameters(DBConnection.GetParamPrefixChar());

                // Processs query parameters
                edSQL.Text = ProcessParameters(currentQuery);

                barQuery.Items.Clear();

                var hyjackquery = false;

                CExplainableQuery expl = QueryExplanationFactory.NewExplain(DBConnection, currentQuery);
                if (expl != null)
                {
                    if (expl.HasErrors())
                    {
                        MessageBox.Show(expl.GetErrorMsg(), "Query error", MessageBoxButton.OK, MessageBoxImage.Error);
                        return;
                    }
                    else if (!expl.IsAllIndexed())
                    {
                        if (MessageBox.Show("This query does not fully make use of indexes, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                    else if (expl.IsUsingBadStuff())
                    {
                        if (MessageBox.Show("This query could take a long time to run, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                    else if (expl.GetMaxResults() >= 65535)
                    {
                        if (MessageBox.Show("This query could possibly return a lot of rows, are you sure you want to execute this query?", "Query warning", MessageBoxButton.YesNo, MessageBoxImage.Question) == MessageBoxResult.No)
                        {
                            hyjackquery = true;
                        }
                    }
                }

                if (hyjackquery)
                {
                    // execute the explain instead (only works for mysql queries)
                    var qryExplain = expl._get();

                    DBConnection.Query(qryExplain);
                }
                else
                {
                    // execute query and get result set
                    DBConnection.Query(currentQuery);
                }

                DataTable dt;
                try
                {
                    // todo: datatable contains all results, no cursor/rowtravel/stream
                    dt = DBConnection.ResultsAsDataTable();

                    // Enable Refresh query button
                    btnRefreshQuery.IsEnabled = true;

                    // Enable Feed to action
                    btnFeedToAction.IsEnabled = true;
                }
                catch (Exception x)
                {
                    // todo: handle query errors in a better way
                    MessageBox.Show(x.Message);
                    return;
                }

                // display results in datagrid
                gridQueryResults.AutoGenerateColumns = true;

                if (dt != null)
                {
                    gridQueryResults.ItemsSource = dt.DefaultView;

                    string s = "Results: " + dt.Rows.Count;
                    barQuery.Items.Add(s);
                }
                else
                {
                    gridQueryResults.ItemsSource = null;
                    string s = "No results";
                    barQuery.Items.Add(s);
                }
            }
        }
コード例 #18
0
ファイル: AppDBMySQL.cs プロジェクト: GDKsoftware/QueryDesk
        public long SaveQuery(AppDBQueryLink query)
        {
            MySqlCommand qry;

            if (query.id > 0)
            {
                // update
                qry = new MySqlCommand("update query set name=?name, sqltext=?sqltext, connection_id=?connection_id where id=?id", db);
                qry.Parameters.AddWithValue("?id", query.id);
            }
            else
            {
                // insert
                qry = new MySqlCommand("insert into query ( connection_id, name, sqltext) values (?connection_id,?name,?sqltext); select last_insert_id();", db);
            }

            qry.Parameters.AddWithValue("?connection_id", query.connection_id);
            qry.Parameters.AddWithValue("?name", query.name);
            qry.Parameters.AddWithValue("?sqltext", query.sqltext);

            qry.ExecuteNonQuery();

            if (query.id <= 0)
            {
                query.id = qry.LastInsertedId;
            }

            return query.id;
        }
コード例 #19
0
        private void btnDelQuery_Click(object sender, RoutedEventArgs e)
        {
            var row = cmbQueries.SelectedItem;
            if (row != null)
            {
                var link = new AppDBQueryLink(row);

                // ask to be sure user hit the right button
                var r = MessageBox.Show("Are you sure you want to delete this query?", "Confirm", MessageBoxButton.YesNo, MessageBoxImage.Question);
                if (r == MessageBoxResult.Yes)
                {
                    btnRefreshQuery.IsEnabled = false;

                    // AppDB needs to be editable in order to have saveQuery and delQuery functions,
                    //  but this button will be disabled if it's not editable, so we can just blindly do a typecast it here
                    var editable = (IAppDBEditableQueries)appDB;
                    editable.DelQuery(link);

                    Reload();
                }
            }
        }
コード例 #20
0
        private void btnAddQuery_Click(object sender, RoutedEventArgs e)
        {
            var link = new AppDBQueryLink(new AppDBDummyQuery(0, connectionId, "New Query", string.Empty));
            var frm = new frmQueryEdit();
            frm.Initialize(link, DBConnection);

            bool? b = frm.ShowDialog();
            if (b == true)
            {
                btnRefreshQuery.IsEnabled = false;

                var editable = (IAppDBEditableQueries)appDB;
                editable.SaveQuery(link);

                Reload();
            }
        }