Importation d'une feuille Excel conviviale dans une table SQL Server comportant de nombreuses clés étrangères

c# excel sqlbulkcopy sqldataadapter sql-server

Question

J'ai une feuille Excel remplie par un employé des ressources humaines avec des milliers de dossiers clients. Elle ressemble à celle-ci:

User Friendly Excel Sheet Exemple de capture d'écran

Le schéma de la table SQL Server de mon client ressemble à ceci

CREATE TABLE [dbo].[Clients] (
[ID]            INT            IDENTITY (1, 1) NOT NULL,
[Name]          NVARCHAR (100) NOT NULL,
[Photo]         VARCHAR (200)  NOT NULL,
[PolicyID]      INT            NOT NULL,
[BirthDay]      DATE           NOT NULL,
[Gender]        BIT            NOT NULL,
[Title]         NVARCHAR (100) NULL,
[Nationality]   NVARCHAR (100) NOT NULL,
[Relationship]  NVARCHAR (50)  NOT NULL,
[ClassID]       INT            NOT NULL,
[SponsorID]     INT            NULL,
[HRID]          INT            NOT NULL,
[Active]        BIT            CONSTRAINT [DF_Clients_Active] DEFAULT ((1)) NOT NULL,
[StartingDate]  DATE           NOT NULL,
[EndingDate]    DATE           NOT NULL,
[AddingDate]    DATETIME       NOT NULL,
[Creator]       INT            NOT NULL,
[UniqueID]      NVARCHAR (50)  NULL,
[PassportNo]    NVARCHAR (50)  NULL,
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [FK_Clients_Clients] FOREIGN KEY ([SponsorID]) REFERENCES [dbo].[Clients] ([ID]),
CONSTRAINT [FK_Clients_Employees] FOREIGN KEY ([HRID]) REFERENCES [dbo].[Employees] ([ID]),
CONSTRAINT [FK_Clients_Employees1] FOREIGN KEY ([Creator]) REFERENCES [dbo].[Employees] ([ID]),
CONSTRAINT [FK_Clients_Policy] FOREIGN KEY ([PolicyID]) REFERENCES [dbo].[Policy] ([ID]),
CONSTRAINT [FK_Clients_Classes] FOREIGN KEY ([ClassID]) REFERENCES [dbo].[Classes] ([ID])
);

Quelle est la meilleure approche pour réaliser de tels inserts?

J'ai essayé d'utiliser SqlBulkCopy mais cela n'autorise aucune manipulation sur les lignes insérées.

J'ai également essayé d'utiliser SqlAdapter.Update(Datatable) mais cela a échoué car j'ai lu la feuille Excel avec ExcelDataReader puis essayé d'ajouter des colonnes telles que Creator et Adding Date à l'exécution et lorsque j'ai essayé d'exécuter Adapter.Update(ModifiedDatatable) il jette une exception

La mise à jour nécessite un UpdateCommand valide lors de la transmission de la collection DataRow avec des lignes modifiées

Lorsque j'ai essayé d'utiliser SqlBulkCopy pour insérer cette feuille Excel, cela a fonctionné comme prévu

Feuille Excel avec capture de clés étrangères

Mais il n’est pas correct de forcer l’utilisateur final à insérer des clés étrangères dans la feuille Excel avant l’importation.

Remarquer:

Désolé de télécharger des captures d'écran sur Tinypic, mais je n'ai pas pu les télécharger ici à cause de mes points de représentation.

Merci d'avance

Réponse acceptée

Je créerais un paquet SSIS dans ce scénario. SSIS peut lire à partir d'Excel et vous pouvez le faire interroger la base de données pour obtenir des informations supplémentaires permettant de créer un jeu de données valide qui ne violera pas les contraintes FK.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi