THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Master Data Services Team

Blog for the Microsoft SQL Server Master Data Services (MDS) team. Blog posts are contributed by various team members.

Getting Started with the Web Services API in SQL Server 2008 R2 Master Data Services

(this post was contributed by Val Lovicz, Principal Program Manager on the MDS Team)

With the November CTP release available, many have been asking for basic information and examples for programming the Web services API. While MDS conceptual content and API reference are available and updated periodically in SQL Server Books Online, we hope this brief post will help get you started and give you a sense of how to use the Web services API.

This post provides instructions and examples using Visual Studio 2008, .NET and C# and is intended for an audience with a basic understanding of these technologies.

The following are general references on MSDN for working with Web services if you would like more background information.

Enable Web Services in Configuration Manager

Before we get started, make sure your installation of MDS has Web services enabled. The prior post Installing and Configuring Master Data Services provided detailed configuration instructions and included a step to check the Enable Web services for the Web application setting as shown below.

clip_image002

Expose the WSDL

Exposing the WSDL is only necessary at the time you want to generate proxy classes using a client development tool such as Visual Studio. After a proxy has been generated, the WSDL does not need to be exposed going forward for client programs to call the API.

Caution: Updating web.config will cause the MDS application domain in IIS to recycle.  Existing user sessions will lose cached information, and users may experience session errors or slow page loads.  Perform changes at off-peak times if possible and use non-production environments for development.

To enable an http/https Get on the WSDL:

  1. Open the MDS web.config file in a text editor (<Program Files>\Microsoft SQL Server\Master Data Services\WebApplication\web.config).
  2. Look for the tag serviceMetadata and set httpGetEnabled to true (or httpsGetEnabled if using SSL).

To also enable service exception details for additional debugging (not necessary for standard, trapped errors):

  1. Look for the tag serviceDebug and set includeExceptionDetailInFaults to true.

<system.serviceModel>
     <behaviors>
          <serviceBehaviors>
               <behavior name="mdsWsHttpBehavior">
                    <!-- Enable to allow clients to retrieve metadata (WSDL) about the service endpoints. -->
                    <!-- If not using SSL (httpGetEnabled="true" httpsGetEnabled="false") to expose service metadata.—> 
                    <!-- If SSL is being used (httpGetEnabled="false" httpsGetEnabled="true") to expose service metadata.-->
                    <serviceMetadata httpGetEnabled="true" httpsGetEnabled="false" />

                    <!-- Enable to allow clients to see service exception details -->
                    <serviceDebug includeExceptionDetailInFaults="true" />

                    <serviceThrottling maxConcurrentSessions="400"/>

                   <dataContractSerializer maxItemsInObjectGraph="999999999"/>
               </behavior>
          </serviceBehaviors>
     </behaviors>

</system.serviceModel>

Note: these settings will apply to all Web application instances on this server. All instances are virtual directories that point to the same Web files and web.config.

Create an Example Application in Visual Studio

Create a simple Console Application using the following steps.

  1. In Visual Studio, select FileNewProject…
  2. In the New Project Dialog, select Visual C# - Windows under Project types
  3. Select Console Application under Templates
  4. Enter “HelloMDS” as the Name
  5. Click OK to create the project

clip_image004

You will now have an empty template program as shown.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace HelloMDS
{
     class Program
     {
          static void Main(string[] args)
          { 
          }
     }
}

Add a Service Reference

Next, add a service reference to the project for consuming the MDS Web service.

  1. In the Solution Explorer window, right-click References
  2. Click Add Service Reference

clip_image006

You will see the Add Service Reference dialog. Now, configure the service reference.

  1. In Address, enter the URL to the MDS service which will be “http://<ServerName>/<MdsSiteName>/service/service.svc”.  If you do this on the computer where MDS is hosted, you can use “localhost” as the server name.
  2. Click Go. Visual Studio will attempt to contact the service and retrieve the WSDL.
  3. If successful, you will see Service and IService in the Services box. Click on IService to preview the list of operations as shown.
  4. Assign a namespace to the service in the Namespace box. In this example, I am using MDService.
  5. Click the Advanced button to configure advanced settings.

