Solved

How to Copy Data from Oracle DB to Access DB in C#?

Posted on 2006-06-25
6
330 Views
Last Modified: 2008-01-09
I have the same exact table structure in both the Oracle DB and the Access DB. Both DBs are installed in one PC which my C# (.NET 2.0) application will run from. However, the Oracle DB is the source one. So what I want is to copy a portion from the source Oracle DB (the portion is a specific SQL SELECT statement) to the Access DB.

I could get the data from the Oracle DB using an OracleDataAdapter and store the data in a DataTable, like this:
    OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM R151BTS WHERE BSC=1, MyConnection);
    DataTable dt = new DataTable("dt");
    da.Fill(dt,"dt");

But after that, how could I copy the data from the DataTable 'dt' to the Access DB?

I might be wrong with my above procedure, so if anyone could suggest to me an optimized way to do my request, I will be really grateful.
0
Comment
Question by:salan_alani
  • 3
  • 3
6 Comments
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 16979101
The task becomes simplier  if the destination database is empty (or at least won't have any primary key collisions)...

In that case you merely "share the DataTable" between the two DataAdapters (one for Oracle and one for Microsoft Access).  Here is some "psuedo-code"

// Fill the dataTable from Oracle
DataAdapter1.Fill(DataTable);

// Mark all of the rows as "added"
foreach (DataRow myRow in DataTable.Rows)
  {
         myRow.GetAdded();  // new to v2.0
  }


// Save to Access with a separate DataAdapter
DataAdapter2.Update(DataTable);


The task is a wee bit more complicated if you need update existing data in the destination database... you'd have to do a fill() followed by a Merge() before the Update()
0
 
LVL 41

Expert Comment

by:graye
ID: 16979104
Damn... that's supposed to bet "SetAdded()"

I should check my spelling before I hit Submit, eh?
0
 
LVL 2

Author Comment

by:salan_alani
ID: 16979127
graye, I did not try your code yet. But, the Access DB is not empty. However, it does not have any primary key or some rules (it is a basic/simple table structure). Although, I don't need to update my existing data in the Access DB, just a matter of copying data from Oracle DB to Access DB.

So, should your code works for me?

Another thing, how should I create the DataAdapter2 object in my case? And does the Update method will affect the Access table itself without the need to do SQL INSERT INTO statement?

Thanks in advance
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Author Comment

by:salan_alani
ID: 16979394
graye, I tried your code and it works for me. However, I have the following error:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement

at the following block:

try
{
    DataAdapter2.Update(DataTable);
}
catch (Exception ex)
{
    SW.WriteLine("Error performing the update process: " + ex.Message);
}

Above is kind of logging the errors (SW is a StreamWriter object).

The problem is how could I know which DataRow through the exception? In other words, what is the SQL INSERT INTO statement that cause the problem? ex.Message or ex.ToString() do not have the information on which Row is not inserted/added !!!

Please reply me as soon as possible and thanks for your help..
0
 
LVL 2

Author Comment

by:salan_alani
ID: 16979664
I figure out the problem and solve it.

Thanks for your help..
0
 
LVL 41

Expert Comment

by:graye
ID: 16980911
I'm glad you figured it out.... and I'm sorry my answer wasn't complete enough.

Yes, you have to either create the InsertCommand (and perhaps DeleteCommand) or use the CommandBuilder to create them for you.

I would imagine that you'd create the DataAdapter for the Microsoft Access database using the same technique ythat you used for the Oracle database.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now