/// <summary>
/// Execute a command that returns multiple result sets, and access each in turn.
/// </summary>
/// <param name="cnn">The connection to query on.</param>
/// <param name="sql">The SQL to execute for this query.</param>
/// <param name="param">The parameters to use for this query.</param>
/// <param name="transaction"> The transaction to use for this query.</param>
/// <param name="commandTimeout">Number of seconds before command execution timeout.</param>
/// <param name="commandType">Is it a stored proc or a batch?</param>
/// <returns></returns>
public static GridReader QueryMultipleOracle(this IDbConnection cnn, string sql, object param, IDbTransaction transaction = null, int?commandTimeout = null, CommandType?commandType = null)
{
string pattern = @"(?:SELECT|INSERT)[\s\S]*?(?=\""|\;)";
string oracleSql = "BEGIN";
OracleDynamicParameters oracleParam = new OracleDynamicParameters(param);
foreach (Match m in Regex.Matches(sql, pattern, RegexOptions.Multiline))
{
oracleSql += $" OPEN :cursor{m.Index} FOR {m.Value};";
oracleParam.AddRefCursorParameters($"cursor{m.Index}");
}
oracleSql += " END;";
return(cnn.QueryMultiple(oracleSql, oracleParam, transaction, commandTimeout, commandType));
}
public virtual string PagedQuery(string fullSqlQuery, ISearchFilter filter, ref object param)
{
SqlBuilder sqlBuilder = new SqlBuilder();
SqlBuilder.Template sqlTemplate = sqlBuilder.AddTemplate(string.Format(PagingQueryTemplate, fullSqlQuery, filter.Offset, filter.PageSize));
if (!string.IsNullOrWhiteSpace(filter.SqlOrderByExpression))
{
sqlBuilder.OrderBy(filter.SqlOrderByExpression);
}
if (!filter.IncludeMetadata)
{
return(sqlTemplate.RawSql);
}
string totalCountQuery = SqlSelectClauseRegex.Replace(fullSqlQuery, m => $"{m.Groups[1]} {SqlTotalCountSelectClauseValue} {m.Groups[3]}", 1);
string[] cursorsName = new string[] { "main_cursor", "counter_cursor" };
if (param == null)
{
param = new OracleDynamicParameters(cursorsName);
}
else
{
OracleDynamicParameters oracleParam = new OracleDynamicParameters((DynamicParameters)param);
oracleParam.AddRefCursorParameters(cursorsName);
param = oracleParam;
}
return($@"BEGIN
OPEN :main_cursor FOR {sqlTemplate.RawSql};
{Environment.NewLine}
OPEN :counter_cursor FOR {totalCountQuery};
END;");
}
