如何使用 SQL、LINQ 和 ADO. NET来 查询 JSON 数据

在当今互联的世界中，数据堆积得非常快。我们正在生成比人类历史上任何时候都多的数据，而且很多这些数据都以非关系格式存储，例如 JSON 文档。JSON 已成为现代应用程序无处不在的格式 - 用于配置文件、API 之间的数据传输、客户端和服务器、数据存储等。鉴于其简单性，使用 JSON 数据非常简单。在本博客中，我们将了解ComponentOne 的ADO.NET 程序，它提供了一种使用 ADO.NET 或 EntityFramework Core 轻松连接到 JSON 数据的方法。我们将讨论使用 C1 ADO.NET ，通过使用 SQL 和 LINQ 查询 JSON 。


如何使用 SQL 查询 JSON

在这个过程中，我们将遵循我们在处理数据库时通常使用的相同方法，即

• 创建连接字符串
• 创建连接对象
• 使用 SQL 查询数据
  

首先，我们从安装所需的 NuGet 包开始。

• 打开“项目”菜单并选择“管理 NuGet 包”。
• 查询”C1. AdoNet. Json“NuGet 包并单击安装。
  

创建连接字符串

为了连接我们的数据源，我们需要一个连接字符串。我们将使用C1JsonConnectionStringBuilder来创建我们的连接字符串。创建连接字符串，需要 3 项：

• JSON DataModel：它指定了用于读取 JSON 数据的访问机制。指定 DataModel，让我们配置如何将对象数组建模为表。可用的不同数据模型是——顶级文档模型、扁平模型、关系模型。
• Uri：指定 JSON 资源位置的 URI。您可以连接到本地文件或 http 流。
• JSON Path : 指定从哪个节点读取 json 内容
  

下面显示了为数据创建连接字符串的示例：

1. public static class Utils
   
2. {
   
3.         public static string JsonDataPath => "Data\\EmployeesData.json";
   
4.         public static C1JsonConnectionStringBuilder JsonConnectionBuilder { get; }
   
5. 
   
6.         static Utils()
   
7.         {
   
8.             JsonConnectionBuilder = new C1JsonConnectionStringBuilder()
   
9.             {
   
10.                 DataModel = "Document",
    
11.                 Uri = JsonDataPath,
    
12.                 JsonPath = "$.employees"
    
13.             };
    
14.         }
    
15. }

复制代码

类似地，以下显示了为关系数据创建连接字符串：

1. public static class Utils
   
2. {
   
3.         public static string JsonCustomerDataPath => "Data\\CustomersData.json";
   
4.         public static C1JsonConnectionStringBuilder CustomerDataConnectionStringBuilder { get; }
   
5. 
   
6.         static Utils()
   
7.         {
   
8.             CustomerDataConnectionStringBuilder = new C1JsonConnectionStringBuilder()
   
9.             {
   
10.                 DataModel = "Relational",
    
11.                 Uri = JsonCustomerDataPath,
    
12.                 JsonPath = "$.customers;$.customers.Transactions"
    
13.             };
    
14.         }
    
15. }

复制代码

如何创建连接对象

现在，我们已经获得了连接字符串，所以下一步是使用C1JsonConnection类初始化一个连接实例，如下所示：

1. private void CreateJsonConnection()
   
2. {
   
3. _jsonConnection = new C1JsonConnection(_connBuilder.ConnectionString);
   
4. }

复制代码

使用 SQL 查询数据

现在连接已准备就绪，我们可以通过创建C1JsonDataAdapter对象来使用 SQL 查询数据，如下所示：

1. C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, sql);
   
2. var table = new DataTable();
   
3. adapter.Fill(table);

复制代码

下面显示了一个示例平面数据和相应的查询：

1. C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, “select * from employees”);
   
2. var table = new DataTable();
   
3. adapter.Fill(table);

复制代码

对于关系数据，最终结果：

