Access SQL Data through Custom Web Service
1.
First connect to your SQL Server and get the
connection string according to the database.
2.
Create your InfoPath Form according to your
requirement. In here we are retrieving data from SQL table using Item Number.
Finally publish the form to the InfoPath Library.
3.
Write a custom Web Service to retrieve data from
SQL table. Deploy it to the Local host
[WebMethod]
public DataSet
GetItems()
{
string constr = "Data
Source=<Server Name>;Initial Catalog=<Database Name>;User
ID=XX;Password=XXXXX";
using (SqlConnection
con = new SqlConnection(constr))
{
using (SqlCommand
cmd = new SqlCommand("SELECT * FROM [SamplesOrderWF].[Products]"))
{
using
(SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection =
con;
sda.SelectCommand =
cmd;
using (DataSet dt
= new DataSet())
{
sda.Fill(dt);
return dt;
}
}
}
}
4.
Then Add data connection to the InfoPath Form to
retrieve data from Custom Web Service
Manage Data Connection -> Add->
Create New Connection to: Receive Data -> Select SOAP Web Service -> Give
the URL http://localhost:59651/Service1.asmx?WSDL->
Select the Web Service Method “GetItems” -> Finished
5.
Then Select the connection in Data Connection
Window and click convert to connection file. Give path to data connection
library.
6.
Then manage data connection -> add ->
Search for connection on a MS SP Server -> Manage site -> Give site
details.-> select .udcx file -> Finish
7.
Then select the Text box you are going to
retrieve data and add rules for it.
8.
Finally publish the form to the library again.
