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;
}
}
}