this is me once again moving around data access. my problem now hide in the import process of an excel file that would be inserted into sql db.
the problem is the excel file may be updated or changed and i have to periodically import it and perform the process but I still cant check or compare between the two Data Source (excel, sql) as I violate the entity integrity (PK)
i found a kind of stupid approach that each time i want to import the file i will empty the table then re-insert the data, but at this step a new problem raise that other tables are !engaged! with it ... so i will violate the referential integrity (FK)
i looking for solution to compare between two sources for each tuple / row, ignore the existing one and insert the new
xlConnection.Open() SqlConnection.Open() empty() xlDataReader.Read() bulkC.DestinationTableName = "Instructor" bulkC.ColumnMappings.Add("id", "InstNo") bulkC.ColumnMappings.Add("name", "InstName") bulkC.ColumnMappings.Add("degree", "InstDegree") bulkC.ColumnMappings.Add("deptno", "DeptNo") bulkC.WriteToServer(xlDataReader) xlConnection.Close() SqlConnection.Close()
I know that the code look so primitive,, any one help