How to perform Insert,Update,Delete operations on a table present in sqserver2008 in MVC3 using Enti [Resolved]

Posted by ramuvalmiki07-22001 under ASP.NET on 5/7/2013 | Points: 10 | Views : 42285 | Status : [Member] | Replies : 14
Hii to all,
For the past 2weeks i started learning MVC,in this regard i want to perform simple Insert,Update,Delete operations from FrontEnd(i.e using MVC3)to a table present in SqlServer2008,i have searched in google but i didn't find the exact article,i have created a table called employee in a database which has the following columns EmpId,EName,Designation,DeptNo.Using MVC3 how can i perform all Insert,Update,Delete operations on employee table,please provide me with the steps for connecting to the database using EntityFramework and by writing some LINQ for all DML operations,when i was googling about performing this DML operations iam finding the DataContext class,please tell me what is this datacontext class where&how we can add this class.Thanks in advance

Thanks
Ramu




Responses

Posted by: Niladri.Biswas on: 5/7/2013 [Member] Platinum | Points: 50

Up
0
Down

Resolved
http://www.dotnet-tricks.com/Tutorial/knockout/0bOU010413-Knockout-CRUD-Operations-using-MVC4.html

Best Regards,
Niladri Biswas

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/7/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Hi Ramu,

Step 1:

Create table named Employee in your database with the columns that you need.
You must provide a primary key to atleast one column of your table otherwise the entity dataModel will not accept the table.
CREATE TABLE EmployeeMvc

(
EmpID int primary key,
EmpName nvarchar(100),
Designation nvarchar(100),
DeptNo int,
Password nvarchar(100)
)


Step 2:

Open Visual studio, add new project MVC 3 or MVC 4,name it (MvcRegisterForm) . In the solution explorer, right click on the Models folder, click on add. Then on the left panel, click on the Data tab, in that select ADO.NET Entity Data Model and click on OK.
Click on generate from database, next on tables tab, and select your table ie, EmployeeMvc and click on OK.
You find see your table.

Step 3:

Again right-click on models folder, select add class, and name it (Employee) and click on OK.
In the Employee class, declare the following properties:

  public class Employee

{
public string EmpID { get; set; }
public string EmpName { get; set; }
public string Designation { get; set; }
public string DeptNo { get;set; }
public string Password { get; set; }
}


Step 4:

Right-click on controllers folder, add new controller and name it (Home), and click on OK.
Open Home controller, You can find a default ActionResult method ie, Index.
Create a new ActionResult method or else you can use the Index one also. Here im creating a new ActionResult method:

      [HttpGet]

public ActionResult Register()
{
return View();
}

Now, right-click on the Register, and add a view.
Check the create strongly-typed check-box and in the below text box give the name.
ApplicationName.Models.ModelName(class that you want)
ie, MvcRegisterForm.Models.Employee
Then click on OK.

More steps in following answer.

Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/7/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Step 5:

Open your View and write the following code:

@using (Html.BeginForm(FormMethod.Post))

{
<fieldset>
<legend>EmployeeInformation</legend>
<table>
<tr>
<td>
@Html.Label("Employee ID :")
</td>
<td>
@Html.TextBoxFor(model => model.EmpID)
</td>
</tr>
<tr>
<td>
@Html.Label("Employee Name :")
</td>
<td>
@Html.TextBoxFor(model => model.EmpName)
</td>
</tr>
<tr>
<td>
@Html.Label("Designation :")
</td>
<td>
@Html.TextBoxFor(model => model.Designation)
</td>
</tr>
<tr>
<td>
@Html.Label("Department No. :")
</td>
<td>
@Html.TextBoxFor(model => model.DeptNo)
</td>
</tr>
<tr>
<td>
@Html.Label("Password :")
</td>
<td>
@Html.PasswordFor(model => model.Password)
</td>
</tr>
<tr>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
<input type="submit" value="Submit" />
</td>
</tr>
</table>
</fieldset>
}


