Specifying Queries Using SOAP
[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]
In a SOAP application, when retrieving a single flexible entity using its Id it is efficient to use the Get() method. However, multiple entities can be retrieved by passing a query to the Query() method. For information on queries, see Querying SQL Data Services.
Using the Get() Method
When using the Get() method to retrieve an authority, container or an entity using its Id, set the Scope to the entity you want to retrieve and then call the Get() method. For example:
To retrieve an authority, create a
Scopeobject and set itsAuthorityIdproperty. Then call theGet()method and pass in theScopeobject as parameter as shown in the following C# fragment.Scope myAuthorityScope = new Scope(); myAuthorityScope.AuthorityId = authorityId; Authority auth = (Authority)proxy.Get(myAuthorityScope);To retrieve a container, create the
Scopeobject and set itsAuthorityIdandContainerIdproperties and then call theGet()method:Scope myContainerScope = new Scope(); myContainerScope.AuthorityId = authorityId; myContainerScope.ContainerId = sampleContainerId; Container myContainer = (Container)proxy.Get(myContainerScope);To retrieve an entity, create the
Scopeobject and set itsAuthorityId,ContainerIdand theEntityIdproperties.Scope myEntityScope = new Scope(); myEntityScope.AuthorityId = authorityId; myEntityScope.ContainerId = sampleContainerId; myEntityScope.EntityId = "MySampleBook1"; Entity book = proxy.Get(myEntityScope);
Using the Query() Method
When calling the Query() method the Scope and a query must be provided as shown in the following C# code fragment. The example sets a container scope and specifies a query to retrieve a set of books by a specific author.
Scope myContainerScope = new Scope();
myContainerScope.AuthorityId = authorityId;
myContainerScope.ContainerId = sampleContainerId;
string sampleQuery = @"from e in entities
where e[""Author""] == ""Mr. Author2""
select e";
IEnumerable<Entity> booksByAuthor = proxy.Query(myContainerScope,
sampleQuery);
The following C# application using the SOAP interface executes a series of Get() and Execute() method calls. The application creates a sample container and several book entities within it. The sample queries executed are given below:
Find books of a specific kind. The Kind is a metadata property. Therefore, the query uses the "." notation.
from e in entities where e.Kind=="ChildrensBook" select eFind a book given its title. The title is a flexible property and therefore the query specifies the indexer notation.
from e in entities where e["Title"] == "My First Book" select eFind books given an author.
from e in entities where e["Author"] == "Mr. Author" select eFind books where the number of copies sold is less than 1000. This query shows use of a comparison operator.
from e in entities where e["NumberOfCopiesSold"] < 1000 select eFind books that are out of print. This query shows comparison of Boolean properties.
from e in entities where e["InPrint"] == false select eFor a given publisher, find all books that are out of print. This query illustrates the use of the AND operator (&&).
from e in entities where e["InPrint"] == false && e["Publisher"] == "Mr. Publisher" select e
To create a working sample:
- Create a sample application and copy the code given below.
- Update the code and provide your own user name and password. Also provide your own existing authority id and a new container id.
- Add service reference. For detail steps, see Examples of Using SOAP Interface with the SQL Data Services.
- In the code, the
using SOAP_QuerySample_1.ssdsClientstatement has two parts:- The first part (
SOAP_QuerySample_1) is the namespace name. This namespace name must match the Default namespace of the application. - The second part (
ssdsClient) is the name you provided when adding the service reference.
- The first part (
using System;
using System.Collections.Generic;
using System.Text;
using System.ServiceModel;
using SOAP_QuerySample_1.ssdsClient;
using System.IO;
namespace SOAP_QuerySample_1
{
class Program
{
// Provide your own values for these member variables
private static string userName;
private static string password;
private const string authorityId = "<YourExistingAuthorityId>";
private const string sampleContainerId = "<NewContainerId>";
private static SitkaSoapServiceClient proxy = null;
static void Main(string[] args)
{
Console.Write("Username: ");
userName = Console.ReadLine();
Console.Write("Password: ");
password = ReadPassword();
using (proxy = new SitkaSoapServiceClient("BasicAuthEndpoint"))
{
proxy.ClientCredentials.UserName.UserName = userName;
proxy.ClientCredentials.UserName.Password = password;
CreateSampleContainer();
AddSampleEntities();
// Use GET when retreiving a single Flexible Entity
ExecuteGETSamples();
// Execute queries when retrieving multiple Flex Entities
ExecuteQuerySamples();
// Cleanup
DeleteContainer();
}
}
private static void CreateSampleContainer()
{
// Identify scope. To create a container you need to specify an authority scope.
Scope myAuthorityScope = new Scope();
myAuthorityScope.AuthorityId = authorityId; // "books-docsamples";
// Add a container
Container c1 = new Container();
// Set metadata property
c1.Id = sampleContainerId;
try
{
proxy.Create(myAuthorityScope, c1);
Console.WriteLine("Container {0} created!", c1.Id);
}
catch (FaultException<Error> e)
{
// Suppress EntityExists errors in case user and/or authority already exists
if (e.Detail.StatusCode != ErrorCodes.EntityExists)
{
Console.WriteLine("Error: {0}:{1}", e.Detail.StatusCode, e.Detail.Message);
Console.WriteLine(e);
return;
}
Console.WriteLine("Container {0} already existed", c1.Id);
}
}
private static void AddSampleEntities()
{
// Identify scope. To add entities, the container must be in scope
Scope myContainerScope = new Scope();
myContainerScope.AuthorityId = authorityId;
myContainerScope.ContainerId = sampleContainerId;
// Boook 1
AddEntity(myContainerScope,
"MySampleBook1", "ChildrensBook",
"My first book", "1-57880-066-1", "Mr. Author1",
"Mr. Publisher1", true, 250, "01/27/2007");
// Boook 2
AddEntity(myContainerScope,
"MySampleBook2", "TechnicalBook",
"My second book", "1-57880-066-2", "Mr. Author1",
"Mr. Publisher2", false, 999, "01/26/2006");
// Boook 3
AddEntity(myContainerScope,
"MySampleBook3", "ChildrensBook",
"My third book", "1-57880-066-3", "Mr. Author2",
"Mr. Publisher1", true, 2000, "01/25/2005");
// Boook 4
AddEntity(myContainerScope,
"MySampleBook4", "ScienceFiction",
"My fourth book", "1-57880-066-4", "Mr. Author3",
"Mr. Publisher1", false, 4000, "01/24/2004");
// Boook 5
AddEntity(myContainerScope,
"MySampleBook5", "ScienceFiction",
"My fifth book", "1-57880-066-5", "Mr. Author1",
"Mr. Publisher2", true, 150, "01/23/2003");
}
private static void AddEntity(Scope myContainerScope,
string inputId, string inputKind, string inputTitle,
string inputISBN, string inputAuthor, string inputPublisher, bool inputInPrint,
decimal inputCopiesSold, string inputPubDate)
{
Entity e1 = new Entity();
// Set metadata
e1.Id = inputId;
e1.Kind = inputKind;
// Set flexible properties
e1.Properties = new Dictionary<string, object>();
e1.Properties["Title"] = inputTitle;
e1.Properties["ISBN"] = inputISBN;
e1.Properties["Author"] = inputAuthor;
e1.Properties["Publisher"] = inputPublisher;
e1.Properties["InPrint"] = inputInPrint;
e1.Properties["NumberOfCopiesSold"] = inputCopiesSold; //decimal
e1.Properties["PublicationDate"] = DateTime.Parse(inputPubDate);
e1.Properties["CoverPhoto"] = new byte[] { 0x1, 0x2, 0x3 }; // replace with real binary data
try
{
proxy.Create(myContainerScope, e1);
Console.WriteLine("Entity {0} created!", e1.Id);
}
catch (FaultException<Error> e)
{
// Suppress EntityExists errors in case user and/or authority already exists
if (e.Detail.StatusCode != ErrorCodes.EntityExists)
{
Console.WriteLine("Error: {0}:{1}", e.Detail.StatusCode, e.Detail.Message);
Console.WriteLine(e);
return;
}
Console.WriteLine("Entity {0} already existed", e1.Id);
}
}
private static void ExecuteGETSamples()
{
// When retreiving a single Flex Entity, GET should be used (instead of querying).
// It is more efficient.
// Example 1: Get a specific authority. The scope must be set to the authority.
Scope myAuthorityScope = new Scope();
myAuthorityScope.AuthorityId = authorityId;
Authority auth = (Authority)proxy.Get(myAuthorityScope);
// Print metadata (Version is a readonly prop)
Console.WriteLine("Id: {0}, Version: {1}", auth.Id, auth.Version);
// Example 2: Get a specific container. The scope must be set to the container.
Scope myContainerScope = new Scope();
myContainerScope.AuthorityId = authorityId;
myContainerScope.ContainerId = sampleContainerId;
Container myContainer = (Container)proxy.Get(myContainerScope);
// Print metadata (Version prop is read only)
Console.WriteLine("Id: {0}, Version: {1}", myContainer.Id, myContainer.Version);
// Example 1: Get a specific entity. The scope must be set to the entity.
Scope myEntityScope = new Scope();
myEntityScope.AuthorityId = authorityId;
myEntityScope.ContainerId = sampleContainerId;
myEntityScope.EntityId = "MySampleBook1";
Entity book = proxy.Get(myEntityScope);
// Print metadata
Console.WriteLine("Id: {0}, Version: {1} Kind: {2}", book.Id, book.Kind, book.Version);
foreach (KeyValuePair<string, object> p in book.Properties)
{
Console.WriteLine("{0}: \t{1}", p.Key, p.Value);
}
}
private static void ExecuteQuerySamples()
{
try
{
// Identify scope. When dealing with containers, authority must be in scope.
Scope myContainerScope = new Scope();
myContainerScope.AuthorityId = authorityId;
// set scope to the container that has the entity we wnat to delete.
myContainerScope.ContainerId = sampleContainerId;
// **********************
// Query metadata
//***********************
// Query 1.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Given a book title, find a book...");
string sampleQuery = @"from e in entities where e.Id==""MySampleBook1""select e";
//IEnumerable<Entity> booksById = proxy.Query(myContainerScope, sampleQuery);
List<Entity> booksById = proxy.Query(myContainerScope, sampleQuery);
Entity aBookById = booksById[0];
Console.WriteLine("\tId={0}", aBookById.Id);
// Print book title
// foreach (Property p in aBookById.Properties)
foreach (KeyValuePair<string, object> p in aBookById.Properties)
{
if (p.Key == "Title")
Console.Write("\tTitle={0}\n", p.Value);
}
// Query 2:
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Find books of a specific kind");
sampleQuery = @"from e in entities where e.Kind==""ChildrensBook"" select e";
//IEnumerable<Entity> childrenBooks = proxy.Query(myContainerScope, sampleQuery);
List<Entity> childrenBooks = proxy.Query(myContainerScope, sampleQuery);
PrintBooks(childrenBooks);
// **************************
// Query flexible properties
// **************************
// Query 3.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Given a book title, find a book...");
sampleQuery = @"from e in entities where e[""Title""] == ""My First Book"" select e";
//IEnumerable<Entity> entities = proxy.Query(myContainerScope, sampleQuery);
List<Entity> entities = proxy.Query(myContainerScope, sampleQuery);
Entity bookEntity = entities[0];
Console.WriteLine("\tId={0}", bookEntity.Id);
// Print book title
foreach (KeyValuePair<string, object> p in bookEntity.Properties)
{
if (p.Key == "Title")
Console.Write("\tTitle={0}\n", p.Value);
}
// Query 4.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Given author find books...");
sampleQuery = @"from e in entities where e[""Author""] == ""Mr. Author2"" select e";
//IEnumerable<Entity> booksByAuthor = proxy.Query(myContainerScope, sampleQuery);
List<Entity> booksByAuthor = proxy.Query(myContainerScope, sampleQuery);
PrintBooks(booksByAuthor);
// Query 5.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Find books where copies sold < 1000...(use comparison operator)");
sampleQuery = @"from e in entities where e[""NumberOfCopiesSold""] < 1000 select e";
List<Entity> booksByCopiesSold = proxy.Query(myContainerScope,
sampleQuery);
PrintBooks(booksByCopiesSold);
// Query 6.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Find out of print books...(compare boolean values)");
List<Entity> booksInPrint = proxy.Query(myContainerScope,
@"from e in entities where e[""InPrint""] == false select e");
PrintBooks(booksInPrint);
// Query 7.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Given a publisher, find all out of print books...(using AND operator)");
sampleQuery = @"from e in entities where e[""InPrint""] == false && e[""Publisher""] == ""Mr. Publisher3"" select e";
List<Entity> booksInPrintByAuthor = proxy.Query(myContainerScope, sampleQuery);
PrintBooks(booksInPrintByAuthor);
// Query 8.
Console.WriteLine("----------------------------------------------------------");
Console.WriteLine("Query: Find books published after May 27, 2006...(comparing dates)");
sampleQuery = @"from e in entities where e[""PublicationDate""] > DateTime(""2006-03-27"") select e";
List<Entity> booksByPublicationDate = proxy.Query(myContainerScope, sampleQuery);
PrintBooks(booksByPublicationDate);
}
catch (FaultException<Error> e)
{
Console.WriteLine("Encountered an error running queries:");
Console.WriteLine("Status Code: {0}", e.Detail.StatusCode);
Console.WriteLine("Error Message: {0}", e.Detail.Message);
}
catch (Exception e)
{
Console.WriteLine("Unexpected error encoutered: " + e);
}
}
private static void PrintBooks(List<Entity> booksByAuthor)
{
if (booksByAuthor.Count == 0)
{
Console.WriteLine("Zero books found");
}
else
{
foreach (Entity e in booksByAuthor)
{
PrintEntityInformation(e);
}
}
}
private static void PrintEntityInformation(Entity e)
{
Console.WriteLine("\t Id={0}", e.Id);
Console.WriteLine("\t Kind={0}", e.Kind);
//foreach (Property p in e.Properties)
foreach (KeyValuePair<string, object> p in e.Properties)
{
Console.WriteLine("{0}:\t{1}", p.Key, p.Value);
}
}
private static void DeleteContainer()
{
Scope myContainerScope = new Scope();
myContainerScope.AuthorityId = authorityId;
myContainerScope.ContainerId = sampleContainerId;
try
{
proxy.Delete(myContainerScope);
Console.WriteLine("Container {0} deleted!", sampleContainerId);
}
catch (FaultException<Error> e)
{
// Suppress EntityExists errors in case user and/or authority already exists
if (e.Detail.StatusCode != ErrorCodes.EntityNotFound)
{
Console.WriteLine("Error: {0}:{1}", e.Detail.StatusCode, e.Detail.Message);
Console.WriteLine(e);
return;
}
Console.WriteLine("Container {0} didn't exist!", sampleContainerId);
}
}
private static string ReadPassword()
{
StringBuilder retval = new StringBuilder();
ConsoleKeyInfo keyInfo;
while ((keyInfo = Console.ReadKey(true)).Key != ConsoleKey.Enter)
{
Console.Write("*");
retval.Append(keyInfo.KeyChar);
}
Console.WriteLine();
return retval.ToString();
}
}
}
See Also
Concepts
Querying SQL Data Services
SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities)