Best UPDATE Method in LINQ to SQL
Below is a typical method for me Update
in L2S. I'm still pretty new in this regard (L2S and business application development) but this is just SENSITIVELY wrong. As well as there MUST be a smarter way to do this. Unfortunately I am having problems rendering and I hope someone can provide an example or point me in the right direction.
To make a hit in the dark, I have Person Object
one that has all these fields as Properties? Then what is it?
Is this redundant since L2S has already mapped my Person table to the class?
It's just "how is it going" that you end up passing 30 parameters (or MORE) to the operator Update
?
For reference this is a business application using C #, WinForms, .Net 3.5 and L2S as per SQL 2005 standard.
Here's a typical call for me. This is a file (BLLConnect.cs) with other CRUD methods . Connect is the name of the database that contains . When the user clicks , this is what is ultimately called with all these fields that may have been updated -> tblPerson
save()
public static void UpdatePerson(int personID, string userID, string titleID, string firstName, string middleName, string lastName, string suffixID,
string ssn, char gender, DateTime? birthDate, DateTime? deathDate, string driversLicenseNumber,
string driversLicenseStateID, string primaryRaceID, string secondaryRaceID, bool hispanicOrigin,
bool citizenFlag, bool veteranFlag, short ? residencyCountyID, short? responsibilityCountyID, string emailAddress,
string maritalStatusID)
{
using (var context = ConnectDataContext.Create())
{
var personToUpdate =
(from person in context.tblPersons
where person.PersonID == personID
select person).Single();
personToUpdate.TitleID = titleID;
personToUpdate.FirstName = firstName;
personToUpdate.MiddleName = middleName;
personToUpdate.LastName = lastName;
personToUpdate.SuffixID = suffixID;
personToUpdate.SSN = ssn;
personToUpdate.Gender = gender;
personToUpdate.BirthDate = birthDate;
personToUpdate.DeathDate = deathDate;
personToUpdate.DriversLicenseNumber = driversLicenseNumber;
personToUpdate.DriversLicenseStateID = driversLicenseStateID;
personToUpdate.PrimaryRaceID = primaryRaceID;
personToUpdate.SecondaryRaceID = secondaryRaceID;
personToUpdate.HispanicOriginFlag = hispanicOrigin;
personToUpdate.CitizenFlag = citizenFlag;
personToUpdate.VeteranFlag = veteranFlag;
personToUpdate.ResidencyCountyID = residencyCountyID;
personToUpdate.ResponsibilityCountyID = responsibilityCountyID;
personToUpdate.EmailAddress = emailAddress;
personToUpdate.MaritalStatusID = maritalStatusID;
personToUpdate.UpdateUserID = userID;
personToUpdate.UpdateDateTime = DateTime.Now;
context.SubmitChanges();
}
}
So, if I understand things correctly, I will have a link datacontext
in my winform UI which I will use when I fill in the fields and ALSO for UPDATE, INSERT and DELETE. Right?
a source to share
A DataContext
follows a pattern known as a Unit of Work. It keeps track of all inserts, updates, and removes you during a code snippet.
After executing this part of the code, the method SubmitChanges
sends all the changes to the database in one go. You don't have to do anything; the changes you made will be automatically saved.
a source to share
Just don't write this method :)
Anytime any business logic needs to update certain fields for a person, update that person's specific fields (and remember to update the datacontext before the HTTP context is unloaded)
You were on the right track when you said, "Is this unnecessary since L2S has already mapped my Person table to a class?" Just use the class that L2S provided :)
If you have a screen (winforms) that needs to edit this 30th field object, then the easiest thing is to databind the fields on your screen directly into the fields on the linq to sql Person object. Here's a typical screen lifecycle:
- Your form is built (with a person id).
- The form load event handler will fetch the Person object from Linq to Sql:
context.tblPersons.Single(x=>x.ID == personID)
- This person will be set as the main form of the BindingSource's DataSource
- Lots of text fields on the screen will be configured to bind to each field of that person's object, allowing the user to edit the properties directly (you can just drag the detail view from the data sources tab into your form in VS before doing this automatically)
- When user clicks on save, just call EndEdit on DataSource and then SubmitChanges on L2s Datacontext
Everything will be fine, you should see the new values in your database ...
a source to share
What's wrong with your Update method is that you are instantiating datacontext in it.
And I suppose you also have other CRUD methods that do the same.
If you render your CRUD operations to the repository class, you will be using the DataContext as it should have been used.
See the answer to this question , if you follow the described project, you just pass the Person object to the store update method and it won't be that cumbersome.
a source to share