1. C1JsonDataAdapter adapter = new C1JsonDataAdapter(_jsonConnection, (“Select FirstName, LastName, Amount from customers INNER JOIN Transactions ON customers._id = Transactions.customers_id where Transactions.IsSuccess=false”);
   
2. var table = new DataTable();
   
3. adapter.Fill(table);

复制代码

填充 DataTable 后，数据可以显示在网格中，如下所示：

在 Entity Framework Core 中使用 LINQ 查询 JSON

在本节中，我们将讨论如何在 Entity Framework Core 中使用 LINQ 查询 JSON 数据。我们从安装所需的 NuGet 包开始，即C1.EntityFrameworkCore.Json

安装 nuget 包后，接下来我们需要设置 DbContext 并提供到 JSON 数据源的连接字符串。为此，我们将使用C1JsonConnectionStringBuilder，如下所示：

1. public abstract partial class DocumentContext : DbContext
   
2. {
   
3.         private C1JsonConnectionStringBuilder _builder;
   
4. 
   
5.         public DocumentContext(C1JsonConnectionStringBuilder builder)
   
6.         {
   
7.             _builder = builder;
   
8.             Database.AutoTransactionsEnabled = false;
   
9.         }
   
10. 
    
11.         public DocumentContext(DbContextOptions<DocumentContext> options)
    
12.             : base(options)
    
13.         {
    
14.             Database.AutoTransactionsEnabled = false;
    
15.         }
    
16. 
    
17.         protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    
18.         {
    
19.             if (!optionsBuilder.IsConfigured)
    
20.             {
    
21.                 optionsBuilder.UseJson(_builder.ConnectionString);
    
22.             }
    
23.         }
    
24. }

复制代码

如何创建 DbContext

上面的DocumentContext类可以作为我们 JSON 文档的 DbContext 的基类。模型实体可以在子类中定义。

下面显示了平面 JSON 数据源的 DbContext 示例：

1. public class EmployeesContext : DocumentContext
   
2. {
   
3.         public virtual DbSet<Employee> Employees { get; set; }
   
4. 
   
5.         public EmployeesContext() : base(Utils.JsonConnectionBuilder) { }
   
6. 
   
7.         protected override void OnModelCreating(ModelBuilder modelBuilder)
   
8.         {
   
9.             modelBuilder.Entity<Employee>(entity =>
   
10.             {
    
11.                 entity.ToTable("employees");
    
12.                 entity.HasKey(x => x.Id);
    
13.                 entity.Property(e => e.Id).HasColumnName("Id");
    
14.                 entity.Property(e => e.FirstName).HasColumnName("FirstName");
    
15.                 entity.Property(e => e.LastName).HasColumnName("LastName");
    
16.                 entity.Property(e => e.Email).HasColumnName("Email");
    
17.                 entity.Property(e => e.DOB).HasColumnName("DOB");
    
18.                 entity.Property(e => e.Address).HasColumnName("Address");
    
19.                 entity.Property(e => e.State).HasColumnName("State");
    
20.                 entity.Property(e => e.Company).HasColumnName("Company");
    
21.                 entity.Property(e => e.Gender).HasColumnName("Gender");
    
22.                 entity.Property(e => e.JobTitle).HasColumnName("JobTitle");
    
23.                 entity.Property(e => e.Skill).HasColumnName("Skill");
    
24.                 entity.Property(e => e.Salary).HasColumnName("Salary");
    
25.             });
    
26.         }
    
27. }

复制代码

下面显示了关系 JSON 数据源的 DbContext 示例：

1. public class CustomersContext : DocumentContext
   
2. {
   
3.         public virtual DbSet<Customer> Customers { get; set; }
   
4.         public virtual DbSet<Transaction> Transactions { get; set; }
   
5. 
   
6.         public CustomersContext() : base(Utils.CustomerDataConnectionStringBuilder) { }
   
7. 
   
8.         protected override void OnModelCreating(ModelBuilder modelBuilder)
   
9.         {
   
10.             modelBuilder.Entity<Transaction>(entity =>
    
11.             {
    
12.                 entity.ToTable("Transactions");
    
13.                 entity.HasOne(e => e.Customer).WithMany(x => x.Transactions).HasForeignKey("customers_id");
    
14.                 entity.Property(e => e.Id).HasColumnName("_id");
    
15.                 entity.Property(e => e.Amount).HasColumnName("Amount");
    
16.                 entity.Property(e => e.Credited).HasColumnName("Credited");
    
17.                 entity.Property(e => e.IsSuccess).HasColumnName("IsSuccess");
    
18.                 entity.Property(e => e.Date).HasColumnName("Date");
    
19.             });
    
20. 
    
21.             modelBuilder.Entity<Customer>(entity =>
    
22.             {
    
23.                 entity.ToTable("customers");
    
24.                 entity.Property(e => e.Id).HasColumnName("_id");
    
25.                 entity.Property(e => e.FirstName).HasColumnName("FirstName");
    
26.                 entity.Property(e => e.LastName).HasColumnName("LastName");
    
27.                 entity.Property(e => e.DOB).HasColumnName("DOB");
    
28.                 entity.Property(e => e.State).HasColumnName("State");
    
29.                 entity.Property(e => e.Gender).HasColumnName("Gender");
    
30.             });
    
31.         }
    
32. }

复制代码

使用 LINQ 查询 DbContext

既然设置了 DbContext，就可以使用 LINQ 查询 JSON 数据源。我们可以使用查询语法或方法语法：

1. _context = new EmployeesContext();
   
2. var employees = await _context.Employees.ToListAsync();
   
3. 
   
4. // Employee count of companies starting with 'A'
   
5. var employeeCountData = from employee in employees
   
6.                         where employee.Company.StartsWith("A", StringComparison.OrdinalIgnoreCase)
   
7.                         group employee by employee.Company into grp
   
8.                         select new { Company = grp.Key, NumberOfEmployees = grp.Count() };
   
9. 
   
10. // Salary growth of people working in Dynabox
    
11. var salaryGrowthData = employees
    
12.          .Where(x => x.Company == "Dynabox").OrderBy(x => x.DOB)
    
13.          .Select(x => new { Age = DateTime.Now.Year - x.DOB.Year, Salary = x.Salary })
    
14.          .Where(x => x.Age >= 18)
    
15.          .GroupBy(x => x.Age)
    
16.          .Select(x => new { Age = x.Key, AverageSalary = x.Select(o => o.Salary).Average() });

复制代码

然后可以在网格/图表中使用数据，如下所示：