HomeActive Query Builder - HowTos and TroubleshootingHow-tos for WinForms .NET EditionHow to preserve comments in the SQL query text?

2.3. How to preserve comments in the SQL query text?

It is not possible to preserve comments in the query in the current version. We hope to fix this problem in the near future. But it is possible to preserve comments until the query is not modified visually. To do this, place the following code to the Leave event of the text editor. To load a query to the query builder, assign query text to the text editor first and then call this event handler.

private void sqlTextEditor1_Leave(object sender, EventArgs e)
{
    try
    {
        queryBuilder1.SQL = sqlTextEditor1.Text;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }

    try
    {
        string generatedSql;
        string userSql = sqlTextEditor1.Text;

        SaveEditState();

        SQLSelectStatement selectStatement = null;

        try
        {
            selectStatement = queryBuilder1.SQLContext.ParseSelect(userSql);
            ArrayList lCTE = new ArrayList();
            ArrayList lFromObj = new ArrayList();
            selectStatement.PrepareAndFixupRecursive(lCTE, lFromObj);

            generatedSql = selectStatement.GetSQL(plainTextSQLBuilder1.SQLBuilder);

            if (plainTextSQLBuilder1.SQL != generatedSql)
            {
                queryBuilder1.BeginUpdate();

                try
                {
                    switch (plainTextSQLBuilder1.TargetQueryPart)
                    {
                        case QueryPart.Query:
                            queryBuilder1.Query.Clear();
                            queryBuilder1.Query.LoadFromAST(selectStatement);
                            break;
                        case QueryPart.SubQuery:
                            queryBuilder1.ActiveSubQuery.Clear();
                            queryBuilder1.ActiveSubQuery.LoadFromAST(selectStatement);
                            break;
                        case QueryPart.UnionSubQuery:
                            plainTextSQLBuilder1.SQL = generatedSql;
                            break;
                        default:
                            throw new ArgumentOutOfRangeException();
                    }
                }
                finally
                {
                    queryBuilder1.EndUpdate();
                }
            }
            else
            {
                selectStatement.Dispose();
            }
        }
        catch (Exception)
        {
            selectStatement.Dispose();
            plainTextSQLBuilder1.SQL = userSql;
            generatedSql = plainTextSQLBuilder1.SQL;
        }

        if (plainTextSQLBuilder1.SQL == generatedSql)
        {
            sqlTextEditor1.Text = userSql;

            RestoreEditState();
        }
    }
    catch (Exception exception)
    {
        MessageBox.Show(exception.Message);
    }
}

private int _caretPos = -1;
private int scrollPosX = -1;
private int scrollPosY = -1;

private void SaveEditState()
{
    _caretPos = sqlTextEditor1.SelectionStart;
    scrollPosX = sqlTextEditor1.HorizontalScroll.Value;
    scrollPosY = sqlTextEditor1.VerticalScroll.Value;
}

private void RestoreEditState()
{
    sqlTextEditor1.SetSelection(_caretPos, 0);
    sqlTextEditor1.HorizontalScroll.Value = scrollPosX;
    sqlTextEditor1.VerticalScroll.Value = scrollPosY;
}

This page was: Helpful | Not Helpful