I created a Web Service method that calls a Stored Procedure, which accepts an XML input parameter, in MSSQL 2005.
I've created one in MSSQL 2008 and got it working but not in MSSQL 2005. The GridView shows the correct number of rows and columns but all are blank.
c# for generating XML:
DataSet CollectedReceivingData = new DataSet("CollectedReceivingData");
DataTable SR_Header = CollectedReceivingData.Tables.Add("SR_Header");
DataTable SR_Details = CollectedReceivingData.Tables.Add("SR_Details");
SR_Header.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("job_no", Type.GetType("System.Int32"));
SR_Header.Columns.Add("item_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("expiry_date", typeof(DateTime));
SR_Header.Columns.Add("remarks", typeof(string));
SR_Header.Columns.Add("rcv_qty", Type.GetType("System.Int32"));
SR_Header.Rows.Add("327051", "0381021", "21848", "02/03/2016", "This is a remarks test.", "50");
SR_Header.Rows.Add("327052", "0381021", "21849", "02/05/2016", "This is a remarks test for item 2", "25");
SR_Details.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("pallet_id", typeof(string));
SR_Details.Columns.Add("qty_in_pallet", Type.GetType("System.Int32"));
SR_Details.Columns.Add("expiry_date", typeof(DateTime));
SR_Details.Columns.Add("remarks", typeof(string));
SR_Details.Columns.Add("warehouse_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("location_id", Type.GetType("System.Int32"));
SR_Details.Rows.Add("327051", "327051-001", "24", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-002", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-003", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-004", "15", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327052", "327052-001", "5", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-002", "15", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-003", "5", "02/03/2016", "This is a remarks test.", "3", "2");
DataSet ds = new DataSet();
WebService1 wsobject = new WebService1();
ds = wsobject.UpdateCollectedReceivingData(CollectedReceivingData);
gvReceiving.DataSource = ds.Tables[0];
gvReceiving.DataBind();
gvReceiving2.DataSource = ds.Tables[1];
gvReceiving2.DataBind();
Web Method:
[WebMethod]
public DataSet UpdateCollectedReceivingData(DataSet ds)
{
DataSet ds_result = new DataSet();
XmlDocument xmlDoc = new XmlDocument();
string connectionString = ConfigurationManager.ConnectionStrings["ConnStringWS"].ConnectionString;
SqlConnection objConn = new SqlConnection(connectionString);
DataSet dsResult = new DataSet();
objConn.Open();
using (SqlCommand cmd = new SqlCommand("[dbo].[SP_Receive]"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = objConn;
cmd.Parameters.Add("@x", SqlDbType.Xml).Value = ds.GetXml();
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
da.Fill(ds_result);
}
catch
{
}
}
objConn.Close();
return ds_result;
}
Stored Procedure:
CREATE PROCEDURE [dbo].[SP_Receive]
(
@x XML
)
AS
BEGIN
DECLARE @t1 TABLE (
[receiving_id] [int] NULL,
[job_no] [int] NULL,
[item_id] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[rcv_qty] [int] NULL
)
DECLARE @t2 TABLE(
[receiving_id] [int] NULL,
[pallet_id] [nvarchar](20) NULL,
[qty_in_pallet] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[warehouse_id] [int] NULL,
[location_id] [int] NULL
)
INSERT INTO @t1 (
[receiving_id],
[job_no],
[item_id],
[expiry_date],
[remarks],
[rcv_qty]
)
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@job_no','int') as [job_no],
xmlVals.rowvals.value('@item_id','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@rcv_qty','int') as [rcv_qty]
FROM
@x.nodes('//CollectedReceivingData/SR_Header') as xmlVals(rowvals)
INSERT INTO @t2 (
[receiving_id],
[pallet_id],
[qty_in_pallet],
[expiry_date],
[remarks],
[warehouse_id],
[location_id]
)
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@pallet_id','nvarchar(20)') as [job_no],
xmlVals.rowvals.value('@qty_in_pallet','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@warehouse_id','int') as [warehouse_id],
xmlVals.rowvals.value('@location_id','int') as [location_id]
FROM
@x.nodes('//CollectedReceivingData/SR_Details') as xmlVals(rowvals)
SELECT * INTO #tmp1 FROM
(
SELECT t1.receiving_id as receiving_id,
error_message = CASE
WHEN t2.receiving_id IS NOT NULL THEN 'Item has already been received.'
WHEN total_rcv_qty > t1.rcv_qty THEN 'Total received quantity exceeded the quantity to be received.'
ELSE NULL
END
FROM (
SELECT SD.receiving_id, SH.job_no, SH.item_id, SH.rcv_qty, SUM(qty_in_pallet) as total_rcv_qty
FROM @t2 SD
INNER JOIN @t1 SH ON SH.receiving_id = SD.receiving_id
GROUP BY SD.receiving_id, SH.job_no, SH.item_id, SH.rcv_qty, SD.receiving_id ) t1
LEFT JOIN StockIN_Header t2 ON t1.receiving_id = t2.receiving_id
LEFT JOIN StorBest.dbo.item_master IM ON t1.item_id = IM.itemid
) AS tmp1
SELECT * INTO #tmp2 FROM
(SELECT SD.receiving_id, SD.pallet_id,
error_message = CASE
WHEN WLV.warehouseid IS NULL THEN 'Invalid location_id.'
WHEN W.warehouseid IS NULL THEN 'Invalid warehouse_id.'
WHEN SD2.pallet_id IS NOT NULL THEN 'Pallet has already been received'
ELSE NULL
END
FROM @t2 SD
LEFT JOIN StorBest.dbo.Warehouse_Loc_VIEW WLV ON SD.location_id = WLV.LocationID
LEFT JOIN StorBest.dbo.Warehouse W ON SD.warehouse_id = W.warehouseid
LEFT JOIN StockIN_Details SD2 ON SD.pallet_id = SD2.pallet_id
) AS tmp2
SELECT * FROM @t1
SELECT * FROM @t2
-- IF (SELECT COUNT(*) FROM (SELECT error_message FROM #tmp1 UNION ALL SELECT error_message FROM #tmp2) AS t WHERE error_message IS NOT NULL) = 0
-- BEGIN
-- INSERT INTO StockIN_Header SELECT * FROM @t1
-- INSERT INTO StockIN_Details SELECT * FROM @t2
-- END
END
Aucun commentaire:
Enregistrer un commentaire