How-to-use-stored-procedures.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to use stored \n",
    "\n",
    "To use stored procedures with Dapper and C#, you need to:\n",
    "\n",
    "* Create a SqlConnection object using a connection string.\n",
    "* Create a <code>DynamicParameters</code> object and add the parameters for the stored procedure.\n",
    "* Use the <code>Query</code> or <code>Execute</code> methods of Dapper and specify the <code>commandType</code> as <code>CommandType.StoredProcedure</code>.\n",
    "\n",
    "Dynamic parameters with Dapper allow you to add parameters on demand without specifying their names, types, or directions. Dapper will automatically infer the parameter names and types from the template object’s properties, and set their directions to input by default."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simple cases, you can add stored procedure input parameters individually to the <code>DynamicParameters</code> object. For instance, if you want to call the <code>uspGetEmployeeManagers</code> stored procedure with the value specified as the <code>@BusinessEntityID</code> parameter and map the results to a list of <code>Employee</code> objects, you can use the <code>Query</code>method like the following example:"
   ]
  },
  {
   "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.4</span></li></ul></div></div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#r \"nuget:Microsoft.Data.SqlClient\"\n",
    "#r \"nuget:Dapper\"\n",
    "\n",
    "using Microsoft.Data.SqlClient;\n",
    "using Dapper;\n",
    "using System.Data;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0, 12: Thierry D'Hers - Ovidiu Cracium\n",
      "1, 11: Ovidiu Cracium - Roberto Tamburello\n",
      "2, 3: Roberto Tamburello - Terri Duffy\n"
     ]
    }
   ],
   "source": [
    "public class Employee\n",
    "{\n",
    "    public int BusinessEntityID { get; set; }\n",
    "    public int RecursionLevel { get; set; }   \n",
    "    public string FirstName { get; set; }\n",
    "    public string LastName { get; set; }\n",
    "    public string ManagerFirstName { get; set; }\n",
    "    public string ManagerLastName { get; set; }\n",
    "}\n",
    "\n",
    "using (var connection = new SqlConnection(connectionString))\n",
    "{   \n",
    "    string spName = \"uspGetEmployeeManagers\";\n",
    "    DynamicParameters parameters = new DynamicParameters();\n",
    "    parameters.Add(\"@BusinessEntityID\", 12);\n",
    "\n",
    "    // Execute the stored procedure and map the results to a list of Employee objects\n",
    "    List<Employee> employees = connection.Query<Employee>(spName, parameters, commandType: CommandType.StoredProcedure).ToList();\n",
    "\n",
    "    // Print the results\n",
    "    foreach (var employee in employees)\n",
    "    {\n",
    "        Console.WriteLine($@\"{employee.RecursionLevel}, {employee.BusinessEntityID}: {employee.FirstName} {employee.LastName} - {employee.ManagerFirstName} {employee.ManagerLastName}\");\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When the stored procedure has multiple input parameters, you can wrap all of them in a template object and pass it to the constructor of the <code>DynamicParameters</code>. Here is an example that uses the <code>uspGetBillOfMaterials</code> stored procedure in AdventureWorks2022 database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3, Cup-Shaped Race, 2\r\n"
     ]
    }
   ],
   "source": [
    "public class BillInput\n",
    "{   \n",
    "    public int StartProductID { get; set; } \n",
    "    public DateTime CheckDate { get; set; } \n",
    "}\n",
    "\n",
    "public class Bill\n",
    "{\n",
    "    public int ProductAssemblyID { get; set; } \n",
    "    public string ComponentDesc { get; set; } \n",
    "    public int TotalQuantity { get; set; } \n",
    "}\n",
    "\n",
    "using (var connection = new SqlConnection(connectionString))\n",
    "{   \n",
    "    string spName = \"uspGetBillOfMaterials\";\n",
    "    BillInput template = new BillInput() {StartProductID = 3, CheckDate = new DateTime(2010, 5, 4)};\n",
    "    DynamicParameters parameters = new DynamicParameters(template);   \n",
    "\n",
    "    // Execute the stored procedure and map the results to a list of Employee objects\n",
    "    List<Bill> bills = connection.Query<Bill>(spName, parameters, commandType: CommandType.StoredProcedure).ToList();\n",
    "\n",
    "    // Print the results\n",
    "    foreach (var bill in bills)\n",
    "    {\n",
    "        Console.WriteLine($@\"{bill.ProductAssemblyID}, {bill.ComponentDesc}, {bill.TotalQuantity}\");\n",
    "    }\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
}