asp.net 如何从外部API存储数据到我的MS SQL数据库

bhmjp9jg  于 2022-12-24  发布在  .NET
关注(0)|答案(3)|浏览(132)

我正在尝试从外部API提取数据,并使用实体框架将此数据保存在MS SQL数据库中。我是实体框架新手,不知道如何使数据持久化。数据库是根据以下模型遵循代码优先原则创建的:

JSON响应.cs

public partial class JsonResponse
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    [JsonProperty("prtg-version")]
    public string PrtgVersion { get; set; }
    [JsonProperty("treesize")]
    public int TreeSize { get; set; }
    [JsonProperty("devices")]
    public Devices[] devices { get; set; }
}

public partial class Devices
{
    [Key]
    [JsonProperty("objid")]
    public int objid { get; set; }
    [JsonProperty("probe")]
    public string probe { get; set; }
    [JsonProperty("device")]
    public string device { get; set; }
    [JsonProperty("host")]
    public string host { get; set; }
}

我成功地从JSON中的API接收了数据,将其反序列化并添加到List <JsonResponse> dataG中。现在我希望通过将其保存到EF数据库来使此数据持久化。我正在努力解决如何做到这一点,因为我的JsonResponse包含Devices对象的列表。我的控制器(在其中从外部API提取数据)如下所示:

CMDB控制器.cs

public class CMDBController : Controller
{
    private DbContext db = new DbContext();

    public async Task<ActionResult> Test()
    {
        List<JsonResponse> dataG = new List<JsonResponse>();
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient
                .GetAsync(
                    "/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
            )
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
                dataG.Add(data);
                var devices = data.devices;

                foreach (var item in devices)
                {
                    db.Add(item);
                    db.SaveChanges();
                }
            }
        }
        return View(dataG);
    }

来自API的JSON数据如下所示:

{
prtg-version: "20.4.63.1412",
treesize: 2,
devices: [
{
objid: 40,
probe: "Local Probe",
group: "Local Probe",
device: "Probe Device",
host: "127.0.0.1"
},
{
objid: 42,
probe: "Local Probe",
group: "Network Infrastructure",
device: "DNS: 84.116.46.23",
host: "84.116.46.23"
}

数据库上下文

public class FrontDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "Server=(localdb)\\MSSQLLocalDB;Database=FrontDB;MultipleActiveResultSets=true");
    }

    public DbSet<JsonResponse> Responses { get; set; }
}

}

编辑1

public class CMDBController : Controller
{
    private DbContext db = new DbContext();

    public async Task<ActionResult> Test()
    {
        List<JsonResponse> dataG = new List<JsonResponse>();
        using (var httpClient = new HttpClient())
        {
            using (var response = await httpClient
                .GetAsync(
                    "/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
            )
            {
                string apiResponse = await response.Content.ReadAsStringAsync();
                var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
                dataG.Add(data);
                var devices = data.devices;
                db.Responses.AddRange(data);
                db.SaveChanges();
            }
        }
        return View(dataG);
    }
bzzcjhmw

bzzcjhmw1#

你好,这是我的第一个答案,我正在学习英语我会努力做得更好。
你有两张table吗?一张主-明细表?
我建议你组织你的代码和分离的责任,首先你需要有一个Dto对象来表示你的JSON对象和一个实体来插入数据到一个数据库,然后分离的方法,但我会分享你的解决方案与您当前的实体。
首先,您需要有一个包含实体和连接字符串的DbContext,这是一个包含EntityFrameworkCore的DbContext示例:

public class MyDbContext:DbContext
    {
        private readonly string connectionString;

        public MyDbContext(string connectionString)
        {
            this.connectionString = connectionString;
            _migrateDatabase = true;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseMySql(connectionString);
        }

        public DbSet<JsonResponse> JsonResponses { get; set; }
        public DbSet<Devices> Devices { get; set; }
    }

如果您有一个主详细信息,您需要创建一个带有外键的导航属性,在本例中,我在Device实体中创建了此属性。

public partial class JsonResponse
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    [JsonProperty("prtg-version")]
    public string PrtgVersion { get; set; }
    [JsonProperty("treesize")]
    public int TreeSize { get; set; }

    [JsonProperty("devices")]
    public List<Devices> devices { get; set; }
}

public partial class Devices
{
    [Key]
    [JsonProperty("objid")]
    public int objid { get; set; }
    [JsonProperty("probe")]
    public string probe { get; set; }
    [JsonProperty("device")]
    public string device { get; set; }
    [JsonProperty("host")]
    public string host { get; set; }

    public int JsonResponseId { get; set; }
    
