修改NHibernate中的SQL
本文关键字:SQL 中的 NHibernate 修改 | 更新日期: 2023-09-27 18:29:33
我需要知道如何修改一个sql,为每个选择添加一个sql代码。我的问题是,我需要在"FROM table"+"sql_code"之后添加这个sql代码,即使在"like a GROUP BY"之后有一些内容。
我知道我需要使用OnPrepareStatement,我只是不知道如何编辑SqlString!
我试过一件事:
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());
}
谢谢!
我参加聚会有点晚了,但以下是我如何看待我们的一些问题。下面采用给定的sql语句,并在末尾添加一点:
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();
}
}
有点太晚了,但仍然如此。我不得不在生成的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;
}
}