clip_image008

Advanced Settings

Here you will change how the proxy classes are generated. This step is only necessary so that your code can be identical to my examples.

  1. Check Always generate message contracts
  2. Set the Collection type drop-down to System.Collections.ObjectModel.Collection
  3. Click OK to return to the Add Service Reference dialog
  4. Click OK. In the status bar you will see “Generating new service reference to…”. The proxy classes are being generated.
  5. When VS is ready, you will see the MDService service reference added to your project (shown in Solution Explorer).

clip_image010

Create a Simple Client Program

Next, you can use the following example code to expand the template program. The program, as shown below, only contains the minimal steps to initialize a client proxy which you may then use to call MDS Web service operations. You will need to add additional statements to make this program perform useful work. You will need System Administrator access to MDS to successfully run the example application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// additional references...
using HelloMDS.MDService; /* for the created service reference */
using System.Collections.ObjectModel; /* supports collection objects used in the proxy */

namespace HelloMDS
{
     class Program
     {
          private static ServiceClient mdsProxy; /* service proxy object */

          static void Main(string[] args)
          {
               // Create the service proxy
               Console.WriteLine("Connecting...");
               try
               {
                    mdsProxy = CreateMdsProxy("http://localhost/MDS/service/Service.svc");
                    Console.WriteLine("Connected.");
               }
               catch (Exception ex)
               {
                    Console.WriteLine("Error connecting: " + ex.Message);
               }

               // At this point we could start calling methods on mdsProxy to execute Web service operations.

          }

          // creates the service client proxy
          private static ServiceClient CreateMdsProxy(string mdsURL)
          {
               // create an endpoint address using the URL
               System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

               // create and configure the WS Http binding
               System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();

               // create and return the client proxy
               return new ServiceClient (wsBinding, endptAddress);
          }
     }
}

Calling Web Service Operations via the Proxy Methods

Now that a service client object mdsProxy is established in the code, you can begin calling its methods.

For example:

  1. In the example code above, place the cursor after the line “//At this point we could start…”
  2. Type “mdsProxy.” and IntelliSense will display the available methods as shown below. Most of these methods correspond directly to MDS Web service operations.

clip_image012

The Basics of Service Operations / Proxy Methods

MDS service operations follow the naming convention of NounVerb. For example, the operation to create new metadata objects such as models, entities and hierarchies is MetadataCreate. Each proxy method that represents a service operation will have a single request object parameter (e.g. MetadataCreateRequest) and will return a response object (e.g. MetadataCreateResponse). Therefore, a typical call of a proxy method, such as MetadataCreate, would include the following lines.

// Create the request and response objects
MetadataCreateRequest request = new MetadataCreateRequest();
MetadataCreateResponse response = new MetadataCreateResponse();

// Here, we need to populate the request in order to do something useful.

// Make the service request
response = mdsProxy.MetadataCreate(request);

This example calls MetadataCreate without populating the request. You would need to populate the request with a new object for creation, such as a new model, to perform a successful and useful call.

Completing the Example

Next, you may use the following code example to complete the program. The program below has an added method CreateModel. This method accepts a new model name string parameter and populates the request with the single model name provided.

To complete the example, add the call to CreateModel(“Hello World”); in Main as shown. This will create a new model in the MDS repository, named “Hello World”.

You’ll also notice the addition of the HandleErrors method. Each response includes an OperationResult object which contains a collection of Error objects if any errors are encountered when processing the request. This method example prints the list of operation error messages if any are returned.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
// additional references...
using HelloMDS.MDService; /* for the created service reference */
using System.Collections.ObjectModel; /* supports collection objects used in the proxy */

namespace HelloMDS
{
     class Program
     {
          private static ServiceClient mdsProxy; /* service proxy object */

          static void Main(string[] args)
          {
               // Create the service proxy
               Console.WriteLine("Connecting...");
               try
               {
                    mdsProxy = CreateMdsProxy("http://localhost/MDS/service/Service.svc");
                    Console.WriteLine("Connected.");
               }
               catch (Exception ex)
               {
                    Console.WriteLine("Error connecting: " + ex.Message);
               }

               CreateModel("Hello World");

          }