    [ForeignKey("JsonResponseId")]
    public JsonResponse JsonResponse { get; set; }
}

要保存数据,您需要添加主实体,明细将自动保存:

public class CMDBController : Controller
{
    private MyDbContext db = new MyDbContext("mycnn");

    public async Task<ActionResult> Test()
    {
        JsonResponse data = GetJsonData();
        db.JsonResponses.Add(data);
        db.SaveChanges();
        return View(data);
    }

    JsonResponse GetJsonData(){
         using (var httpClient = new HttpClient())
        {
          var response = await httpClient.GetAsync(
                    "/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host");
          string apiResponse = await response.Content.ReadAsStringAsync();
          return JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
        }
    }
}

如果您没有主详细信息,但需要保存一定范围的数据,则必须使用AddRange,并在一个操作中保存所有数据:

public async Task<ActionResult> Test()
    {
        JsonResponse data = GetJsonData();
        db.Devices.AddRange(data.Devices);
        db.SaveChanges();
        return View(data);
    }
bqjvbblv

bqjvbblv2#

正如marc_s所说,EF核心支持访问许多不同的数据库,您使用的是哪种数据库?
一般情况下,通过EF core向数据库中插入新数据,可以使用DbContext.Add方法或DbContext.AddRange方法添加新项,您可以查看以下文章:EF core Saving data.
我假设您使用的是MS SQL Server数据库,根据您的描述,JsonResponseDevices对象包含one-to-Many relationship,因此您可以使用导航属性,而不是Devices[],尝试更改您的代码如下:

public partial class JsonResponse
{
    [Key] 
    public int Id { get; set; }
    [JsonProperty("prtg-version")]
    public string PrtgVersion { get; set; }
    [JsonProperty("treesize")]
    public int TreeSize { get; set; }
    [JsonProperty("devices")]
    public List<Devices> devices { get; set; }
}

public partial class Devices
{
    [Key()]
    [DatabaseGenerated(DatabaseGeneratedOption.None)] // prevent database auto generate objid.
    [JsonProperty("objid")]
    public int objid { get; set; }
    [JsonProperty("probe")]
    public string probe { get; set; }
    [JsonProperty("device")]
    public string device { get; set; }
    [JsonProperty("host")]
    public string host { get; set; }
}

然后,在迁移并在数据库中生成相关表之后,可以引用以下代码将新项插入数据库:

private readonly ILogger<HomeController> _logger;
    private readonly WebApplication2Context _dbcontext;

    public HomeController(ILogger<HomeController> logger, WebApplication2Context context)
    {
        _logger = logger;
        _dbcontext = context;
    }

    public IActionResult Index()
    {
        List<JsonResponse> data = new List<JsonResponse>()
        {
            new JsonResponse(){ PrtgVersion ="20.4.63.1412", TreeSize = 2, devices = new List<Devices>()
            {
                new Devices(){ objid= 40, probe="Local Probe", device = "Probe Device", host = "127.0.0.1"},
                new Devices(){ objid= 41, probe="Local Probe", device = "DNS: 84.116.46.23", host = "86.114.46.23"}
            }
            }
        };
        _dbcontext.JsonResponses.AddRange(data);
        _dbcontext.SaveChanges();
        return View();
    }

注解:由于JsonResponse对象和Devices对象是一对多的关系,所以当使用上面的代码插入一个新的JsonResponse时,它会自动将相关的Devices对象插入到Devices表中。更多详细信息,请查看Saving Related Data
此外,还可以使用以下代码将新设备添加到Devices表中:

List<Devices> devicesdata = new List<Devices>()
        {
             //add Devices.
         };
        _dbcontext.Devices.AddRange(devicesdata);
        _dbcontext.SaveChanges();

参考:Entity Framework Core One To Many Relationships Conventions
导航属性

pgccezyw

pgccezyw3#

Update the DbContextClass

public class MyDbContext:DbContext
{
    private readonly string connectionString;

    public MyDbContext(string connectionString)
    {
        this.connectionString = connectionString;
        _migrateDatabase = true;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        optionsBuilder.UseMySql(connectionString);
    }

    public DbSet<JsonResponse> JsonResponses { get; set; }
}

更新控制器

public async Task<ActionResult> Test()
{        
    using (var httpClient = new HttpClient())
    {
        using (var response = await httpClient
            .GetAsync(
                "/api/table.json?content=devices&output=json&columns=objid,probe,group,device,host")
        )
        {
            string apiResponse = await response.Content.ReadAsStringAsync();
            var data = JsonConvert.DeserializeObject<JsonResponse>(apiResponse);
            db.JsonResponses.Add(data);
            db.SaveChang();
        }
    }
    return View(dataG);
}

相关问题