Designing a browser-enabled InfoPath form that can store data in a database - Part1

Article
05/13/2008

As I haven’t found so far any blog post explaining how to design an InfoPath form that could save its data to a database I decided to write this post. I hope the content is useful. Any feedback is appreciated.

Assumptions:

The goal is to create a web-enabled form that is able to both retrieve and store data in a database.

Used technology:

You will need Infopath 2007 to design the form, a Form Services enabled server (either SharePoint Server 2007 or Form Server 2007) and a database server that is able to expose web services (I will use SQL Server 2005).

Scenario:

A city hall needs to receive data from shops regarding discounts, in particular the discount type (30% off, 40% off, etc) and the period during which the discount will be applied.

Let’s assume that the shops are already registered in the city hall database.

The forms will retrieve the shop data based on the shop ID (that the owner will already know) and the owner will post data regarding the period and the discounts that will be applied.

Please take this as an example, the important part is setting up the form to talk to the DB and send data to it.

Set up the DB

The DB (InfoCityHall) will contain two tables: one for the SHOPS(ID, NAME, ADDRESS, OWNER) and one for the DISCOUNTS(SHOPID, STARTDATE, ENDDATE, DISCOUNTRATE).

The data already inserted is the following:

SHOPS

1	Pike and Pine	1025 Pine Str.	John Smith
2	Leo’s	903 S. Racine Ave.	Mark Johnson
3	Michigan Place	100 N. Michigan Ave.	Glenn Hughes
4	EleStore	302 N. Springfield Str.	Spider Pork

use infocityhall

insert into shops values (1,'Pike and Pine','1025 Pine Str.','John Smith')

insert into shops values (2,'Leo’s','903 S. Racine Ave.','Mark Johnson')

insert into shops values (3,'Michigan Place','100 N. Michigan Ave.','Glenn Hughes')

insert into shops values (4,'EleStore','302 N. Springfield Str.','Spider Pork')

select * from shops

DISCOUNTS is empty. InfoPath will insert data into it.

Set up the Web Service

Now we will need to create the web service. We will use the web service to store discounts into the DB from InfoPath.

The Web Service in SQL Server is created through an “HTTP Endpoint” and a T-SQL stored procedure.

Here is the stored procedure

create procedure StoreDiscount

@ShopId numeric,

@StartDate datetime,

@EndDate datetime,

@DiscountRate int

BEGIN

insert into discounts values(@ShopId,@StartDate,@EndDate,@DiscountRate)

END

And here is the HTTP Endpoint:

CREATE ENDPOINT discount_endpoint

STATE = STARTED

AS HTTP (

PATH = '/sql',

AUTHENTICATION = (INTEGRATED ),

PORTS = ( CLEAR ),

SITE = 'YourSqlServerName'

)

FOR SOAP (

WEBMETHOD 'StoreDiscountData'

(name='infocityhall.dbo.StoreDiscount',

SCHEMA=STANDARD ),

WSDL = DEFAULT,

SCHEMA = STANDARD,

DATABASE = 'infocityhall',

NAMESPACE = 'https://tempUri.org/'

)

The web service will now be discoverable at the location https://yoursqlservername/sql?wsdl

In the next post we will start designing our InfoPath form.