I am using Entity Framework code-first approach. I want to call a stored procedure from the DbContext
class and to get XML output.
Stored procedure (SQL Server):
CREATE PROCEDURE xml_test
AS
BEGIN
DECLARE @xml1 xml
SET @xml1 = (SELECT * from Product FOR XML RAW)
SELECT @xml1 AS my_xml
END
LINQ Entity Framework:
using (DBContext db = new DBContext())
{
var ProductList = await db.Database.ExecuteSqlCommandAsync("exec xml_test");
}
Here the ProductList
list is returning -1.
I want to get the xml output which is returned by the stored procedure.
Note: I have also tried methods like: ExecuteSqlCommand, SqlQuery with no help.
Simply uses of Stored Procedure with LINQ. Stored Procedure: Collection and Group of T-SQL commands is called Stored procedure. In this we can write commands of Creation, updation and deletion of parts of Data Definition Language (DDL), Data Manipulation Language (DML). We all prefer stored procedure writing compared to writing hardcoded query.
User level protection. Reduce network traffic. Language Integrated Query (LINQ), Linq to SQL works as ORD (Object Relational Designer). Before starting to work on LINQ To SQL you should understood the ORD. ORD is a canvas where you can create or drag and drop the table or entity on it from Server Explorer.
As you click on Configure Behavior option , Now time to Select BEHAVIOR and CUSTOMISE option to configure Stored Procedure with DBML. In the above screen shot we had configured stpInsertFriend same way we have to do Mapping for all CRUD base stored procedures.
Now switch to Visual Studio and click on App_Code folder then Double click on FriendDataClasses.dbml file. Click on Server Explorer which is left hand side. Drag n drop tblFriends on DBML canvas. Drag n drop stored procedure on DBML canvas. After draggin and dropping stored procedure we have to attach stored procedure with tblFriend entity.
I think you can use SQLQuery
like this:
using (var dbcontext = new DBContext())
{
//Reading stored procedure results as List<string>
var r = dbcontext.Database.SqlQuery<string>("EXEC xml_test").ToList(); //Note: EXEC is optional
//Joining strings to one string that causes in resulting long strings
var xmlString = string.Join("", r);
//Now you can load your string to a XmlDocument
var xml = new XmlDocument();
//Note: You need to add a root element to your result
xml.LoadXml($"<root>{xmlString}</root>");
}
Note: To get records from your stored procedure you need to add
SET NOCOUNT ON;
afterBEGIN
;).
CREATE PROCEDURE [dbo].[xml_test]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from dbo.AspNetUsers FOR XML RAW;
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With