(C# ASP.NET Core) Practice Exercises and Questions on Database Connectivity

Exercises on Database Connectivity

1. [review] Write down all the steps needed for the initial setup for connecting to a Sqlite database.

2. [String PK] Create a model - called, say, MyModel - that has a primary key property of string datatype - called CountryID - and another property of string type - called DisplayName. Both the properties should be marked as required. Create a form (complete with inputs, submit and validation spans) that helps you save records of type "MyModel" to a table of sqlite database. Your code should gracefully handle a DbUpdateException. Test your project by adding a few records, and also verify that duplicate CountryID cannot be saved to the database. Note: Primary key of string datatype must be marked as DatabaseGeneratedOption.None, otherwise it will not work because the default is DatabaseGeneratedOption.Identity.

3. [bulk insert with textarea] Create a form of one textarea and a submit button. This form is to be used for bulk insert of records of type "MyModel" [see previous exercise] to a table of sqlite database. This is how the form will be used: a user will type rows of records in a semi-colon separated format, for example Brazil may be typed as BZ;Brazil, where "BZ" is CountryID and "Brazil" is the DisplayName. When the form is posted, the rows should be read in a loop and each record extracted, and inserted into the database. Finally, a summary of the number of records succeeded and that failed should be shown somewhere on the same form.

4. [checkbox insert and display] Create a razor page that has a FORM of two inputs - first a textbox for the name of a player and second a checkbox "Plays Chess". When the form is submitted, the data is stored in a table. The same razor page should separately display the lists of chess players and non-chess players. We suggest that a three column layout be created using suitable CSS. The left column should show non-chess players, the middle column should show the data entry FORM and the right column should the chess players. Note: You will need to use the ".Where" LINQ for filtering chess players.

5. [transfer between lists] Extend the above exercise thus: Add an anchor link (called, say, "move to non-chess players") adjacent to each record of chess players. When this anchor is clicked the chess playing status of the player should be toggled so that the same player gets "transferred" to the list of non-chess players. Similarly add an anchor link adjacent to each record of non-chess players. Run the project and verify that "transfers" actually happen.

6. [seed random data] Create a model class having three properties - ID [auto int PK], FirstName, LastName and DateOfBirth - the last one being of the DateTime datatype. Configure your project for sqlite database connectivity. The Index razor page should have an anchor link called "Insert 10 random Records" and connected to a function "SeedData". Just below the anchor link, the data is displayed in an html table of four columns - ID, First Name, Last Name and Date Of Birth. Run the project to verify that each click adds 10 random records to the table. Note: you can use the Random class to generate random ASCII text.

7. [pagination] Add paging to the previous exercise by adding "Next", "Prev" anchors below the table. Take a page size of, say, 7. "Prev" link should be disabled on the first page, whereas the "Next" should be disabled on the last page. Use suitable CSS to disable an anchor link.

8. [sorting] Add sorting to the previous exercise by replacing the Date of Birth heading with an anchor link. When the anchor is clicked repeatedly, the sort order changes from ascending to descending, and vice versa. Test it thus: First run the project to verify that paging works and the sort order is the default order as seen from the sequence of ID. Now move to the next page and click the Date Of Birth heading. The table should now reset to the first page, but the list should display as sorted. Now click on the Next link [and then back and forth] to verify that the remaining records can be seen in the same sorted order.