none
writing new records to a table in a database using Entity framework 4.0 RRS feed

  • Question

  • Hi

    I am trying to parse a PIPE delimted text file and loop over parsed data and write to a table in SQL Server database.

    Following is the code I am using.

        private void BuildMasterRecords()
        {
            pdsModel.pdsEntities wrkmstr = new pdsEntities();
            ParseTestWrkMaster mstr = wrkmstr.ParseTestWrkMasters.CreateObject();
            try
            {       
            var query = from line in File.ReadAllLines(@"C:\FilesToParse\"+ HCMasName)
                            let ParsedRecord = line.Split(Delimiter)
                            select new MasterRecord()                           
                                sHospital_id  = ParsedRecord[1],
                                sPatient_acct = ParsedRecord[2],
                                Load_id = 36605,
                                Master_seq = 1,                          
                                sBirth_date   = ParsedRecord[5],                           
                                sAdmission_date = ParsedRecord[7],

                foreach (var item in query) 
                    mstr.Load_id = item.Load_id;
                    mstr.Master_seq = item.Master_seq;
                    mstr.Hospital_id = item.sHospital_id;
                    mstr.Patient_acct = item.sPatient_acct;
                    mstr.Admission_date = Convert.ToDateTime(item.sAdmission_date);
                    mstr.Birth_date = Convert.ToDateTime(item.sBirth_date);

                    wrkmstr.AcceptAllChanges();
                    wrkmstr.SaveChanges();              
                }           
            }
            catch (Exception Ex)
            {
                ExcepMsg.Text = Ex.Message;
            }
        }

    Here MasterRecord is class where all variables like Load_id etc are defined.

    pdsEntities is a Entity Data Model and ParseTestWrkMaster is Entity( tabel in database).

    But appliction does not insert any record in the table in database.

    Am I missing something?

    Application does not generate any error.

    Thanks in advance for any hint or help.

    Bharat Gadhia


    bgadhia

    Friday, April 13, 2012 8:53 PM

Answers

  • Hi Bharat;

    The following is from the Microsoft documenation on ObjectContext.AcceptAllChanges Method :

    Accepts the changes on all associated entries in the ObjectStateManager so their resultant state is either unchanged or detached.

    This method iterates all the ObjectStateEntry objects within the ObjectStateManager that are Added or Modified, and then sets the state of the entry to Unchanged. The Deleted items become detached.

    What you need to do is replace the statement wrkmstr.AcceptAllChanges(); with the following line of code :

    wrkmstr.ParseTestWrkMasters.AddObject(mstr);

    which will add the new object to the ObjectContext and then save the record.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, April 13, 2012 11:23 PM
  • Hi Bharat;

    This message, "The property 'Patient_acct' is part of the object's key information and cannot be modified.",  generally means that the primary key of the record is made of of multiple columns and by attempting to change the column Patient_acct you are trying to change the primary key. Once a record is created its primary key can not be changed.

      

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 7:35 PM
    • Unmarked as answer by Bharat Gadhia Monday, April 16, 2012 7:35 PM
    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 7:36 PM
    Monday, April 16, 2012 6:45 PM
  • Hi Bharat;

    So here is your primary key:

    <Key>
      <PropertyRef Name="Hospital_id" />
      <PropertyRef Name="Patient_acct" />
      <PropertyRef Name="Load_id" />
      <PropertyRef Name="Master_seq" />
    </Key>  

    In that table all keys, the combination of the four columns, must be unique. So lets say you create a record as follows :

    ParseTestWrkMaster mstr = wrkmstr.ParseTestWrkMasters.CreateObject();

    And fill its columns with data:

     mstr.Hospital_id  = 444;
     mstr.Patient_acct = 12345;
     mstr.Load_id = 36605;
     mstr.Master_seq = 1;
     ... Other columns here
     
    Then insert the record into the ObjectContext and then do a SaveChanges. One record inserted into the database. Then lets say you need to insert a new record and the only difference between them is the Patient_acct number then you create a new ParseTestWrkMaster object and initialize the fields :

     mstr.Hospital_id  = 444;
     mstr.Patient_acct = 15478;
     mstr.Load_id = 36605;
     mstr.Master_seq = 1;
     ... Other columns here
     
    Inset it into the ObjectContext and do a SaveChanges. The second record inserted. Now that should work. If it is not I would need to see the code where it is failing.

      

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 8:39 PM
    Monday, April 16, 2012 8:25 PM
  • Hi Bharat; 

    To your question, "so the key to inseritng a new recoed is to create evrytime a new ParseTestWrkMaster object and not to use the same object!!!.", That is correct. You can't reuse the object because EF will not allow you to change the key.

    If my last post answered you most recent question please mark it as an answer or that the post was helpful.

    Thanks.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Tuesday, April 17, 2012 3:22 PM
    Monday, April 16, 2012 10:16 PM

