How to Store C-Sharp Object in SQL Server

Hello Friends,

In this article, We will use Entity framework model. We will serialize and deserialize C# object using extension method and apply CRUD Operations on it using EF-Model.

Step 1:  Create MVC Project with Asp.net and MVC Selection.

Step 2:  Create Table in Database

CREATE TABLE [dbo].[StudentObject] ( [Id] INT IDENTITY (1, 1) NOT NULL, [StudentObject] VARCHAR (MAX) NULL, PRIMARY KEY CLUSTERED ([Id] ASC) );

Step 3:  Add an folder with name EF in Solution.

Step 4:  Add ADO.net Entity Data Model with name EFTestModel in EF folder with working Database.

Step 5:  Open EFTestModel.edmx form EF folder by double click. And Add [dbo].[StudentObject] table. Make sure following option is unchecked.

Pluralize or singularize columns in the model.

Save edmx file. and close.

Step 6:  Add new Folder App_Code, and Add new class SerializationExtensions.cs

Step 7:  Create two generic extension methods for Serialization and deserialization as follows.

namespace EFTestApplication.CustomExtensions { public static class SerializationExtensions { public static string Serialize(this T x) => Newtonsoft.Json.JsonConvert.SerializeObject(x); public static T Deserialize(this string s) => Newtonsoft.Json.JsonConvert.DeserializeObject(s); } }

Step 8:  Add new class under Models naming StudentMaster as follows.

namespace EFTestApplication.EF { using System; using System.Collections.Generic; public partial class StudentMaster { public int StudentId { get; set; } public string StudentName { get; set; } public Nullable ClassId { get; set; } public Nullable SectionId { get; set; } public string RollNo { get; set; } } }

Step 9:   Build Entire Project using Ctrl+Shift+B.

Step 10:  Add new Controller with name StudentMaster in Controllers Folder with MVC 5 Controller-Empty template.

Step 11:  Add private local Entity Object of EFTestDBEntities class in StudentMasterController.

private EFTestDBEntities db = new EFTestDBEntities();

Step 12:  Add two following directives.

using EFTestApplication.EF; using EFTestApplication.CustomExtensions;

Step 13:  Create Action Method for getting List of students under StudentMasterController.cs class.

// GET: StudentMaster public ActionResult Index() { IList sm = new List(); List so = db.StudentObject.ToList(); foreach (StudentObject s in so) { StudentMaster ss = s.StudentObject1.Deserialize(); ss.StudentId = s.Id; sm.Add(ss); } return View(sm); }

Adding View index.cshtml by right click on Index method and click Add View.. with default layout. copy following code.

@model IEnumerable<EFTestApplication.EF.StudentMaster> @{     ViewBag.Title = "Index"; } <h2>Index</h2> <p>     @Html.ActionLink("Create New", "Create") </p> <table class="table">     <tr>         <th>             @Html.DisplayNameFor(model => model.StudentName)         </th>         <th>             @Html.DisplayNameFor(model => model.ClassId)         </th>         <th>             @Html.DisplayNameFor(model => model.SectionId)         </th>         <th>             @Html.DisplayNameFor(model => model.RollNo)         </th>         <th></th>     </tr> @foreach (var item in Model) {     <tr>         <td>             @Html.DisplayFor(modelItem => item.StudentName)         </td>         <td>             @Html.DisplayFor(modelItem => item.ClassId)         </td>         <td>             @Html.DisplayFor(modelItem => item.SectionId)         </td>         <td>             @Html.DisplayFor(modelItem => item.RollNo)         </td>         <td>             @Html.ActionLink("Edit", "Edit", new { id=item.StudentId }) |             @Html.ActionLink("Details", "Details", new { id=item.StudentId }) |             @Html.ActionLink("Delete", "Delete", new { id=item.StudentId })         </td>     </tr> } </table>

Step 14:  Create Action for Save student data under StudentMasterController.cs class.

[HttpPost] [ValidateAntiForgeryToken] public ActionResult Create([Bind(Include = "StudentId,StudentName,ClassId,SectionId,RollNo")] StudentMaster studentMaster) { if (ModelState.IsValid) { db.StudentObject.Add(new StudentObject { StudentObject1 = Newtonsoft.Json.JsonConvert.SerializeObject(studentMaster) }); db.SaveChanges(); return RedirectToAction("Index"); } return View(studentMaster); }

Step 15:  Create Action for Save View under StudentMasterController.cs class.

// GET: StudentMaster/Create public ActionResult Create() { return View(); }

Adding View Create.cshtml by right click on above Create method and click Add View.. with default layout. copy following code.