          public static void CreateModel(string newModelName)
          {
               // Create the request and response objects
               MetadataCreateRequest request = new MetadataCreateRequest();
               MetadataCreateResponse response = new MetadataCreateResponse();

               // Build the request with the new model
               request.Metadata = new Metadata();
               request.Metadata.Models = new Collection<Model>() { new Model() };
               request.Metadata.Models[0].Identifier = new Identifier();
               request.Metadata.Models[0].Identifier.Name = newModelName;

               // Make the service request to Create.
               response = mdsProxy.MetadataCreate(request);

               HandleErrors(response.OperationResult);
          }

           // creates the service client proxy
          private static ServiceClient CreateMdsProxy(string mdsURL)
          {
               // create an endpoint address using the URL
               System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

               // create and configure the WS Http binding
               System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();

               // create and return the client proxy
               return new ServiceClient (wsBinding, endptAddress);
          }

          // Handles the operations results
          private static void HandleErrors(OperationResult result)
          {
               string errorMessage = string.Empty;
               if (result.Errors.Count() != 0)
               {
                    for (int i = 0; i <= result.Errors.Count() - 1; i++)
                    {
                         errorMessage += " OperationResult:Error: " + result.Errors[i].Code + ":"
                              + result.Errors[i].Description + ":" + result.Errors[i].Context.Type.ToString();
                    }
                    Console.WriteLine("Error: " + errorMessage);
               }
          }
     }
}

Try it Out

If you have copied the example code so far, build (F6) and run (F5) your solution to test it. You will briefly see the console application appear and then complete. Verify the results by logging into the Master Data Manager Web application (MDM). You will see “Hello World” added to the list of models on the home page.

Note: MDM caches metadata, so if you already had MDM open in your browser when the HelloMDS application was run, you will not see the newly created model. You would need to click Refresh at the bottom of the Home page or close your browser and reconnect.

clip_image014

More on Bindings

As you expand the solution to get or receive larger, more complex messages, you may find the need to increase certain default binding settings. In the example below, the CreateMdsProxy method has been expanded to include additional settings on wsBinding. These settings are not recommended settings for all situations; they merely show how to increase these settings should you run into timeouts or message overflows. These same settings may also be configured in the app.config file within your project. For more information, refer to the MSDN article: Using Bindings to Configure Windows Communication Foundation Services and Clients.

          // creates the service client proxy
          private static ServiceClient CreateMdsProxy(string mdsURL)
          {
               // create an endpoint address using the URL
               System.ServiceModel.EndpointAddress endptAddress = new System.ServiceModel.EndpointAddress(mdsURL);

               // create and configure the WS Http binding
               System.ServiceModel.WSHttpBinding wsBinding = new System.ServiceModel.WSHttpBinding();
               // binding settings such as the following may be set here or in app.config
               wsBinding.CloseTimeout = TimeSpan.FromMinutes(30);
               wsBinding.OpenTimeout = TimeSpan.FromMinutes(30);
               wsBinding.SendTimeout = TimeSpan.FromMinutes(30);
               wsBinding.ReceiveTimeout = TimeSpan.FromMinutes(30);
               wsBinding.MaxReceivedMessageSize = 999999999;
               wsBinding.MaxBufferPoolSize = 999999999;
               XmlDictionaryReaderQuotas ReaderQuotas = new XmlDictionaryReaderQuotas();
               ReaderQuotas.MaxArrayLength = 999999999;
               ReaderQuotas.MaxBytesPerRead = 999999999;
               ReaderQuotas.MaxDepth = 999999999;
               ReaderQuotas.MaxNameTableCharCount = 999999999;
               ReaderQuotas.MaxStringContentLength = 999999999;
               wsBinding.ReaderQuotas = ReaderQuotas;
               wsBinding.Security.Mode = System.ServiceModel.SecurityMode.Message;
               wsBinding.Security.Message.ClientCredentialType = System.ServiceModel.MessageCredentialType.Windows;

               // create and return the client proxy
               return new ServiceClient(wsBinding, endptAddress);
          }

Setting the properties of XMLDictionaryReaderQuotas requires the following additional reference line in the program.

