I have a table hm_item in oracle database which has 10 columns and i have a table of same name in sql server database. Now I have to import data from oracle database when user click button. Which is the best way for doing this??
1 Answer
No need to use DataSet; you should just be able to use the ADO.NET command API, i.e. with your two connections:
using(var sqlServer = GetOpenSqlServerConnection()) // TODO
using(var oracle = GetOpenOracleConnection()) // TODO
using(var cmd = oracle.CreateCommand())
using(var bcp = new SqlBulkCopy(sqlServer))
{
bcp.DestinationTableName = "TableName";
cmd.CommandText = "select * from TableName";
using(var reader = cmd.ExecuteReader())
{
bcp.WriteToServer(reader);
}
}
Advantages (over DataSet):
- no need to hold all the data in memory at once; great for huge tables
- no need to wait to load all the data before you can start writing
- high-performance raw data in both directions (no per-row / per-batch commands - just "here's the data: blah blah blah blah")
8 Comments
user1578607
My SQl server database is sqlserver compact edition database and i m using sqlceconnection for the connection to database and bulkcopy class is not part of sqlserverce class.
Marc Gravell
@user1578607 then you really should have included that in the question... you could look at sqlcebulkcopy.codeplex.com perhaps?
user1578607
Can u please give me complete coding for using bulkcopy??
Marc Gravell
@user1578607 er, I pretty much have. The API for the CE version is similar, so you should be able to just substitute
SqlCeBulkCopyuser1578607
The call is ambiguous between the following methods or properties: 'ErikEJ.SqlCe.SqlCeBulkCopy.WriteToServer(System.Collections.IEnumerable)' and 'ErikEJ.SqlCe.SqlCeBulkCopy.WriteToServer(System.Data.IDataReader)'
|