SQL Database Integration

Re: SQL Connection via script

Mik — Tue, 06 Sep 2005 04:26:49 GMT

Many many thanks vt_asparagus!
The submission period has been extended, and now there is time to work on this.

Re: SQL Connection via script

vt_asparagus — Fri, 02 Sep 2005 20:14:53 GMT

Sure...Check out these InfoPath tutorials on MSN website. They show how to create a simple web service and connect to an InfoPath form.

- Lab 8: Data Sources in InfoPath 2003 -http://msdn.microsoft.com/library/en-us/odc_ip2003_tr/html/odc_INF_Lab_08.asp?frame=true&_r=1
- Lab 9: ADO.NET DataSets in InfoPath 2003 -http://msdn.microsoft.com/library/en-us/odc_ip2003_tr/html/odc_INF_Lab_09.asp?frame=true&_r=1
- Other InfoPath Labs -http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_2003_ta/html/odc_landinfo03_tr.asp

Re: SQL Connection via script

Mik — Fri, 02 Sep 2005 01:01:58 GMT

I have never produced a webservice before and this form has to be out quite soon. I am worried about the research time of producing the webservice at this point because of that.

I do want to develop them soon, but I have very little time to actually develop anything substancial at this point due to a number of events.

If you could point me to a specific site maybe? I could google, but if you could recommend anything that would be nice.

Re: SQL Connection via script

vt_asparagus — Thu, 01 Sep 2005 14:05:00 GMT

From an architecture and maintenance standpoint I would not put an insert statement into InfoPath. Since you have SQL Server, it would be best if you created a stored procedure in SQL Server. Then write a web service that will call the stored procedure. Then have your InfoPath form call the web service. The advantages are:

1 - You will decouple your UI layer (InfoPath) from the database layer.
2 - You have now created a re-useable service that can perform the same insert from other applications.
3 - If the insert needs to be changed, the change will be isolated to the stored procedure and the InfoPath form will not have to be re-published (decoupled).
4 - If your insert stored procedure needs to be re-written to connect to a different technology, Oracle or Access, only the web service would have to change and again InfoPath would not be affected (decoupled).
5 - As you mentioned, you want to do multiple insert statements, you can create a transaction within your web service to commit all of the insert statements or none either using ADO.NET or Transact SQL.

The development time to put together a simple stored proc and web service to call the stored proc is quick and has high impact.

SQL Connection via script

Mik — Wed, 31 Aug 2005 23:36:43 GMT

I have worked out how to do sql queries, but I read somewhere that you cannot use an insert function to a database via the script without it being fully trusted?
I am new to all of this scripting to take care of the entire submission process, the reasoning is that the content must go to multiple tables.

Right now I do not have the resources to try this but I would like to know before I start working on it as the due date for this form keeps getting moved closer and I only have a few days to finish it.

Also, we are using Microsoft SQL Server, this has an XML adapter of some type but i dont know how it works. Does anyone have an idea?

Cheers,