CODEWITHSUNDEEP
c#sqlnhibernate
Leonardo
Leonardo

Reputation: 3191

Modify SQL in NHibernate

I need to know how to modify a sql, to add to every select a sql code. My problem is, I need to add this sql code after a "FROM table" + " sql_code", even if there is something after "like a GROUP BY".

I know that I need to use the OnPrepareStatement, I just don't know how to edit the SqlString to do so !

One thing that I've tried:

public override SqlString OnPrepareStatement(SqlString sql)
    {
       if (sql.Parts.Cast<string>().FirstOrDefault().Trim().ToLower() != "select")
        {
            return sql;
        }
        StringBuilder novaString = new StringBuilder(sql.ToString());
        String corte = sql.SubstringStartingWithLast("where").ToString();
        if (corte != "")
        {
            StringBuilder sb = new StringBuilder(corte);
            sb.Insert(0, "with(nolock) ");
            novaString.Replace(corte, sb.ToString());
        }
        else
        {
            novaString.Append(" with(nolock)");
        }
        return new SqlString(novaString.ToString());
    }

Thanks !

Upvotes: 2

Views: 1837

Answers (2)

athabaska
athabaska

Reputation: 455

A bit too late, but still. I had to add a hint into generated sql:

private class SqlLogger : EmptyInterceptor
{
    private const string Select = "SELECT";
    private const int SelectLen = 6;
    private const string Hint = " /*+ dynamic_sampling(0) */";
    private const char Param = '?';

    public override SqlString OnPrepareStatement(SqlString sql)
    {
        var s = sql.ToString().ToUpper();
        var start = s.IndexOf(Select, StringComparison.Ordinal);
        if (start != -1)
            s = s.Insert(SelectLen, Hint);

        var builder = new SqlStringBuilder();
        var parts = s.Split(Param);
        for (var i = 0; i < parts.Length - 1; i++)
        {
            builder.Add(parts[i]);
            builder.AddParameter();
        }
        builder.Add(parts.Last());
        sql = builder.ToSqlString();

        return sql;
    }
}

Upvotes: 0

mcfea
mcfea

Reputation: 1139

I'm a little late to the party but here is how I optomize some of our queries. The below takes a given sql statement and appends a little bit to the end:

public class OptionInterceptor: EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        var parameters = sql.GetParameters();
        var paramCount = parameters.Count();

        if (paramCount > 0)
            return sql;

        string optionString = " OPTION (OPTIMIZE FOR (";

        for (var i = 0; i < paramCount; i++)
        {
            var comma = i > 0 ? "," : string.Empty;
            optionString = optionString + comma + "@p" + i + " UNKNOWN";
        }

        optionString = optionString + "))";

        var builder = new SqlStringBuilder(sql);

        builder.Add(optionString);

        return builder.ToSqlString();
    }
}

Upvotes: 1

Related Questions