@model EFTestApplication.EF.StudentMaster @{     ViewBag.Title = "Create"; } <h2>Create</h2> @using (Html.BeginForm())  {     @Html.AntiForgeryToken()          <div class="form-horizontal">         <h4>StudentMaster</h4>         <hr />         @Html.ValidationSummary(true, "", new { @class = "text-danger" })         <div class="form-group">             @Html.LabelFor(model => model.StudentName, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.StudentName, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.StudentName, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.ClassId, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.ClassId, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.ClassId, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.SectionId, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.SectionId, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.SectionId, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.RollNo, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.RollNo, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.RollNo, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             <div class="col-md-offset-2 col-md-10">                 <input type="submit" value="Create" class="btn btn-default" />             </div>         </div>     </div> } <div>     @Html.ActionLink("Back to List", "Index") </div> @section Scripts {     @Scripts.Render("~/bundles/jqueryval") }

Step 16:  Student details Action method.

// GET: StudentMaster/Details/5 public ActionResult Details(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); }

Step 17:  Student details View.

@model EFTestApplication.EF.StudentMaster @{     ViewBag.Title = "Details"; } <h2>Details</h2> <div>     <h4>StudentMaster</h4>     <hr />     <dl class="dl-horizontal">         <dt>             @Html.DisplayNameFor(model => model.StudentName)         </dt>         <dd>             @Html.DisplayFor(model => model.StudentName)         </dd>         <dt>             @Html.DisplayNameFor(model => model.ClassId)         </dt>         <dd>             @Html.DisplayFor(model => model.ClassId)         </dd>         <dt>             @Html.DisplayNameFor(model => model.SectionId)         </dt>         <dd>             @Html.DisplayFor(model => model.SectionId)         </dd>         <dt>             @Html.DisplayNameFor(model => model.RollNo)         </dt>         <dd>             @Html.DisplayFor(model => model.RollNo)         </dd>     </dl> </div> <p>     @Html.ActionLink("Edit", "Edit", new { id = Model.StudentId }) |     @Html.ActionLink("Back to List", "Index") </p>

Step 18:  Edit Student Data for Update and delete.

[HttpPost] [ValidateAntiForgeryToken] public ActionResult Edit([Bind(Include = "StudentId,StudentName,ClassId,SectionId,RollNo")] StudentMaster studentMaster) { if (ModelState.IsValid) { StudentObject so = new StudentObject { Id=studentMaster.StudentId, StudentObject1 = studentMaster.Serialize() }; db.Entry(so).State = EntityState.Modified; db.SaveChanges(); return RedirectToAction("Index"); } return View(studentMaster); }

Step 19:  Edit View for edit student selected record.

Add Action method as follows

// GET: StudentMaster/Edit/5 public ActionResult Edit(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); }

Add View for Edit and copy following code.

@model EFTestApplication.EF.StudentMaster @{     ViewBag.Title = "Edit"; } <h2>Edit</h2> @using (Html.BeginForm()) {     @Html.AntiForgeryToken()          <div class="form-horizontal">         <h4>StudentMaster</h4>         <hr />         @Html.ValidationSummary(true, "", new { @class = "text-danger" })         @Html.HiddenFor(model => model.StudentId)         <div class="form-group">             @Html.LabelFor(model => model.StudentName, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.StudentName, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.StudentName, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.ClassId, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.ClassId, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.ClassId, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.SectionId, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.SectionId, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.SectionId, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             @Html.LabelFor(model => model.RollNo, htmlAttributes: new { @class = "control-label col-md-2" })             <div class="col-md-10">                 @Html.EditorFor(model => model.RollNo, new { htmlAttributes = new { @class = "form-control" } })                 @Html.ValidationMessageFor(model => model.RollNo, "", new { @class = "text-danger" })             </div>         </div>         <div class="form-group">             <div class="col-md-offset-2 col-md-10">                 <input type="submit" value="Save" class="btn btn-default" />             </div>         </div>     </div> } <div>     @Html.ActionLink("Back to List", "Index") </div> @section Scripts {     @Scripts.Render("~/bundles/jqueryval") }

Step 20:  Action method for delete selected student record.

// POST: StudentMaster/Delete/5 [HttpPost, ActionName("Delete")] [ValidateAntiForgeryToken] public ActionResult DeleteConfirmed(int id) { StudentObject so = db.StudentObject.Find(id); db.StudentObject.Remove(so); db.SaveChanges(); return RedirectToAction("Index"); }

Step 21:  Action to invoke Studet Delete view.

// GET: StudentMaster/Delete/5 public ActionResult Delete(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); }

Step 22:  Add view for delete student selected record.

@model EFTestApplication.EF.StudentMaster @{     ViewBag.Title = "Delete"; } <h2>Delete</h2> <h3>Are you sure you want to delete this?</h3> <div>     <h4>StudentMaster</h4>     <hr />     <dl class="dl-horizontal">         <dt>             @Html.DisplayNameFor(model => model.StudentName)         </dt>         <dd>             @Html.DisplayFor(model => model.StudentName)         </dd>         <dt>             @Html.DisplayNameFor(model => model.ClassId)         </dt>         <dd>             @Html.DisplayFor(model => model.ClassId)         </dd>         <dt>             @Html.DisplayNameFor(model => model.SectionId)         </dt>         <dd>             @Html.DisplayFor(model => model.SectionId)         </dd>         <dt>             @Html.DisplayNameFor(model => model.RollNo)         </dt>         <dd>             @Html.DisplayFor(model => model.RollNo)         </dd>     </dl>     @using (Html.BeginForm()) {         @Html.AntiForgeryToken()         <div class="form-actions no-color">             <input type="submit" value="Delete" class="btn btn-default" /> |             @Html.ActionLink("Back to List", "Index")         </div>     } </div>

Step 23:  Add Mehtod in StudentMasterController.cs for desposing its object.

protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); }

