In this chapter you will learn
- How to create Database in ASP.NET Core?
- Store Connection String in appsettings.json file
- How to retrieve connection string from appsettings.json file?
- Make model class to save data to database
Till now, you have learned how to make model class in asp.net core and how to connect it with controller. In this chapter you will learn whole package including creating model and saving data to database. This chapter is going to be quite interesting because once you complete this chapter you will be able to apply your own logic to database and project. So, study this chapter thoroughly to understand basic concept of working with database in ASP.NET Core.
Here, I am creating a New Project UserProfile, that will ask user details and save into database.
Design View Page
Step 2. Add a View Page Profile.cshtml under Home folder and add the following code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | @{ ViewBag.Title = “User Profile”; }
<h1>User Profile Page</h1> <form asp-controller=“Home” asp-action=“GetDetails” method=“post”> <table> <tr> <td>Name :</td> <td><input name=“txtName” type=“text” /></td> </tr> <tr> <td>Age :</td> <td><input name=“txtAge” type=“text” /></td> </tr> <tr> <td>City :</td> <td><input name=“txtCity” type=“text” /></td> </tr> <tr> <td colspan=“2”><input id=“Submit1” type=“submit” value=“submit” /></td> </tr> </table> </form> <br /><br />
<h2 style=“font-style:italic; color:deeppink”>@ViewBag.Result</h2> |
Create Database and Table
Step 3. Create UserProfile Database to save these details. Go to View Server Explorer. Right Click on Data Connections and select Create New SQL Server Database.
Step 4. Enter .SQLEXPRESS as Server Name and UserProfile as Database Name as picture below.
Step 5. Expand UserProfile Database and then right click on Tables Add New Table. Design your table as picture below or paste the following script to design your table automatically. Then click on Update button to save your table. If you are designing your table yourself then don’t forget to set Identity Specification = True for Id column. You can set it in properties windows for Id column.
CREATE TABLE [dbo].[Profile] ( [Id] INT NOT NULL PRIMARY KEY IDENTITY, [Name] NVARCHAR(50) NULL, [Age] INT NULL, [City] NVARCHAR(50) NULL ) |
Step 6. Refresh your Server Explorer to see your Profile table under your database.
Get Connection String
Step 7. Go to Server Explorer, Right click on your database and select Properties. Here you can see Connection String.
Data Source=.SQLEXPRESS;Initial Catalog=UserProfile;Integrated Security=True;Pooling=FalseStep 8. Copy Connection Strings and Open appsettings.json file. Add your Connection Strings using following method.
{ “Logging”: { “IncludeScopes”: false, “LogLevel”: { “Default”: “Debug”, “System”: “Information”, “Microsoft”: “Information” } }, “ConnectionStrings”: { “UserProfile”: “Data Source=.\SQLEXPRESS;Initial Catalog=UserProfile;Integrated Security=True;Pooling=False” } } |
Now, I will make a class in models folder that will access this connection strings when needed.
Creating Models Folder and Class
using Microsoft.Extensions.Configuration; using System.IO;
namespace UserProfile.Models { public static class GetConString { public static string ConString() { var builder = new ConfigurationBuilder().SetBasePath(Directory.GetCurrentDirectory()).AddJsonFile(“appsettings.json”, optional: true, reloadOnChange: true); var config = builder.Build(); string constring = config.GetConnectionString(“UserProfile”); return constring; } } } |
This class file will return Connection String that will be used to connecting with your database.
Installing Microsoft.EntityFrameworkCore.SqlServer is Necessary
You must install Microsoft.EntityFrameworkCore.SqlServer to work with Sql Data Client. It is very easy to install. Go to Tools NuGet Package Manager Package Manager Console. Here, type the following command and Hit Enter. Package will be automatically installed and restored on your project within couple of seconds.
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Step 10. Create one more class file UserDataModel.cs in models that will handle input/output and saving details on database. Open UserDataModel.cs and paste the following code in it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | using System.Data.SqlClient;
namespace UserProfile.Models { public class UserDataModel { public string Name { get; set; } public int Age { get; set; } public string City { get; set; }
public int SaveDetails() { SqlConnection con = new SqlConnection(GetConString.ConString()); string query = “INSERT INTO Profile(Name, Age, City) values (‘” + Name + “‘,'” + Age + “‘,'” + City + “‘)”; SqlCommand cmd = new SqlCommand(query, con); con.Open(); int i = cmd.ExecuteNonQuery(); con.Close(); return i; } } } |
Step 11. Now open HomeController.cs and add the following highlighted code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | using System; using Microsoft.AspNetCore.Mvc; using UserProfile.Models;
namespace UserProfile.Controllers { public class HomeController : Controller { public IActionResult Index() { return View(); }
public IActionResult About() { ViewData[“Message”] = “Your application description page.”;
return View(); }
public IActionResult Contact() { ViewData[“Message”] = “Your contact page.”;
return View(); }
public IActionResult Error() { return View(); }
public IActionResult Profile() { return View(); }
[HttpPost] public IActionResult GetDetails() { UserDataModel umodel = new UserDataModel(); umodel.Name = HttpContext.Request.Form[“txtName”].ToString(); umodel.Age = Convert.ToInt32(HttpContext.Request.Form[“txtAge”]); umodel.City = HttpContext.Request.Form[“txtCity”].ToString();
int result = umodel.SaveDetails(); if(result>0) { ViewBag.Result = “Data Saved Successfully”; } else { ViewBag.Result = “Something Went Wrong”; } return View(“Profile”); } } } |
Step 12. Linking Profile Page to Home Page. Open Shared _Layout.cshtml and find the following block of codes and add the highlighted line as below:
<div class=“navbar-collapse collapse”> <ul class=“nav navbar-nav”> <li><a asp-area=“” asp-controller=“Home” asp-action=“Index”>Home</a></li> <li><a asp-area=“” asp-controller=“Home” asp-action=“About”>About</a></li> <li><a asp-area=“” asp-controller=“Home” asp-action=“Contact”>Contact</a></li> <li><a asp-area=“” asp-controller=“Home” asp-action=“Profile”>Profile Page</a></li> </ul> </div> |
Step 13. It’s time to execute your program. Press Ctrl + F5 or F5 to launch the website. Click on Profile Page Link to open your Profile Page. Now Enter some value in the field and press Submit button.
Step 14. Refresh your database in server explorer then right click on Profile table and choose Show Table Data. You will see here that data has been saved here successfully.
Summary
This chapter explains complete guide of MVC. Here, you learned how to design a form in View, How to write code in controller, how to create model class file and save data to database. It is just a demo and you will learn more in next chapter.