Tuesday, October 13, 2015

Access SQL Data through Custom Web Service for InfoPath

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.


No comments:

Post a Comment