Update data using Entity model

Question

How to force Entity Framework to work with stored procedure with XML input parameter? Data is Updating Perfectly with Sql connection but I only have to use Entity framework

I have tried ExecuteFunction and ExecuteStoreQuery both are giving different Error as below

Both statements are giving Error

return base.ExecuteFunction("pr_UpdateSupplierSubmittedExportdata_1", tblExportDataParameter, batchIDParameter, userNameParameter); ex = {"The stored procedure or function 'pr_UpdateSupplierSubmittedExportdata_1' does not have a return type. ExecuteFunction only supports stored procedures and functions that have a return type. "}

return base.ExecuteStoreQuery("Exec pr_UpdateSupplierSubmittedExportdata_1 @tblExportData, @BatchID, @UserName ", tblExportData, batchID, userName); Must declare the scalar variable "@tblExportData".

var getColumn = (from sp in (objLCSEntities.pr_UpdateSupplierSubmittedExportdata_1(xmlString1, Convert.ToInt32(sExportID), strUserFullName)) select sp).First().ToString();

Niketa Kapoor · Answer

I am passing all parameters with same datatype.Still facing Error.

Munesh Sharma · Answer

if it is giving error for conversion then yoy have see the field data type in sql server and the passing value to that field .,may be its mitchmatch ...hence giving error.

Niketa Kapoor · Answer

Getting Error at Line that Convertion Error From Nvarchar to int return base.ExecuteStoreQuery ("exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName ", tblExportData, batchID, userName); Below is my SP CREATE PROCEDURE [UpdateSupplierSubmittedExportdata_1] ( @tblExportData XML, @BatchID INT , @UserName varchar (200) ) AS BEGIN SET NOCOUNT ON ; --UPDATE EXISTING RECORDS -- import xml into temp table -- Creating Temporary Table CREATE TABLE #TEMP_TABLES ( Updated_Accepted_Shipment_Date_EX_Works nvarchar( max ), Updated_Invoice_Number nvarchar( max ), Updated_Invoice_Date nvarchar( max ), Updated_Handover_Date nvarchar( max ), Updated_Revised_Delivery_Date_Ex_Works nvarchar( max ), Updated_PO_Line_Progress nvarchar( max ), Updated_Mode_of_Shipment nvarchar( max ), Updated_Supplier_Remarks nvarchar( max ), Updated_GSM_Remarks nvarchar( max ), Updated_Weight int , Updated_Inspection_Offer_Date nvarchar( max ), Updated_Development nvarchar( max ), Updated_Transit_Qty int , Order_x0020_ID int ); -- insert into @temp --select T.c.value('@CustomerID', 'nchar(5)'), -- T.c.value('@ContactName', 'nvarchar(40)') --from @XML.nodes('/ROOT/Customer') AS T(c) INSERT INTO #TEMP_TABLES SELECT (x.c.value( 'Updated_Accepted_Shipment_Date_EX_Works[1]' , 'nvarchar(max)' )), --as Updated_Accepted_Shipment_Date_EX_Works, x.c.value( 'Updated_Invoice_Number[1]' , 'nvarchar(max)' ), -- as Updated_Invoice_Number, x.c.value( 'Updated_Invoice_Date[1]' , 'nvarchar(max)' ), --as Updated_Invoice_Date, x.c.value( 'Updated_Handover_Date[1]' , 'nvarchar(max)' ), -- as Updated_Handover_Date, x.c.value( 'Updated_Revised_Delivery_Date_Ex_Works[1]' , 'nvarchar(max)' ), --as Updated_Revised_Delivery_Date_Ex_Works, x.c.value( 'Updated_PO_Line_Progress[1]' , 'nvarchar(max)' ), --as Updated_PO_Line_Progress, x.c.value( 'Updated_Mode_of_Shipment[1]' , 'nvarchar(max)' ), --as Updated_Mode_of_Shipment, x.c.value( 'Updated_Supplier_Remarks[1]' , 'nvarchar(max)' ), --as Updated_Supplier_Remarks, x.c.value( 'Updated_GSM_Remarks[1]' , 'nvarchar(max)' ), --as Updated_GSM_Remarks, x.c.value( 'Updated_Weight[1]' , 'int' ), --as Updated_Weight, x.c.value( 'Updated_Inspection_Offer_Date[1]' , 'nvarchar(max)' ), --as Updated_Inspection_Offer_Date, x.c.value( 'Updated_Development[1]' , 'nvarchar(max)' ), --as Updated_Development x.c.value( 'Updated_Transit_Qty[1]' , 'int' ), --as Updated_Development x.c.value( 'Order_x0020_ID[1]' , 'int' ) --as Updated_Development FROM @tblExportData.nodes( '/DocumentElement/MyTableName' ) as x(c) --select * from #TEMP_TABLE232 --UPDATE EXISTING RECORDS UPDATE dbo.tblExportOrderdata SET Original_Ship_Dt = convert (datetime,c2.[Updated_Accepted_Shipment_Date_EX_Works]) ,In_Transit_Qty = c2.[Updated_Transit_Qty] ,Invoice_No=c2.[Updated_Invoice_Number] ,Invoice_Date=c2.[Updated_Invoice_Date] ,Handover_Dt=c2.[Updated_Handover_Date] ,Rev_Delivery_Dt=c2.[Updated_Revised_Delivery_Date_Ex_Works] ,PO_line_progress=c2.[Updated_PO_Line_Progress] ,Mode_Of_Shipment=c2.[Updated_Mode_of_Shipment] --,GSM_Buyer_Remark=c2.[Updated_GSM Remarks] ,Supplier_Remark=c2.[Updated_Supplier_Remarks] ,Inspection_Offer_Date=c2.[Updated_Inspection_Offer_Date] ,weight=c2.[Updated_Weight] ,Development=c2.[Updated_Development] --,Status='Updated by Supplier' FROM dbo.tblExportOrderdata c1 INNER JOIN #TEMP_TABLES c2 ON c1.Order_Id = c2.Order_x0020_ID and c1.Batch_ID=@BatchID DROP table #TEMP_TABLES UPDATE tblExportOrderdata SET Original_Ship_Dt= NULL where Original_Ship_Dt= '1900-01-01 00:00:00.000' and Batch_ID=@BatchID UPDATE tblExportOrderdata SET Invoice_Date= NULL where Invoice_Date= '1900-01-01 00:00:00.000' and Batch_ID=@BatchID UPDATE tblExportOrderdata SET Handover_Dt= NULL where Handover_Dt= '1900-01-01 00:00:00.000' and Batch_ID=@BatchID UPDATE tblExportOrderdata SET Rev_Delivery_Dt= NULL where Rev_Delivery_Dt= '1900-01-01 00:00:00.000' and Batch_ID=@BatchID UPDATE tblExportOrderdata SET Inspection_Offer_Date= NULL where Inspection_Offer_Date= '1900-01-01 00:00:00.000' and Batch_ID=@BatchID IF EXISTS ( select 1 from [LCS_QA].[dbo].[tblExportInbox] where Batch_No=@BatchID AND Status= 'Submitted To Supplier' ) --and Fls_Site_Name IN (SELECT * FROM DBO.CSVTOTABLE(@FlsSiteName)) --and Country IN (SELECT * FROM DBO.CSVTOTABLE(@Country))) -- BEGIN UPDATE [LCS_QA].[dbo].[tblExportInbox] SET Status= 'Updated By Supplier' , Updated_Date=getdate() , Updated_By=@UserName WHERE Batch_No=@BatchID --AND Status='Submitted To Supplier' END DECLARE @ResultValue int SET @ResultValue=-1 select @ResultValue END

