Dapper Tutorial - Dapper Plus - Example - Update with relation (One to Many)

CSharp · Console

/Program.cs

// Dapper Plus
// Doc: https://dapper-tutorial.net/bulk-update

// @nuget: Dapper -Version 1.60.6
// @nuget: Z.Dapper.Plus
// @nuget: System.Data.SqlClient

using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using Z.Dapper.Plus;
using System.Linq;
					
public class Program
{		
	public class Supplier 
	{
		public int SupplierID {get;set;}
		public string SupplierName {get;set;}
		public List<Product> Products {get;set;}
	}
	
	public class Product 
	{
		public int ProductID {get;set;}
		public string ProductName {get;set;}
		public string Unit {get;set;}
		public int SupplierID {get;set;}
	}
	
	public static void Main()
	{
		List<Supplier> suppliers = new List<Supplier>();
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
		{			
			var supplierDictionary = new Dictionary<int, Supplier>();
			
			suppliers = connection.Query<Supplier, Product, Supplier>("Select A.SupplierID, A.SupplierName, B.ProductID, B.ProductName, B.Unit, B.SupplierID FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where A.supplierID = 1",
			(supplier, product) => 
			{  
				Supplier supplierEntry;
                
                	if (!supplierDictionary.TryGetValue(supplier.SupplierID, out supplierEntry))
                	{
                    	supplierEntry = supplier;
                    	supplierEntry.Products = new List<Product>();
                    	supplierDictionary.Add(supplier.SupplierID, supplierEntry);
                	}

                	supplierEntry.Products.Add(product);
                	return supplierEntry;
				}, splitOn: "ProductID").Distinct().ToList();
			
			FiddleHelper.WriteTable(suppliers);
			FiddleHelper.WriteTable(suppliers.FirstOrDefault().Products);
		}
		
		suppliers.ForEach(x => 
		{
			x.SupplierName = "ExampleSupplierBulkUpdate";
			x.Products.ForEach(y => y.ProductName = "ExampleProductBulkUpdate");
		});

		
		DapperPlusManager.Entity<Supplier>().Table("Suppliers").Identity(x => x.SupplierID);
		DapperPlusManager.Entity<Product>().Table("Products").Identity(x => x.ProductID);
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
		{
			connection.BulkUpdate(suppliers, x => x.Products);
		}
		
		
		
		suppliers = null;
		
		using (var connection = new SqlConnection(FiddleHelper.GetConnectionStringSqlServerW3Schools()))
		{
			var supplierDictionary = new Dictionary<int, Supplier>();
			
			suppliers = connection.Query<Supplier, Product, Supplier>("Select A.SupplierID, A.SupplierName, B.ProductID, B.ProductName FROM Suppliers as A inner join Products as B on B.SupplierID =A.SupplierID where A.SupplierName = 'ExampleSupplierBulkUpdate'",
			(supplier, product) => 
			{  
				Supplier supplierEntry;
                
                	if (!supplierDictionary.TryGetValue(supplier.SupplierID, out supplierEntry))
                	{
                    	supplierEntry = supplier;
                    	supplierEntry.Products = new List<Product>();
                    	supplierDictionary.Add(supplier.SupplierID, supplierEntry);
                	}

                	supplierEntry.Products.Add(product);
                	return supplierEntry;
				}, splitOn: "ProductID").Distinct().ToList();
						
			FiddleHelper.WriteTable(suppliers);
			FiddleHelper.WriteTable(connection.Query<Product>("select * from Products where ProductName = 'ExampleProductBulkUpdate'"));
			FiddleHelper.WriteTable(connection.QueryFirstOrDefault("select * from Products where ProductName = 'ExampleProductBulkUpdate' order by ProductID"));
		}
	}
}