0

Suppose we have class PersonModel, user edits a detail form in the browser and hits the submit button.

How to write a controller logic to create an UPDATE SqlComand (no EF) and update only those of fields from class PersonModel (FirstName, LastName, Age...) which have been changed. What should be an argument of the /Person/Edit/<id> controller?

Cartesius00
  • 23,584
  • 43
  • 124
  • 195
  • 3
    Why are you not using an ORM? That's silly in this day and age, because it handles all this for you. You may start a project thinking that an ORM is overkill, and then as it progresses, you end up writing way more code this way. – Erik Funkenbusch Dec 08 '11 at 17:10

3 Answers3

2

You don't do anything special to your MVC code, and you don't add any special URL parameters.

If you only want to update the changed fields, then you have to write a lot of code. You have to get the current record, compare each of your models fields to the records fields, figure out which ones have changed, then you have to construct a SQL statement based on the changed fields, then update it, making sure to use optomistic record locking to insure the field has not changed between when you retrieved it and when you update it.

This is probably going to be a couple hundred lines of code. Or it could be one line of code if you were using an ORM.

Also, keep in mind you need to use parameterized statements as well, to insure against a SQL injection attack (something you get for free in an ORM as well).

Good luck, I certainly am not going to write it.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
1

What I would suggest is that you don't use the generalized PersonModel which I assume is a 1:1 representation of your data model. You should create a watered down view model of with only the fields you care about. Then use something like AutoMapper to map the ViewModel back to your DomainModel.

Here is a reference on how to use AutoMapper for such a scenario.

How to cross map objects using AutoMapper

Community
  • 1
  • 1
lamchakchan
  • 71
  • 2
0

You may use Linq and let the datacontext do everything for you. In the Model you just create your data class with the linq queries for the update, retrieve the object, set all the values that have been passed from the view in the object you've retrieved (you'll have to do this anyway, since the pattern decouples the view from the model | Remember too, that MVC implements observer for letting the view to know about changes in the model, so you can't know what was modified in the view from the controller), and then you call the DataContext.SubmitChanges(); method. The data context will do the job in its own. It will know what to change and modify and will make the most performant query.

JuanC
  • 119
  • 2
  • 10
  • He said he's not using Entity Framework (and I assume no other kind of ORM), he wants to generate SQL statements using SqlCommand. – Erik Funkenbusch Dec 08 '11 at 17:16
  • What part of "no other kind of ORM" didn't you understand? Linq-to-sql is another kind of ORM. – Erik Funkenbusch Dec 08 '11 at 17:24
  • 1
    Where did you read "no other kind of ORM"? Because I read the Answer again and again and again and I can't really find that. That's just one assumption you are making. – JuanC Dec 08 '11 at 17:47
  • @JuanC - *I* said that in my first comment to you, and my comment was based on the fact that he said he wanted to generate an Update SQL statement using SqlCommand, which is not using an ORM. – Erik Funkenbusch Dec 09 '11 at 17:20