Step 6:

Open Home controller, create object for your entity class
public class RegistrationController : Controller
{
Freshers2012Entities db = new Freshers2012Entities();
// when you add your entity data model there only the name will be displayed.
For me it is Fresher2012Entities.

Now , create a Post ActionResult method for the Register.
[HttpPost]

public ActionResult Register(Employee objEmp)
{
EmployeeMvc objEmpMvc = new EmployeeMvc();
objEmpMvc.EmpID = objEmp.EmpID;
objEmpMvc.EmpName = objEmp.EmpName;
objEmpMvc.Designation = objEmp.Designation;
objEmpMvc.DeptNo = objEmp.DeptNo;
objEmpMvc.Password = objEmp.Password;
db.AddToDetailsMvcs(objdt);
db.SaveChanges();
return View();
}

// This performs inserting into your Database.

Step 7:

Take a WebGrid and display the Employee table, add two new columns and put two links one for Delete and other for Update .
Eg. for Update operation (Im taking Change Password as example)

More steps in following answer....

Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/7/2013 [Member] Silver | Points: 50

Up
0
Down

Resolved
Step 1:

Create a new ActionResult method Named ChangePassword
 public ActionResult ChangePssword()

{
return View();
}

Right-click on ChangePassword and add View, write the following code in that.
  @using (Html.BeginForm(FormMethod.Post))

{
<div align="center" style="background-color: Black; color: Orange; font-weight: bold;
width: 83%; font-size: x-large">
Change Password
</div>
<center>
<table>
<tr>
<td>EmpName:</td>
<td>
@Html.TextBox("EmpName")
</td>
</tr>
<tr>
<td>Old Password :</td>
<td>
@Html.Password("OldPassword")
</td>
</tr>
<tr>
<td>New Password :</td>
<td>
@Html.Password("NewPassword")
</td>
</tr>
<tr>
<td>Confirm Password :</td>
<td>
@Html.Password("ConfirmPassword :")
</td>
</tr>
<tr>
<td>
<input type="submit" value="Change Password" name="Submit" />
</td>
<td>
<input type="submit" value="Cancel" name="Submit" />
</td>
</tr>
</table>
</center>
}


Step 2:

Open Database and write a stored procedure for Update operation.
CREATE Proc usp_UpdatePassword

(
@EnpName nvarchar(200),
@Password nvarchar(200)
)
as
begin
UPDATE EmployeeMvc
SET Password=@Password WHERE EmpName=@EmpName
end


Now, open the entity model class, right-click on the page and click on "Update Model from Database".
In that, click on Stored procedures tab, and select your stored procedure and click OK.
Model-Browser will be displayed on the right-side.
Expand the EntityContainer, in that right-click on Function Imports and select Add Function Import.
Give a name in the first field and in the second field, select your stored procedure name, and click on OK.

Step 3:

In the controller, write Post ActionResult method for CangePassword.
    [HttpPost]

public ActionResult ChangePassword(string EmpName, string NewPassword, string submit)
{
if (submit == "Change Password")
{
db.usp_UpdatePassword(EmpName, NewPassword);
db.SaveChanges();
}
return View();
}


Similarly, you can perform delete operation.

Hope you got clarified.

Mark as Answer if satisfied.....

Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: ramuvalmiki07-22001 on: 5/8/2013 [Member] Starter | Points: 25

Up
0
Down
Hi Shree,
Thanks for ur responce,its very clear and understandble,i have gone thrown ur code and implemented the same in my PC,when iam inserting the data,iam finding the below error at db.SaveChanges line of code
Error:- "the underlying provider failed on open"
I have googled a lot to overcome the above error but i didn't find the exact solution for the above error,please try to solve my error.....

Thanks
Ramu

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 5/8/2013 [Member] Silver | Points: 25

Up
0
Down
Hi Ramu,

The error is becoz your database connection name will be different and mine will be different and so you are getting that error. Check your database whether the connection string is present or not.
Also put a debug point and check whether your connection to the database is getting opened or not...

Mark as answer if satisfied...

Regards,
Shree M.


Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/6/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

Kmandapalli How to insert,update,Delete Multiple Tables using primary and foreign key using mvc3

can you post the code.


Mark as Answer if its helpful to you

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/8/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

Kmandapalli

How to insert,update Delete Multiple table then can you attached zip format above mvc samples thats helpful for me.


Mark as Answer if its helpful to you

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 7/8/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

I will give you a suggestion ie,
instead of doing it from the front-end you can just write a stored procedure in your database and access that database.
This would reduce the complexity and also satisfies code minimization.

I can give you a example, you try it and you can reach me out for any queries.

I have developed a shopping cart application and i have the following tables:
1. Customers(Id, UserName, Password)
2. Address (Id, FirstName, LastName, EmailID, Country, State, City)
3.CustomerAddresses (CustomerId,AddressId)

all these tables are linked with each other (they have foreign key relation)

So, your proc should be sumthing like this:

Mark as answer if satisfied....

Regards,
Shree M.


Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 7/8/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

I will give you a suggestion ie,
instead of doing it from the front-end you can just write a stored procedure in your database and access that database.
This would reduce the complexity and also satisfies code minimization.

I can give you a example, you try it and you can reach me out for any queries.

I have developed a shopping cart application and i have the following tables:
1. Customers(Id, UserName, Password)
2. Address (Id, FirstName, LastName, EmailID, Country, State, City)
3.CustomerAddresses (CustomerId,AddressId)

all these tables are linked with each other (they have foreign key relation)

So, your proc should be sumthing like this:


CREATE PROC GenerateOrder
AS
BEGIN
DECLARE @CustomerId int,
@UserName nvarchar(100),
@Password nvarchar(100),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@EmailID nvarchar(100),
@Country nvarchar(100),
@State nvarchar(100),
@City nvarchar(100),
@AddressId int

BEGIN
INSERT INTO Customer(UserName, Password) VALUES(@UserName,@Password)

SELECT @CustomerId = @@IDENTITY

INSERT INTO Address(FirstName,LastName,EmailID,Country,State,city)
VALUES(@FirstNAme,@LastName,@EmailID,@Country,@State,@City)

SELECT @AddressId = @@IDENTITY

INSERT INTO CustomerAddresses(CustomerId,AddressId)
VALUES(@CustomerId,@AddressId)

END

Mark as answer if satisfied........

Regards,
Shree M.

Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/9/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Kavya Shree Mandapalli

I need my table header and details table have so how to insert,update,Delete using MVC3 Entity Framework
can you do and send me my mail that useful for me.

1.TblBillHead
==========
Id
Bno
Date
TotalAmt

2.TblBillDetails
==============
Id
RefId
Item
Rate
Qry
RowTotal

Mark as Answer if its helpful to you

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 7/9/2013 [Member] Silver | Points: 25

Up
0
Down
Hey,

Can you exactly tell me vt u want so that il do it send it.
table that you require and all..

Regards,
Shree M.

Kavya Shree Mandapalli

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 7/9/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

Shree M.

I have 2 table TblBillHead and TblBillDetails here i need data entry page using webgrid
for entry items,qty,rate also using MVC3 entity Framework how will do this




Mark as Answer if its helpful to you

ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Mandlaa on: 8/19/2013 [Member] Starter | Points: 25

Up
0
Down
This is my sample data from database,

ID Name ImageUrl

1 a http://notous.blob.core.windows.net/images/1-9.jpg

2 b http://notous.blob.core.windows.net/images/10_discount-150x150.jpg

3 c http://notous.blob.core.windows.net/images/FB-button-341x341.png

I want display this data not for same This ImageURL display as image in My view Using ASP.NET MVC,

And also At the time of insert also after uploading the image save in database Like above(its possible???)

please help me,I am new to this MVC


ramuvalmiki07-22001, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response