How-to-connect-to-a-database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to connect to a database \n",
    "\n",
    "To connect to a database with C# and Dapper, you need to do the following steps:\n",
    "1. Install the Dapper NuGet package in your project.\n",
    "2. Add a <code>using</code> statement for Dapper at the top of your code.\n",
    "3. Create a connection string to your database.\n",
    "4. Create a <code>SqlConnection</code> object with the connection string and open it. You can use the <code>using</code> statement to ensure that the connection is closed and disposed when you are done with it.\n",
    "5. Use the Dapper extension methods on the connection object to perform queries with SQL statements or stored procedures. You can pass parameters as anonymous objects or dynamic parameters. You can also use the <code>Query</code>, <code>QueryAsync</code>, <code>Execute</code>, <code>ExecuteAsync</code>, and other methods to return different types of results."
   ]
  },
  {
   "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></ul></div></div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#r \"nuget:Dapper\"\n",
    "#r \"nuget:Microsoft.Data.SqlClient\"\n",
    "using Dapper;\n",
    "using Microsoft.Data.SqlClient;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "dotnet_interactive": {
     "language": "csharp"
    },
    "polyglot_notebook": {
     "kernelName": "csharp"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dbo.AWBuildVersion : BASE TABLE\n",
      "dbo.DatabaseLog : BASE TABLE\n",
      "dbo.ErrorLog : BASE TABLE\n",
      "dbo.sysdiagrams : BASE TABLE\n",
      "HumanResources.Department : BASE TABLE\n",
      "HumanResources.Employee : BASE TABLE\n",
      "HumanResources.EmployeeDepartmentHistory : BASE TABLE\n",
      "HumanResources.EmployeePayHistory : BASE TABLE\n",
      "HumanResources.JobCandidate : BASE TABLE\n",
      "HumanResources.Shift : BASE TABLE\n",
      "HumanResources.vEmployee : VIEW\n",
      "HumanResources.vEmployeeDepartment : VIEW\n",
      "HumanResources.vEmployeeDepartmentHistory : VIEW\n",
      "HumanResources.vJobCandidate : VIEW\n",
      "HumanResources.vJobCandidateEducation : VIEW\n",
      "HumanResources.vJobCandidateEmployment : VIEW\n",
      "Person.Address : BASE TABLE\n",
      "Person.AddressType : BASE TABLE\n",
      "Person.BusinessEntity : BASE TABLE\n",
      "Person.BusinessEntityAddress : BASE TABLE\n",
      "Person.BusinessEntityContact : BASE TABLE\n",
      "Person.ContactType : BASE TABLE\n",
      "Person.CountryRegion : BASE TABLE\n",
      "Person.EmailAddress : BASE TABLE\n",
      "Person.Password : BASE TABLE\n",
      "Person.Person : BASE TABLE\n",
      "Person.PersonPhone : BASE TABLE\n",
      "Person.PhoneNumberType : BASE TABLE\n",
      "Person.StateProvince : BASE TABLE\n",
      "Person.vAdditionalContactInfo : VIEW\n",
      "Person.vStateProvinceCountryRegion : VIEW\n",
      "Production.BillOfMaterials : BASE TABLE\n",
      "Production.Culture : BASE TABLE\n",
      "Production.Document : BASE TABLE\n",
      "Production.Illustration : BASE TABLE\n",
      "Production.Location : BASE TABLE\n",
      "Production.Product : BASE TABLE\n",
      "Production.ProductCategory : BASE TABLE\n",
      "Production.ProductCostHistory : BASE TABLE\n",
      "Production.ProductDescription : BASE TABLE\n",
      "Production.ProductDocument : BASE TABLE\n",
      "Production.ProductInventory : BASE TABLE\n",
      "Production.ProductListPriceHistory : BASE TABLE\n",
      "Production.ProductModel : BASE TABLE\n",
      "Production.ProductModelIllustration : BASE TABLE\n",
      "Production.ProductModelProductDescriptionCulture : BASE TABLE\n",
      "Production.ProductPhoto : BASE TABLE\n",
      "Production.ProductProductPhoto : BASE TABLE\n",
      "Production.ProductReview : BASE TABLE\n",
      "Production.ProductSubcategory : BASE TABLE\n",
      "Production.ScrapReason : BASE TABLE\n",
      "Production.TransactionHistory : BASE TABLE\n",
      "Production.TransactionHistoryArchive : BASE TABLE\n",
      "Production.UnitMeasure : BASE TABLE\n",
      "Production.vProductAndDescription : VIEW\n",
      "Production.vProductModelCatalogDescription : VIEW\n",
      "Production.vProductModelInstructions : VIEW\n",
      "Production.WorkOrder : BASE TABLE\n",
      "Production.WorkOrderRouting : BASE TABLE\n",
      "Purchasing.ProductVendor : BASE TABLE\n",
      "Purchasing.PurchaseOrderDetail : BASE TABLE\n",
      "Purchasing.PurchaseOrderHeader : BASE TABLE\n",
      "Purchasing.ShipMethod : BASE TABLE\n",
      "Purchasing.Vendor : BASE TABLE\n",
      "Purchasing.vVendorWithAddresses : VIEW\n",
      "Purchasing.vVendorWithContacts : VIEW\n",
      "Sales.CountryRegionCurrency : BASE TABLE\n",
      "Sales.CreditCard : BASE TABLE\n",
      "Sales.Currency : BASE TABLE\n",
      "Sales.CurrencyRate : BASE TABLE\n",
      "Sales.Customer : BASE TABLE\n",
      "Sales.PersonCreditCard : BASE TABLE\n",
      "Sales.SalesOrderDetail : BASE TABLE\n",
      "Sales.SalesOrderHeader : BASE TABLE\n",
      "Sales.SalesOrderHeaderSalesReason : BASE TABLE\n",
      "Sales.SalesPerson : BASE TABLE\n",
      "Sales.SalesPersonQuotaHistory : BASE TABLE\n",
      "Sales.SalesReason : BASE TABLE\n",
      "Sales.SalesTaxRate : BASE TABLE\n",
      "Sales.SalesTerritory : BASE TABLE\n",
      "Sales.SalesTerritoryHistory : BASE TABLE\n",
      "Sales.ShoppingCartItem : BASE TABLE\n",
      "Sales.SpecialOffer : BASE TABLE\n",
      "Sales.SpecialOfferProduct : BASE TABLE\n",
      "Sales.Store : BASE TABLE\n",
      "Sales.vIndividualCustomer : VIEW\n",
      "Sales.vPersonDemographics : VIEW\n",
      "Sales.vSalesPerson : VIEW\n",
      "Sales.vSalesPersonSalesByFiscalYears : VIEW\n",
      "Sales.vStoreWithAddresses : VIEW\n",
      "Sales.vStoreWithContacts : VIEW\n",
      "Sales.vStoreWithDemographics : VIEW\n"
     ]
    }
   ],
   "source": [
    "public class Table\n",
    "{         \n",
    "    public string TABLE_SCHEMA { get; set; }\n",
    "    public string TABLE_NAME { get; set; }  \n",
    "    public string TABLE_TYPE { get; set; }  \n",
    "}\n",
    "\n",
    "using (var connection = new SqlConnection(connectionString))\n",
    "{\n",
    "    var tables = connection.Query<Table>(\"SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME\");\n",
    "    foreach (Table table in tables)\n",
    "    {\n",
    "        Console.WriteLine($\"{table.TABLE_SCHEMA}.{table.TABLE_NAME} : {table.TABLE_TYPE}\");\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
}