none
Link to Sql - Problems on Updating Database RRS feed

  • Question

  • Hello Everyone, I'm quite new to Linq and have had some problems updating my dabase.
    I don't know if it is actually happening for be using a global data context or I'm missing something.
    I have my typed DataContex and one Static Public Var to initialize it located within the namespace AlpaCommon, like following:
     

    *********************************************************My partial datacontext*******************
     
    // partial datacontext class
    namespace
    AlpaCommon
    {
    public partial class AlpaDataContext : System.Data.Linq.DataContext
    {


    //Insert method is working...
    public void InsertAnimal2(Animal instance)
    {
    Animais.InsertOnSubmit(instance);
    SubmitChanges();
    }
     
    //Delete method is working...
    public void DeleteAnimal2(int animalID)
    {
    var animal = (from a in Animais where a.AnimalID == animalID select a).First();
    Animais.DeleteOnSubmit(animal);
    SubmitChanges();
    }
     
    //Update method IS NOT working...
    public void UpdateAnimal2(Animal newAnimal)
    {
    var animal = (from a in Animais where a.AnimalID == newAnimal.AnimalID select a).First();
    animal = newAnimal;
    SubmitChanges();
    }


    *******************This is where I'm instanciating the datacontext e other variables I'll need in the whole app*************

     
    //global DataContext instance
    namespace AlpaCommon
    {
    public static class Globals
    {
    public static AlpaDataContext db = new AlpaDataContext();

     
    ********************************************* This is the call to the update method ******************************

    ....................
    ....................
    using AlpaCommon;
    namespace Animais
    {
    public partial class Altera : System.Web.UI.Page
    {
    ....................
    ....................
    ....................
    ....................
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
    try
    {
    //cria um novo Objeto do tipo Animal
    Animal animalAltera = new Animal();
    //set new values
    animalAltera.AnimalID = Convert.ToInt32(Request.Params["AnimalID"]);
    animalAltera.Castrado = CastradoCheckBox.Checked;
    animalAltera.DisponivelAdocao = DisponivelCheckBox.Checked;
    animalAltera.Adotado = AdotadoCheckBox.Checked;
    animalAltera.Nome = NomeTextBox.Text;
    animalAltera.Tipo = TipoDropDownList.SelectedValue;
    animalAltera.Sexo = SexoDropDownList.SelectedValue;
    animalAltera.Descricao = DescricaoTextBox.Text;
    animalAltera.Local = LocalTextBox.Text;
    animalAltera.Foto = AlteraFoto();

    AlpaCommon.Globals.db.UpdateAnimal2(animalAltera);

    redirect = redirectSucesso;

    }
    catch
    {
    redirect = redirectErro;
    }
    finally
    {
    Helper.Redirect(redirect);
    }
    }


    ******************************************************************************************

    I'm not catching any exception, it just does not update the database.
    Am I missing something in my updating or calling method?
    I'm looking forward for suggestions.
     
    Thank you

    Josimari Martarelli

    Friday, July 17, 2009 3:11 AM

Answers

  • Hi,

    No, "animal = newAnimal;" will replace the animal object with newAnimal.

    To copy the properties you need to either manually assign the properties (animal.property1 = newAnimal.property1; ...etc) or you could use something like this:

    dc.CopyDataMembers(newAnimal, animal);
    ...and copydatamembers uses the mapping namespace to pick just the scalar members from the entity class:

        public static void CopyDataMembers(this DataContext dc, object sourceEntity, object targetEntity)
        {
            //get entity members
            IEnumerable<MetaDataMember> dataMembers = from mem in dc.Mapping.GetTable(sourceEntity.GetType()).RowType.DataMembers
                                                      where mem.IsAssociation == false
                                                      select mem;
    
            //go through the list of members and compare values
            foreach (MetaDataMember mem in dataMembers)
            {
                object originalValue = mem.StorageAccessor.GetBoxedValue(targetEntity);
                object newValue = mem.StorageAccessor.GetBoxedValue(sourceEntity);
    
                //check if the value has changed
                if (newValue == null && originalValue != null || newValue != null && !newValue.Equals(originalValue))
                {
                    //use reflection to update the target
                    System.Reflection.PropertyInfo propInfo = targetEntity.GetType().GetProperty(mem.Name);
                    propInfo.SetValue(targetEntity, propInfo.GetValue(sourceEntity, null), null);
    
                    //setboxedvalue bypasses change tracking - otherwise mem.StorageAccessor.SetBoxedValue(ref targetEntity, newValue); could be used instead of reflection
                }
            }
        }
    


    ...note that this approach disables update checking, so use it only if you're fine with "last update wins" in multi-user-concurrency situations. If you do want change tracking, the best approach is to include a timestamp column in every table and use the .Attach method to re-attach objects for updating...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Jmartarelli Friday, July 17, 2009 6:01 AM
    Friday, July 17, 2009 5:04 AM
    Answerer

All replies

  • //Update method IS NOT working...
    public void UpdateAnimal2(Animal newAnimal)
    {.
    var animal = (from a in Animais where a.AnimalID == newAnimal.AnimalID select a).First();
    animal = newAnimal;
    SubmitChanges();
    }


    The problem is that you're replacing the 'animal' object loaded from the database with 'newAnimal' passed in to the function.

    You need to either attach newAnimal ( dc.Animals.Attach(newAnimal, true) ) -or- copy the property values from newAnimal to animal.

    Using the .Attach method is the preferred way but it can not be used under some circumstances (single tier apps using multiple DCs can run into some issues, as can n-tier that don't use timestamp/rowversion for concurrency checking).
    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    Friday, July 17, 2009 3:30 AM
    Answerer
  • Hello Kristofer,

    I meant to copy the properties from the new updated instance to the old one on this line "animal = newAnimal;"
    If this is not the correct way to transfer the new properties values from one object to another, how would the sintax be?

    Thank you

    Josi
    Friday, July 17, 2009 4:46 AM
  • Hi,

    No, "animal = newAnimal;" will replace the animal object with newAnimal.

    To copy the properties you need to either manually assign the properties (animal.property1 = newAnimal.property1; ...etc) or you could use something like this:

    dc.CopyDataMembers(newAnimal, animal);
    ...and copydatamembers uses the mapping namespace to pick just the scalar members from the entity class:

        public static void CopyDataMembers(this DataContext dc, object sourceEntity, object targetEntity)
        {
            //get entity members
            IEnumerable<MetaDataMember> dataMembers = from mem in dc.Mapping.GetTable(sourceEntity.GetType()).RowType.DataMembers
                                                      where mem.IsAssociation == false
                                                      select mem;
    
            //go through the list of members and compare values
            foreach (MetaDataMember mem in dataMembers)
            {
                object originalValue = mem.StorageAccessor.GetBoxedValue(targetEntity);
                object newValue = mem.StorageAccessor.GetBoxedValue(sourceEntity);
    
                //check if the value has changed
                if (newValue == null && originalValue != null || newValue != null && !newValue.Equals(originalValue))
                {
                    //use reflection to update the target
                    System.Reflection.PropertyInfo propInfo = targetEntity.GetType().GetProperty(mem.Name);
                    propInfo.SetValue(targetEntity, propInfo.GetValue(sourceEntity, null), null);
    
                    //setboxedvalue bypasses change tracking - otherwise mem.StorageAccessor.SetBoxedValue(ref targetEntity, newValue); could be used instead of reflection
                }
            }
        }
    


    ...note that this approach disables update checking, so use it only if you're fine with "last update wins" in multi-user-concurrency situations. If you do want change tracking, the best approach is to include a timestamp column in every table and use the .Attach method to re-attach objects for updating...


    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Jmartarelli Friday, July 17, 2009 6:01 AM
    Friday, July 17, 2009 5:04 AM
    Answerer
  • Hi Kristofer,

    Copying each property from the new to the old object worked fine:

     

    public void UpdateAnimal2(Animal animalAltera)

    {

     

    // Query for a specific animal.

     

    var animal =(from a in Animais

     

    where a.AnimalID == animalAltera.AnimalID

     

    select a).First();

    animal.AnimalID = animalAltera.AnimalID;

    animal.Castrado = animalAltera.Castrado;

    animal.DisponivelAdocao = animalAltera.DisponivelAdocao;

    animal.Adotado = animalAltera.Adotado;

    animal.Nome = animalAltera.Nome;

    animal.Tipo = animalAltera.Tipo;

    animal.Sexo = animalAltera.Sexo;

    animal.Descricao = animalAltera.Descricao;

    animal.Local = animalAltera.Local;

    animal.Foto = animalAltera.Foto;

    SubmitChanges();

    }

    Attaching doesn't work because I don't have a timestamp field and don't think it is necessary for now. (I'll have one user admin updating the homepage)
    The only thing that bothers is that assigning each property from the new to the old object will require additional work if I have changes in my table structure, so your function "CopyDataMembers" will feet perfectly.

    Thank you!!!
     

    • Marked as answer by Jmartarelli Friday, July 17, 2009 6:01 AM
    • Unmarked as answer by Jmartarelli Friday, July 17, 2009 6:02 AM
    Friday, July 17, 2009 6:00 AM