public static async Task <AnkerData> GetItemsAsync(AnkerhRequest request, int pageSize, string responseContinutationToken)
{
// Get a Database by querying for it by id
Database db = client.CreateDatabaseQuery()
.Where(d => d.Id == DatabaseId)
.AsEnumerable()
.Single();
// Use that Database's SelfLink to query for a DocumentCollection by id
DocumentCollection coll = client.CreateDocumentCollectionQuery(db.SelfLink)
.Where(c => c.Id == CollectionId)
.AsEnumerable()
.Single();
StringBuilder sqlQuery = new StringBuilder("SELECT c.id,c.BilagsNummer, c.Sagsnummer,c.SagsNavn,c.AfsnitTekst,c.AktivitetTekst,c.Kreditornummer,c.Kreditornavn,c.Filnavn,c.Fakturadato FROM c");
StringBuilder filters = new StringBuilder();
var parms = new SqlParameterCollection();
if (request.StartDate.HasValue && request.EndDate.HasValue)
{
filters.Append(" (c.Fakturadato between @startDate and @endDate)");
parms.Add(new SqlParameter("@startDate", request.StartDate));
parms.Add(new SqlParameter("@endDate", request.EndDate.Value.AddDays(1)));
}
if (!string.IsNullOrEmpty(request.BilagsNummer))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.BilagsNummer,@BilagsNummer))");
parms.Add(new SqlParameter("@BilagsNummer", request.BilagsNummer));
}
if (!string.IsNullOrEmpty(request.Sagsnummer))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.Sagsnummer,@Sagsnummer))");
parms.Add(new SqlParameter("@Sagsnummer", request.Sagsnummer));
}
if (!string.IsNullOrEmpty(request.SagsNavn))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.SagsNavn,@SagsNavn))");
parms.Add(new SqlParameter("@SagsNavn", request.SagsNavn));
}
if (!string.IsNullOrEmpty(request.AfsnitTekst))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.AfsnitTekst,@AfsnitTekst))");
parms.Add(new SqlParameter("@AfsnitTekst", request.AfsnitTekst));
}
if (!string.IsNullOrEmpty(request.AktivitetTekst))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.AktivitetTekst,@AktivitetTekst))");
parms.Add(new SqlParameter("@AktivitetTekst", request.AktivitetTekst));
}
if (!string.IsNullOrEmpty(request.Kreditornummer))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.Kreditornummer,@Kreditornummer))");
parms.Add(new SqlParameter("@Kreditornummer", request.Kreditornummer));
}
if (!string.IsNullOrEmpty(request.Kreditornavn))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.Kreditornavn,@Kreditornavn))");
parms.Add(new SqlParameter("@Kreditornavn", request.Kreditornavn));
}
if (!string.IsNullOrEmpty(request.Filnavn))
{
filters = filters.Length > 0 ? filters.Append(" AND ") : filters;
filters.Append(" (CONTAINS( c.Filnavn,@Filnavn))");
parms.Add(new SqlParameter("@Filnavn", request.Filnavn));
}
if (filters.Length > 0)
{
sqlQuery.Append(" WHERE");
}
sqlQuery.Append(" {0}");
filters.Append(" order by c.Fakturadato desc");
var sqlquery = new SqlQuerySpec
{
QueryText = string.Format(sqlQuery.ToString(), filters.ToString()),
Parameters = parms
};
var query = client
.CreateDocumentQuery(coll.SelfLink, sqlquery, new FeedOptions {
MaxItemCount = pageSize, RequestContinuation = responseContinutationToken
})
.AsDocumentQuery();
List <AnkerDTO> results = new List <AnkerDTO>();
if (query.HasMoreResults)
{
var data = new AnkerData();
var result = await query.ExecuteNextAsync <AnkerDTO>();
data.ContinutationToken = result.ResponseContinuation;
data.Items.AddRange(result);
return(data);
}
return(null);
}
public static async Task <AnkerData> GetItemsAsync(Expression <Func <T, bool> > predicate, int pageSize, string responseContinutationToken)
{
// Get a Database by querying for it by id
Database db = client.CreateDatabaseQuery()
.Where(d => d.Id == DatabaseId)
.AsEnumerable()
.Single();
// Use that Database's SelfLink to query for a DocumentCollection by id
DocumentCollection coll = client.CreateDocumentCollectionQuery(db.SelfLink)
.Where(c => c.Id == CollectionId)
.AsEnumerable()
.Single();
var sql = "SELECT TOP 3 * FROM c";
var d1 = new DateTime(2019, 02, 01);
var d2 = new DateTime(2019, 02, 02);
var sqlquery = new SqlQuerySpec
{
QueryText = "SELECT * FROM c WHERE (c.Forfaldsdato between @d1 and @d2) order by c.Forfaldsdato desc",
Parameters = new SqlParameterCollection()
{
new SqlParameter("@d1", d1),
new SqlParameter("@d2", d2)
}
};
var query1 = client
.CreateDocumentQuery(coll.SelfLink, sqlquery, new FeedOptions {
MaxItemCount = pageSize, RequestContinuation = responseContinutationToken
})
.AsDocumentQuery();
while (query1.HasMoreResults)
{
var documents = await query1.ExecuteNextAsync <AnkerDTO>();
foreach (var document in documents)
{
//Console.WriteLine(" PublicId: {0}; Magnitude: {1};", document.publicid,
// document.magnitude);
}
}
IDocumentQuery <T> query;
if (predicate == null)
{
query = client.CreateDocumentQuery <T>(
UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
new FeedOptions {
MaxItemCount = pageSize, RequestContinuation = responseContinutationToken
})
.AsDocumentQuery();
}
else
{
query = client.CreateDocumentQuery <T>(
UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
new FeedOptions {
MaxItemCount = pageSize, RequestContinuation = responseContinutationToken
})
.Where(predicate)
.AsDocumentQuery();
}
List <T> results = new List <T>();
if (query.HasMoreResults)
{
var data = new AnkerData();
var result = await query.ExecuteNextAsync <AnkerDTO>();
data.ContinutationToken = result.ResponseContinuation;
data.Items.AddRange(result);
return(data);
}
return(null);
}