The final StudentMasterController.cs Class will be as follows.

using System; using System.Collections.Generic; using System.Data; using System.Data.Entity; using System.Linq; using System.Net; using System.Web; using System.Web.Mvc; using EFTestApplication.EF; using EFTestApplication.CustomExtensions; namespace EFTestApplication.Controllers { public class StudentMasterController : Controller { private EFTestDBEntities db = new EFTestDBEntities(); // GET: StudentMaster public ActionResult Index() { IList sm = new List(); List so = db.StudentObject.ToList(); foreach (StudentObject s in so) { StudentMaster ss = s.StudentObject1.Deserialize(); //Newtonsoft.Json.JsonConvert.DeserializeObject(s.StudentObject1); ss.StudentId = s.Id; sm.Add(ss); } return View(sm); } // GET: StudentMaster/Details/5 public ActionResult Details(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); } // GET: StudentMaster/Create public ActionResult Create() { return View(); } [HttpPost] [ValidateAntiForgeryToken] public ActionResult Create([Bind(Include = "StudentId,StudentName,ClassId,SectionId,RollNo")] StudentMaster studentMaster) { if (ModelState.IsValid) { db.StudentObject.Add(new StudentObject { StudentObject1 = Newtonsoft.Json.JsonConvert.SerializeObject(studentMaster) }); db.SaveChanges(); return RedirectToAction("Index"); } return View(studentMaster); } // GET: StudentMaster/Edit/5 public ActionResult Edit(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); } [HttpPost] [ValidateAntiForgeryToken] public ActionResult Edit([Bind(Include = "StudentId,StudentName,ClassId,SectionId,RollNo")] StudentMaster studentMaster) { if (ModelState.IsValid) { StudentObject so = new StudentObject { Id=studentMaster.StudentId, StudentObject1 = studentMaster.Serialize() }; db.Entry(so).State = EntityState.Modified; db.SaveChanges(); return RedirectToAction("Index"); } return View(studentMaster); } // GET: StudentMaster/Delete/5 public ActionResult Delete(int? id) { if (id == null) { return new HttpStatusCodeResult(HttpStatusCode.BadRequest); } StudentObject so = db.StudentObject.Find(id); if (so == null) { return HttpNotFound(); } StudentMaster sm = so.StudentObject1.Deserialize(); sm.StudentId = so.Id; return View(sm); } // POST: StudentMaster/Delete/5 [HttpPost, ActionName("Delete")] [ValidateAntiForgeryToken] public ActionResult DeleteConfirmed(int id) { StudentObject so = db.StudentObject.Find(id); db.StudentObject.Remove(so); db.SaveChanges(); return RedirectToAction("Index"); } protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); } } }

Step 24:  Open HomeController.cs class file. and replace its Index method with following code to call Student Master Details form.

public ActionResult Index() { return RedirectToAction("Index", "StudentMaster"); }

Step 25:  Press F5 it will start the application with Student Master.

To learn more about how we can convert JSON string in Sql Database into Table Columns using OPENJSON Sql function click here.

Moreover to get JSON property value from JSON string in SQL Database using ISJSON, JSON_VALUE, JSON_QUERY and JSON_MODIFY in Sql click here.

If you have any query or question or topic on which, we might have to write an article for your interest or any kind of suggestion regarding this post, Just feel free to write us, by hit add comment button below or contact via Contact Us form.


Your feedback and suggestions will be highly appreciated. Also try to leave comments from your valid verified email account, so that we can respond you quickly.

 
 

{{c.Content}}

Comment By: {{c.Author}}  On:   {{c.CreatedDate|date:'dd/MM/yyyy'}} / Reply


Categories