Bulk Data Migration from DB to DB via Web Service

Hi Charles,

Thanks for your info.

But as I checked with the .NET SDK on DataAdapter, it seems the the batch
update using DataAdatper as described in

http://msdn.microsoft.com/en-us/library/s5xy331f.aspx
and
http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

require the source and destination to be the same DataSet of the same DB (as
it uses DataRowState to check if insert/update/delete is required). Also it
basically triggers inidividual operations for each insert/update/delete, so
there should be not much difference in performance.

I guess I should just use the primitive way.

But thanks anyway.


have a nice day
walty




> http://msdn.microsoft.com/en-us/library/s5xy331f.aspx

""Charles Wang [MSFT]"" wrote in message

> Hi Walty,
> Thank you for using Microsoft Managed TechNet Newsgroup. This is Charles
> and I am very glad to work with
> you on this issue.
>
> It seems that you were asking an ADO.NET question. You wanted to bulk
> insert the data in your dataset to your
> target database in your ASP.NET web service, right? If so, you can use
> SqlDataAdapter.Update. I recommend
> that you refer to this article which shows a good sample for your
> requirement:
> Consuming a DataSet from an XML Web Service (ADO.NET)
> http://msdn.microsoft.com/en-us/library/s5xy331f.aspx
>
> If you want to know more of SqlDataAdapter, please refer to the following
> articles:
> Updating Data Sources with DataAdapters (ADO.NET)
> http://msdn.microsoft.com/en-us/library/33y2221y.aspx
> Performing Batch Operations Using DataAdapters (ADO.NET)
> http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx
>
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
>
> Best regards,
> Charles Wang
>
> Microsoft Online Community Support
> Get Secure! - www.microsoft.com/security
> ====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ====================================================
>
>
>
>
 >> Stay informed about: Bulk Data Migration from DB to DB via Web Service 

Hi Walty,

I also need to move data from a remote database via web service. Can you please through me some idea. If possible can you send me sample code. Your help is very much appreciated.

Thank you,
Jasper



walty wrote:

Bulk Data Migration from DB to DB via Web Service
28-Aug-08

Hi,

