protected virtual string BuildSelectText()
{
return("SELECT "
+ SelectModifier + " "
+ GetFieldList()
+ " FROM " + SqlTable
+ (SqlConditionals.Count == 0 ? ""
: " WHERE " + SqlConditionals.Join(" AND "))
+ (OrderBys.Count == 0 ? ""
: " ORDER BY " + OrderBys.Join(",")));
}
protected virtual TCmd BuildDelete()
{
TCmd cmd = new TCmd();
cmd.CommandText = "DELETE FROM " + SqlTable
+ (SqlConditionals.Count == 0 ? ""
: " WHERE " + SqlConditionals.Select(sc => sc.Replace("@", "@" + ParamUniqueKey)).Join(" AND "));
SetParameters(cmd);
return(cmd);
}
protected virtual TCmd BuildUpdate()
{
if (SqlSets.Count == 0)
{
return(null);
}
TCmd cmd = new TCmd();
cmd.CommandText = "UPDATE " + SqlTable
+ " SET "
+ SqlSets.Where(kvp => !PreservedFields.Contains(kvp.Key))
.Select(kvp => QuoteName(kvp.Key) + " = @" + ParamUniqueKey + "S_" + kvp.Key)
.Join(", ")
+ (SqlConditionals.Count == 0 ? ""
: " WHERE " + SqlConditionals.Select(sc => sc.Replace("@", "@" + ParamUniqueKey)).Join(" AND "));
SetParameters(cmd);
SetUpdateParameters(cmd, true);
return(cmd);
}
protected virtual TCmd BuildPagedSelect(int skip, int take, string idField, Type idType,
string sortField, Type sortType)
{
var subBuilder = new QueryBuilder <TConn, TCmd, TParam, TReader>(ConnectionString);
subBuilder.SqlFields = this.SqlFields.ToList();
subBuilder.SqlTable = this.SqlTable;
subBuilder.SqlConditionals = this.SqlConditionals.ToList();
subBuilder.OrderBys = this.OrderBys.ToList();
string wherePart = (SqlConditionals.Count == 0 ? ""
: " WHERE " + SqlConditionals.Join(" AND "));
if (skip == 0)
{
subBuilder.SelectModifier = "TOP " + take.ToString();
var selCmd = subBuilder.BuildSelect();
selCmd.CommandText += string.Format("; SELECT Count(*) FROM {0}{1};", subBuilder.SqlTable, wherePart);
SetParameters(selCmd);
return(selCmd);
}
string text;
if (string.IsNullOrEmpty(sortField))
{
text = string.Format(@"DECLARE @firstId {0}, @skip int = {1}, @take int = {2};
SET ROWCOUNT @skip;
SELECT @firstId = {3} FROM {4}{6} ORDER BY {3};
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @take;
SELECT @firstId;
SELECT {5}
FROM {4}
WHERE {3} >= @firstId {7}
ORDER BY {3};
SET ROWCOUNT 0;
SELECT Count(*) FROM {4}{6};", DbTypeName(idType), skip + 1, take,
idField, subBuilder.SqlTable, GetFieldList(),
wherePart, wherePart.Replace(" WHERE ", " AND "));
}
else
{
text = string.Format(@"DECLARE @firstValue {0}, @firstId {1}, @skip int = {2}, @take int = {3};
SET ROWCOUNT @skip;
SELECT @firstValue = {4}, @firstId = {5} FROM {6}{8} ORDER BY {4}, {5};
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @take;
SELECT @firstValue, @firstId;
SELECT {7}
FROM {6}
WHERE {5} >= @firstId AND {4} >= @firstValue{9}
ORDER BY {4}, {5};
SET ROWCOUNT 0;
SELECT Count(*) FROM {6}{8};", DbTypeName(sortType), DbTypeName(idType), skip + 1, take, sortField,
idField, subBuilder.SqlTable, GetFieldList(),
wherePart, wherePart.Replace(" WHERE ", " AND "));
}
TCmd cmd = new TCmd();
cmd.CommandText = text;
SetParameters(cmd);
return(cmd);
}
protected virtual string BuildCountText()
{
return("SELECT Count(*) FROM " + SqlTable
+ (SqlConditionals.Count == 0 ? ""
: " WHERE " + SqlConditionals.Join(" AND ")));
}