{"id":1979,"date":"2021-06-15T12:00:00","date_gmt":"2021-06-15T10:00:00","guid":{"rendered":"https:\/\/kindsonthegenius.com\/blog\/build-a-complete-app-with-asp-net-c-sql-lite-part-3\/"},"modified":"2026-07-05T03:25:44","modified_gmt":"2026-07-05T01:25:44","slug":"build-a-complete-app-with-asp-net-c-sql-lite-part-3","status":"publish","type":"post","link":"https:\/\/kindsonthegenius.com\/blog\/build-a-complete-app-with-asp-net-c-sql-lite-part-3\/","title":{"rendered":"Build a Complete App with ASP.Net, C#, SQL Lite \u2013 Part 3"},"content":{"rendered":"<p>This is part 3 of our complete application in ASP.NET with C# and SQL Lite. In this part we would do the following<\/p>\n<ul>\n<li><a href=\"#t1\">Create the SQL Lite database connection<\/a><\/li>\n<li><a href=\"#t2\">Install Entity Framework<\/a><\/li>\n<li><a href=\"#t3\">Setup the Data Context<\/a><\/li>\n<li><a href=\"#t4\">Generate Tables Using Migration<\/a><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h5><strong id=\"t1\">1. Create the SQL Lite Connection<\/strong><\/h5>\n<p>First create a folder named Data.<\/p>\n<p>Open the\u00a0 the Server Explorer Windows (Views &gt; Server Explorer). Shown below<\/p>\n<figure id=\"attachment_14674\" aria-describedby=\"caption-attachment-14674\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Server-Explorer-Window-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-14674\" src=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Server-Explorer-Window-1-300x249.jpg\" alt=\"Server Explorer Window\" width=\"300\" height=\"249\" srcset=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Server-Explorer-Window-1-300x249.jpg 300w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Server-Explorer-Window-1.jpg 545w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-14674\" class=\"wp-caption-text\">Server Explorer Window<\/figcaption><\/figure>\n<p>Click on the SQL Lite\/SQL Server Compact Toolbox icon to open<\/p>\n<p>Then click on Add New SQL Lite Connection<\/p>\n<figure id=\"attachment_14676\" aria-describedby=\"caption-attachment-14676\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Add-New-SQL-Lite-Connection-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-14676\" src=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Add-New-SQL-Lite-Connection-1-300x213.jpg\" alt=\"Add New SQL Lite Connection\" width=\"300\" height=\"213\" srcset=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Add-New-SQL-Lite-Connection-1-300x213.jpg 300w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Add-New-SQL-Lite-Connection-1-120x85.jpg 120w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Add-New-SQL-Lite-Connection-1.jpg 531w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/a><figcaption id=\"caption-attachment-14676\" class=\"wp-caption-text\">Add New SQL Lite Connection<\/figcaption><\/figure>\n<p>The Create New SQL Lite connection window is opened as shown below. So you can define new SQL Lite Connection<\/p>\n<figure id=\"attachment_14677\" aria-describedby=\"caption-attachment-14677\" style=\"width: 258px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Define-New-SQL-Lite-Connection.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-14677\" src=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Define-New-SQL-Lite-Connection-258x300.jpg\" alt=\"Define New SQL Lite Database Connection\" width=\"258\" height=\"300\" srcset=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Define-New-SQL-Lite-Connection-258x300.jpg 258w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Define-New-SQL-Lite-Connection-600x699.jpg 600w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Define-New-SQL-Lite-Connection.jpg 749w\" sizes=\"auto, (max-width: 258px) 100vw, 258px\" \/><\/a><figcaption id=\"caption-attachment-14677\" class=\"wp-caption-text\">Define New SQL Lite Database Connection<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n<p>Click on Create<\/p>\n<p>Navigate the the Data folder you created earlier and give a name to your database.<\/p>\n<p>Provide a name for your database (I call it HospitalDB)<\/p>\n<p>Finally, click on Test Connection. If the test is successful, you will notice that the database file is created in the Data folder.<\/p>\n<p>You can then close the window.<\/p>\n<p>&nbsp;<\/p>\n<h5><strong id=\"t2\">2. Install Entity Framework<\/strong><\/h5>\n<p>Now, what is Entity Framework? This is an ORM(Object Relational Mapping) tool that helps you map between database tables and C# classes in your application.<\/p>\n<p>To install EF, open Package Manager console (Views &gt; Other Windows &gt; Package Manager)<\/p>\n<p>Run the following two commands one after the other<\/p>\n<pre style=\"margin: 0; line-height: 125%;\">Install-Package Microsoft.EntityFrameworkCore -Version <span style=\"color: #6600ee; font-weight: bold;\">3.1<\/span>.<span style=\"color: #6600ee; font-weight: bold;\">6<\/span>\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre style=\"margin: 0; line-height: 125%;\">Install-Package Microsoft.EntityFrameworkCore.Tools -Version <span style=\"color: #6600ee; font-weight: bold;\">3.1<\/span>.<span style=\"color: #6600ee; font-weight: bold;\">6<\/span>\r\n<\/pre>\n<p>&nbsp;<\/p>\n<pre style=\"margin: 0; line-height: 125%;\">Install-Package Microsoft.EntityFrameworkCore.Sqlite -Version <span style=\"color: #6600ee; font-weight: bold;\">3.1<\/span>.<span style=\"color: #6600ee; font-weight: bold;\">6<\/span>\r\n<\/pre>\n<p>Note that the version numbers have to be the same<\/p>\n<p>&nbsp;<\/p>\n<h5><strong id=\"t3\">3. Create the Data Context<\/strong><\/h5>\n<p>A data context is used to communicate with the database and perform various operation included Create, Read, Update and Delete( CRUD). Follow the steps below:<\/p>\n<p><strong>Step 1<\/strong> &#8211; Create a class in the Data folder. Name it <em>HospitalContext. <\/em>This class should inherit from DBContext.<\/p>\n<p><strong>Step 2<\/strong> &#8211; Add the following code into the class:<\/p>\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">class<\/span> <span style=\"color: #bb0066; font-weight: bold;\">HospitalContext<\/span> : DbContext\r\n{\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #0066bb; font-weight: bold;\">HospitalContext<\/span>(DbContextOptions&lt;HospitalContext&gt; options)\r\n    : <span style=\"color: #008800; font-weight: bold;\">base<\/span>(options)\r\n    {\r\n    }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Patient&gt; Patients {<span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>;}\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Appointment&gt; Appointments { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Room&gt; Rooms { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Admission&gt; Admissions { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Discharge&gt; Discharges { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Physician&gt; Physicians { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Specialty&gt; Specialties { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n    <span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">virtual<\/span> DbSet&lt;Country&gt; Countries { <span style=\"color: #008800; font-weight: bold;\">get<\/span>; <span style=\"color: #008800; font-weight: bold;\">set<\/span>; }\r\n}\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h5><strong id=\"t4\">4. Generate the Tables Using Migration<\/strong><\/h5>\n<p>Now we would use migrations\u00a0 to generate the tables in the database. Follow the steps below:<\/p>\n<p><strong>Step 1<\/strong> &#8211; Add a connection string to the appsettings.json file.<\/p>\n<pre style=\"margin: 0; line-height: 125%;\">  <span style=\"background-color: #fff0f0;\">\"ConnectionStrings\"<\/span><span style=\"color: #ff0000; background-color: #ffaaaa;\">:<\/span> {\r\n    <span style=\"color: #007700;\">\"HospitalConnectionString\"<\/span>: <span style=\"background-color: #fff0f0;\">\"Data Source=Data\/HospitalDB.db\"<\/span>\r\n  }\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>Step 2<\/strong> &#8211; Also add the connectionstring to the Startup.cs file, in the ConfigureServices method as shown below:<\/p>\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008800; font-weight: bold;\">public<\/span> <span style=\"color: #008800; font-weight: bold;\">void<\/span> <span style=\"color: #0066bb; font-weight: bold;\">ConfigureServices<\/span>(IServiceCollection services)\r\n{\r\n    services.AddControllersWithViews();\r\n                services.AddDbContext&lt;HospitalContext&gt;(options =&gt;\r\n            options.UseSqlite(Configuration.GetConnectionString(<span style=\"background-color: #fff0f0;\">\"HospitalConnectionString\"<\/span>)));\r\n}\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p><strong>Step 3<\/strong> &#8211; Finally run to code below to add migrations <em>Initialize<\/em>(you can change the name if you want). This would simply generate the script that would create the database object for you<\/p>\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008800; font-weight: bold;\">add<\/span>-migration Initialize\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>If this code executes successfully, you will notice that a folder is create called Migrations. This folder contains two files: the migration file and the snapshot<\/p>\n<p><strong>Step 4<\/strong> &#8211; At this point, I recommend you run the program the make sure nothing is broken.<\/p>\n<p><strong>Step 5<\/strong> &#8211; Run the following command to actually generate your tables<\/p>\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008800; font-weight: bold;\">Update<\/span>-Database\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>If this command executes successfully, you will notice that your database tables is created. You can expand the SQL Lite data connection in the server explorer to check this as shown below:<\/p>\n<figure id=\"attachment_14680\" aria-describedby=\"caption-attachment-14680\" style=\"width: 211px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Generated-Database-Tables.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-14680\" src=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Generated-Database-Tables-211x300.jpg\" alt=\"Generated Database Tables\" width=\"211\" height=\"300\" srcset=\"https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Generated-Database-Tables-211x300.jpg 211w, https:\/\/www.kindsonthegenius.com\/wp-content\/uploads\/2021\/06\/Generated-Database-Tables.jpg 497w\" sizes=\"auto, (max-width: 211px) 100vw, 211px\" \/><\/a><figcaption id=\"caption-attachment-14680\" class=\"wp-caption-text\">Generated Database Tables<\/figcaption><\/figure>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is part 3 of our complete application in ASP.NET with C# and SQL Lite. In this part we would do the following Create the &hellip; <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"pagelayer_contact_templates":[],"_pagelayer_content":"","footnotes":""},"categories":[311],"tags":[],"class_list":["post-1979","post","type-post","status-publish","format-standard","hentry","category-sql"],"_links":{"self":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/comments?post=1979"}],"version-history":[{"count":1,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1979\/revisions"}],"predecessor-version":[{"id":2147,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/posts\/1979\/revisions\/2147"}],"wp:attachment":[{"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/media?parent=1979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/categories?post=1979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kindsonthegenius.com\/blog\/wp-json\/wp\/v2\/tags?post=1979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}