Databases and Datatables

[liquidchild]

Hey

I am 'trying' to code an application that moves data from one database to another, then deletes the original source data.

However the main problem is that the source data in the original database is bound by ref constraints etc which makes the process harder as the data obviously has to be deleted in an certain order.

I was wondering if I can use ado tables and datasets to achieve this? I know there is the obvious way of doing individual select and delete statements based on the parent and child relationships.

However I have read about setting up constraints on ado tables and using cascade delete on these constraints, so basically I thought of replicating the constraints that are in my source database - would this work?

Also, If lets say I have my parent table 'A' populated by doing a general select, I then want to populate my next table 'B' based on a field in this table, i.e. so I only get the children from 'B', is there another way to do this rather than loop round each time creating a row and adding it to a datatable? Perhaps using the select command or the like?

This is an old system that I have picked up, while I know there are other ways of doing this, I am trying to keep it in the realms of what I have been handed.

Thanks in advance!

 

[jmcilhinney]

You could do what you suggest but, if you need to delete ALL the data then I don't see the point. You'd have to retrieve all the parent and child data anyway. You'd then create the DataRelation, delete all the parent data, then save the child changes, then save the parent changes. You may as well just retrieve all the data, delete all the child data, delete all the parent data, save the child changes, then save the parent changes.

Dim connection As New SqlConnection("connection string here")
Dim parentAdapter As New SqlDataAdapter("SELECT * FROM ParentTable", connection)
Dim parentBuilder As New SqlCommandBuilder(parentAdapter)
Dim parentTable As New DataTable
Dim childAdapter As New SqlDataAdapter("SELECT * FROM ChildTable", connection)
Dim childBuilder As New SqlCommandBuilder(childAdapter)
Dim childTable As New DataTable

'This will keep every DataRow's RowState property as
'Added so they can be inserted into another database.
parentAdapter.AcceptChangesDuringFill = False
childAdapter.AcceptChangesDuringFill = False

'Get all the data.
connection.Open()
parentAdapter.Fill(parentTable)
childAdapter.Fill(childTable)
connection.Close()

'Insert the data into the other database here.

'Change all RowStates from Added to Unchanged.
parentTable.AcceptChanges()
childTable.AcceptChanges()

Dim row As DataRow

'Change all the parent RowStates from Unchanged to Deleted.
For Each row In parentTable.Rows
    row.Delete()
Next row

'Change all the child RowStates from Unchanged to Deleted.
For Each row In childTable.Rows
    row.Delete()
Next row

connection.Open()

'Use a transaction so we don't end up with partially deleted data if there's an issue.
Using tx As New TransactionScope
    'Delete the child data first.
    childAdapter.Update(childTable)

    'Delete the parent data second.
    parentAdapter.Update(parentTable)

    'Indicate that the transaction should be committed.
    tx.Complete()
End Using

connection.Close()

'Clean up.
connection.Dispose()
parentAdapter.Dispose()
parentBuilder.Dispose()
childAdapter.Dispose()
childBuilder.Dispose()

 

[liquidchild]

Thanks for the reply.

However I don't require to delete all the data in the parent and child tables, only a subset of the data. The constraints on the parent and child tables then causes problems when it comes to do the deletes as we effectively break down the archiving of the whole subset into smaller subsets.

Basically what happens is:

we select from the parent table where the rows are past a certain date (date - 10 lets say)

we then select from the child table where the rows are past a certain date (date - 10 also)

Each select also has a row num attribute added.

Now I think the problem is that Oracle can give us ten child rows back that don't match the parent rows. I can change the select to loop round the parent table and use the ID's in that to retrieve the child rows (if I choose to do this - could someone give me an example of the code?), but am open to better suggestions.

 

[cjard]

Most databases are intelligent enough to only check contraints when a transaction finishes, so you would make a DataSet WITHOUT ANY CONSTRAINTS, select all your data into it, start a transaction on the new db, upload all the data, finish the transaction and then the target DB will check the data.

If it fails, well.. it's because you didnt transfer all the data to support the constraint.

Using a transaction in this manner obviates the need for ordered transfer



Note that if youre using a "good" database for source and dest (though they neednt be the same) like SQLS or Oracle, then you might be able to cause the RDBMS to connect directly to the other DB, removing the need for the dataset..

e.g. Access to SQLS, get SQLS to make an external databse conenction to Access, then do e.g.

INSERT into destTbl
SELECT * FROM dbo.externalaccessdb.sourcetbl

 

[liquidchild]

Hi,

I think the problem is though that we are splitting the data into smaller subsets and oracle doesn't return the child/parent relationship when this is done.

Doing it as one massive job can take some time, so it was thought better to break it down.

 

[jmcilhinney]

Yous should be joining the child table with the parent table in your second query and imposing the same constraints on your child data as you do on your parent data, e.g.

SELECT * FROM Parent WHERE Date > SomeDate
SELECT c.* FROM Child c INNER JOIN Parent p WHERE c.ParentID = p.ID AND p.Date > SomeDate

Now you're guaranteed of only getting all the child data that is related to the parent data you're going to delete.