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

2.3. How to preserve the user formatting of the SQL query text?

It is not possible to preserve comments and formatting in the query that was generated by a query builder, and this behavior can't be changed in the near future. But it is possible to preserve the user formatting 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 the text to the text editor first and call this event handler after that.

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