public override string KeyAllQuery(Entity entity) {
var sql = string.Format(
@"SELECT {0} FROM {1} ",
string.Join(", ", entity.SelectKeys(this)),
Enclose(entity.Name)
);
if (entity.Filters.Any()) {
sql += " WHERE " + entity.Filters.ResolveExpression(TextQualifier);
}
sql += ";";
return sql;
}
public override string KeyQuery(Entity entity) {
const string sql = @"
SELECT {0}
FROM [{1}].[{2}] WITH (NOLOCK)
WHERE [{3}] <= @End
";
return string.Format(
sql,
string.Join(", ", entity.SelectKeys(this)),
entity.Schema,
entity.Name,
entity.Version.Name
);
}
public override string KeyAllQuery(Entity entity) {
const string format = @"
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT {0} FROM `{1}` ";
var sql = string.Format(
format,
string.Join(", ", entity.SelectKeys(this)),
entity.Name
);
if (entity.Filters.Any()) {
sql += " WHERE " + entity.Filters.ResolveExpression(TextQualifier);
}
sql += ";COMMIT;";
return sql;
}
public override string KeyRangeQuery(Entity entity) {
const string pattern = @"
SELECT {0}
FROM [{1}].[{2}] WITH (NOLOCK)
WHERE [{3}] BETWEEN @Begin AND @End
AND [{4}] IS NOT NULL
";
var sql = string.Format(
pattern,
string.Join(", ", entity.SelectKeys(this)),
string.IsNullOrEmpty(entity.Schema) ? DefaultSchema : entity.Schema,
entity.Name,
entity.Version.Name,
entity.PrimaryKey.WithInput().First().Name
);
if (entity.Filters.Any()) {
sql += " AND " + entity.Filters.ResolveExpression(TextQualifier);
}
return sql;
}
public override string KeyAllQuery(Entity entity) {
const string format = @"
SELECT {0} FROM [{1}].[{2}]";
var sql = string.Format(
format,
string.Join(", ", entity.SelectKeys(this)),
string.IsNullOrEmpty(entity.Schema) ? this.DefaultSchema : entity.Schema,
entity.Name
);
if (entity.NoLock) {
sql += " WITH (NOLOCK)";
}
if (entity.Sample > 0m && entity.Sample < 100m && TableSample && !entity.Sampled) {
entity.Sampled = true;
sql += string.Format(" TABLESAMPLE ({0:##} PERCENT)", entity.Sample);
}
sql += " WHERE [" + entity.PrimaryKey.WithInput().First().Name + "] IS NOT NULL";
if (entity.Filters.Any()) {
sql += " AND " + entity.Filters.ResolveExpression(TextQualifier);
}
return sql;
}
public override string KeyAllQuery(Entity entity) {
const string sql = @"
SELECT {0} FROM [{1}].[{2}] WITH (NOLOCK);
";
return string.Format(
sql,
string.Join(", ", entity.SelectKeys(this)),
entity.Schema,
entity.Name
);
}
public override string KeyRangeQuery(Entity entity) {
var sql = string.Format(
@"SELECT {0} FROM {1} WHERE {2} BETWEEN @Begin AND @End ",
string.Join(", ", entity.SelectKeys(this)),
Enclose(entity.Name),
Enclose(entity.Version.Name)
);
if (entity.Filters.Any()) {
sql += " AND " + entity.Filters.ResolveExpression(TextQualifier);
}
sql += ";";
return sql;
}
