获取所有类别及其相关产品的查询
创建四个包含序列化类别和产品的文件。
Mian程序:
//导出类别和产品数据 到 xml csv json文件
static void Main(string[] args)
{
WriteLine("Creating four files containing serialized categories and products...");
using (var db = new Northwind())
{
//获取所有类别及其相关产品的查询 a query to get all categories and their related products
IQueryable<Category> cats = db.Categories.Include(c => c.Products);
/*创建四个包含序列化类别和产品的文件。
Creating four files containing serialized categories and products...
categories-and-products-using-attibutes.xml contains 8,486 bytes.
categories-and-products-using-elements.xml contains 16,440 bytes.
categories-and-products.csv contains 6,686 bytes.
categories-and-products.json contains 14,146 bytes.
*/
GenerateXmlFile(cats);
GenerateXmlFile(cats, useAttributes: false);
GenerateCsvFile(cats);
GenerateJsonFile(cats);
}
}
写入xml文件
// we want to easily show the difference between outputting
// XML using elements or attributes so we will define a
// delegate to reference the two different methods.
//我们希望轻松显示使用元素或属性输出 XML 之间的区别,
//因此我们将定义一个委托来引用这两种不同的方法。
private delegate void WriteDataDelegate(string name, string value);
private static void GenerateXmlFile(IQueryable<Category> cats, bool useAttributes = true)
{
string which = useAttributes ? "attibutes" : "elements";
string xmlFile = $"categories-and-products-using-{which}.xml";
using (FileStream xmlStream = File.Create(
Combine(CurrentDirectory, xmlFile)))
{
using (XmlWriter xml = XmlWriter.Create(xmlStream,
new XmlWriterSettings { Indent = true }))
{
WriteDataDelegate writeMethod;
if (useAttributes)
{ //在派生类中重写时,写出具有指定本地名称和值的属性。
writeMethod = xml.WriteAttributeString;
}
else // use elements
{
writeMethod = xml.WriteElementString;//写入具有指定本地名称和值的元素。
}
xml.WriteStartDocument();
xml.WriteStartElement("categories");//根节点
foreach (Category c in cats)
{
xml.WriteStartElement("category"); //类别
writeMethod("id", c.CategoryID.ToString());
writeMethod("name", c.CategoryName);
writeMethod("desc", c.Description);
writeMethod("product_count", c.Products.Count.ToString());
xml.WriteStartElement("products");
foreach (Product p in c.Products)
{
xml.WriteStartElement("product");
writeMethod("id", p.ProductID.ToString());
writeMethod("name", p.ProductName);
writeMethod("cost", p.Cost.Value.ToString());
writeMethod("stock", p.Stock.ToString());
writeMethod("discontinued", p.Discontinued.ToString());
xml.WriteEndElement(); // </product>
}
xml.WriteEndElement(); // </products>
xml.WriteEndElement(); // </category> //类别
}
xml.WriteEndElement(); // </categories> //结束根节点元素
}
}
WriteLine("{0} contains {1:N0} bytes.",
arg0: xmlFile,
arg1: new FileInfo(xmlFile).Length);
}
写入csv文件:
//生成CSV文件
private static void GenerateCsvFile(IQueryable<Category> cats)
{
string csvFile = "categories-and-products.csv";
using (FileStream csvStream = File.Create(Combine(CurrentDirectory, csvFile)))
{
using (var csv = new StreamWriter(csvStream))
{
csv.WriteLine("CategoryID,CategoryName,Description,ProductID,ProductName,Cost,Stock,Discontinued");
foreach (Category c in cats)
{
foreach (Product p in c.Products)
{
csv.Write("{0},\"{1}\",\"{2}\",", // 类别ID,类别名,描述
arg0: c.CategoryID.ToString(),
arg1: c.CategoryName,
arg2: c.Description);
csv.Write("{0},\"{1}\",{2},",// 产品id,产品名,单价
arg0: p.ProductID.ToString(),
arg1: p.ProductName,
arg2: p.Cost.Value.ToString());
csv.WriteLine("{0},{1}",// 库存 、停产
arg0: p.Stock.ToString(),
arg1: p.Discontinued.ToString());
}
}
}
}//自动释放文件流
WriteLine("{0} contains {1:N0} bytes.",
arg0: csvFile,
arg1: new FileInfo(csvFile).Length);
}
写入json文件:
//生成json格式文件
private static void GenerateJsonFile(IQueryable<Category> cats)
{
string jsonFile = "categories-and-products.json";
using (FileStream jsonStream = File.Create(Combine(CurrentDirectory, jsonFile)))
{//使用指定的流和自定义选项初始化 System.Text.Json.Utf8JsonWriter 类的新实例以将输出写入。
using (var json = new Utf8JsonWriter(jsonStream,
new JsonWriterOptions { Indented = true }))//true 格式化 JSON 输出; false 表示没有任何额外的空白。 默认为false。
{
json.WriteStartObject();
json.WriteStartArray("categories");//使用指定为字符串的属性名称写入 JSON 数组的开头作为键。
foreach (Category c in cats)//遍历类别
{
json.WriteStartObject();
json.WriteNumber("id", c.CategoryID);
json.WriteString("name", c.CategoryName);
json.WriteString("desc", c.Description);
json.WriteNumber("product_count", c.Products.Count);
json.WriteStartArray("products");
foreach (Product p in c.Products)
{
json.WriteStartObject();//
json.WriteNumber("id", p.ProductID);
json.WriteString("name", p.ProductName);
json.WriteNumber("cost", p.Cost.Value);
json.WriteNumber("stock", p.Stock.Value);
json.WriteBoolean("discontinued", p.Discontinued);
json.WriteEndObject(); // product
}
json.WriteEndArray(); // products
json.WriteEndObject(); // category
}
json.WriteEndArray(); // categories
json.WriteEndObject();
}
}
WriteLine("{0} contains {1:N0} bytes.",
arg0: jsonFile,
arg1: new FileInfo(jsonFile).Length);
}
附:数据库对应的EF CORE 类:
数据库上下文类:
using Microsoft.EntityFrameworkCore;
namespace Packt.Shared
{
// this manages the connection to the database
public class Northwind : DbContext
{
// these properties map to tables in the database
public DbSet<Category> Categories { get; set; }
public DbSet<Product> Products { get; set; }
protected override void OnConfiguring(
DbContextOptionsBuilder optionsBuilder)
{
string path = System.IO.Path.Combine(
System.Environment.CurrentDirectory, "Northwind.db");
optionsBuilder.UseSqlite($"Filename={path}");
}
protected override void OnModelCreating(
ModelBuilder modelBuilder)
{
// example of using Fluent API instead of attributes
// to limit the length of a category name to under 15
modelBuilder.Entity<Category>()
.Property(category => category.CategoryName)
.IsRequired() // NOT NULL
.HasMaxLength(15);
// added to "fix" the decimal support in SQLite
modelBuilder.Entity<Product>()
.Property(product => product.Cost)
.HasConversion<double>();
}
}
}
类别-类:
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
namespace Packt.Shared
{
public class Category
{
// these properties map to columns in the database
public int CategoryID { get; set; }
public string CategoryName { get; set; }
[Column(TypeName = "ntext")]
public string Description { get; set; }
// defines a navigation property for related rows
public virtual ICollection<Product> Products { get; set; }
public Category()
{
// to enable developers to add products to a Category we must
// initialize the navigation property to an empty list
this.Products = new List<Product>();
}
}
}
产品类:
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Packt.Shared
{
public class Product
{
public int ProductID { get; set; }
[Required]
[StringLength(40)]
public string ProductName { get; set; }
[Column("UnitPrice", TypeName = "money")]
public decimal? Cost { get; set; }
[Column("UnitsInStock")]
public short? Stock { get; set; }
public bool Discontinued { get; set; }
// these two define the foreign key relationship
// to the Categories table
public int CategoryID { get; set; }
public virtual Category Category { get; set; }
}
}
数据库: