Re: Submit Multiple Lines from InfoPath to SQL table via c# webservice

Patrick Halstead — Wed, 19 Aug 2009 06:41:54 GMT

Why not just use the free trial Web Service that we provide? It handles the mapping dynamically to multiple rows, and arbitrarily deep child tables and you don't have to write any code which means less maintenance and time. The Web Service is DBXL. It's got a free trial license and the perpetual server license for a single form is only $395 if you find it useful. Lots of other features too. Qdabra.com -> Products -> DBXL -> save time and money.

Submit Multiple Lines from InfoPath to SQL table via c# webservice

Garethw — Mon, 17 Aug 2009 15:40:44 GMT

I have written a web service to submit data from an InfoPath form to an SQL table,

This works fine for one line but does not allow me to submit multiple lines, as I'm new to C# and InfoPath I need some assistance. I think I need to create a loop/array but don't quite have the C# skills to do this yet...

I've added my code below...

using System;

using System.Data;

using System.Web;

using System.Collections;

using System.Web.Services;

using System.Web.Services.Protocols;

using System.ComponentModel;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Xml;

namespace NAVWebPurchase

{

/// <summary>

/// Webservice for InfoPath to SQL to NAV connction

/// </summary>

[WebService(Namespace = "http://litwaredemo/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ToolboxItem(false)]

public class Service : System.Web.Services.WebService

{

public class Purchase_Header

{

public System.Data.SqlTypes.SqlDateTime DocumentDate;

public System.Data.SqlTypes.SqlDateTime DueDate;

public System.Data.SqlTypes.SqlString Purpose;

public System.Data.SqlTypes.SqlString Name;

public System.Data.SqlTypes.SqlString Manager;

public Decimal TotalCost;

public System.Data.SqlTypes.SqlString No_;

public System.Data.SqlTypes.SqlString VendorInvoiceNo;

public System.Data.SqlTypes.SqlString Department;

public System.Data.SqlTypes.SqlString VendorNo;

public int LineNo;

public System.Data.SqlTypes.SqlString Description;

public System.Data.SqlTypes.SqlString Cost;

}

[WebMethod]

public int AddNewPurchase_Header(Purchase_Header Purchase_Header)

{

int rowsAffected = 0;

using (SqlConnection conn = new SqlConnection("Data Source=LITWAREDEMO;Initial Catalog=Demo Database NAV (6-0);Integrated Security=SSPI"))

{

SqlCommand cmd = new SqlCommand("INSERT INTO [CRONUS UK Ltd_$SharePoint PQ Header] " +

"([Document Date], [Due Date], [Purpose], [Name], [Manager], [Total Cost], [No_], [Vendor Invoice No_], [Department], [Vendor No_], [Line No_], [Description], [Cost]) " +

"VALUES(@DocumentDate, @DueDate, @Purpose, @Name, @Manager, @TotalCost, @No_, @VendorInvoiceNo_, @Department, @VendorNo_, @LineNo, @Description, @Cost)", conn);

cmd.Parameters.AddWithValue("@DocumentDate", Purchase_Header.DocumentDate);

cmd.Parameters.AddWithValue("@DueDate", Purchase_Header.DueDate);

cmd.Parameters.AddWithValue("@Purpose", Purchase_Header.Purpose);

cmd.Parameters.AddWithValue("@Name", Purchase_Header.Name);

cmd.Parameters.AddWithValue("@Manager", Purchase_Header.Manager);

cmd.Parameters.AddWithValue("@TotalCost", Purchase_Header.TotalCost);

cmd.Parameters.AddWithValue("@No_", Purchase_Header.No_);

cmd.Parameters.AddWithValue("@VendorInvoiceNo_", Purchase_Header.VendorInvoiceNo);

cmd.Parameters.AddWithValue("@Department", Purchase_Header.Department);

cmd.Parameters.AddWithValue("@VendorNo_", Purchase_Header.VendorNo);

cmd.Parameters.AddWithValue("@LineNo", Purchase_Header.LineNo);

cmd.Parameters.AddWithValue("@Description", Purchase_Header.Description);

cmd.Parameters.AddWithValue("@Cost", Purchase_Header.Cost);

conn.Open();

rowsAffected = cmd.ExecuteNonQuery();

}

return rowsAffected;

}

Newbie Questions

Re: Submit Multiple Lines from InfoPath to SQL table via c# webservice

Submit Multiple Lines from InfoPath to SQL table via c# webservice