How-to-handle-errors-and-exceptions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to handle errors and exceptions\n",
    "\n",
    "To handle errors and exceptions with Dapper using C#, you can use the following techniques:\n",
    "\n",
    "* Use the <code>try-catch-finally</code> blocks to catch and handle any exceptions that may occur when executing SQL queries or mapping the results to objects. You can use the <code>Exception</code> class or its subclasses to get the details of the error, such as the message, the stack trace, the source, etc. You can also use the <code>finally</code> block to close the connection or perform any cleanup operations1.\n",
    "* Use the <code>using</code> statement to ensure that the connection is disposed automatically when it goes out of scope. This can help prevent memory leaks and connection pool exhaustion. The <code>using</code> statement also implements the <code>try-finally</code> pattern internally, so you don’t need to explicitly close the connection2.\n",
    "* Use the <code>Dapper.SqlMapper.TypeHandler</code> class to create custom type handlers for complex or non-standard data types, such as enums, JSON, XML, etc. This can help you avoid data conversion errors or impedance mismatch issues when working with Dapper. You can override the <code>SetValue</code> and <code>Parse</code> methods to define how to serialize and deserialize your custom types to and from the database3.\n",
    "\n",
    "Here is a sample code snippet that shows how to use these techniques to handle errors and exceptions with Dapper using C# and the AdventureWorks2022 database:"
   ]
  },
  {
   "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"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 - 295847284 - adventure-works\\ken0 - Chief Executive Officer - 1/29/1969 12:00:00 AM - S - M\r\n"
     ]
    }
   ],
   "source": [
    "#r \"nuget:Microsoft.Data.SqlClient\"\n",
    "#r \"nuget:Dapper\"\n",
    "\n",
    "using Microsoft.Data.SqlClient;\n",
    "using Dapper;\n",
    "\n",
    "public class Employee\n",
    "{\n",
    "    public int BusinessEntityID { get; set; }\n",
    "    public string NationalIDNumber { get; set; }\n",
    "    public string LoginID { get; set; }\n",
    "    public string JobTitle { get; set; }\n",
    "    public DateTime BirthDate { get; set; }\n",
    "    public string MaritalStatus { get; set; }\n",
    "    public string Gender { get; set; }\n",
    "}\n",
    "\n",
    "// Use the using statement to create and dispose the connection\n",
    "using (SqlConnection db = new SqlConnection(connectionString))\n",
    "{\n",
    "    try\n",
    "    {\n",
    "        // Execute a SQL query and map the results to an object\n",
    "        var employee = db.QuerySingle<Employee>(\"SELECT * FROM HumanResources.Employee WHERE BusinessEntityID = @BusinessEntityID\", new { BusinessEntityID = 1 });\n",
    "\n",
    "        // Display the result in the console\n",
    "        Console.WriteLine($\"{employee.BusinessEntityID} - {employee.NationalIDNumber} - {employee.LoginID} - {employee.JobTitle} - {employee.BirthDate} - {employee.MaritalStatus} - {employee.Gender}\");\n",
    "    }\n",
    "    catch (SqlException ex)\n",
    "    {\n",
    "        // Handle any SQL exceptions that may occur\n",
    "        Console.WriteLine($\"A SQL error occurred: {ex.Number} - {ex.Message}\");\n",
    "    }\n",
    "    catch (InvalidOperationException ex)\n",
    "    {\n",
    "        // Handle any invalid operation exceptions that may occur\n",
    "        Console.WriteLine($\"An invalid operation occurred: {ex.Message}\");\n",
    "    }\n",
    "    catch (Exception ex)\n",
    "    {\n",
    "        // Handle any other exceptions that may occur\n",
    "        Console.WriteLine($\"An unknown error occurred: {ex.Message}\");\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
}