/// <summary>
/// Creates a new DynamicSQLinq object for the specified table name.
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static DynamicSQLinq Create(string tableName)
{
// initialize the Default ISqlDialect
var dialect = DialectProvider.Create();
return(Create(tableName, dialect));
}
/// <summary>
/// Creates a new SQLinqInsert object for the specified Object.
/// </summary>
/// <param name="data"></param>
/// <returns></returns>
public static SQLinqInsert <T> Insert <T>(T data)
{
// initialize the Default ISqlDialect
var dialect = DialectProvider.Create();
return(Insert <T>(data, dialect));
}
/// <summary>
/// Creates a new SQLinqInsert object for the specified Object and table name.
/// </summary>
/// <param name="data"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static SQLinqUpdate <T> Update <T>(T data, string tableName)
{
// initialize the Default ISqlDialect
var dialect = DialectProvider.Create();
return(Update <T>(data, tableName, dialect));
}
/// <summary>
/// Creates a new SQLinq object for the Type of the object specified.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="obj">The object that defines the Type to use for creating the SQLinq object instance for.</param>
/// <returns></returns>
public static SQLinq <T> Create <T>(T obj, string tableName)
{
// initialize the Default ISqlDialect
var dialect = DialectProvider.Create();
return(Create <T>(obj, tableName, dialect));
}
public static SQLinqUpdate <T> ToSQLinqUpdate <T>(this T obj, string tableName = null, ISqlDialect dialect = null)
{
if (dialect == null)
{
dialect = DialectProvider.Create();
}
return(SQLinqHelper.Update(obj, tableName, dialect));
}
public SQLinqInsertResult()
: this(DialectProvider.Create())
{
}
public SQLinqUpdate(T data)
: this(data, DialectProvider.Create())
{
}
public string ToQuery(SQLinqSelectResult selectResult)
{
var orderby = DialectProvider.ConcatFieldArray(selectResult.OrderBy);
var groupby = DialectProvider.ConcatFieldArray(selectResult.GroupBy);
var sb = new StringBuilder();
if (selectResult.Distinct == true)
{
sb.Append("DISTINCT ");
}
// SELECT
sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
if (selectResult.Skip != null)
{
if (sb.Length > 0)
{
sb.Append(",");
}
sb.Append(string.Format(" ROW_NUMBER() OVER (ORDER BY {0}) AS [SQLinq_row_number]", orderby));
}
sb.Append(" FROM ");
if (selectResult.Distinct == true && selectResult.Skip != null && selectResult.Take != null)
{
sb.Append("(SELECT DISTINCT ");
sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
sb.Append(" FROM ");
sb.Append(selectResult.Table);
sb.Append(") AS d");
}
else
{
sb.Append(selectResult.Table);
}
if (selectResult.Join != null)
{
foreach (var j in selectResult.Join)
{
sb.Append(_Space);
sb.Append(j);
}
}
if (!string.IsNullOrEmpty(selectResult.Where))
{
sb.Append(" WHERE ");
sb.Append(selectResult.Where);
}
if (!string.IsNullOrEmpty(groupby))
{
sb.Append(" GROUP BY ");
sb.Append(groupby);
}
if (!string.IsNullOrEmpty(selectResult.Having))
{
sb.Append(" HAVING ");
sb.Append(selectResult.Having);
}
var sqlOrderBy = string.Empty;
if (orderby.Length > 0)
{
sqlOrderBy = " ORDER BY " + orderby;
}
if (selectResult.Skip != null)
{
// paging support
var start = (selectResult.Skip + 1).ToString();
var end = (selectResult.Skip + (selectResult.Take ?? 0)).ToString();
if (selectResult.Take == null)
{
if (selectResult.Distinct == true)
{
return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] >= {1} ORDER BY [SQLinq_row_number]", sb.ToString(), start));
}
else
{
return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] >= {1}", sb.ToString(), start));
}
}
return(string.Format(@"WITH SQLinq_data_set AS (SELECT {0}) SELECT * FROM SQLinq_data_set WHERE [SQLinq_row_number] BETWEEN {1} AND {2}", sb.ToString(), start, end));
}
else if (selectResult.Take != null)
{
var sbQuery = sb.ToString();
if (sbQuery.ToLower().StartsWith("distinct "))
{
return("SELECT DISTINCT TOP " + selectResult.Take.ToString() + _Space + sbQuery.Substring(9) + sqlOrderBy);
}
else
{
return("SELECT TOP " + selectResult.Take.ToString() + _Space + sbQuery + sqlOrderBy);
}
}
return("SELECT " + sb.ToString() + sqlOrderBy);
}
public SQLinqInsert(T data)
: this(data, DialectProvider.Create())
{
}
public string ToQuery(SQLinqSelectResult selectResult)
{
var orderby = DialectProvider.ConcatFieldArray(selectResult.OrderBy);
var groupby = DialectProvider.ConcatFieldArray(selectResult.GroupBy);
var sb = new StringBuilder();
if (selectResult.Distinct == true)
{
sb.Append("DISTINCT ");
}
// SELECT
sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
////if (selectResult.Skip != null)
////{
//// if (sb.Length > 0)
//// {
//// sb.Append(",");
//// }
//// sb.Append(string.Format(" ROW_NUMBER() OVER (ORDER BY {0}) AS [SQLinq_row_number]", orderby));
////}
sb.Append(" FROM ");
if (selectResult.Distinct == true && selectResult.Skip != null && selectResult.Take != null)
{
sb.Append("(SELECT DISTINCT ");
sb.Append(DialectProvider.ConcatFieldArray(selectResult.Select));
sb.Append(" FROM ");
sb.Append(selectResult.Table);
sb.Append(") AS d");
}
else
{
sb.Append(selectResult.Table);
}
if (selectResult.Join != null)
{
foreach (var j in selectResult.Join)
{
sb.Append(_Space);
sb.Append(j);
}
}
if (!string.IsNullOrEmpty(selectResult.Where))
{
sb.Append(" WHERE ");
sb.Append(selectResult.Where);
}
if (!string.IsNullOrEmpty(groupby))
{
sb.Append(" GROUP BY ");
sb.Append(groupby);
}
if (!string.IsNullOrEmpty(selectResult.Having))
{
sb.Append(" HAVING ");
sb.Append(selectResult.Having);
}
var sqlOrderBy = string.Empty;
if (orderby.Length > 0)
{
sqlOrderBy = " ORDER BY " + orderby;
}
if (selectResult.Skip != null)
{
// paging support
var start = (selectResult.Skip + 1).ToString();
var end = (selectResult.Skip + (selectResult.Take ?? 0)).ToString();
if (selectResult.Take == null)
{
return(string.Format("SELECT * FROM (SELECT {0}) WHERE ROWNUM >= {1}", sb.ToString(), start));
}
return(string.Format("SELECT SQLinq_Outer.* FROM (SELECT ROWNUM rn, SQLinq_Inner.* FROM (SELECT {0}) SQLinq_Inner) SQLinq_Outer WHERE SQLinq_Outer.rn >= {1} AND SQLinq_Outer.rn <= {2}",
sb.ToString(),
start,
end));
}
else if (selectResult.Take != null)
{
return(string.Format("SELECT * FROM (SELECT {0}) WHERE ROWNUM <= {1}", sb.ToString(), selectResult.Take.ToString()));
}
return("SELECT " + sb.ToString() + sqlOrderBy);
}
/// <summary>
/// Creates a new SQLinq object
/// </summary>
/// <param name="tableNameOverride">The database table name to use. This explicitly overrides any use of the SQLinqTable attribute.</param>
public SQLinq(string tableNameOverride)
: this(tableNameOverride, DialectProvider.Create())
{
}
/// <summary>
/// Creates a new SQLinq object
/// </summary>
public SQLinq()
: this(DialectProvider.Create())
{
}
