c# - 如何在 Asp Net Core 中将 XML 输出从存储过程转换为 C# 对象
问题描述
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;
存储过程的实际输出
最后一列将包含这些信息
解决方案
根据您的描述,如果您已经使用 EF Core 创建数据库,并且可以通过 using _dbContext.<yourdbsetclass>.FromSqlRaw
方法接收存储过程数据。我建议您可以尝试为 EF 创建一个自定义ValueConverter来满足您的要求。
当您使用 EF 核心从 sqldatbase 获取数据时,valueconvert 可以将字符串转换为对象,当您要将数据插入数据库时,会将对象转换为字符串。
更多细节,您可以参考以下代码:
首先,您应该创建 StateWiseCompany 类
[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; }
}
其次,您应该创建转换器类:
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();
}
}
}
第三,您应该将 dbcontext 模型的属性类型从字符串修改为对象。
public class Organization
{
[Key]
public int OrgID { get; set; }
public StateWiseCompany OrgName { get; set; }
}
第四,我建议您应该修改 dbcontext 以覆盖 OnModelCreating 方法,如下所示:
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>());
}
) ;
}
最后,您可以使用 EF core dbcontext 调用 SP:
var re = _dbContext.Organizations.FromSqlRaw("EXEC [dbo].[getStateWiseCompanyDetails]").ToList();
结果:
数据库:
查询结果:
推荐阅读
- javafx - 如何设置滑块更改侦听器仅在 JavaFX 中释放鼠标拖动时触发?
- html - "font-style: italic" 使文本从容器中突出,切断文本
- sql - 使用 json_extract 在 JSON 数组中的所有对象中查找
- asp.net-mvc - Include/Where VS Where/Include EF MVC 性能
- algorithm - Fenwick Trees 中的更新步骤
- java - android.view.ContextThemeWrapper 无法转换为 android.app.Activity
- css - 词缀打破了引导程序的嵌套网格
- android - 如何使用 Kotlin 将我计算机中托管的 MySQL 连接到 Android 应用程序
- android - 无法获取 Google 依赖项 PKIX 路径构建失败
- sql - 分组数据的 SQL 查询