Showing posts with label sharepoint 2013. Show all posts
Showing posts with label sharepoint 2013. Show all posts

Wednesday, November 29, 2017

Simplified CRUD Operation Using Entity Framework



 I am going to tell you  very simple manner to do the CRUD operation using LocalDB with entity framework and how to use code first Database migration.

After reading this article you are going to know about,

  • Uses and purpose of LocalDb.
  • Connection string about LocalDb.
  • Model Binding.
  • What is DBContext class ?
  • Step by step description of CRUD operation using LocalDb with Entity Framework.
  • What is Data Migration?
  • How to Do Migration using in code first approach.

Note: Before we start if you are new to Asp.net MVC and you want complete project as reference then click

download link
 to download complete project.

Let's Start,

What is LocalDb and where it Exist ?

In general LocalDb is very similar to our Sql Server.

But LocalDb has some Limited feature then Sql Server.LocalDb only found inside Visual studio.

By default Visual studio provide one local Server and local database in the development phase.

Local server is called as IIS Express and local database is LocalDB. So both these two are pre-installed inside visual studio and helps the developer to continue development process although Local IIS and Sql server stopped providing services.

Note: If you change the setting of the project that to run the project using LocalIS then VisualStudio show you a pop-up window as given bellow,

project

Click on No. If you click on yes then configuration set for Local IIS.

This is basic concept about Local DB and purpose; as we go into deeper of this article we see where LocalDB exist and how to access it, so to see this live

Let's start Visual studio to see the concept in live,

Open VisualStudio. (I am using VS2013)

Select New project from file menu,

file

Then give the project name as "CRUDDemo"

CRUDDemo

Select ASP.Net Web Application then select MVC template to create project; after successfully created your project then go to solution explorer and check whether LocalDB available inside App_Data folder or not. Because as I told LocalDB by default location
is App_Data.

You cannot find any thing inside App_Data.The data which present under App_Data folder is always is in hidden format so click on show all file like bellow.

App_Data folder

So as we see also we click on show all file we won't find any file inside App_Data folder.

So let's start creating controller class and model to get the DBFile.

Add a controller called as BookController as follows,

controller

controller

Chose empty controller and click on add.

empty

Now you get the BookController class is created.

Inside BookController you get a default action method called as Index().

Now what we do ?

These are our objective to implement inside this project.

Objective 1 – Our first objective is to shows list of Book Details inside index page using LocalDb and EntityFramework

To do this follow bellow step,

Step 1 – Add class for BookModel which contain all the property for book details. So go to model folder and right click over it and go to add and click on class.

Add class

Now click on class and give the class name as BookModel.

class

Then you got BookModel class ; then add bellow code inside the model,

BookModel class

In the above code we see that there are two class one is called BookModel.cs which represent as model for our CRUD operation and one more class we have that is BookDBContext.

So what is the use of BookDBContext class ?

The use of BookDBContextClass is to make DB operations using that class object. Due to this class inherited from DbContext class it's allows us to access all the method of Dbcontext class.

DbContext class is specially designed for to work with DB operation using Codefirst approach.

Note: Here We inherited BookDBContext class Explicilty form DbContext class because we are not going to use any Entity Framework template to generate the code implicitly.So we write all the code
manually.

Inside BookDBContext class we have BookLst property of type Dbset which going to return list of Book.

Now Build your project -> Ctrl+shift+B

After build succeed add the bellow code inside BookController action method index().

code

In the above code we have create BookDBConetext object called as objBookDb; which helps us to provide different operation using it's property. In return view we are sending list of Book to our view to see all the book.

Now we can go to the RoutConfig.cs file and change the application rout by specifying our controller name and action method name as follows,

code

Now if we directly run the application without adding view also we can get our LocalDB inside AppData folder; before run the application first check once if the LocalDB is exist or not.

You found there is no LocalDB exist but once you run the application you can find LocalDB file inside AppData folder.

Once your app run due to view not exist you get the exception that view not found. Close the browser and comes to visualstudio and check the LocalDB file with your DBContext class name is created as bellow.

app data

Note: If you won't find this file then click on show all option in solutuion explorer as shown above.

Now we go to create a view page for Index action method which show us List of Book.

To create view : Right click inside Index action method and click on add view then you get a template which ask to bind your model and add scaffolding template as show bellow,

app data

view

Inside Template select "List" and model class as "Book" and DataContext as "BookDB",
add view

Click on Add.

You get a nicely designed view called as Index.cshtml.

Now run your app,

