I have had my work cut out on how to write a web service in C# .NET and make it work along with plugging it into Infopath 2007 and have both of them fall in love. Ah a love story.... Lets start with the code for the Web Service(mind you it is in C#.net 2.0) so all you .net 1.0 people go back to google or enjoy some fun little game/s while your bored.
Note the database and sql string is to protect the innocent and accused. Words used like Vista and hello kitty was a good Idea( I think)
Please Notice all the fancy using statements....
Now a little more on the background. Web Services are very cool as you can see you can point to a flat table like this or many tables depending on your choice. However Infopath 2007 is very very did I say very? hesitant to accept any stray xml dataset so you will notice the two web methods that have been created. One is for the connection to the table on the database. The other is to basically strip the "diffgram" tag that infopath dislikes out of the xml data.
1: < %@ WebService Language="C#" Class="DataService" %>
2: using System;
3: using System.Data;
4: using System.Data.SqlClient;
5: using System.Web;
6: using System.Web.Services;
7: using System.Web.Services.Protocols;
8: using System.Data.Odbc;
9: [WebService(Namespace = "http://localhost/")]
10: [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
11: public class DataService { 12: [WebMethod]
13: public DataSet GetVista()
14: { 15: OdbcConnection myConnection = new OdbcConnection("Dsn=WindowVista;uid=slow;pwd=memoryhog"); 16: OdbcDataAdapter myCommand1 = new OdbcDataAdapter("select * from *Your_Table* WHERE Hello_Kitty='Cute' AND Shipped = 0", myConnection); 17: DataSet ds = new DataSet();
18: myCommand1.Fill(ds, "*Your_Table*");
19: return ds;
20: }
21:
Notice this connection uses odbc connections. I had many reasons but connecting to a proprietary database requires proprietary odbc connections if you get what I mean***
Here is where the web service or method if you will and i know you will is turned into appropriate code for Infopath 2007 to digest.
1: [WebMethod]
2: public System.Xml.XmlDocument GetInfoPathSchema()
3: { 4: //Get the core data.
5: System.Data.DataSet theDataSet =
6: GetVista();
7: //Create a new XmlDocument from the data of the dataset.
8: System.Xml.XmlDocument theDocument = new System.Xml.XmlDocument();
9: theDocument.LoadXml( theDataSet.GetXml() );
10: //Return the result.
11: return theDocument;
12: }
13: }
That was easy wasn't it? Ok 2 months and I lost my hair coming up with that.....
Drop me a line if you would like more.
Another quick note:
You don't have to compile the code just plug the .asmx file into IIS and let it fly...