Code first development - data from local and remote database

  • For my project I have to read data from a remote employee database : data like LastName, Firstname, Email, SerialNumber.

    In the new databse (other than the remote one) I have to add some other fields like Role, Profile, Salarylevel, Active.

    I read remote data by a DBContextA and using a class created with CodeFirst from existing database, mapping data from a view.

    For the new app I use a DBContexB (using another database) .

    My first approach is to have a new Employee table (in the new databse) with only the added fields and the SerialNumber used as a key (not database generated).

    The Employee class is :

    public class Employee
    {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public string SerialNumber{ get; set; }
    public string Role { get; set; }
    public string Profile { get; set; }
    public string SalaryLevel { get; set; }
    public bool Active { get; set; }
    }

    To view the list of employees I have to create a ViewModel with data from both DBContex. Since data like Firstname, LastName, etc are used in many views, I'm afraid there would be many query to read remote data.

    What other solution would you recommend?

    I also thought to replicate the remote view data into the local Employee data letting the user manually run a syncronization method to update local data as needed.

     

  • As this is a SQL Server forum, you won't find many code-first people here. We like to design our DBs first 🙂

    As you suggest, it seems that a good solution would be to copy the remotely stored employee data into your local database and periodically sync it. There are multiple ways of doing this ... the most efficient will depend on requirements and the remote data model (do they store DateCreated, DateModifed etc? Can employee data be deleted? Soft deletes or hard deletes?)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • You're right, sorry. Wrong place 🙂

    But the main question is basically  : sync or not to sync ?

    To answer your questions : remote data doesn't have fields like datecreated or date modified. Employess are never deleted from the remote database. When an employee leaves, the EndDate is filled but data remain visible. Inthe new app, instead, I 've added an Active fields because (at least the first time) I need to import data also for Employees no longer in service. User can set Active to false (soft deletion) just to hide employee in views.

    • This reply was modified 1 year, 5 months ago by  spilo.
  • As you suggest that there will be many queries on the local database which reference data maintained on the remote database, my advice would be 'sync' for sure. The lack of created/modified info will make the sync process painful, as you'll have to grab a complete dataset snapshot every time.

    Is it fair to assume that you cannot change the remote DB at all?

    I'd make the sync an automatic system process, rather than a 'user clicks a button' thing. If a degree of latency is acceptable, I'd also run it during non-work hours.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Is it fair to assume that you cannot change the remote DB at all?

    Absolutely. I have only read access to some views. It's a legacy application Sql database.

    I'd make the sync an automatic system process, rather than a 'user clicks a button' thing. If a degree of latency is acceptable, I'd also run it during non-work hours.

    Good idea. I'll think about it.

    The lack of created/modified info will make the sync process painful, as you'll have to grab a complete dataset snapshot every time.

    Exactly. I've made some test : I need to read all data to find out if a new employee was added (it's serialnumber is not in the local database) and check if data was updated by comparing remote data with the locally stored ones (i.e. remote LastName<> local LastName for employee with the same SerialNumber).

    At the moment , for updates, I have not found a better solution. The other option is to update data (lastname, firstname, email, ...) anyway if a record with the same serialnumber already exists inthe local database.

  • If 'SerialNumber' is unique per employee, I suggest you add it to the local database and put a clustered index on it.

    To do the sync, create a staging table locally which contains SerialNumber, LastName, EmailAddress etc), also with a clustered index on SerialNumber.

    Your sync process then runs as follows:

    1. Truncate staging table
    2. Import remote data to staging table
    3. Run an UPDATE query, matching on SerialNumber, to update existing rows in the main table from the staging table. Your clustered indexes will ensure that this runs as fast as possible.
    4. Run an INSERT query to add any new rows. The SerialNumber index will help here too.

    If you need help with the queries in (3) or (4), feel free to post again.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thank you very much!

    The staging table is a "copy" of the remote table with only the relevant data ?

  • spilo wrote:

    Thank you very much!

    The staging table is a "copy" of the remote table with only the relevant data ?

    Correct.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi,

    below is the code I wrote to import data from remote database.

    On the remote database I have a view with data I need to import.

    On the local SqlServer instance I've created a linked server to access the remote one.

    This is the stored procedure :

    SET NOCOUNT ON;

    --Clear table
    DELETE FROM ImportTimeWeb

    -- Copy remote data from view VW_PREFNO_EMPWITHROLE into local table
    -- TIMEWEB_DBSTART is the linked server name
    -- Role field in the view has a numeric prefix I don't need
    INSERT INTO ImportTimeWeb (LastName,FirstName,SerialNumber,Email,Role)
    SELECT LastName,FirstName,SerialNumber,Email,TRIM(substring(Role,PATINDEX ( '%[^0-9]%' ,Role),LEN(Role)))
    FROM TIMEWEB_DBSTART.DBSTART.dbo.VW_PREFNO_EMPWITHROLE


    --Insert into Profile table the roles that doesn't exists yet
    INSERT INTO PROFILE (Code, Descr)
    SELECT DISTINCT '*', Role
    FROMImportTimeWeb I LEFT OUTER JOIN PROFILE P on I.Role = P.Descr
    WHEREP.Descr is null


    --Update Employee data
    UPDATE Employee
    SET LastName = I.Lastname,
    Firstname = I.Firstname,
    Email = I.Email,
    ProfileId = P.Id
    FROM ImportTimeWeb I
    LEFT OUTER JOIN Profile P on I.Role = P.Descr
    WHERE I.SerialNumber = Employee.SerialNumber



    --Insert new employee record
    INSERT INTO Employee (LastName, Firstname, SerialNumber, Email, ProfileId, Active)
    SELECT I.LastName, I.Firstname, I.SerialNumber, I.Email, P.Id, 1
    FROM ImportTimeWeb I
    LEFT OUTER JOIN Employee on I.SerialNumber = Employee.SerialNumber
    LEFT OUTER JOIN Profile P on I.Role = P.Descr
    WHERE Employee.SerialNumber is null

     

  • Cool! I'd suggest you test/consider a couple of tweaks. The first, in particular, may improve performance, while the second is (IMO at least!) easier to read:

    INSERT INTO PROFILE
    (
    Code
    ,Descr
    )
    SELECT DISTINCT
    '*'
    ,Role
    FROM ImportTimeWeb I
    WHERE NOT EXISTS
    (
    SELECT 1 FROM Profile p WHERE p.Descr = I.Role
    );

    UPDATE e
    SET LastName = I.Lastname
    ,Firstname = I.Firstname
    ,Email = I.Email
    ,ProfileId = P.Id
    FROM Employee e
    JOIN ImportTimeWeb I
    ON I.SerialNumber = e.SerialNumber
    LEFT OUTER JOIN Profile P
    ON I.Role = P.Descr;

    Also, rather than delete, use truncate. It's faster.

    --Clear table
    TRUNCATE TABLE ImportTimeWeb;

    • This reply was modified 1 year, 5 months ago by  Phil Parkin. Reason: Fix query

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thank you very much !!!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply