/// <summary>
/// Creates an closed sub select
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="subSelect">The sub select.</param>
/// <returns></returns>
public static SelectQuery <T> SubSelect <T>(this RootQuery query,
Func <SelectQuery <T>, SelectQuery <T> > subSelect)
{
var q = subSelect(query
.QueryText("(")
.Select.Table <T>());
return(q.QueryText(")"));
}
/// <summary>
/// Sets an Variable to the given value
/// </summary>
/// <param name="query">The query.</param>
/// <param name="name">The name.</param>
/// <param name="value">The value.</param>
/// <returns></returns>
public static RootQuery SetVariable(this RootQuery query, string name, object value)
{
var transpiledValue = MsSql.ParameterValue(new SqlParameter(name, value));
var sqlName = name;
if (!sqlName.StartsWith("@"))
{
sqlName = "@" + sqlName;
}
query.QueryText("SET {0} = {1}", sqlName, transpiledValue);
return(query);
}
/// <summary>
/// Adds a between statement followed by a query defined in <paramref name="valA" /> folowed by an and statement and
/// an secound query defined in the <paramref name="valB" />
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <param name="valA">The value a.</param>
/// <param name="valB">The value b.</param>
/// <returns></returns>
public static ConditionalEvalQuery <T> Between <T>(
this ConditionalOperatorQuery <T> query,
Func <RootQuery, RootQuery> valA,
Func <RootQuery, RootQuery> valB)
{
var condtion = new RootQuery(query.QueryText("BETWEEN"));
condtion = valA(new RootQuery(condtion));
condtion = condtion.QueryText("AND");
condtion = valA(new RootQuery(condtion));
return(new ConditionalEvalQuery <T>(condtion, query.State));
}
public static IQueryBuilder Callup(RootQuery builder, string tableName, string database)
{
return(builder.QueryText("SELECT ccu.column_name AS SourceColumn ,kcu.table_name AS TargetTable ,kcu.column_name AS TargetColumn")
.QueryText("FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu")
.QueryText("INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc")
.QueryText("ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME")
.QueryText("INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu")
.QueryText("ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME")
.QueryD("WHERE ccu.TABLE_NAME = @tableName AND ccu.TABLE_CATALOG = @database", new
{
database = database,
tableName = tableName
})
.QueryText("ORDER BY ccu.table_name"));
}
/// <summary>
/// Creates a Common Table Expression that selects a Specific type
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TN">The type of the n.</typeparam>
/// <param name="query">The query.</param>
/// <param name="cteName">Name of the cte.</param>
/// <param name="subCte">if set to <c>true</c> [sub cte].</param>
/// <returns></returns>
public static ElementProducer <TN> AsCte <T, TN>(this ElementProducer <T> query,
string cteName,
bool subCte = false)
{
var cp = new RootQuery(query.ContainerObject.AccessLayer);
var prefix = string.Format("WITH {0} AS (", cteName);
cp = cp.QueryText(prefix);
foreach (var genericQueryPart in query.ContainerObject.Parts)
{
cp = cp.Add(genericQueryPart);
}
return(new ElementProducer <TN>(cp.Add(new CteQueryPart(")")).QueryText(string.Format("SELECT {1} FROM {0}", cteName,
query.ContainerObject.AccessLayer.Config.GetOrCreateClassInfoCache(typeof(TN))
.GetSchemaMapping()
.Aggregate((e, f) => e + ", " + f))), query.CurrentIdentifier));
}
/// <summary>
/// Declares a new Variable of the Given SQL Type by using its length
/// </summary>
/// <param name="query">The query.</param>
/// <param name="name">The name.</param>
/// <param name="type">The type.</param>
/// <param name="length">The length.</param>
/// <param name="value">The value.</param>
/// <returns></returns>
public static RootQuery DeclareVariable(this RootQuery query, string name, SqlDbType type, int length = int.MaxValue,
object value = null)
{
var sqlName = name;
if (!sqlName.StartsWith("@"))
{
sqlName = "@" + sqlName;
}
var typeName = type.ToString();
if (new SqlParameter("xxx", type).Size > 0)
{
typeName = "(MAX)";
}
query = query.QueryText("DECLARE {0} {1};", sqlName, typeName);
if (value != null)
{
query = query.SetVariable(sqlName, value);
}
return(query);
}
/// <summary>
/// Creates an TSQL Count(1) statement
/// </summary>
/// <typeparam name="TPoco">The type of the poco.</typeparam>
/// <param name="query">The query.</param>
/// <returns></returns>
public static ElementProducer <long> Count <TPoco>(this RootQuery query)
{
return(new ElementProducer <long>(query.QueryText("SELECT COUNT(1) FROM {0}",
query.ContainerObject.AccessLayer.Config.GetOrCreateClassInfoCache(typeof(TPoco)).TableName), null));
}
/// <summary>
/// Creates an TSQL Apply statement
/// </summary>
/// <param name="query">The query.</param>
/// <param name="mode">The mode.</param>
/// <param name="innerText">The inner text.</param>
/// <returns></returns>
public static RootQuery Apply(this RootQuery query,
ApplyMode mode,
Func <RootQuery, IQueryBuilder> innerText)
{
return(new RootQuery(query.QueryText(mode.ApplyType).InBracket(innerText)));
}
/// <summary>
/// Creates a QueryCommand that uses the * Operator to select all date from the inner query
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="query">The query.</param>
/// <returns></returns>
public static SelectQuery <T> SelectStar <T>(this RootQuery query)
{
return
(new SelectQuery <T>(
query.QueryText("SELECT * FROM " + query.ContainerObject.AccessLayer.GetClassInfo(typeof(T)).TableName)));
}