using System.Xml;

Further Recommendations

The preceding example was very basic. To create a more robust, object-oriented solution, I recommend creating an “MDSServiceWrapper” class that encapsulates mdsProxy and its service methods and exposes granular, purpose-built methods such as CreateModel, CreateEntity, etc. This approach would yield a reusable API wrapper that cleanly separates the body of your application from calls to the MDS API.

API Quick Reference

The following information is a brief reference, listing the operation stereotypes and links to further MSDN documentation. The Master Data Services – Technical Reference in SQL Server 2008 R2 Books Online currently provides the lists of operations and classes.

Operation Stereotypes

The following operation stereotypes are defined for consistency. Understanding the functional patterns implemented by each stereotype helps you understand how to use the operations properly, how to form valid requests and what to expect in responses.

Note the term “ID” is used generically. This may be a GUID, member code or other unique ID as appropriate to the type of data.

Get operations…

  • search for items by ID’s and/or names and return matched items in their response
  • may make use of “<objectType>SearchCriteria” parameters that act as a filter on the retrieved items
  • may make use of “<objectType>GetCriteria”, not SearchCriteria, when the criteria are explicit, exact matches
  • may also use “<objectType>ResultCriteria” parameters that define the information to be retrieved on the matched items

If you were to think of the Criteria parameters in the form of a SQL-like expression, they would be represented as:

SELECT ResultCriteria WHERE records match SearchCriteria

Create operations…

  • create new items that do not exist
  • error when the item to be created already exists (matching context and name exists)
  • require names for new items in the request and assign new internal ID’s to the created items
  • ignore ID’s that are provided in the request data
  • may include a “ReturnCreatedItems” option to respond with the ID’s of newly created items

Clone operations…

  • create items within an MDS instance as exact copies of items from another MDS instance, including the ID’s
  • require an ID along with a name for creation of the new item
  • assign both the supplied ID and name to the newly created item
  • update existing items that are matched by ID

Update operations…

  • modify existing items and never create new items
  • error when the item to be modified is not found by its ID

Delete operations…

  • remove or deactivate existing items by ID
  • error when the item to be deleted is not found by its ID
  • may be overloaded with an Undelete option for items such as master data that can be deactivated (soft-deleted)

Merge operations…

  • automatically create new items or update existing items as appropriate
  • are only implemented for master data members

Typical functions / operations for editing would include three distinct operations for creating, updating and deleting. This approach is sufficient but sometimes requires extra work by the user. When there is a mix of pre-existing and new items, the user may need to issue additional commands to determine what pre-exists to conditionally execute create or update requests to avoid errors. MDS provides “Merge” operations for master data so the user may avoid these extra steps that increase coding time and complexity.

List of Operations

Refer to the list of Service Methods on MSDN.

Web Service Class Reference

Visit the MDS Web Service Class Reference on MSDN to look up class descriptions for the message and data contracts of the services.

Legal Notice

© 2010 Microsoft Corporation.  All rights reserved.  This information is provided “as-is”. Information and views expressed, including URL and other Internet Web site references, may change without notice. You bear the risk of using this information. Examples are provided for illustration only.  Provision of this information does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this information for your internal, reference purposes only.

Published Tuesday, January 12, 2010 4:00 PM by mattande
Filed under: ,

Comments

 

Meinrad said:

Great article.

Do you have a sample of a concrete Get operation including the search condition.

Regards

Meinrad

January 18, 2010 8:50 AM
 

mattande said:

Here is a MetadataGet example that could be added to the sample app.

// Create the request and response objects

MetadataGetRequest request = new MetadataGetRequest();

MetadataGetResponse response = new MetadataGetResponse();

// create a search criteria for objects within the Product model

request.SearchCriteria = new MetadataSearchCriteria();

request.SearchCriteria.Models = new Collection<Identifier>() { new Identifier() };

request.SearchCriteria.Models[0].Name = "Product";

// set ResultOptions to only retrieve a collection of models and to only populate the Identifiers within that collection

request.ResultOptions = new MetadataResultOptions();

request.ResultOptions.Models = ResultType.Identifiers;

// call the service operation

response = mdsProxy.MetadataGet(request);

