Click here to Skip to main content
14,697,404 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have a little background in .NET, mostly self taught and I rarely have to ask for help, however here I am.

I have a script that analyses CSV files (KBCSV Lib) and updates a database with the values from these CSV files. I can get that to work fine using SQLBulkCopy. The problem I am having is when the values already exist in the database, SQLBulkCopy will just append. After a few google searches, the solution seems to reside in populating datatables; One with values from the CSV, One with values from the Database - And then merging the two together.

I have done this, and the datatables populate and merge. The issue I am having is the data from the CSV gets appended, it does not update the original values in the datatable storing the database values.

Still following?

I have tried changing preserveChanges to both true and false. No avail.

Please help!?

Code below:

Dim firstdatatable As DataTable = New DataTable
            Dim Seconddatatable As DataTable = New DataTable
            Dim finaldatatable As DataTable = New DataTable
            Dim DbProdList = EntityToDatatable(fillqry, econtext)
            Dim TempProdList = EntityToDatatable(fillfromtemp, econtext)
            Firstdatatable = DbProdList.Copy()
            Seconddatatable = TempProdList.Copy()
            firstdatatable.Merge(Seconddatatable, False, MissingSchemaAction.Add
            firstdatatable.AcceptChanges()


The input:

User Name, Password, Full Name, Restriction list name, Banned word checking, Allow attachments, Template, Group, Mailbox size, Calendar, SyncML
psmith01,CLASS3B,Pauly Smith 1,YEAR2,1,N,ADVANCED,STAFF,1,Y,Y
smehta,CLASS3G,Smith Mehta,LOCAL,1,Y,STANDARD,PUPIL,2.1,N,Y
mrsjohns,SNHOJ,Mrs R Johns,UNRESTRICTED,-1,Y,ADVANCED,STAFF,2,Y,N
psmith02,CLASS588,Peter Smith 2, UKSCHOOLS,0,N,ADVANCED,STAFF,10,Y,Y
scohen,CLASS3G,Saul Cohen,LOCAL,2,Y,STANDARD,PUPIL,1,N,N
swright,CLASS1J,Shaun Wright,YEAR1,1,N,STANDARD,PUPIL,1,N,Y
amarkov,CLASS4E,Anya Markov,UKSCHOOLS,3,Y,STANDARD,PUPIL,1,N,N



Just a quick note: When I am importing the CSV, I have templates specified (I have coded logic where the user selects what column in the CSV corresponds to what column in the database) and before the CSV is SQLBulkCopied, I map the columns.

EG:

Using bcp As SqlBulkCopy = New SqlBulkCopy(connStr)

            'Checking to see if column has been specified, and mapping the column to the appropriate column in database.
            bcp.DestinationTableName = "dbo.tempinventoryimport"
            If categorybool = True Then
                bcp.ColumnMappings.Add(category, 2)
            End If
            If subcategorybool = True Then
                bcp.ColumnMappings.Add(subcategory, 3)


No matter how I try and merge the two datatables, the information appends to the end (Add's new rows with the same information). Some help would be muchly appreciated :)
Posted

1 solution

For each dataTable you need to define a column that acts as a primary key.

like :
<pre lang="vb"> Dim table As New DataTable()

table.Columns.Add(New DataColumn("MyColumn"))

Dim primaryKey(1) As DataColumn
primaryKey(1) = table.Columns("MyColumn")
table.PrimaryKey = primaryKey </pre>
Let me know
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900