You get Default page with strcture like bellow,

page

To Insert the data into local DB we can use CreateNew Link shown above.

But we don't have any CreateNew page in our project, so we can add this page later.

Why I am telling later because here we can understand what is DB Migration ?

We all generally know we have two types of method to insert the data; first one using web page and second one is to open SQL Server and write the Sql Query to insert the data.

But today we insert data using another technique i.e using Seed() method of Migration class.

Don't confuse about seed() method we see the live example how seed method is working when we do migration.

Let's first Understand what is Migration and why we required migration ?

Migration is a technique which helps us to enable auto update to our DB while we are working with entity framework. In simple if we change any thing inside our model class and we want that same thing to be refelect inside our DB then we required to enable the
migration feature for our project.

To enable migration for our Project we need to open "Package Manger Console" and write the following command inside it.

Cmd : "enable-migrations –ContextTypeName Fully Qulified YourDBContextName"

For my project I write following cmd :- "enable-migrations -ContextTypeName

CRUDDemo.Models.BookDBContext"

Package Manger Console

After run you get bellow output,

output

After succefully enable migration then we find one migration folder added inside solution and that migration folder contain two file first one is IntialCreate.cs and other one is Configuration.cs.

migration

InitalCreate.cs file is the design view of our Model which going to map the Database; where as Configuration.cs file plays an important role while working with migration.

It contain a sealed class inherited from "DBMigrationConfiguration" class. This class contain the seed() method which I mention earlier.

Note: When we update the Database using update-database command then the seed() method will automatically execute. So it means seed() method going execute on execution of Update-Database command.

In earlier I told you that we can insert data using Seed method to our Local Db. To do this we need to replace the seed method code by following code.

code

After adding this Build your project.

Then again open "Package Manager Console" and write the command "update-database"

After running this command it will show you seed method is running.

method

After this run your project you find two record showing inside the page which we writeen under seed().

project

This is how we insert data to Database.

Now let's do CRUD operation,

We already did Creation part using Seed(); I am skiping to CreateNew page and add a new record. I am directly Inserting data using Seed().

Now we update the record using Edit Link.

To do this follow bellow step,

Add Action method called as Edit as bellow,

code

Then add a view for this action method.

Here I am using scaffolding to add the view; right click inside action method and follow bellow screen shot.

add a view

add a view

Now select your Model class then click on add Button.

You will get a nicely designed view.

Then run your project and click on Edit you will get Edit page with the details.

project

When you click on Save button we need to save it inside Database.

For save data we write another method using HttpPost attribute and give same name as edit.

Write bellow code for Save Data.

code

Note: While calling method of BCL(for eg:- HttpStatusCodeResult) you get error due to namespace to avoid this write the method name correctly and press "ctrl+.(dot)" in that statement resolve using
Visualstudio.

After writing the above code run you application and edit your data and save it.

Now last operation we need to do Delete data.

To do this add the following code,

code

Now run your app and click on delete button then your record deleted.

Now we see the last concept how to add migration file inside our project :-

Before we see the it live example first we need to understand why we need to add migration to our project or when I need to add migration file to our project ?
The purpose of generating migration file is to make update our local Database when anything changed inside our model claas.

Note:-
 So always keep in mind that the table where you inserting data these table is created by using our current model class. So when ever you change any thing inside model class then we are responsible to add new migration file to our project. The
migration file contains information about our changes in model class. So when we run the "update-database" command then it automatically updated our Database by using our current change.

Let see the concept in Live :-

Go to BookModel.cs file and add a new property called as Rating.

 

public

class
 
BookModel

    {


public

int
 Id {
get;
set; }


public

string
 BookName {
get;
set; }


public

string
 AuthorName {
get;
set; }

 


public

DateTime
 PublishDate {
get;
set; }


public

int
 Rating {
get;
set; }

    }

Now build your project.

If we run now our project without adding migration file or updating database we get exception at run time which say that your model changed but you won't added migration file.

So to get rid from this exception we need to add migration file to our project.

Before adding migration file let's once look over the migration folder inside solution explorer, where you see we have only one intialcreate.cs file and configuration.cs file.

Now open the "package manager console" from tool menu and run the following command :-

Cmd:- "add-migration AnyName"

For my project I run the command :- "add-migration Rating"

After successful adding migration a Rating.cs file automatically open inside visual studio which show the changes which we made inside our model class in the code.

Now check the solution explorer you find the Rating.cs file migration file added.

Now to update your LocalDB run the command "update-database" inside package manger console.

Note:- Before run this command don't forget that to add the rating value inside seed method because I told you earlier that at every update database command internally run the seed(). In
this project we are inserting data using seed method. So first update seed() method by following code then run that command.
 

protected

override
 
void
 Seed(CRUDDemo.Models.BookDBContext
context)

        {

            context.BookLst.AddOrUpdate(p => p.Id,
new

BookModel

            {

                Id = 0,

                AuthorName =
"Suryakant",

                BookName =
"CRUD DEMO",

                PublishDate =
new

DateTime
(2014, 09, 12),

                Rating=4

            },
new

BookModel

            {

                Id = 1,

                AuthorName =
"John",

                BookName =
"Digital Life",

                PublishDate =
new

DateTime
(2013, 10, 23),

                Rating=4

            });

 

        }

Now run the update-database command you see the seed method run which show in o/p of Package manager console.

 Now add the following line of code inside index.cshtml and run your project you get the rating properties data inside the page.

<td>

@Html.DisplayFor(modelItem=>item.Rating)

</td>

Now you get the page with Rating as bellow;

That's all about CRUD operation using Local DB with Data Migration.

Hope you enjoyed reading this article.

Comment your query if any thing not working for you.

Thanks for Reading.

You can download whole Project from this 
link
 with complete functionality.

SQL Important Query

Select * from(
SELECT 0 as SORTCOLUMN, -1 AS [KEY], 'Select' AS [VALUE]
UNION all
SELECT 1 AS SORTCOLUMN, PARAMALLOWEDVALUEID AS [KEY], PARAMETERVALUE AS [VALUE]
FROM ICM_COMMPARAMALLOWDVALUE where PARAMETERID = 1 AND ISACTIVE = 1) tbl order by case when SORTCOLUMN=0 then 3 else 1 end,[value]

Controller.cs inside WebApi

namespace CSSI.VUE.Web.API.Controllers
{
public class ProducerEligibiltyController : ApiController
{

 

[CSSIScope("GET_PRODUCER_ELIGIBILITY")]
[HttpGet]
public ResponseMessageResult Get(
string ApplicationSignedDate,
string CarrierCode,
string LOB,
string SitusState,
string RequestType,
string AgentCode = null,
string WritingNumber = null,
string SolicitationState = null)
{
AgentDemographicResponse obj = null;
AgentDemographicRequest req = new AgentDemographicRequest();
MetLifeEncryptDecryptAES encryption = new MetLifeEncryptDecryptAES();

req.RequestType = RequestType;
req.AgentCode = AgentCode;
req.ApplicationSignedDate = DateTime.ParseExact(ApplicationSignedDate, "yyyy-MM-dd", CultureInfo.InvariantCulture);
req.CarrierCode = CarrierCode;
req.LOB = LOB;
req.SitusState = SitusState;
req.SolicitationState = SolicitationState;
req.WritingNumber = WritingNumber;

ICommonExtService service = ProxyHelper.GetCommonServiceProxy();
if (service != null)
{
obj = service.ProducerEligibility(req);
if (obj.ErrorMessage == "" || string.IsNullOrEmpty(obj.ErrorMessage))
{
if (obj.IsAgency == "1")
{
if (!string.IsNullOrEmpty(obj.TaxID))
{
obj.TaxID = encryption.Encrypt(WebHelper.Encryption(obj.TaxID, false));
}
}
else
{
if (!string.IsNullOrEmpty(obj.SSN))
{
obj.SSN = encryption.Encrypt(WebHelper.Encryption(obj.SSN, false));
}
}
}
}

//XElement xeAgentDemographicResponse = EntityHelper.XmlSerializeEntity(obj);
//return xeAgentDemographicResponse.ToString();
//if (obj.AgentCode == null)
//{
// obj.AgentCode = "";
//}

return ResponseMessage(new HttpResponseMessage(HttpStatusCode.OK)
{
Content = new ObjectContent<AgentDemographicResponse>(obj,
new System.Net.Http.Formatting.XmlMediaTypeFormatter
{
UseXmlSerializer = true
})

});

}}
}

Web Service Project Structure :-

  1. Entity Project Class file–>DLL

Agent.cs  File:-

#region usings

using System;
using System.Collections.Generic;
using System.Runtime.Serialization;
using CSSI.Common.Utilities;
using CSSI.VUE.Common.Entities;
using CSSI.VUE.Custom.Entities;
using System.Web.Mvc;
using CSSI.VUE.Entities;