All replies

  • Hi Bharat;

    The following is from the Microsoft documenation on ObjectContext.AcceptAllChanges Method :

    Accepts the changes on all associated entries in the ObjectStateManager so their resultant state is either unchanged or detached.

    This method iterates all the ObjectStateEntry objects within the ObjectStateManager that are Added or Modified, and then sets the state of the entry to Unchanged. The Deleted items become detached.

    What you need to do is replace the statement wrkmstr.AcceptAllChanges(); with the following line of code :

    wrkmstr.ParseTestWrkMasters.AddObject(mstr);

    which will add the new object to the ObjectContext and then save the record.

       


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, April 13, 2012 11:23 PM
  • Hi Fernando,

    Thank you for suggestion.

    Your suggested code correction did work well.

    Thanks.

    Bharat.


    bgadhia

    Monday, April 16, 2012 3:33 PM
  •  

    Not a problem Bharat, glad I was able to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, April 16, 2012 4:11 PM
  • Hi Fernando,

    While parsing record and writing to database I get following error mesage

    "The property 'Patient_acct' is part of the object's key information and cannot be modified." at

    ReportPropertyChanging("Patient_acct");

    For each parsed record I have a new 'Patient_acct'  and it's related items which I need to write to the table in database.

    Only one record is written to the table in database that too with existing 'Patient_acct' only !!!!

    While inserting another record with new 'Patient_acct'   I get above error mesage.

    How to avaoid this error mesage.

    Your suggestio will help.

    Thank you.

    Bharat.


    bgadhia

    Monday, April 16, 2012 5:25 PM
  • Hi Bharat;

    This message, "The property 'Patient_acct' is part of the object's key information and cannot be modified.",  generally means that the primary key of the record is made of of multiple columns and by attempting to change the column Patient_acct you are trying to change the primary key. Once a record is created its primary key can not be changed.

      

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 7:35 PM
    • Unmarked as answer by Bharat Gadhia Monday, April 16, 2012 7:35 PM
    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 7:36 PM
    Monday, April 16, 2012 6:45 PM
  • Hi Fernando,

    Thanks for your answer.

    Before moving to Entity Framwork, we used DTS for parsing and inserting data to a table in database. We never had before mentioned problme with 'Patient_acct'.

    Following is the Entity Key structure

            <EntityType Name="ParseTestWrkMaster">
              <Key>
                <PropertyRef Name="Hospital_id" />
                <PropertyRef Name="Patient_acct" />
                <PropertyRef Name="Load_id" />
                <PropertyRef Name="Master_seq" />
              </Key>

    so each record with new ="Patient_acct" and same Hospital_id,Load_id and Master_seq becomes a uniqe record, otherwise a duplicate record may make an entry to the table, so how to achieve that unique records are written to table?

    Thanks.

    Bharat.


    bgadhia

    Monday, April 16, 2012 7:51 PM
  • Hi Bharat;

    So here is your primary key:

    <Key>
      <PropertyRef Name="Hospital_id" />
      <PropertyRef Name="Patient_acct" />
      <PropertyRef Name="Load_id" />
      <PropertyRef Name="Master_seq" />
    </Key>  

    In that table all keys, the combination of the four columns, must be unique. So lets say you create a record as follows :

    ParseTestWrkMaster mstr = wrkmstr.ParseTestWrkMasters.CreateObject();

    And fill its columns with data:

     mstr.Hospital_id  = 444;
     mstr.Patient_acct = 12345;
     mstr.Load_id = 36605;
     mstr.Master_seq = 1;
     ... Other columns here
     
    Then insert the record into the ObjectContext and then do a SaveChanges. One record inserted into the database. Then lets say you need to insert a new record and the only difference between them is the Patient_acct number then you create a new ParseTestWrkMaster object and initialize the fields :

     mstr.Hospital_id  = 444;
     mstr.Patient_acct = 15478;
     mstr.Load_id = 36605;
     mstr.Master_seq = 1;
     ... Other columns here
     
    Inset it into the ObjectContext and do a SaveChanges. The second record inserted. Now that should work. If it is not I would need to see the code where it is failing.

      

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Monday, April 16, 2012 8:39 PM
    Monday, April 16, 2012 8:25 PM
  • HI Fernando,

    so the key to inseritng a new recoed is to create evrytime a new ParseTestWrkMaster object and not to use the same object!!!.

    Thanks a lot for this clearificationa and great help.

    Thanks.

    Bharat.


    bgadhia

    Monday, April 16, 2012 8:39 PM
  • Hi Fernando,

    I was able to insert all  records in database table.

    It worked perfectly.

    Thanks.

    Bharat.


    bgadhia

    Monday, April 16, 2012 9:01 PM
  • Hi Bharat; 

    To your question, "so the key to inseritng a new recoed is to create evrytime a new ParseTestWrkMaster object and not to use the same object!!!.", That is correct. You can't reuse the object because EF will not allow you to change the key.

    If my last post answered you most recent question please mark it as an answer or that the post was helpful.

    Thanks.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Bharat Gadhia Tuesday, April 17, 2012 3:22 PM
    Monday, April 16, 2012 10:16 PM