As requested by customer, I need to allow a rich-client program (in C#) to
pass some data from local DB (MSDE) to the remote MS-SQL server, via WEB
SERVICE. (~1000 record each time) Since I guess there is no way to control
the transaction in the client side, my idea is to pass the entire data set
from local DB to the web service, and then the web service insert the data
to remote DB (within one transaction).

Now, I wonder if there is any better way for the web service to inject the
data (already passed in as DataSet object) into the SQL server? The most
trivial way would be to pase each DataRow, and trigger the insert SP/SQL one
by one, yet I wonder if there is any slightly better way to do that?

Thanks.

have a nice day
walty.

Previous Posts In This Thread:

On Thursday, August 28, 2008 11:31 AM
walty wrote:

Bulk Data Migration from DB to DB via Web Service
Hi,

As requested by customer, I need to allow a rich-client program (in C#) to
pass some data from local DB (MSDE) to the remote MS-SQL server, via WEB
SERVICE. (~1000 record each time) Since I guess there is no way to control
the transaction in the client side, my idea is to pass the entire data set
from local DB to the web service, and then the web service insert the data
to remote DB (within one transaction).

Now, I wonder if there is any better way for the web service to inject the
data (already passed in as DataSet object) into the SQL server? The most
trivial way would be to pase each DataRow, and trigger the insert SP/SQL one
by one, yet I wonder if there is any slightly better way to do that?

Thanks.

have a nice day
walty.

On Thursday, August 28, 2008 10:55 PM
changli wrote:

Hi Walty,Thank you for using Microsoft Managed TechNet Newsgroup.
Hi Walty,
Thank you for using Microsoft Managed TechNet Newsgroup. This is Charles and I am very glad to work with
you on this issue.

It seems that you were asking an ADO.NET question. You wanted to bulk insert the data in your dataset to your
target database in your ASP.NET web service, right? If so, you can use SqlDataAdapter.Update. I recommend
that you refer to this article which shows a good sample for your requirement:
Consuming a DataSet from an XML Web Service (ADO.NET)
http://msdn.microsoft.com/en-us/library/s5xy331f.aspx

If you want to know more of SqlDataAdapter, please refer to the following articles:
Updating Data Sources with DataAdapters (ADO.NET)
http://msdn.microsoft.com/en-us/library/33y2221y.aspx
Performing Batch Operations Using DataAdapters (ADO.NET)
http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

Hope this helps. If you have any other questions or concerns, please feel free to let me know.

Best regards,
Charles Wang

Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

On Friday, August 29, 2008 8:15 AM
walty wrote:

Hi Charles,Thanks for your info.But as I checked with the .
Hi Charles,

Thanks for your info.

But as I checked with the .NET SDK on DataAdapter, it seems the the batch
update using DataAdatper as described in

http://msdn.microsoft.com/en-us/library/s5xy331f.aspx
and
http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx

require the source and destination to be the same DataSet of the same DB (as
it uses DataRowState to check if insert/update/delete is required). Also it
basically triggers inidividual operations for each insert/update/delete, so
there should be not much difference in performance.

I guess I should just use the primitive way.

But thanks anyway.


have a nice day
walty





""Charles Wang [MSFT]"" wrote in message



Submitted via EggHeadCafe - Software Developer Portal of Choice
WPF Binding Beyond the Limitation of Name Scopes
http://www.eggheadcafe.com/tutorials/aspnet/ef583104-e507-491d-b05f-49...c8854c8
 >> Stay informed about: Bulk Data Migration from DB to DB via Web Service 

> Now, I wonder if there is any better way for the web service to inject the
> data (already passed in as DataSet object) into the SQL server? The most
> trivial way would be to pase each DataRow, and trigger the insert SP/SQL
> one
> by one, yet I wonder if there is any slightly better way to do that?

The most efficient way the SqlBulkCopy class. One of the WriteToServer
method overloads takes a DataTable parameter so it very easy to use for this
purpose.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Jasper Septimus" wrote in message
...
> Hi Walty,
>
> I also need to move data from a remote database via web service. Can you
> please through me some idea. If possible can you send me sample code. Your
> help is very much appreciated.
>
> Thank you,
> Jasper
>
>
>
> walty wrote:
>
> Bulk Data Migration from DB to DB via Web Service
> 28-Aug-08
>
> Hi,
>
> As requested by customer, I need to allow a rich-client program (in C#) to
> pass some data from local DB (MSDE) to the remote MS-SQL server, via WEB
> SERVICE. (~1000 record each time) Since I guess there is no way to control
> the transaction in the client side, my idea is to pass the entire data set
> from local DB to the web service, and then the web service insert the data
> to remote DB (within one transaction).
>
> Now, I wonder if there is any better way for the web service to inject the
> data (already passed in as DataSet object) into the SQL server? The most
> trivial way would be to pase each DataRow, and trigger the insert SP/SQL
> one
> by one, yet I wonder if there is any slightly better way to do that?
>
> Thanks.
>
> have a nice day
> walty.
>
> Previous Posts In This Thread:
>
> On Thursday, August 28, 2008 11:31 AM
> walty wrote:
>
> Bulk Data Migration from DB to DB via Web Service
> Hi,
>
> As requested by customer, I need to allow a rich-client program (in C#) to
> pass some data from local DB (MSDE) to the remote MS-SQL server, via WEB
> SERVICE. (~1000 record each time) Since I guess there is no way to control
> the transaction in the client side, my idea is to pass the entire data set
> from local DB to the web service, and then the web service insert the data
> to remote DB (within one transaction).
>
> Now, I wonder if there is any better way for the web service to inject the
> data (already passed in as DataSet object) into the SQL server? The most
> trivial way would be to pase each DataRow, and trigger the insert SP/SQL
> one
> by one, yet I wonder if there is any slightly better way to do that?
>
> Thanks.
>
> have a nice day
> walty.
>
> On Thursday, August 28, 2008 10:55 PM
> changli wrote:
>
> Hi Walty,Thank you for using Microsoft Managed TechNet Newsgroup.
> Hi Walty,
> Thank you for using Microsoft Managed TechNet Newsgroup. This is Charles
> and I am very glad to work with
> you on this issue.
>
> It seems that you were asking an ADO.NET question. You wanted to bulk
> insert the data in your dataset to your
> target database in your ASP.NET web service, right? If so, you can use
> SqlDataAdapter.Update. I recommend
> that you refer to this article which shows a good sample for your
> requirement:
> Consuming a DataSet from an XML Web Service (ADO.NET)
> http://msdn.microsoft.com/en-us/library/s5xy331f.aspx
>
> If you want to know more of SqlDataAdapter, please refer to the following
> articles:
> Updating Data Sources with DataAdapters (ADO.NET)
> http://msdn.microsoft.com/en-us/library/33y2221y.aspx
> Performing Batch Operations Using DataAdapters (ADO.NET)
> http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx
>
> Hope this helps. If you have any other questions or concerns, please feel
> free to let me know.
>
> Best regards,
> Charles Wang
>
> Microsoft Online Community Support
> Get Secure! - www.microsoft.com/security
> ====================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ====================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ====================================================
>
> On Friday, August 29, 2008 8:15 AM
> walty wrote:
>
> Hi Charles,Thanks for your info.But as I checked with the .
> Hi Charles,
>
> Thanks for your info.
>
> But as I checked with the .NET SDK on DataAdapter, it seems the the batch
> update using DataAdatper as described in
>
> http://msdn.microsoft.com/en-us/library/s5xy331f.aspx
> and
> http://msdn.microsoft.com/en-us/library/aadf8fk2.aspx
>
> require the source and destination to be the same DataSet of the same DB
> (as
> it uses DataRowState to check if insert/update/delete is required). Also
> it
> basically triggers inidividual operations for each insert/update/delete,
> so
> there should be not much difference in performance.
>
> I guess I should just use the primitive way.
>
> But thanks anyway.
>
>
> have a nice day
> walty
>
>
>
>
>
> ""Charles Wang [MSFT]"" wrote in message
>
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> WPF Binding Beyond the Limitation of Name Scopes
> http://www.eggheadcafe.com/tutorials/aspnet/ef583104-e507-491d-b05f-49...c8854c8
 >> Stay informed about: Bulk Data Migration from DB to DB via Web Service