how to do gedcom import with minimal database roundtrip. what is best practice for this kind of development

Question

In My current application, I need to import users from gedcom file. these users may exist in my registered users or i need to create one registered user for the same. now gedcom file contain s many information e.g. PersonalDetails,Addresses, Education Details, ProfessionalDetails this is one sample of xml file we are storing to store user's profile.

<UserProfile xmlns="">
  <BasicInfo>
    <Title value="Basic Details" />
    <Fields>
      <UserId title="UserId" right="Public" value="151" />
      <EmailAddress title="Email Address" right="CUG" value="[email protected]" />
      <FirstName title="First Name" right="Public" value="Anju" />
      <LastName title="Last Name" right="Public" value="Trivedi" />
      <DisplayName title="Display Name" right="Private" value="Anju" />
      <RegistrationStatusId title="RegistrationStatusId" right="Public" value="19" />
      <RegistrationStatus title="Registration Status" right="Private" value="Registered" />
      <CityId title="CityId" right="Private" value="19" />
      <CityName title="City" right="Public" value="Delhi" />
      <StateId title="StateId" right="Private" value="69" />
      <StateName title="State" right="Public" value="Delhi" />
      <CountryId title="CountryId" right="Private" value="109" />
      <CountryName title="Country" right="Public" value="India" />
      <Gender title="Gender" right="Private" value="Male" />
      <CreatedBy title="CreatedBy" right="Public" value="0" />
      <CreatedOn title="CreatedOn" right="Public" value="Nov 27 2009  3:08PM " />
      <ModifiedBy title="ModifiedBy" right="Public" value="13" />
      <ModifiedOn title="ModifiedOn" right="Public" value="Mar  3 2010  6:56PM " />
      <LogInStatusId title="LogInStatusId" right="Public" value="1" />
      <LogInStatus title="LogIn Status" right="Private" value="Free" />
      <ProfileImagePath title="Profile Pic" right="Public" value="~/Images/13_HolidayBarbie07CL2010427143129.jpg" />
      <ProfileThumbnailPath title="Profile Thumbnail" right="Public" value="~/Images/Thumb13_HolidayBarbie07CL2010427143129.jpg" />
    </Fields>
  </BasicInfo>
  <PersonalInfo>
    <Title value="Personal Details" />
    <Fields>
      <Nickname title="Nick Name" right="Public" value="Anju" />
      <NativeLocation title="Native" right="Public" value="Mehsana" />
      <DateofAnniversary title="Anniversary Dt." right="Private" value="4/1/2010" />
      <BloodGroupId title="BloodGroupId" right="Public" value="24" />
      <BloodGroupName title="Blood Group" right="Public" value="A+" />
      <MaritalStatusId title="MaritalStatusId" right="Private" value="35" />
      <MaritalStatusName title="Marital status" right="Private" value="UnMarried" />
      <DateofDeath title="Death dt" right="Private" value="" />
      <CreatedBy title="CreatedBy" right="Public" value="" />
      <CreatedOn title="CreatedOn" right="Public" value="" />
      <ModifiedBy title="ModifiedBy" right="Public" value="13" />
      <ModifiedOn title="ModifiedOn" right="Public" value="4/27/2010 2:32:07 PM" />
      <DateOfBirth title="Birth Date" value="" right="Public" />
      <BirthPlace title="Birth Place" value="Jaipur" right="Private" />
    </Fields>
  </PersonalInfo>
  <FamilyInfo>
    <Title value="Family Details" />
    <Fields>
      <GallantryHistory title="Gallantry History" right="Public" value="Anjli History" />
      <Ethinicity title="Ethinicity" right="Public" value="Indian" />
      <KulDev title="KulDev" right="Public" value="Krishna" />
      <KulDevi title="KulDevi" right="Public" value="Lakhsmi" />
      <Caste title="Caste" right="Private" value="Vaishnav" />
      <SunSignId title="SunSignId" right="Public" value="15" />
      <SunSignName title="SunSignName" right="Public" value="Gemini" />
    </Fields>
  </FamilyInfo>
  <HobbyInfo>
    <Title value="Hobbies/Interests" />
    <Fields>
      <AbountMe title="Abount Me" right="Public" value="" />
      <Hobbies title="Hobbies" right="Public" value="" />
      <Food title="Food" right="Public" value="" />
      <Movies title="Movies" right="Public" value="" />
      <Music title="Music" right="Public" value="" />
      <TVShows title="TV Shows" right="Public" value="" />
      <Books title="Books" right="Public" value="" />
      <Sports title="Sports" right="Public" value="" />
      <Will title="Will" right="Public" value="" />
      <FavouriteQuotes title="Favourite Quotes" right="Public" value="" />
      <CremationPrefernces title="Cremation Prefernces" right="Public" value="" />
    </Fields>
  </HobbyInfo>
  <PermenantAddr>
    <Title value="Permenant Address" />
    <Fields>
      <Address title="Address" right="Public" value="Select" />
      <CityId title="CityId" right="Public" value="116" />
      <CityName title="City" right="Public" value="Iran" />
      <StateId title="StateId" right="Public" value="95" />
      <StateName title="State" right="Public" value="Iran" />
      <CountryId title="CountryId" right="Public" value="7" />
      <CountryName title="Country" right="Public" value="Afghanistan" />
      <ZipCode title="ZipCode" right="Private" value="" />
    </Fields>
  </PermenantAddr>
  <PresentAddr>
    <Title value="Present Address" />
    <Fields>
      <Address title="Address" right="Public" value="Select" />
      <CityId title="CityId" right="Public" value="1" />
      <CityName title="City" right="Public" value="Select" />
      <StateId title="StateId" right="Public" value="1" />
      <StateName title="State" right="Public" value="Select" />
      <CountryId title="CountryId" right="Public" value="1" />
      <CountryName title="Country" right="Public" value="Select" />
      <ZipCode title="ZipCode" right="Private" value="" />
    </Fields>
  </PresentAddr>
  <ContactInfo>
    <Title value="Contact Details" />
    <Fields>
      <DayPhoneNo title="Day Phone" right="Public" value="" />
      <NightPhoneNo title="Night Phone" right="Public" value="" />
      <MobileNo title="Mobile No" right="Private" value="" />
      <FaxNo title="Fax No" right="CUG" value="" />
    </Fields>
  </ContactInfo>
  <EmailInfo>
    <Title value="Alternate Email Addresses" />
    <Fields>
      <Record right="Public">
        <Id title="Id" right="Public" value="3" />
        <Provider title="Provider" right="Public" value="google" />
        <EmailAddress title="Email Address" right="Public" value="[email protected]" />
        <IsActive title="IsActive" right="Public" value="false" />
        <CreatedBy title="CreatedBy" right="Public" value="13" />
        <CreatedOn title="CreatedOn" right="Public" value="Mar  3 2010 10:17AM " />
        <ModifiedBy title="ModifiedBy" right="Public" value="0" />
        <ModifiedOn title="ModifiedOn" right="Public" value="                    " />
      </Record>
      <Record right="Public">
        <Id title="Id" right="Public" value="4" />
        <Provider title="Provider" right="Public" value="Yahoo" />
        <EmailAddress title="Email Address" right="Public" value="[email protected]" />
        <IsActive title="IsActive" right="Public" value="false" />
     </Record>
      <Record right="Private">
        <Provider value="111" right="Private" />
        <EmailAddress value="[email protected]" right="Private" />
        <Id value="5" />
     </Record>
    </Fields>
  </EmailInfo>
  <AcademicInfo>
    <Title value="Education Details" />
    <Fields>
      <Record right="Public">
        <Id title="Id" right="Public" value="0" />
        <Education title="Education" right="Public" value="" />
        <Institute title="Institute" right="Public" value="" />
        <PassingYear title="Passing Year" right="Public" value="" />
       </Record>
    </Fields>
  </AcademicInfo>
  <AchievementInfo>
    <Title value="Achievement Details" />
    <Fields>
      <Record right="Public">
        <Id title="Id" right="Public" value="0" />
        <Awards title="Award" right="Public" value="" />
        <FieldOfAward title="Field Of Award" right="Public" value="" />
        <Tournament title="Tournament" right="Public" value="" />
        <AwardDescription title="Description" right="Public" value="" />
        <AwardYear title="Award Year" right="Public" value="" />
      </Record>
    </Fields>
  </AchievementInfo>
  <ProfessionalInfo>
    <Title value="Professional Details" />
    <Fields>
      <Record right="Public">
        <Id title="Id" right="Public" value="4" />
        <Occupation title="Occupation" right="Public" value="a" />
        <Organization title="Organization" right="Public" value="a" />
        <ProjectsDescription title="Description" right="Public" value="a" />
        <Duration title="Duration" right="Public" value="2" /
      </Record>
      <Record right="Public">
        <Id title="Id" right="Public" value="5" />
        <Occupation title="Occupation" right="Public" value="ab" />
        <Organization title="Organization" right="Public" value="zsd" />
        <ProjectsDescription title="Description" right="Public" value="sd" />
        <Duration title="Duration" right="Public" value="5" />
      </Record>
      <Record right="Public">
        <Id title="Id" right="Public" value="8" />
        <Occupation title="Occupation" right="Public" value="fgdf" />
        <Organization title="Organization" right="Public" value="gdfg" />
        <ProjectsDescription title="Description" right="Public" value="dfgdf" />
        <Duration title="Duration" right="Public" value="12" />
      </Record>
      <Record right="Public">
        <Id title="Id" right="Public" value="9" />
        <Occupation title="Occupation" right="Public" value="fgdf" />
        <Organization title="Organization" right="Public" value="gdfg" />
        <ProjectsDescription title="Description" right="Public" value="dfgdf" />
        <Duration title="Duration" right="Public" value="12" />
      </Record>
      <Record right="Public">
        <Id title="Id" right="Public" value="10" />
        <Occupation title="Occupation" right="Public" value="fgdf" />
        <Organization title="Organization" right="Public" value="gdfg" />
        <ProjectsDescription title="Description" right="Public" value="dfgdf" />
        <Duration title="Duration" right="Public" value="12" />
      </Record>
    </Fields>
  </ProfessionalInfo>
</UserProfile>

now when i am importing data from gedcom, i am creating one person object which contains all this info. but before i insert it itodatabase have to check if userid exist for the emailaddress dont update data else create a user and update its profilexml from data fecthed from gedcom.

for this i think i need some soln by which i can do only one roundtrip to database and can update all user's xml.

or i can execute one sp to get userid from all users where i'll check if user exist then return userid else insert basic data and return inserted userid

then for every user make xml from data and update it.

please provide be suggestion what is best practice to do this kind of development.

if need any more details please write me

Answer 1

You could send one query to the database to get users for your new file:

select * from person_table where [email protected]  or  userID = [email protected] or ...

Then from the results you get back you can check to see if there was a record for each user in the result set and procede accordingly.