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.
May 18, 2023 at 9:17 am
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
May 18, 2023 at 9:42 am
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.
May 18, 2023 at 9:52 am
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
May 18, 2023 at 10:03 am
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.
May 18, 2023 at 10:56 am
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:
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
May 18, 2023 at 11:05 am
Thank you very much!
The staging table is a "copy" of the remote table with only the relevant data ?
May 18, 2023 at 11:14 am
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
May 22, 2023 at 12:20 pm
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;
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
May 22, 2023 at 1:44 pm
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