How-to-insert,-update,-and-delete-multiple-records.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to insert, update, and delete multiple records\n",
    "\n",
    "To perform insert, update, and delete operations with multiple records on a database, you can use the <code>Execute</code> or <code>ExecuteAsync</code> methods of Dapper to run SQL statements or stored procedures with parameters. These methods return the number of affected rows by the operation. You can also use the <code>Query</code> or <code>QueryAsync</code> methods of Dapper to get the values from the inserted, updated, or deleted rows, by using clauses such as OUTPUT in your SQL statements. For example:\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "// Connection string\n",
    "#load \"AppSettings.cs\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><div></div><div></div><div><strong>Installed Packages</strong><ul><li><span>Dapper, 2.1.28</span></li><li><span>Microsoft.Data.SqlClient, 5.1.2</span></li><li><span>System.ComponentModel, 4.3.0</span></li></ul></div></div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#r \"nuget:Microsoft.Data.SqlClient\"\n",
    "#r \"nuget:System.ComponentModel\"\n",
    "#r \"nuget:Dapper\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "using Microsoft.Data.SqlClient;\n",
    "using System;\n",
    "using System.Collections.Generic;\n",
    "using System.Data;\n",
    "using Dapper;\n",
    "\n",
    "public class Product\n",
    "{    \n",
    "    public int ProductID { get; set; }\n",
    "    public string Name { get; set; }\n",
    "    public string ProductNumber { get; set; }\n",
    "    public string Color { get; set; }\n",
    "    public decimal StandardCost { get; set; }\n",
    "    public decimal ListPrice { get; set; }\n",
    "    public int SafetyStockLevel { get; set; }\n",
    "    public int ReorderPoint { get; set; }\n",
    "    public int DaysToManufacture { get; set; }\n",
    "    public DateTime SellStartDate { get; set; }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To insert new records into the Person.Person table, you can use the following code:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1193, Gloves-5807, GL-H01-9440, Black, 5.0000, 9.9900\n",
      "1192, Bike Helmet-3780, BK-H01-7965, Red, 15.5000, 34.9900\n"
     ]
    }
   ],
   "source": [
    "var rand = new Random();\n",
    "var productsToInsert = new List<Product>()\n",
    "{\n",
    "    new Product() { Name = \"Bike Helmet-\" + rand.Next(1000, 9999), ProductNumber = \"BK-H01-\" + rand.Next(1000, 9999), Color = \"Red\", StandardCost = 15.50m, ListPrice = 34.99m, \n",
    "                    SafetyStockLevel = 999, ReorderPoint = 777, DaysToManufacture = 1, SellStartDate = new DateTime(2000, 1, 2) },    \n",
    "    new Product() { Name = \"Gloves-\" + rand.Next(1000, 9999), ProductNumber = \"GL-H01-\" + rand.Next(1000, 9999), Color = \"Black\", StandardCost = 5.00m, ListPrice = 9.99m, \n",
    "                    SafetyStockLevel = 999, ReorderPoint = 777, DaysToManufacture = 1, SellStartDate = new DateTime(2000, 1, 2) }\n",
    "};\n",
    "\n",
    "List<Product> newPoducts;\n",
    "int count;\n",
    "using (var connection = new SqlConnection(connectionString))\n",
    "{   \n",
    "    string sqlQuery = $@\"INSERT INTO Production.Product (Name, ProductNumber, Color, StandardCost, ListPrice, SafetyStockLevel, ReorderPoint, DaysToManufacture, SellStartDate) \n",
    "                         VALUES (@Name, @ProductNumber, @Color, @StandardCost, @ListPrice, @SafetyStockLevel, @ReorderPoint, @DaysToManufacture, @SellStartDate)\";\n",
    "    count = connection.Execute(sqlQuery, productsToInsert);\n",
    "\n",
    "    // Query the Product table and display the results   \n",
    "    newPoducts = connection.Query<Product>($\"SELECT top {count} * FROM Production.Product order by ProductID desc\").ToList();\n",
    "\n",
    "    foreach (var product in newPoducts)\n",
    "    {\n",
    "        Console.WriteLine($\"{product.ProductID}, {product.Name}, {product.ProductNumber}, {product.Color}, {product.StandardCost}, {product.ListPrice}\");\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To update records into the above table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1193, Adjustable Race-7956, AR-8400, Silver, 300.0000, 400.0000, 555, 0, 1/6/2024 7:26:35 PM\n",
      "1192, BB Ball Bearing-1854, BE-8715, Silver, 0.7500, 1.4900, 555, 0, 1/6/2024 7:26:35 PM\n"
     ]
    }
   ],
   "source": [
    "var productsToUpdate = new List<Product>()\n",
    "{\n",
    "    new Product() { ProductID = newPoducts[0].ProductID, Name = \"Adjustable Race-\" + rand.Next(1000, 9999), ProductNumber = \"AR-\" + rand.Next(1000, 9999), \n",
    "                    Color = \"Silver\", StandardCost = 300.00m, ListPrice = 400.00m, SafetyStockLevel = 555, ReorderPoint = 747, DaysToManufacture = 0,\n",
    "                    SellStartDate = DateTime.Now },   \n",
    "    new Product() { ProductID = newPoducts[1].ProductID, Name = \"BB Ball Bearing-\" + rand.Next(1000, 9999), ProductNumber = \"BE-\" + rand.Next(1000, 9999), \n",
    "                    Color = \"Silver\", StandardCost = 0.75m, ListPrice = 1.49m, SafetyStockLevel = 555, ReorderPoint = 747, DaysToManufacture = 0, \n",
    "                    SellStartDate = DateTime.Now  }\n",
    "};\n",
    "\n",
    "using (var connection = new SqlConnection(connectionString))\n",
    "{    \n",
    "    string sqlQuery = $@\"UPDATE Production.Product SET Name = @Name, ProductNumber = @ProductNumber, Color = @Color, StandardCost = @StandardCost, \n",
    "                         ListPrice = @ListPrice, SafetyStockLevel = @SafetyStockLevel, ReorderPoint = @ReorderPoint, DaysToManufacture = @DaysToManufacture, \n",
    "                         SellStartDate = @SellStartDate WHERE ProductID = @ProductID\";\n",
    "    connection.Execute(sqlQuery, productsToUpdate);\n",
    "\n",
    "    // Query the Product table and display the results\n",
    "    var sql = $@\"SELECT ProductID, Name, ProductNumber, Color, StandardCost, ListPrice, SafetyStockLevel, DaysToManufacture, SellStartDate\n",
    "                        FROM Production.Product\n",
    "                        WHERE ProductID = @ProductID\";\n",
    "\n",
    "    foreach(Product product in productsToUpdate)                    \n",
    "    {\n",
    "        List<Product> products = connection.Query<Product>(sql, product).ToList();\n",
    "        foreach (var p in products)\n",
    "        {\n",
    "            Console.WriteLine($\"{p.ProductID}, {p.Name}, {p.ProductNumber}, {p.Color}, {p.StandardCost}, {p.ListPrice}, {p.SafetyStockLevel}, {p.DaysToManufacture}, {p.SellStartDate}\");\n",
    "        }\n",
    "    } \n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To delete above records from the above table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [],
   "source": [
    "using (var connection = new SqlConnection(connectionString))\n",
    "{    \n",
    "    string sqlQuery = \"DELETE FROM Production.Product WHERE ProductID IN @Ids\";\n",
    "    connection.Execute(sqlQuery, new { Ids = newPoducts.Select(s => s.ProductID).ToArray() });\n",
    "\n",
    "    // Query the Product table and display the results, which have been deleted already.    \n",
    "    var sql = $@\"SELECT ProductID, Name, ProductNumber, Color, StandardCost, ListPrice, SafetyStockLevel, DaysToManufacture, SellStartDate\n",
    "                        FROM Production.Product\n",
    "                        WHERE ProductID = @ProductID\";\n",
    "\n",
    "    foreach(Product product in productsToUpdate)                    \n",
    "    {\n",
    "        List<Product> products = connection.Query<Product>(sql, product).ToList();\n",
    "        foreach (var p in products)\n",
    "        {\n",
    "            Console.WriteLine($\"{p.ProductID}, {p.Name}, {p.ProductNumber}, {p.Color}, {p.StandardCost}, {p.ListPrice}, {p.SafetyStockLevel}, {p.DaysToManufacture}, {p.SellStartDate}\");\n",
    "        }\n",
    "    } \n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using Dapper’s <code>Query</code> and <code>Execute</code> method with parameters is a simple and convenient way to insert, update, and delete one or a list of objects, as it handles the mapping of the object properties to the SQL parameters automatically. However, this approach executes multiple INSERT statements sequentially for multiple objects, which can be slow and inefficient for large data sets. This approach also requires writing the SQL query manually, which can be prone to errors or typos.\n",
    "\n",
    "If you need to work with a small or moderate amount of data occasionally, you may prefer this approach. However, if you need to work with a large amount of data frequently, and you want to optimize the performance and network usage, you may prefer the bulk operations with Dapper.Contrib.Extensions..\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "name": "polyglot-notebook"
  },
  "polyglot_notebook": {
   "kernelInfo": {
    "defaultKernelName": "csharp",
    "items": [
     {
      "aliases": [],
      "name": "csharp"
     }
    ]
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}