如何将XML输出从存储过程转换为Asp Net Core中的C#对象 [英] How to Convert XML output from Stored Procedure to C# object in Asp Net Core

问题描述

  SET ANSI_NULLS ON;GO SET QUOTED_IDENTIFIER ON;修改程序[dbo].[getStateWiseCompanyDetails] AS开始有数据AS(SELECT Companies.RegionId，公司代码Companies.CompanyName，Users.FirstName，公司.办公室电话来自公司INNER JOIN用户打开Companies.DirectorId = Users.Id)SELECT States.Id AS RegionId，States.StateName，States.IsRegion AS状态，Users.FirstName +''+ Users.LastName AS RegionDirector，Users.PhoneNumber，(选择 *来自数据WHERE data.RegionId = States.Id FOR XML PATH('CompanyList')，ROOT('StateWiseCompany')，TYPE)AS公司列表来自国家INNER JOIN用户在States.RegionDirector = Users.Id;结尾; 

存储过程的实际输出

将包含这些信息的最后一列

我想在我的点网Core Web应用程序中调用此存储过程.

解决方案

根据您的描述，如果您已经使用EF核心创建数据库并且可以通过使用 _dbContext来接收存储过程数据.; .FromSqlRaw 方法.我建议您可以尝试创建自定义

查询结果:

SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO ALTER PROC
[dbo].[getStateWiseCompanyDetails] AS
BEGIN
With Data
     AS (SELECT Companies.RegionId,
                Companies.Code,
                Companies.CompanyName,
                Users.FirstName,
                Companies.OfficePhone
         FROM Companies
              INNER JOIN Users ON Companies.DirectorId = Users.Id)
     SELECT States.Id AS RegionId,
            States.StateName,
            States.IsRegion AS Status,
            Users.FirstName + ' ' + Users.LastName AS RegionDirector,
            Users.PhoneNumber,
     (
         SELECT *
         FROM Data
         WHERE data.RegionId = States.Id FOR XML PATH('CompanyList'), ROOT('StateWiseCompany'), TYPE
     ) AS CompanyList
     FROM States
          INNER JOIN Users ON States.RegionDirector = Users.Id;
END;

Actual Output Of Stored Procedure

Last Column That will contain These information

I would like to call this stored procedure in my dot net Core Web application.

解决方案

According to your description, if you have already used EF core to create the database and could receive the Stored Procedure data by using _dbContext.<yourdbsetclass>.FromSqlRaw method. I suggest you could try to create a custom ValueConverter for EF to achieve your requirement.

The valueconvert could convert the string to object when you get the data from sqldatbase by using EF core and will convert the object to string when you want to insert the data into database.

More details, you could refer to below codes:

First, you should create StateWiseCompany class

[XmlRoot(ElementName = "CompanyList")]
public class CompanyList
{
    [XmlElement(ElementName = "RegionId")]
    public string RegionId { get; set; }
    [XmlElement(ElementName = "Code")]
    public string Code { get; set; }
    [XmlElement(ElementName = "CompanyName")]
    public string CompanyName { get; set; }
    [XmlElement(ElementName = "FirstName")]
    public string FirstName { get; set; }
    [XmlElement(ElementName = "OfficePhone")]
    public string OfficePhone { get; set; }
}

[XmlRoot(ElementName = "StateWiseCompany")]
public class StateWiseCompany
{
    [XmlElement(ElementName = "CompanyList")]
    public List<CompanyList> CompanyList { get; set; }
}

Second, you should create converter class:

public class ObjectToDbStringEquivalentConvertor<T> : ValueConverter<T, string>
{
    public ObjectToDbStringEquivalentConvertor(ConverterMappingHints mappingHints = null) : base(convertToProviderExpression, convertFromProviderExpression, mappingHints)
    { }

    private static Expression<Func<T, string>> convertToProviderExpression = x => ToDbString(x);
    private static Expression<Func<string, T>> convertFromProviderExpression = x => ToObject<T>(x);

    public static string ToDbString<T>(T obj)
    {

        using (var stringwriter = new System.IO.StringWriter())
        {
            var serializer = new XmlSerializer(typeof(T));
            serializer.Serialize(stringwriter, obj);
            return stringwriter.ToString();
        }

     }

    public static T ToObject<T>(string stringValue)
    {
        if (stringValue != string.Empty)
        {
            using (var stringReader = new System.IO.StringReader(stringValue))
            {
                var serializer = new XmlSerializer(typeof(T));
                var re = serializer.Deserialize(stringReader);
                return (T)re;
            }
        }
        else
        {
            throw new Exception();
        }
  
    }



}

Thirdly, you should modify your dbcontext model's property type from string to object.

public class Organization
{
    [Key]
    public int OrgID { get; set; }
    public StateWiseCompany OrgName { get; set; }
}

Forth, I suggest you should modify the dbcontext to override the OnModelCreating method like below:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Organization>(
            entity => {
                entity.HasKey(e => e.OrgID);
                entity.Property(e => e.OrgName).HasConversion(new ObjectToDbStringEquivalentConvertor<StateWiseCompany>());
                }
           
            ) ;

    }

At last, you could call the SP by using EF core dbcontext:

        var re = _dbContext.Organizations.FromSqlRaw("EXEC [dbo].[getStateWiseCompanyDetails]").ToList();

Result:

Database:

Query result:

这篇关于如何将XML输出从存储过程转换为Asp Net Core中的C#对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！