Madan Shekar · Answer

please go with debug mode put brak point bigining only and please let me know where you exactly getting error

Niketa Kapoor · Answer

I have done it Using the article but Now it is giving me error of convertion failure from nvarchar to int public ObjectResult pr_UpdateSupplierSubmittedExportdata_1(global::System.String tblExportData, Nullable batchID, global::System.String userName) { ObjectParameter tblExportDataParameter; if (tblExportData != null ) { tblExportDataParameter = new ObjectParameter( "tblExportData" , tblExportData); } else { tblExportDataParameter = new ObjectParameter( "tblExportData" , typeof (global::System.String)); } ObjectParameter batchIDParameter; if (batchID.HasValue) { batchIDParameter = new ObjectParameter( "BatchID" , batchID); } else { batchIDParameter = new ObjectParameter( "BatchID" , typeof (global::System.Int32)); } ObjectParameter userNameParameter; if (userName != null ) { userNameParameter = new ObjectParameter( "UserName" , userName); } else { userNameParameter = new ObjectParameter( "UserName" , typeof (global::System.String)); } return base .ExecuteStoreQuery( "exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName " , tblExportData, batchID, userName); //return base.ExecuteFunction("pr_UpdateSupplierSubmittedExportdata_1", tblExportDataParameter, batchIDParameter, userNameParameter); // return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery("exec Reason_Codes_Search_SP @SearchField, @SearchText ", searchFieldParameter, searchTextParameter); // return base.ExecuteStoreQuery("Exec pr_UpdateSupplierSubmittedExportdata_1 tblExportData, BatchID,UserName", tblExportData, batchID, userName); // return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery //("exec Reason_Codes_Search_SP @SearchField, @SearchText ", searchFieldParameter, searchTextParameter); }

Madan Shekar · Answer

http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx if this link helps you means pls accept the ans