Lab Exercise 2: C\# .NET 6 Class Library For exercises 24, create a blogging site using ASP.NET Web API and SQL Server Database. The site must have log in, registration, and post functionality (adding a post, listing all posts, and showing details of one post). This specific lab exercise will deal with backend functionality, using class libraries and connect through console UI with dependency injection. The following should be downloaded: - Visual Studio Community Edition - SQL Server In Visual Studio, go to Tools > Get Tools and Features. See Data Storage and Processes in the riaht bane whether SOL Server Data Tools is checked. Downloads per project After setting up, right click each project and click Manage Nuget Packages. Browse for the following packages, ensuring that the 6.0 version is installed: BlogDataLibrary (Class Library) - Dapper - Microsoft.Extensions.Configuration - System.Data.SqlClient BlogTestUI (Console App) - Microsoft.Extensions.Configuration - Microsoft.Extensions.Configuration.Json BlogDB (SQL Server Database Project) Setting up the project 1. Open Visual Studio 2. Select Create New Project 3. Search for C\# Class Library 4. Name the project as BlogDataLibrary 5. Name the Solution as ErpeloLE2 6. Click Next 7. For Framework, choose NET 6.0 8. Click Next 9. In Solution Explorer, in the right pane (View > Solution Explorer if not displayed), delete Class 1.cs 10. Right click the Solution (ErpeloLE2) > Add > New Project 11. Search for SQL Server Database Project 12. Name the project as ErpeloBlogDB, hereon referred to as "DB Project" 13. Add another project, searching for Console App. 14. Name it as BlogTestUI 15. Use .NET 6.0, and check "Do not use top level statements" 16. Refer to the list of downloads per project 1. In the DB Project, create new folder named dbo through Solution Explorer. 2. Under dbo, create a new folder named Tables. 3. Under Tables, right click > Add > Table 4. Name the first table as Users 5. Refer to the ERD for the columns and data type While it is not necessary to copy the capitalization of the column names, it is naming convention. 6. Uncheck Allow Nulls in all columns 7. Ensure in T-SQL, bottom pane, that Id has a keyword IDENTITY Kindly refer to the figure below for Users table configuration: 8. Screenshot your own database design. Kindly show the Solution Explorer as well. 9. Create a new table under the same folder, named as Posts 10. Refer to the ERD for the column names and data types. 11. Uncheck Allow Nulls in all columns 12. Ensure in T-SQL, bottom pane, that Id has a keyword 13. In the pane next to the columns, right click Foreign Keys > Add New Foreign Key 14. Name it FK_Posts_Users 15. In T-SQL supply the following: Column: UserId ToTable: Users ToTableColumn: Id This will connect the Posts' Userld to Users' Id, making a one-to-many relationship. Refer to the figure below for the Posts table configuration: Screenshot the database design for Posts. Ensure that the Solution Explorer is included. Publishing the database 1. Right click the root of the DB Project and choose Publish 2. A Publish Database Pop up will show, click Edit > Browse for the MSSQL Local DB 3. Name the database as BlogDB, and leave the Publish script name as ErpeloBlogDB.sql Verify whether the database is published 1. Go to View > SQL Server Object Explorer 2. Expand SQL Server > MSSQLLocalDB > Databases > 3. Ensure BlogDB is included in the list of databases Adding rows through SQL Server Object Explorer 1. Expand BlogDB> Tables 2. Right click the Users table and choose View Data 3. Fill the first row with the following details: Username: fmErpelo ( f = given name initials; m: middle initial; eg: ) Password: Student Number Do not put anything in 1D. Dut your first name and last name. 5 Sample: 4. Click Create Profile 5. Click Publish 4. Screenshot your entry. Include the SQL Server Object Explorer pane with BlogDB expanded Setting up Models 1. In the BlogDataLibrary project, add a new folder named Models 2. Under Models, right click and add a new class. 3. Name it Usermodel, and ensure the class is public. 4. Type in the following: You can type in "prop" and tab twice to automate public int MyProperty and change data type and name. Capitalization is not required, but is naming convention. 5. Create a new class named Postmodel, and ensure the class is public. 6. Type in the following in the file: 7. Add another class named ListPostModel, and ensure the class is public. 8. Type in the following in the file: Setting up dependency injection 1. In the BlogDataLibary, add a new folder named Database. Having a different project separated from the UI that interacts with the database allows us to reuse the functionality project in other UI. For this lab exercise, we'll be using a console app to test the functionality, while we can use it again for the ASP.NET Web API without needing to redo SQL commands. 2. Create a new class called SqIDataAccess, and ensure the class is public. 3. Type in the configuration property and the constructor: \( \begin{array}{l} \text { private } \text { IConfiguration _config; } \\ \text { public SqlDataAccess(IConfiguration config) } \\ \{\quad \text { _config = config; } \\ \}\end{array} \) _config contains the connection string to the database. Passing it as a parameter rather than hardcoding it in the program allows it to be easily modified and therefore have loosely coupled. For SQL commands that require return of data, type in the following inside the SqlDataAccess class as another method: Stored procedures are a set of SQL code. They are usually used for commands that are frequently run, as the databased stores it tokenized in memory unlike queries, that has to be compiled every time. T,U are generics, which is a concept that is a replacement of a specific data type. 5. For SQL commands that save data into the database, type in the following inside the SqIDataAccess class as another method: 6. Right click "public class SqIDataAccess" > Quick Actions and Refactoring > Extract Interface 7. Keep the default settings. For reference: Extract Interface New Type Name: ISqIDataAccess Generated name: BlogDatalibrary.Database.ISqIDataAccess Select destination Add to current file New file name: Select public members to form interface (1) LoadData T,U> (string, U, string, bool) () SaveData T> (string, T, string, bool) 8. Your SqIDataAccess.cs should look like this: 9. On the BlogDataLibrary Project, add a new folder named Data. 10. Create a new class named SqIData. 11. Type in the db, connectionStringName property and the constructor: private ISqLDataAccess _db; private const string connectionstringName = "SqlDb"; public SqlData(ISqLDataAccess db ) f _ db = db; \} ISqIDataAccess is used as a parameter so that it accepts anything that implements the interface. This lab exercise uses SQL Server, but if ever other SQL DBMS is used (eg. MySQL), there's no need to change this code as long as the MySQL class implements ISqIDataAccess. Put all screenshots of this activity with the link of your GitHub repository in a MS Word file and submit it in the Blackboard link provided