Reza Alirezaei’s Blog

Summary: Explore different options you have to work with structured data in a high volume while you need to perform complex queries and actions against such data ranging from authoring, approval and landing information on Web Part pages, all the way down to the physical storage. This blog post is part 2 of a blog post series that I am planning to write on this topic. (9 printed pages)

Applies to: Microsoft Office SharePoint Server 2007. SQL Server 2005 /2008

See Also:

• Part 1 : Configuring Single Sign On Service and Database
• Part 3 : Browser enabled InfoPath Forms
• Part 4 : SharePoint Designer

Download this post in PDF version here.

Content:

• Introduction
• Coding the Northwind Web Service
• Exposing the ProductsBySupplierID stored proc as a document literal SOAP Web Service

Introduction: SharePoint is a composite application platform meaning that it can be layered on the top of a variety of Microsoft or non-Microsoft data repositories and interact with them either in real time or in background. An issue that arises in the early stages of the design of your SharePoint applications is that how best to make this interaction happen.

Well, Web Services are part of the .NET framework and therefore they are available to SharePoint as well. They provide a cross-platform solution for exchanging data between SharePoint and other systems in a distributed model. As you will see later in this blog post, I will create two service interfaces to expose LOB data to SharePoint and its complementary citizens such as InfoPath forms, Data View Web part and BDC.

Coding the Northwind Web Service:
For the sake of brevity and for demonstration purpose, we will create an asmx Web service to expose Northwind data to SharePoint. If you are a WCF guru or you would hate asmx Web services for all their limitations, I am sure you know how to convert them to WCF or WSE services blindfolded!
All right, Let’s just get busy and create the Northwind Web service first.
1.Launch Visual Studio
2.From the File menu, select Open and choose Web Site
3.Select File System and then navigate to: C:\Inetpub\wwwroot\

4.Click Open
5.In the Solution Explorer, right-click on the web site and choose New Folder

6.Rename this folder to: NorthwindService
7.Right-click on NorthwindService and choose Add New Item
8.From the Visual Studio installed templates list choose Web Service
9.In the Name box, rename this to: Service.asmx

10.Uncheck the option “Place code in a separate file” and click Add
11.Add the following web method to retrieve the top 10 products from the Products table in the Northwind database.

[CSharp]
[WebMethod]
public DataSet GetTop10Products()
{
//The following code snippet is for demonstration only and by no means is production ready
SqlConnection cn = new SqlConnection(“Data Source=LITWAREDEMO; Integrated Security=SSPI;Initial Catalog=Northwind”);
string strProductsOrigSQL = “SELECT top 10 productname FROM Products”;
SqlDataAdapter daProducts = new SqlDataAdapter(strProductsOrigSQL,cn);
DataSet Ds = new DataSet();
cn.Open();
daProducts.Fill(Ds, “Products”);
cn.Close();
cn = null;
daProducts = null;
return Ds;
}
[/CSharp]
12.Add the following web method to retrieve the associated products for the selected supplier.

[CSharp]
[WebMethod]
public DataSet GetProductsBySupplierID(int suppID)
{
//The following code snippet is for demonstration only and by no means is production ready
SqlConnection cn = new SqlConnection(“Data Source=LITWAREDEMO; Integrated Security=SSPI;Initial Catalog=Northwind”);
string strProductsOrigSQL = “SELECT * FROM Products Where SupplierID=” + suppID;
SqlDataAdapter daProducts = new SqlDataAdapter(strProductsOrigSQL,cn);
DataSet Ds = new DataSet();
cn.Open();
daProducts.Fill(Ds, “Products”);
cn.Close();
cn = null;
daProducts = null;
return Ds;
}
[/CSharp]

13.Build and save the project
14.Open a browser and navigate to: http://Service_URL/Service.asmx (replace Service_URL with your Web service URL)
15.You should see the two web methods created above:

16.Click the GetTop10Products link and then click Invoke – this should return a list of the products

17.Click the GetProductsBySupplierID link, in the suppID box enter: 1 and then click Invoke – this should return a list of those products whose supplierId =1

Exposing ProductsBySupplierID stored proc as a document literal SOAP Web Service:
With our ASMX web service completely coded, let’s move on to creating the SOAP endpoint in SQL Server. Since the release SQL Server 2005 , a new feature has been added to this product that allows you to create SOAP “endpoints” which can be used to expose a stored procedure as a document literal SOAP Web Service.
Simply think of it as a wrapper around a stored procedure over HTTP. To set up SOAP endpoints in SQL Server 2005, you can use CREATE ENDPOINT T-SQL. For more information on creating endpoints in SQL Server 2005, see http://msdn2.microsoft.com/en-gb/library/ms345123.aspx .

In order to create this endpoint, follow these steps:
1.Start SQL Server Management Studio
2.Create the following stored procedure in Northwind database

[Sql]
CREATE PROCEDURE [dbo].[ProductsBySupplierID] @SupID int
AS
SELECT ProductName
FROM Products
WHERE SupplierID = @SupID
[/Sql]

3.Paste the following TSQL statement in a New Query window and execute it:

[Sql]
CREATE ENDPOINT getProductsBySupplierEndpoint
STATE = STARTED
AS HTTP
(
SITE = ‘LITWAREDEMO’,
PATH = ‘/getproductsbysupplierwebservice’,
AUTHENTICATION = ( NTLM ),
PORTS = ( CLEAR )
)
FOR SOAP
(
WEBMETHOD ‘GetProductsBySupplier’
(
NAME = ‘Northwind.dbo.ProductsBySupplierID’,
SCHEMA = DEFAULT,
FORMAT = ROWSETS_ONLY
),
WSDL = DEFAULT,
BATCHES = DISABLED,
DATABASE = ‘Northwind’
)
[/Sql]

The above TSQL syntax to create a SAOP endpoint contains three major parts:
A) The first part starts with CREATE and ends before the AS clause.
[Sql]
CREATE ENDPOINT getProductsBySupplierEndpoint
STATE = STARTED
[/Sql]

In this case, endpoint name is set to getProductsBySupplierEndpoint. STARTED means that endpoint is started and is actively listening for connections.

B) The second part starts with AS and ends before the FOR clause.
[Sql]
AS HTTP
(
SITE = ‘LITWAREDEMO’,
PATH = ‘/getproductsbysupplierwebservice’,
AUTHENTICATION = ( NTLM ),
PORTS = ( CLEAR )
)
[/Sql]

In this case, HTTP is chosen as the transport protocol and incoming requests must use HTTP .The method of endpoint authentication is set to NTLM (Watch out for double-hop issue!) and PATH identifies the location of the endpoint on the host computer specified in the SITE argument.
C) The third part starts with the FOR clause.

[Sql]
FOR SOAP
(
WEBMETHOD ‘GetProductsBySupplier’
(
NAME = ‘Northwind.dbo.ProductsBySupplierID’,
SCHEMA = DEFAULT,
FORMAT = ROWSETS_ONLY
),
WSDL = DEFAULT,
BATCHES = DISABLED,
DATABASE = ‘Northwind’
)
[/Sql]

In this case, the payload that is supported on the endpoint is identified as SOAP. ProductsBySupplierID stored procedure which we created above is also specified to be exposed in the endpoint as a Web method (this also can be a user-defined function). Ad-hoc SQL requests in disabled to this endpoint by default (BATCHES = DISABLED). Since we are going to use the Visual Studio dynamic proxy class generator, we would like the results to be returned as a single dataset (System.Data.Dataset object) and not as an object array , so we set FORMAT to ROWSETS_ONLY. Other arguments are self-explanatory so I will skip drilling into them.

4.Open a browser and navigate to: http://SITE_NAME/getproductsbysupplierwebservice?wsdl
Replace SITE_NAME with the name you chose for SITE argument in the above TSQL statement (i.e LITWAREDEMO)

5.You should see the following result being returned from your Web service:

Now that we have coded both soap endpoints, let’s move on to Part 3. In Part 3, I will be walking you through InfoPath forms and how they can be leveraged to interact with LOB data. In particular, we will be looking into Browser-Enabled InfoPath forms.