A better UPDATE method in LINQ to SQL

Question

The below is a typical, for me, Update method in L2S. I am still fairly new to a lot of this(L2S & business app development) but this just FEELs wrong. Like there MUST be a smarter way of doing this. Unfortunately, I am having trouble visualizing it and am hoping someone can provide an example or point me in the right direction.

To take a stab in the dark, would I have a Person Object that has all these fields as Properties? Then what, though?

Is that redundant since L2S already mapped my Person Table to a Class?

Is this just 'how it goes', that you eventually end up passing 30 parameters(or MORE) to an UPDATE statement at some point?

For reference, this is a business app using C#, WinForms, .Net 3.5, and L2S over SQL 2005 Standard.

Here is a typical Update Call for me. This is in a file(BLLConnect.cs) with other CRUD methods. Connect is the name of the DB that holds tblPerson When a user clicks save() this is what is eventually called with all of these fields having, potentially, been updated-->

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 am understanding things correctly I would have a datacontext reference in my winform UI that I would use when I populate the fields and ALSO for UPDATES, INSERTS, and DELETES. Correct?

Answer 1

Just don't write this method :)

Any time some business logic needs to update specific fields on a person, update the specific fields on that person (and remember to update the datacontext before the http context is unloaded)

You were on the right track when you said "Is that redundant since L2S already mapped my Person Table to a Class?". Just use the class that L2S has provided :)

If you've got a (winforms) screen that needs to edit this 30 field person object, then the easiest thing to do is to databind the fields on your screen directly to the fields on linq to sql's Person object. Here's a typical screen's lifecycle:

• Your form is constructed (with a person ID)
• Your form's load event handler will retrieve a Person object from Linq to Sql: context.tblPersons.Single(x=>x.ID == personID)
• This Person will be set as the form's main BindingSource's DataSource
• A whole lot of textboxes on the screen will be setup to bind to each field on that person object, allowing the user to edit the properties directly (you can just drag a detailsview from the data sources tab onto your form in VS to do these automatically)
• When the user hits save, just call EndEdit on the DataSource, then SubmitChanges on the L2s Datacontext

All going well, you should see new values in your database...

Answer 2

A DataContext follows a pattern known as a Unit of Work. It tracks all inserts, updates, and deletes you do during a piece of code.

Once that piece of code is done running, the SubmitChanges method sends all modifications to the database at one time. There is no need for you to do anything; changes you make will automatically be persisted.

Answer 3

What is wrong with your Update method is that you have are creating a datacontext instance within it.

And I presume you also have other CRUD methods which do the same.

If you abstract your CRUD operations to a Repository class you will be using the DataContext as it was meant to be used.

See the answer to this question, if you follow the design outlined you will be just passing a Person object to the repository Update method and it will not be so unwieldy.