Home > MOSS 2007 > Working with Structured Data in Microsoft Office SharePoint Server 2007 (Part2): Exposing LOB Data

Working with Structured Data in Microsoft Office SharePoint Server 2007 (Part2): Exposing LOB Data

March 23rd, 2009 Leave a comment Go to comments

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:

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.

Important
Throughout the upcoming blog posts, I will be demonstrating different functionalities that rely on the endpoints we will review in this blog post.
This blog post is not meant to walk you through SharePoint OOTB Web services or how to write custom Web services to call into the SharePoint object model. This will be covered in great details in my upcoming posts.


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\

Important
You can create this web service in C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS or C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI folder.Web services in ISAPI folder can be virtualized, context aware and easily discovered no matter how deep you call them in the overall site hierarchy.
Depending on the implemented logic in your Web services, the context may or may not be an important thing. In Northwind Web service SharePoint context is not important , because SharePoint is simply the consumer of this service.When writing a Web service that calls into te SharePoint object model , all you need to do is to host it on the WFE server(es) where SharePoint object model is available.
If you like to develop your Web services this way, it’s super important to pass in the right URL to Web methods and construct the right context at run time, otherwise you will end up in other contexts.

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 .

Important
The SOAP endpoint that we are going to create ,in just a moment, will come handy in the upcoming blog post, when we create Full trust, master-detail and browser-enabled InfoPath forms as a composite no-code solution.

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.

Categories: MOSS 2007 Tags:
  1. ziyaee
    May 31st, 2009 at 04:21 | #1

    hi,please help me.
    how we can delete one row in infopath (that works with sql)?
    how we can select a row in infopath and retrive its value of primary key?
    how we can edit a row in a form ????????