// print the GUID of the first returned model in the collection

String productModelGUID = response.Metadata.Models[0].Identifier.Id.ToString();

Console.WriteLine(productModelGUID);

January 18, 2010 2:44 PM
 

Meinrad said:

Excellent :-)

Works like a charme.

Regards

Meinrad

January 19, 2010 2:14 AM
 

Sandy said:

Can you help? I am not finding the materdataservices.dll reference on my development server... where is this? The ServiceClient is an invalid reference right now...

Thank you!

Sandy

April 9, 2010 11:55 AM
 

Xavier said:

Hi Sandy

You can find masterdataservices.dll on the server where MDS web app is installed. Typically in

C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin

August 11, 2010 5:10 AM
 

mattande said:

You do not need to and should not reference the masterdataservices.dll directly. Generate a service reference in Visual Studio or use svcutil to generate proxy classes.

August 11, 2010 9:30 AM
 

Morten said:

Is it possible to use BasicHttpBinding with MDS web services?

October 20, 2010 8:26 AM
 

David said:

I cant get the 'Add service reference' to find the service on the host.

I have enabled the web services check box on the MDS set up, amended the web.config on the host, bit still just get

There was an error downloading 'http://mdsdev.twentyci.local/mds/service/service.svc'.

The request failed with HTTP status 503: Service Unavailable.

Metadata contains a reference that cannot be resolved: 'http://mdsdev.twentyci.local/mds/service/service.svc'.

The HTTP service located at http://mdsdev.twentyci.local/mds/service/service.svc is too busy.

The remote server returned an error: (503) Server Unavailable.

If the service is defined in the current solution, try building the solution and adding the service reference again.

Any ideas?

December 10, 2010 7:59 AM
 

Jason said:

@David - try the webservice URL in your browser. If it shows a message like "Service Service" "You have created a service." and give some C# and VB test information then you know the webservice and the WSDL is up and running.

March 22, 2011 9:17 PM
 

Nagu said:

Hi,

I created an example just like you have i above, but I am getting a 120002 error "User does not have permission to perform this operation" when I execute the metadatacreate operation. It then goes to HandleErrors and comes out with ""Object reference not set to an instance of an object." on the errormessage+= line.

I went ahead and created a test model directly on the MDS portal and it goes through fine, so not sure where I need additional permissions!! I am  sys admin on the SQL server also.

Any help appreciated.

Thanks.

April 18, 2011 10:49 PM
 

JasonH said:

After creating the web service with the MDS Configuration Manager, I got an error 404.3 The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map.

Wrote up the solution on the blog post here if you run into that too

http://blogs.msdn.com/b/jason_howell/archive/2011/08/04/mds-services-svc-web-service-gives-http-error-404-3-not-found-after-i-just-installed-it-successfully.aspx

August 4, 2011 8:13 PM
 

qwerty123 said:

Hi,

I am trying to call the MDS WebService from SOAPUI and the following SoapFault is returned -

The message could not be processed. This is most likely because the action 'http://schemas.microsoft.com/sqlserver/masterdataservices/2009/09/IService/EntityMembersCreate' is incorrect or because the message contains an invalid or expired security context token or because there is a mismatch between bindings. The security context token would be invalid if the service aborted the channel due to inactivity. To prevent the service from aborting idle sessions prematurely increase the Receive timeout on the service endpoint's binding.

Any ideas?

August 30, 2011 7:49 PM
 

Raja Infy said:

Do you have the code for data members merge?? If so plz do share it..

April 10, 2013 9:30 AM
 

ahmad said:

asdasdasd

February 18, 2014 10:45 AM
 

Geoff said:

I get a compile error for the method MetaDataCreate. The error message is: "Method MetaDataCreate has 4 parameters but is invoked with 1 argument(s)".

I understand the error but don't know how to adjust my code to overcome it. The method MetaDataCreate is designed to handle 4 arguments but in the code above only one argument is passed to the method. What should I do?

April 24, 2014 7:43 AM
 

Mick said:

Awesome thanks mate, the serviceMetadata issue had me stuck for ages!

June 5, 2014 6:25 PM
New Comments to this post are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement